I've written some code in which I use a dictcursor to get query data. I then wanted to get both the headers and the data from the query, like this:
for field, value in result[rownum].items():
blah blah blah
My problem is that the columns don't come out in the same order as they exist in the actual table. Why is this? Is there any way for me to get the column names and table data from a query, and get it in the right order?
Thanks,
Andy
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The whole idea of using a dictcursor is to make access to the
resulting data easier and less error-prone. You're asking a
dictionary for its keys and values, and they come out in
whatever order the dictionary implementation decides.
Assuming you know the order you want them to come out
in, just walk the dict in that order, e.g.:
for row in result:
for key in "field1 field2 field3 field4".split():
blah_blah_blah_with(row[key])
blah blah blah some more
If you don't know the field order, there's probably some way
to ask the cursor, though I suspect you can always just walk
the results of "describe tablename":
>>> c.execute("describe urls")
>>> for row in c.fetchall():
... print row["Field"]
...
url
description
refid
tabletype
category
mime_type
Skip
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
My goal is to be able to draw a gtk treeview based on an arbitrary query. If I only wanted to show full tables, I could use your method ("describe table"), but I may have to output views based on show, describe, or select queries. You said there might be a way to get the cursor to return the column names in order, but I couldn't find any such function in the cursors.py file. Do you know how to do this? Any suggestions would be great.
Thanks again,
Andy
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Just extend what Skip gave you for grabbing field names off of a table...
c.execute("DESCRIBE some_table")
field_names = [x["Field"] for x in c.fetchall()]
Then do one of the following
a) construct query using the column names and use a non-dict cursor to run the query. Everything should be in the "correct" order after this is done..
non_dict_c.execute("SELECT " + string.join(field_names, ", ") + " FROM some_table")
b) use the dict cursor and then retrieve based on the field names. Again, everything should be in the "correct" order after this is done.
c.execute("SELECT * FROM some_table")
result = []
for x in c.fetchall():
result.append([x[y] for y in field_names])
Obviously, the first technique is far more efficient and more straightforward anyway. It is a little strange to ask for the data as a dictionary when you want everything array based anyway. dict cursors probably shouldn't be used for what you are doing.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've written some code in which I use a dictcursor to get query data. I then wanted to get both the headers and the data from the query, like this:
for field, value in result[rownum].items():
blah blah blah
My problem is that the columns don't come out in the same order as they exist in the actual table. Why is this? Is there any way for me to get the column names and table data from a query, and get it in the right order?
Thanks,
Andy
The whole idea of using a dictcursor is to make access to the
resulting data easier and less error-prone. You're asking a
dictionary for its keys and values, and they come out in
whatever order the dictionary implementation decides.
Assuming you know the order you want them to come out
in, just walk the dict in that order, e.g.:
for row in result:
for key in "field1 field2 field3 field4".split():
blah_blah_blah_with(row[key])
blah blah blah some more
If you don't know the field order, there's probably some way
to ask the cursor, though I suspect you can always just walk
the results of "describe tablename":
>>> c.execute("describe urls")
>>> for row in c.fetchall():
... print row["Field"]
...
url
description
refid
tabletype
category
mime_type
Skip
My goal is to be able to draw a gtk treeview based on an arbitrary query. If I only wanted to show full tables, I could use your method ("describe table"), but I may have to output views based on show, describe, or select queries. You said there might be a way to get the cursor to return the column names in order, but I couldn't find any such function in the cursors.py file. Do you know how to do this? Any suggestions would be great.
Thanks again,
Andy
Just extend what Skip gave you for grabbing field names off of a table...
c.execute("DESCRIBE some_table")
field_names = [x["Field"] for x in c.fetchall()]
Then do one of the following
a) construct query using the column names and use a non-dict cursor to run the query. Everything should be in the "correct" order after this is done..
non_dict_c.execute("SELECT " + string.join(field_names, ", ") + " FROM some_table")
b) use the dict cursor and then retrieve based on the field names. Again, everything should be in the "correct" order after this is done.
c.execute("SELECT * FROM some_table")
result = []
for x in c.fetchall():
result.append([x[y] for y in field_names])
Obviously, the first technique is far more efficient and more straightforward anyway. It is a little strange to ask for the data as a dictionary when you want everything array based anyway. dict cursors probably shouldn't be used for what you are doing.