SQLite – How to use VARIABLE for TABLE NAME + VARIABLE FOR INSERT VALUES

im new in programing, starting with python

I found this script in a post (link below)

query = 'SELECT * FROM {}'.format(table) 
c.execute(query)

How to use variable for SQLite table name

it worked, but now i have to add some complexity and dont know how…

i had this befour:

def add(order, theme):

    parameters = [order, theme]    
    c.execute("INSERT INTO TABLE_NAME VALUES(NULL, ?,?)", parameters)

so im trying have the same, but with de name of the table free to choose. trying things like this, but dont really know the syntax for it:

def add(order, theme):
    table = input("with what table do you want to work with? ")
    parameters = [order, theme] 
    insert = 'INSERT INTO {} VALUES(NULL, ?,?)'.format(table, parameters)
    c.execute(insert)

i suspected it woudnt work Hope you can help! 🙂

Answer

The following line substitutes {} with the contents of the variable table and stores the result in the variable query. format works with any string:

query = 'SELECT * FROM {}'.format(table) 

On the other hand the kind of substitution done by c.execute replaces the ? in the string with the values in the list parameters:

c.execute("INSERT INTO TABLE_NAME VALUES(NULL, ?,?)", parameters)

You could combine them both to achieve the effect you want:

table = input("with what table do you want to work with? ")
query = 'INSERT INTO {} VALUES(NULL, ?, ?)'.format(table)
parameters = [order, theme]
c.execute(query, parameters)