Insert python variables into SQLITE DB from another cursor object

Question

Hey All,

Using an example from the Python DOCs:

stocks = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]: 
for t in stocks
    c.execute('insert into stocks values (?,?,?,?,?)', t)

In my code, the stocks from above is generated from a query to another DB.

Since tuples are immutable, how do you pass additional values to the cursor execute statement (in addition to the tuple).

Is there a better solution then the example below?:

stocks = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]: 
for t in stocks
    t = list(t)
    t.append('Some Arb Value')
    t = tuple(t)
    c.execute('insert into stocks values (?,?,?,?,?,?)', t)

You could also do this:

stocks = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]: 
for t in stocks
    c.execute('insert into stocks values (?,?,?,?,?,?)', (t[0],t[1],t[2],t[3],t[4],'some value')

However, the solutions above wont work for the executemany method i.e

c.executemany('insert into stocks values (?,?,?,?,?,?)', t)

Is there a better way of doing this?

Answer 1

Tuples are immutable, but you can easily extract their contents and form new tuples. Also, I'm not sure, but I don't think the execute() call absolutely must have a tuple. Can't any sequence, including lists, work as well?

Anyway, here's what you need:

for t in stocks:
    c.execute('insert into stock values (?,?,?,?,?,?)', t + ('some value',))

That adds a one-element tuple to the existing one, forming a new six-element tuple.

Answer 2

I assume you meant to use stocks instead of t for the executemany version For the executemany version, you can also do

c.executemany('insert into stocks (?,?,?,?,?,?)', (t + ('Arb value',) for t in stocks))

Using a generator expression instead of a list comprehension will keep you from creating an entire new data structure, which, if you have many inputs, is essential.