How to script database inserts with python?

Question

Using old example:

 csr = con.cursor()
 csr.execute('Truncate table test.data')
 csr.executemany('INSERT INTO test.data VALUES (?,?,?,?)', Newdata)
 con.commit()

How would I insert %s into

csr.executemany('INSERT INTO test.data VALUES (?,?,?,?)', Newdata)

tried:

csr.executemany('INSERT INTO test.%s VALUES (?,?,?,?)', Newdata) % symbol

Answer 1

Should be:

csr.executemany('INSERT INTO test.%s VALUES (?,?,?,?)' % symbol, Newdata)

Although I would instead do something like:

query = 'INSERT INTO test.%s VALUES (?,?,?,?)' % symbol
csr.executemany(query, Newdata)

Note that ? is not the default placeholder on all database wrappers. In some %s is used instead (mysqldb, psycopg2 for instance). In these cases, It's sometimes simpler to build the query in parts.

Doing:

query = 'INSERT INTO test.%s' % symbol
query += ' VALUES (%s,%s,%s,%s)'

otherwise you would need something like:

query = 'INSERT INTO test.%s VALUES (%%s,%%s,%%s,%%s)' % symbol

which is a pain.