使用“?"sqlite3 语句中的占位符 [英] Using '?' placeholder in sqlite3 statements

问题描述

因此，出于某种原因，我在 select 语句中使用占位符时出现错误.

So for some reason I am getting errors when using placeholders in select statements.

def get_id(table_name, id_name):
    db = sqlite3.connect('test_db')
    max_id = db.execute('''SELECT max(?) FROM ?''', (id_name, table_name)).fetchone()[0]
    if not max_id:
        primary_id = 0
    else:
        primary_id = max_id + 1

此函数返回此错误:

File "test.py", line 77, in get_id
max_id = db.execute('''SELECT max(?) FROM ?''', (id_name, table_name)).fetchone()[0]
sqlite3.OperationalError: near "?": syntax error

也以防万一:

id_name = 'pos_id'
table_name = 'Poscar'

推荐答案

您不能对列名或表名使用占位符.占位符用于用于从数据库插入或检索数据的值.图书馆对它们进行了适当的消毒.

You aren't able to use placeholders for column or table names. The placeholders are for values used to insert or retrieve data from the database. The library properly sanitizes them.

要做你想做的事，试试这样的:

To do what you want, try something like this:

db.execute('''SELECT max({}) FROM {}'''.format(id_name, table_name)).fetchone()[0]

这将使用字符串格式来构建您的查询.如果您需要为此添加一个 WHERE 条件，您仍然可以使用参数来做到这一点:

This will use string formatting to build your query. If you need to add a WHERE condition to this, you can still do that using parameters:

db.execute('''SELECT max({}) FROM {} WHERE ID = ?'''.format(id_name, table_name), id_variable).fetchone()[0]

这篇关于使用“?"sqlite3 语句中的占位符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！