Inserting to sqlite dynamically with Python 3

Question

I want to write to multiple tables with sqlite, but I don't want to manually specify the query ahead of time (there are dozens of possible permutations).

So for example:

def insert_sqlite(tablename, data_list)
    global dbc
    dbc.execute("insert into " + tablename + " values (?)", data_list)


tables_and_data = {
                   'numbers_table': [1,2,3,4,5],
                   'text_table': ["pies","cakes"]
                   }

for key in tables_and_data:
    insert_sqlite(key, tables_and_data[key])

I want two things to happen:

a) for the tablename to be set dynamically - I've not found a single example where this is done.

b) The data_list values to be correctly used - note that the length of the list varies (as per the example).

But the above doesn't work - How do I dynamically create a sqlite3.execute statement?

Thanks

KorreyD · Accepted Answer · 2013-12-26 18:37:54Z

5

a) Your code above seems to be setting the table name correctly so no problem there

b) You need a ?(placeholder) per column you wish to insert a value for.

when i recreate your code as is and run it i get the error message: "sqlite3.OperationalError: table numbers_table has 5 columns but 1 values were supplied".

A solution would be to edit your function to dynamically create the correct number of placeholders:

def insert_sqlite(tablename, data_list):
    global dbc
    dbc.execute("insert into " + tablename + " values (" + ('?,' * len(data_list))[:-1] + ")", data_list)

after doing this and then re-executing the code with an added select statements (just to test it out):

dbc.execute("""
select * from numbers_table
""")
print(dbc.fetchall());

dbc.execute("""
select * from text_table
""")
print(dbc.fetchall());

I get the result:

[(1, 2, 3, 4, 5)]
[(u'pies', u'cakes')]

edited Dec 26, 2013 at 18:37

answered Dec 26, 2013 at 18:11

KorreyD

1,2948 silver badges15 bronze badges

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

2 Comments

Donal Fellows Over a year ago

Decent analysis, and you tested your suggestion too. Good answer!

GIS-Jonathan Over a year ago

Thanks. I had tried that method of tablename placing but it wasn't working. I've since discovered I had a bad table name. Anyway, I just dropped it in and it worked perfectly. Many thanks. And welcome to SO! :-)

Collectives™ on Stack Overflow

Inserting to sqlite dynamically with Python 3

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related