Insert python variables into SQLITE DB from another cursor object

Question

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?

Peter Hansen · Accepted Answer · 2009-12-08 13:41:55Z

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.

answered Dec 8, 2009 at 13:41

Peter Hansen

22.3k6 gold badges57 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jcdyer · Accepted Answer · 2009-12-08 14:04:58Z

0

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.

answered Dec 8, 2009 at 14:04

jcdyer

19.3k6 gold badges45 silver badges49 bronze badges

Collectives™ on Stack Overflow

Insert python variables into SQLITE DB from another cursor object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related