Search code examples
pythonsqlnumpysqliteexecutemany

python sqlite3 executemany using multiple lists

Background:
So I have a large array that I am reading from one source and trying to write (efficiently) into SQLite3 using python.

Currently I use the default form:

cursor.executemany("INSERT into mytable1 VALUES(?,?,?)", my_arr.tolist())

Now I want to expand to a few hundred thousand tables. I would like to be able to do something like the following (wish):

cursor.executemany("INSERT into ? VALUES(?,?,?)", TableNameList, my_arr.tolist())

Questions:

  • Is there a way to do this without inserting columns into the array before converting it to list? What?
  • If there is not such a way, then suggestions and alternatives are requested.

I tried looking in stackexchange, but may have missed something.
I tried looking in the Python SQLite docs, but did not see something like this. I tried generic google search.

Solution

  • First, the Python bit. Assuming that my_arr is some sort of two-dimensional array, and that .tolist() produces a list-of-lists, Yes, there is a way to add an element to every row in your list:

    result = [[a]+b for a,b in zip(TableNameList, my_arr.tolist()]

    Second, the SQL bit. No, you can't use ? to specify a table name. The table name must be literally present in the SQL statement. The best that I can offer you is to run curssor.execute several times:

    for table, values in zip(TableNameList, my_arr):
    c.execute("INSERT INTO %s VALUES (?, ?, ?)"%table, values)

    But, be mindful of whether you trust the source of TableNameList. Using untrusted data in a %s leads to SQL injection security flaws.

    Sample program:

    import sqlite3
import numpy as np
import itertools

my_arr = np.array([[1,2,3],[4,5,6],[7,8,9],[10,11,12]])
TableNameList = 't1', 't1', 't2', 't3'

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

c.execute('''CREATE TABLE t1 (c1, c2, c3)''')
c.execute('''CREATE TABLE t2 (c1, c2, c3)''')
c.execute('''CREATE TABLE t3 (c1, c2, c3)''')

## Insert a row of data
#c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

for table, values in itertools.izip(TableNameList, my_arr):
    c.execute("INSERT INTO %s VALUES (?, ?, ?)"%table, values)

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()