Python Forum
iterate through query result
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
iterate through query result
#1
I am trying to take data from a database and put it in an excel spreadsheet.

##################################
with connection.cursor() as cursor:
        # Select a record
        sql = "SELECT DISTINCT JobID FROM tblDTTicket WHERE Ticket_Date = '2020-03-24'"

        cursor.execute(sql)
        jobs = cursor.fetchall()
        joblist.append(cursor.execute(sql))

    for job in jobs:
        print(job)

finally:
    connection.close()
#####################

EXAMPLE OF OUTPUT
Output:
{'JobID': 'STOCK7'} {'JobID': 'STOCK2'} {'JobID': 'STOCK5'}
I keep trying to assign the data to a cell, I am using openpyxl and trying to pass even one of these three pieces of data to a cell.

I have tried:

ws['A1']=job[0]
ws['A1']=joblist[0] #After appending an empty joblist[]

I could use a point in the right direction...
Reply
#2
I have tried something different:

print(jobs[14]) #### I just chose 14 as a point in the list. My output is :

('1918',)

HOW can I strip out the parenthesis, the single quotation marks, and the one comma...???

I tried using

ws['A1']=jobs[14] and I get an error. I tried using a fetchone() and it allowed me to insert a single data into the spreadsheet, but I could not figure out how to advance the next data with fetchone().
Reply
#3
You cannot strip that stuff out because it isn't there. kpbs[14] returns a tuple, and this is how a tuple prints. If you want pretty formatting you should look at using "format". If you are fine with just 1918, you can get the string from the tuple and print that.

Do you think there's a chance your other problems are also caused by the fact that you are using a tuple instead of a string?
Reply
#4
before I came back to this page, I was trying something else and an error mentioned tuple. What you are telling me seems to match. Now I am trying to figure out how to step through each tuple element and put each in a cell in an EXCEL spreadsheet...

Is there something that I need to do to allow me to pass the tuple to the spreadsheet cell, like some form of data conversion?
Reply
#5
No idea about adding tuples to a spreadsheet. The help text for whatever spreadsheet package you are using will provide that information. You mentioned your code worked when you used fetchone(). How does the fetchone() return value differ from the fetchall() return value? Is there a way you can process the fetchall() return value to get what you want?

Why do you think fetchone() and fetchall() return different data types? There has to be a reason. When you understand that reason I think you will no longer have a problem adding values to the spreadsheet.
Reply
#6
You are right, I'm not at my coding machine, I just saw a question above mine that asks about "named tuples" and talks about accessing specific fields. I read through that... it looks that may be my answer... I wasn't thinking about my information being a tuple, nor did I think in terms of "named tuple"... I keep thinking in terms of query and results.

I'm thinking I may have to rethink my terminology that I am used to in terms of approach to python. fetchone gives a single piece of data. fetchall gives everything that meets my criteria. I guess it sticks it a tuple format. I don't want to change the data so a tuple will be fine. For lack of a better word, I want to copy and paste it into excel or libreoffice calc.
Reply
#7
I am floundering for a moment. I have figured out that I have a "dictionary" inside of a "tuple". If I call out

print(jobs[0])

I return:

{'JobID': 'STOCK7'}

How is this named so that I know what to call it because I want the STOCK7 part?
Reply
#8
If you know the key, you can ask the dictionary for the value:

jobs[0]['Job ID']

If you don't know the key and you only want the value you can ask the dictionary for the key/value pairs it contains. Read the help for dict.items(). Guess what data type dict.items uses to return the key/value pair.

If you only care about the keys you can use dict.keys(). If you only care about the values in the dictionary you can use dict.values(). Reading up about dictionaries, tuples and lists is time well spent if you want to program in Python.
Reply
#9
It's not clear what DB you use and what package/connector is used to connect. Also, after you initial code it's not clear if you do any changes.
generally you may control what is returned by when you query the database -tuple, dict, namedtuple...
e.g. for MySQL connector https://dev.mysql.com/doc/connector-pyth...ursor.html

of course, the option to simply work with dict.values() is also possible as suggested by @deanhystad
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#10
I was using pymysql.
I have changed to MySql.connector.

My luck has changed. I am managing to iterate through the for loop and just get the answers now. Without the tuple configuration.

I can get the target cell to be assigned the data that I am iterating though. The only problem is because i don't know how to step to the "next" cell down, whatever the last piece of data in the loop is the data that is saved in the cell. I'm getting there.. i'm just slow with it...

I really appreciate all of the help. Sometimes what you guys say points me in a direction that gives me a pointer in the right direction.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Write SQLite query result to file hjk6734 1 1,893 May-27-2020, 12:17 PM
Last Post: menator01
  python code (embedded in Redshift) to return result of the query Mel 0 2,409 Aug-24-2018, 06:12 PM
Last Post: Mel

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020