Data Science Asked by WackyTaco636 on December 6, 2020
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)
.
Answered by n1k31t4 on December 6, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP