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

Question

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! :)

score 0 · Answer 1 · edited Jul 19 '20 at 05:06

You are mixing things.

Format is a method of Python string object used to generate dynamically your string based on variables from your code. In your first example is correct to define the name of the database.

Questions marks as a placeholder for the values of your query is proper of SQL and must be used without format as you did in the second example.

To fix your code, first build the string template of the query using format to define the databasename. Then, execute the resultant string with execute and passing the arguments targeting the question marks.

score 0 · Accepted Answer · answered Jul 19 '20 at 05:22

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)

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

2 Answers2