|
Rows
Row API documentation
Row objects are returned from Cursor fetch functions. As specified in the DB API, they are tuple-like. row = cursor.fetchone() print row[0] However, there are some pyodbc additions that make them very convenient:
Accessing rows by column name is very convenient, readable, and Pythonish: cursor.execute("select album_id, photo_id from photos where user_id=1")
row = cursor.fetchone()
print row.album_id, row.photo_id
print row[0], row[1] # same as above, but less readableHaving access to the cursor's description even after the Cursor is closed makes Rows very convenient data structures -- you can pass them around and they are self describing: def getuser(userid):
cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()
return cursor.execute("""
select album_id, photo_id
from photos
where user_id = ?
""", userid).fetchall()
row = getuser(7)
# At this point the cursor has been closed and deleted
# But the columns and datatypes can still be access:
print 'columns:', ', '.join(t[0] for t in row.cursor_description)Unlike normal tuples, values in Row objects can be replaced. (This means you shouldn't use rows as dictionary keys!) The intention is to make Rows convenient data structures to replace small or short-lived classes. While SQL is powerful, there are sometimes slight changes that need to be made after reading values: # Replace the 'start_date' datetime in each row with one that has a time zone.
rows = cursor.fetchall()
for row in rows:
row.start_date = row.start_date.astimezone(tz)Note that slicing rows returns tuples, not Row objects! variablescursor_descriptionA copy of the Cursor.description object from the Cursor that created this row. This contains the column names and data types of the columns. See Cursor.description |
I was just curious if there is something special that needs to be done to the cursor or the row object in order to process the row using the column names instead of list indices.
Kevin
Hi Kevin,
Nothing special needed to enable this; it just works!
If you have trouble accessing the data via column name, try printing out the description like this:
x = 0 # column index for row in rows: print row.cursor_description[x][0]That will return column name at index 0.
It may just be that the database is returning an uppercase column name, and you're expecting a lowercase column name (this happened to me).
Best regards,
Adam
What must I do to serialize a row object using the json module? Just passing it to json.dumps() doesn't seem to work.
Thanks, Ralph
Ralph, I think you can just wrap them in a tuple() call before passing to json.dumps.
Is there anyway to return a column name that has a space in it? Here's an example query
Obviously you can't do this:
It would be helpful (not only because of this example) if you could also access the column values using the column names as the array keys:
Jake, this should work: row.__getattribute__('My Value')
Or this: getattr(row, 'My Value')
What must I do to serialize a row object using the json module?
To serialize, you can convert each row to an object first, like so:
obj = { t[0]: value for (t, value) in zip(row.cursor_description, row) }The cursor_description variable is simply the same tuple returned by Cursor.description. The first entry (0) in each is the column name.
Note that this assumes your column names are reasonable Javascript names, of course.
How can know the number of the columns returned in a row?
len(row.cursor_description) should work.
http://anekajaringmurah.blogspot.com/ http://pusatjaringmurah.blogspot.com/ http://anekajaringpengaman.blogspot.com/ http://agenjaringpengaman.blogspot.com/ http://jaringpengamanfutsal.blogspot.com/ http://jaring-pengamanmurah.blogspot.com/ http://jaringcenter.blogspot.com/ http://agenjaringjakata.blogspot.com/ http://jualjaringpengamanmurah.blogspot.com/ http://jaringsafetyjakarta.blogspot.com/ http://jaringpengaman-murah.blogspot.com/ http://jaringmurah.blogspot.com/ http://jaring-murah.blogspot.com/ http://jaringpengamanmurah.blogspot.com/ http://jaringbangunan.blogspot.com/ http://agenjaringsafety.blogspot.com/ http://sentral-jaring.blogspot.com/ http://sentraljaring.blogspot.com/ http://tokojaringpengaman.blogspot.com/ http://pusatjaringjakarta.blogspot.com/ http://tokojaringpengamanmurah.blogspot.com/ http://jualjaringsafetymurah.blogspot.com/ https://pancasamudera.wordpress.com/ https://pasangjaringfutsal.wordpress.com/ https://jualtambangmurah.wordpress.com/ https://tokojaring.wordpress.com/ https://jualjaringfutsal.wordpress.com/ https://jaringfutsal.wordpress.com/
Hi, how to use row variable while inserting to another table. EX: data_dump=cur.fetchall() #this is getting some data from a table how to insert it into another table like: for row in data_dump:
same question: http://stackoverflow.com/questions/30398664/how-to-select-data-from-mysql-table-and-insert-into-another-using-python