# Passing tuples (key, value) into parameterized SQL query in Python

NOTE: I am new to this community, so please let me know if I can help you help me!

I built a scraper in python that creates a tuple of (key, value).

I would like to pass both key and value as parameterized variables in an SQL query that updates a table in my sqlite database.

I have exhausted researching online and trial and error and hope you guys can help. Is this even possible?

Example of what I want to achieve:

    cur.execute(''' UPDATE Ads SET ?=?''', (tup.keys(),tup.values()))


Columns in my SQLite3 database (identical to tuple.keys() ):

Marke, Kilometerstand, Erstzulassung, Kraftstoffart, Leistung (PS), Getriebe

Example of my tuple:

{'Marke': 'Weitere Automarken', 'Kilometerstand': '88.888 km', 'Erstzulassung': 'Juli 1963', 'Kraftstoffart': 'Diesel', 'Leistung (PS)': '69 PS', 'Getriebe': 'Manuell'}

Generally speaking, you need a string to be executed that contains placeholders to accept the values contained within the tuple. Also, what you are calling your "tuple" is actually a Python dictionary, but that isn't a problem.

From the sqlite3 documentation, we can see there are two ways to correctly use the execute() method:

an example command is given as:

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})


And the second example, the named style is exactly what would work with your situation. You can try the following, using the keys of the dictionary as the named placeholders:

cmd = """insert into YOUR_TABLE values Marke=:Marke, Kilometerstand=:Kilometerstand, Erstzulassung=:Erstzulassung, Kraftstoffart=:Kraftstoffart, "Leistung (PS)"=:"Leistung (PS)", Getriebe=:Getriebe"""
cur.execute(cmd, your_dictionary)


So each of the keys of your dictionary matches the name of the corresponding column in the target table.

Notice that I put the entire cmd in triple " to make a string literal that can itself contain the " character. I think it will be required, because you have a column name in your table that contains a space: Leistung (PS).

• Thanks for responding to my question! Unfortunately I wasn't able to get it to work despite trying exactly what you added above and a lot of trial and errros around that. I am going to go with the simpler solution of repeating sql commands for each possible variable (i.e. Marke, Kilometerstand, etc.). Just out of curiosity (and perhaps a stupid question) did this work for you when you tried this? Feb 18 '20 at 13:32
• Yes, I have inserted things before en-masse in this way before. Actually I think I have also put all my data into a pandas DataFrame then used its to_sql method. Feb 18 '20 at 15:07