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

Question

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'}

Answer 1

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).