1
$\begingroup$

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

$\endgroup$
0
$\begingroup$

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

$\endgroup$
2
  • $\begingroup$ 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? $\endgroup$ Feb 18 '20 at 13:32
  • $\begingroup$ 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. $\endgroup$
    – n1k31t4
    Feb 18 '20 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.