How to loop thorough rows of each column in a table?

I am trying to write a small program to transform a database. It is an SQLite database, and I am using Python. I am experiencing a problem where the query I’m writing doesn’t seem to work properly:

def transform():
con = sqlite3.connect('database.db')
cur = con.cursor()
cur2 = con.cursor()
data = cur.execute('''SELECT * FROM table_1''')
columns = []
for column in data.description:
    columns.append(column[0])
for column2 in columns:
    cur2.execute('''SELECT ? FROM table_1''', (column2, ))
    content = cur2.fetchall()
    print(content)

For some reason, what’s printed here is just a list on the column name repeated a number of times. If I replace the ? with the column name literally copied from the output of the columns variable however, it works, and prints every row in the column.

What am I doing wrong?

Answer

You can’t let the connector do substitution for table names and column names. What happens is that the connector produces SQL like:

    SELECT 'column' FROM table_1;

and what you get back is the literal string 'column'. You need

    curr2.execute(f"SELECT {column2} FROM table_1;")

Advertisement

Answer