Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I reference columns by their names in python calling SQLite?

Tags:

python

sqlite

I have some code which I've been using to query MySQL, and I'm hoping to use it with SQLite. My real hope is that this will not involve making too many changes to the code. Unfortunately, the following code doesn't work with SQLite:

cursor.execute(query)

rows = cursor.fetchall()

data = []
for row in rows
  data.append(row["column_name"])

This gives the following error:

 TypeError: tuple indices must be integers

Whereas if I change the reference to use a column number, it works fine:

  data.append(row[1])

Can I execute the query in such a way that I can reference columns by their names?

like image 582
Ben Avatar asked Feb 23 '09 08:02

Ben


People also ask

How do I get the column names in a python database?

Approach: Connect to a database using the connect() method. Create a cursor object and use that cursor object created to execute queries in order to create a table and insert values into it. Use the description keyword of the cursor object to get the column names.

How do you reference a column in a table in Python?

You can use the loc and iloc functions to access columns in a Pandas DataFrame. Let's see how. If we wanted to access a certain column in our DataFrame, for example the Grades column, we could simply use the loc function and specify the name of the column in order to retrieve it.


2 Answers

In the five years since the question was asked and then answered, a very simple solution has arisen. Any new code can simply wrap the connection object with a row factory. Code example:

import sqlite3

conn = sqlite3.connect('./someFile')
conn.row_factory = sqlite3.Row        // Here's the magic!

cursor = conn.execute("SELECT name, age FROM someTable")
for row in cursor:
    print(row['name'])

Here are some fine docs. Enjoy!

like image 141
dotancohen Avatar answered Oct 07 '22 23:10

dotancohen


To access columns by name, use the row_factory attribute of the Connection instance. It lets you set a function that takes the arguments cursor and row, and return whatever you'd like. There's a few builtin to pysqlite, namely sqlite3.Row, which does what you've asked.

like image 22
Richard Levasseur Avatar answered Oct 08 '22 00:10

Richard Levasseur