CODEWITHSUNDEEP
pythonsqlitepython-3.x
GIS-Jonathan
GIS-Jonathan

Reputation: 4647

Inserting to sqlite dynamically with Python 3

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

Upvotes: 2

Views: 2367

Answers (1)

KorreyD
KorreyD

Reputation: 1294

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')]

Upvotes: 5

Related Questions