如何格式化引用列名的UPDATE查询?

huangapple go评论82阅读模式
英文:

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(&#39;&#39;&#39;UPDATE alib set live = (?) WHERE rowid = (?);&#39;&#39;&#39;, (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(&#39;&#39;&#39;CREATE INDEX IF NOT EXISTS dedupe_tag ON alib (?) WHERE (?) IS NOT NULL;&#39;&#39;&#39;, (text_tag, text_tag))
    print(f&quot;- {text_tag}&quot;)

    &#39;&#39;&#39; get list of matching records &#39;&#39;&#39;
    &#39;&#39;&#39; as you cannot pass variables as field names to a SELECT statement build the query string dynamically then run it &#39;&#39;&#39;
    query = f&quot;SELECT rowid, {text_tag} FROM alib WHERE {text_tag} IS NOT NULL;&quot;
    dbcursor.execute(query)

    &#39;&#39;&#39; now process each matching record &#39;&#39;&#39;
    records = dbcursor.fetchall()
    records_returned = len(records) &gt; 0
    if records_returned:
          
        for record in records:
            &lt;SNIP&gt;

                if final_value != stored_value_sorted:

                    &#39;&#39;&#39; write out {final_value} to column {text_tag} for record = rowid &#39;&#39;&#39;
                    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 \, &#39;, &quot;, [, ], (, ) and punctuation marks. Line 77 is dbcursor.execute(query):

query = f&quot;UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);&quot;, (final_value, row_to_process)
(&#39;UPDATE alib SET artist = (?) WHERE rowid = (?);&#39;, (&#39;8:58\\\\The Unthanks&#39;, 305091))
Traceback (most recent call last):
  File &quot;&lt;string&gt;&quot;, line 80, in &lt;module&gt;
  File &quot;&lt;string&gt;&quot;, line 77, in dedupe_fields
TypeError: execute() argument 1 must be str, not tuple
query = f&quot;UPDATE alib SET {text_tag} = &#39;{final_value}&#39; WHERE rowid = {row_to_process};&quot;
UPDATE alib SET recordinglocation = &#39;Ashwoods, Stockholm\\Electric Lady Studioss, Stockholm\\Emilie&#39;s, Stockholm\\Ingrid Studioss, Stockholm\\Judios Studioss, Stockholm\\Nichols Canyon Houses, Stockholm\\Ocean Way Studioss, Stockholm\\RAK Studios, London\\Studio De La Grande Arm&#233;e, Paris\\The Villages, Stockholm\\Vox Studioss, Stockholm&#39; WHERE rowid = 124082;
Traceback (most recent call last):
  File &quot;&lt;string&gt;&quot;, line 80, in &lt;module&gt;
  File &quot;&lt;string&gt;&quot;, line 77, in dedupe_fields
sqlite3.OperationalError: near &quot;s&quot;: syntax error
query = f&quot;UPDATE alib SET {text_tag} = {final_value} WHERE rowid = {row_to_process};&quot;
UPDATE alib SET artist = 8:58\\The Unthanks WHERE rowid = 305091;
Traceback (most recent call last):
  File &quot;&lt;string&gt;&quot;, line 80, in &lt;module&gt;
  File &quot;&lt;string&gt;&quot;, line 77, in dedupe_fields
sqlite3.OperationalError: near &quot;:58&quot;: 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&quot;UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);&quot;, (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)

huangapple
  • 本文由 发表于 2023年8月9日 01:55:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76862084.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定