Add columns to an SQLite table in a Python loop

Question

I make a database (using sqlite3) for a scientific program (Python). This program build makes loops which gradually build the database. So the problem is to add column with a loop. I made an example. It shows that the problem is the variable which defines the new column name (Name1).

import sqlite3
import os

conn=sqlite3.connect(':memory:')
c=conn.cursor()

c.execute('''CREATE TABLE TableName (Var1 REAL, Var2 REAL)''')

Name1='Test1'
c.execute('''ALTER TABLE TableName ADD COLUMN "+Name1+" INTEGER''')
Name1='Test2'
c.execute('''ALTER TABLE TableName ADD COLUMN "+Name1+" INTEGER''')

Does anyone have an advice to solve this problem please ? Thanks and have a nice day.

So what about this isn't working? If there is a traceback or any other error message, can you please edit your question to include it? — Eric Renouf
– Eric Renouf, Commented Jun 13, 2015 at 23:08

das-g · Accepted Answer · 2022-10-02 00:17:10Z

String literals must be terminated by the same sort of quoting that started them. Thus the "+Name1+" is interpreted as a literal part of your strings and passed on to c.execute(...) without the variable Name1 being inserted.

Thus both new columns would have the literal name +Name1+, which leads to the error message

sqlite3.OperationalError: duplicate column name: +Name1+

You probably wanted something like:

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.execute('''CREATE TABLE TableName (Var1 REAL, Var2 REAL)''')

name1 = 'Test1'
c.execute('''ALTER TABLE TableName ADD COLUMN ''' + name1 + ''' INTEGER''')
name1 = 'Test2'
c.execute('''ALTER TABLE TableName ADD COLUMN ''' + name1 + ''' INTEGER''')

Rewritten with a loop, that'd be

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.execute('''CREATE TABLE TableName (Var1 REAL, Var2 REAL)''')

for column_name in ['Test1', 'Test2']:
    c.execute('''ALTER TABLE TableName ADD COLUMN ''' + column_name + ''' INTEGER''')

To avoid the risk of SQL injections, you shouldn't do string concatenation or manipulation on the SQL query or command, though. Instead use the parameter substitution Python's database API already offers:

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.execute('''CREATE TABLE TableName (Var1 REAL, Var2 REAL)''')

for column_name in ['Test1', 'Test2']:
    c.execute('''ALTER TABLE TableName ADD COLUMN ? INTEGER''', (column_name,))

In fact, the API will make an explicit loop unnecessary:

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.execute('''CREATE TABLE TableName (Var1 REAL, Var2 REAL)''')

column_names = [('Test1',), ('Test2',)]
c.executemany('''ALTER TABLE TableName ADD COLUMN ? INTEGER''', column_names)

Using parameter substitution gives me an operational error. Also parameter substitution is designed for values, not identifiers like column names. In most RDBMS identifiers and values will have different quoting rules, so parameter substitution for identifiers won't work.
Hmm… I'm pretty sure it worked back when I posted this answer. Maybe sqlite got stricter in what can be a substitutable parameter? (And it probably makes sense to restrict that to actual values, even though for the usecase at hand, identifiers would be quite useful.)

Collectives™ on Stack Overflow

Add columns to an SQLite table in a Python loop

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related