Python Sqlite3-使用f字符串更新数据库功能 [英] Python Sqlite3 - Using f strings for update database function

问题描述

我有一个很有趣的个人数据库(因此，与我自己创建的私有数据库无关的是sql注入)，并且正在尝试更改使用字符串格式(.format())创建的函数和占位符(?，％s等)，并改用f字符串.我遇到了一个问题，因为我将sqlite3查询更改为f字符串，现在无法运行将指定列更新为指定行的函数之一.

I have my own personal database I made for fun (so not that concerned with sql injections as its my own private database I made) and am trying to change the functions I created that use string formatting (.format()) and placeholders (?, %s, etc) and use f strings instead. I ran into a problem where one of my functions that updates a specified column into a specified row won't run now that I changed the sqlite3 query to f strings.

这是我当前使用f字符串的函数:

This is my current function using f strings:

import sqlite3
from tabulate import tabulate
conn = sqlite3.connect("Table.db")
c = conn.cursor()

def updatedb(Column, Info, IdNum):  
    with conn:
        data = c.execute(f"UPDATE Table_name SET {Column} = {Info} WHERE IdNum={IdNum}")
        c.execute(f"SELECT * FROM Table_name WHERE IdNum = {IdNum}")
    print(tabulate(data, headers="keys", tablefmt="grid", stralign='center', numalign='center'))

该函数通过使用所需的新信息更新指定行中的指定列来更新该表.例如，在3 x 3表中，如果该列是年龄或其他内容，则可以使用该函数将第1行，第2列更新为18，而不是第1列，第2列为17.之后的选择查询只是选择已更新的特定行，然后使用列表表包打印出打印语句，以打印出整洁有序的表.

The function updates the table by updating a specified column of a specified row with the new info you want in that column. For example in a 3 x 3 table, instead of row 1, column 2 being 17, I can use the function to update row 1, column 2 to 18 if that column is an age or something. The select query after that is to just select that particular row that was updated and the print statement after that uses the tabulate package to print out a neat and organized table.

每当我尝试使用此功能时遇到的错误是:

The error I get whenever I try to use this function is:

sqlite3.OperationalError: no such column: Info

无论我在函数中为Info变量键入什么内容，都会导致错误，但我不知道如何解决该问题.

Whatever I type in for the Info variable in the function is what the error becomes but I can't figure out how to fix the problem.

这是我尝试更改为f字符串之前所拥有的更新语句，对我来说很好用:

This is the update statement I had before attempting to change to f strings and it worked fine for me:

data = c.execute("UPDATE Table_name SET {} = ? WHERE IdNum=?".format(Column), (Info, IdNum))

将上述查询更改为f字符串似乎没有太大的变化，但是它不起作用，因此希望得到任何反馈.

It didn't seem like to would be that big of a change to change the above query to a f string but it isn't working so any feedback would be appreciated.

推荐答案

这里的问题是理解参数化-它仅适用于参数，不适用于列名和其他内容.

The problem here is understanding parameterization - it works only for parameters, not for column names and other stuff.

在此示例中:

 query = 'SELECT * FROM foo WHERE bar = ? AND baz = ?'
 params = (a, b)
 cursor.execute(query, params)

请注意，查询和数据分别传递到.execute-数据库的工作是进行插值-将您从引号地狱中解放出来，并通过禁用任何命令使程序更安全某种SQL注入.它的性能也可能更好-它允许数据库缓存已编译的查询，并在您更改参数时使用它.

Note that the query and the data are passed separately to .execute - it is the database's job to do the interpolation - that frees you from quote hell, and makes your program safer by disabling any kind of sql injection. It also could perform better - it allows the database to cache the compiled query and use it when you change parameters.

现在仅适用于数据.如果要在变量中使用实际的列名，则必须在查询中自行对其进行插值:

Now that only works for data. If you want to have the actual column name in a variable, you have to interpolate it yourself in the query:

 col1 = 'bar'
 col2 = 'baz'
 query = f'SELECT * FROM foo WHERE {col1} = ? AND {col2} = ?'
 cursor.execute(query, params)

这篇关于Python Sqlite3-使用f字符串更新数据库功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！