Using a function to get column names Sqlite3

Question

I have a short question for one of my classes; it involves building a function in python that takes a sqlite database name and a table name as arguments, and returns the column names inside this table.

So far I have done the following:

#!/user/bin/env python

import sqlite3
import pprint
pp = pprint.PrettyPrinter()

def print_table_columns(database_name, table_name):
    conn = sqlite3.connect(database_name)
    c = conn.cursor()
    c.execute('SELECT sql FROM sqlite_master WHERE type=\'table\' AND name=\'table_name\'')
    print c.fetchall()
    conn.close()

Sadly, this code produces an empty list. I suspect that the SQL command inside the execute function does not take variables defined in Python, but I am not sure. Any help on this would be much appreciated.

score 2 · Answer 1 · answered Apr 05 '16 at 20:45

Sure, you need to parameterize the query:

c.execute("""
    SELECT 
        sql 
    FROM 
        sqlite_master 
    WHERE 
        type = 'table' AND 
        name = ?""", (table_name, ))

where ? is a placeholder that sqlite3 would fill with the query parameter (table_name here).

Using a function to get column names Sqlite3

1 Answers1