My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
Rows  
Row API documentation
Updated Jul 28, 2011 by mkleehammer

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:

  • Values can be accessed by column name.
  • The Cursor.description values can be accessed even after the cursor is closed.
  • Values can be replaced
  • Rows from the same select share memory.

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 readable

Having 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!

variables

cursor_description

A 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

Comment by millsks, Oct 23, 2009

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

Comment by adam.c.b...@gmail.com, Nov 11, 2009

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

Comment by ralmor...@gmail.com, Feb 14, 2010

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

Comment by pland...@gmail.com, May 18, 2010

Ralph, I think you can just wrap them in a tuple() call before passing to json.dumps.

Comment by jake.e.w...@gmail.com, Jun 20, 2010

Is there anyway to return a column name that has a space in it? Here's an example query

SELECT something AS 'My Value' FROM mytable WHERE whatever=1

Obviously you can't do this:

print row.My Value

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:

print row["My Value"]
Comment by sad.n...@gmail.com, Aug 25, 2010

Jake, this should work: row.__getattribute__('My Value')

Or this: getattr(row, 'My Value')

Comment by 343656...@qq.com, Dec 21, 2011

What must I do to serialize a row object using the json module?

Comment by project member mkleehammer, Dec 26, 2011

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.

Comment by separa...@gmail.com, Apr 22, 2013

How can know the number of the columns returned in a row?

Comment by pedram.n...@gmail.com, Jul 26, 2013

len(row.cursor_description) should work.

Comment by jephinda...@gmail.com, May 22, 2015

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:

query="""insert into table (x,y,z) values(# i need the values from row here) cur.execute(query,row)

Powered by Google Project Hosting