英文:
How to format UPDATE query referencing column names?
问题
我的函数迭代一个包含在表中的列名列表,并运行一个选择查询。然后,它遍历每条记录,并将字段值与清理后的版本进行比较。如果它们不同,我需要运行一个更新查询,将清理后的值写入该记录。我无法让更新查询工作。另一个类似的函数使用以下查询执行类似的操作:
dbcursor.execute('UPDATE alib set live = (?) WHERE rowid = (?);', (islive, row_to_process))
不同之处在于需要迭代列名。我已经了解到不能将变量作为字段名传递给SELECT语句,因此我动态构建了查询字符串。这个方法是有效的。但是更新查询就不同了:
query = f"UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);", (final_value, row_to_process)
我尝试了3种不同的更新查询变体,每种都会抛出不同的错误。要写入的值可以包含任意数量的\
、'
、"
、[
、]
、(
、)
和标点符号。第77行是dbcursor.execute(query)
:
query = f"UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);", (final_value, row_to_process)
('UPDATE alib SET artist = (?) WHERE rowid = (?);', ('8:58\\\\The Unthanks', 305091))
Traceback (most recent call last):
File "<string>", line 80, in <module>
File "<string>", line 77, in dedupe_fields
TypeError: execute() argument 1 must be str, not tuple
query = f"UPDATE alib SET {text_tag} = '{final_value}' WHERE rowid = {row_to_process};"
UPDATE alib SET recordinglocation = 'Ashwoods, Stockholm\\Electric Lady Studioss, Stockholm\\Emilie's, Stockholm\\Ingrid Studioss, Stockholm\\Judios Studioss, Stockholm\\Nichols Canyon Houses, Stockholm\\Ocean Way Studioss, Stockholm\\RAK Studios, London\\Studio De La Grande Armée, Paris\\The Villages, Stockholm\\Vox Studioss, Stockholm' WHERE rowid = 124082;
Traceback (most recent call last):
File "<string>", line 80, in <module>
File "<string>", line 77, in dedupe_fields
sqlite3.OperationalError: near "s": syntax error
query = f"UPDATE alib SET {text_tag} = {final_value} WHERE rowid = {row_to_process};"
UPDATE alib SET artist = 8:58\The Unthanks WHERE rowid = 305091;
Traceback (most recent call last):
File "<string>", line 80, in <module>
File "<string>", line 77, in dedupe_fields
sqlite3.OperationalError: near ":58": syntax error
英文:
My function iterates a list of column names contained in a table and runs a select query. It then iterates through each record and compares the field value with a cleansed version. If they differ I need to run an UPDATE query writing the cleansed value to that record. I cannot get the UPDATE query to work. Another function that does something similar using the following query:
dbcursor.execute('''UPDATE alib set live = (?) WHERE rowid = (?);''', (islive, row_to_process))
The difference is necessity of iterating column names. I've come to understand you cannot pass variables as field names to a SELECT statement, thus I've built the query string dynamically. That works. The update query is a different story:
for text_tag in text_tags:
dbcursor.execute('''CREATE INDEX IF NOT EXISTS dedupe_tag ON alib (?) WHERE (?) IS NOT NULL;''', (text_tag, text_tag))
print(f"- {text_tag}")
''' get list of matching records '''
''' as you cannot pass variables as field names to a SELECT statement build the query string dynamically then run it '''
query = f"SELECT rowid, {text_tag} FROM alib WHERE {text_tag} IS NOT NULL;"
dbcursor.execute(query)
''' now process each matching record '''
records = dbcursor.fetchall()
records_returned = len(records) > 0
if records_returned:
for record in records:
<SNIP>
if final_value != stored_value_sorted:
''' write out {final_value} to column {text_tag} for record = rowid '''
row_to_process = record[0]
query = SEE QUERIES BELOW THIS CODE SNIPPET
print(query) # temp code to see what query is generated
dbcursor.execute(query)
I attempted 3 variations of UPDATE query and each throws a different error. The value to be written can have any number of \
, '
, "
, [
, ]
, (
, )
and punctuation marks. Line 77 is dbcursor.execute(query)
:
query = f"UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);", (final_value, row_to_process)
('UPDATE alib SET artist = (?) WHERE rowid = (?);', ('8:58\\\\The Unthanks', 305091))
Traceback (most recent call last):
File "<string>", line 80, in <module>
File "<string>", line 77, in dedupe_fields
TypeError: execute() argument 1 must be str, not tuple
query = f"UPDATE alib SET {text_tag} = '{final_value}' WHERE rowid = {row_to_process};"
UPDATE alib SET recordinglocation = 'Ashwoods, Stockholm\\Electric Lady Studioss, Stockholm\\Emilie's, Stockholm\\Ingrid Studioss, Stockholm\\Judios Studioss, Stockholm\\Nichols Canyon Houses, Stockholm\\Ocean Way Studioss, Stockholm\\RAK Studios, London\\Studio De La Grande Armée, Paris\\The Villages, Stockholm\\Vox Studioss, Stockholm' WHERE rowid = 124082;
Traceback (most recent call last):
File "<string>", line 80, in <module>
File "<string>", line 77, in dedupe_fields
sqlite3.OperationalError: near "s": syntax error
query = f"UPDATE alib SET {text_tag} = {final_value} WHERE rowid = {row_to_process};"
UPDATE alib SET artist = 8:58\\The Unthanks WHERE rowid = 305091;
Traceback (most recent call last):
File "<string>", line 80, in <module>
File "<string>", line 77, in dedupe_fields
sqlite3.OperationalError: near ":58": syntax error
答案1
得分: 1
问题出在你定义的 query
变量上:
这是一个元组(tuple):
query = f"UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);", (final_value, row_to_process)
因此这个:
dbcursor.execute(query)
实际上等同于这个:
dbcursor.execute((sql, values))
所以你将一个单一对象(元组)传递给了 execute
,它默认将其作为查询字符串处理,因此出现错误。
相反,解包元组,这样 execute
就会接收到两个单独的参数:
dbcursor.execute(*query)
英文:
The problem is with how your query
variable is defined:
This is a tuple:
query = f"UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);", (final_value, row_to_process)
Therefore this:
dbcursor.execute(query)
is effectively this
dbcursor.execute((sql, values))
so you are passing a single object (the tuple) to execute
, which assumes it is the query string and so errors.
Instead, unpack the tuple so that execute
receives two separate parameters:
dbcursor.execute(*query)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论