How do I select a column by its name? (Python & SQLite3)

Question

Currently I have the following code:

c.execute("SELECT * FROM table")
for row in c.fetchall():
    print row[0]
    print row[1]

However, I changed the structure of my table and now I have to change the index values to represent this change. Is there a way to get use column names instead?

Iguananaut · Accepted Answer · 2012-11-14 19:51:12Z

13

See Row Objects in the docs for the sqlite3 module. If you use the sqlite3.Row row_factory you'll get back an object that's slightly more powerful than the normal tuples. I imagine it has slightly higher overhead, hence not being the default behavior.

answered Nov 14, 2012 at 19:51

Iguananaut

23.7k6 gold badges54 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jeromej Over a year ago

No real higher overhead according to the doc: "Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db_row based solution." docs.python.org/3/library/…

Iguananaut Over a year ago

Yeah that makes sense--I haven't looked at the code but it probably just uses __getitem__ to map column names to their corresponding indices.

CL. · Accepted Answer · 2012-11-14 20:03:44Z

12

For this reason, it is recommended to always use explicit column names when doing a SELECT:

c.execute("SELECT color, fluffiness FROM table")
for row in c.fetchall():
    print row[0]         #  <-- is always guaranteed to be the color value
    print row[1]

answered Nov 14, 2012 at 20:03

CL.

182k18 gold badges241 silver badges282 bronze badges

1 Comment

Iguananaut Over a year ago

This is a good answer too. Or some combination thereof. Though it can be a pain of you're fetching all columns of a large-ish table.

Collectives™ on Stack Overflow

How do I select a column by its name? (Python & SQLite3)

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related