sqlite 如何为多个查询创建一个函数，该函数可以接受零个、一个或多个占位符？

jgzswidk 于 2023-01-17 发布在 SQLite

关注(0)|答案(1)|浏览(120)

因为我对不同的表有多个查询，使用不同的占位符，我想为此创建一个函数，但是我不能决定这个函数的参数
例如

def function_q(query, placeholder):
    cursor.execute(query, (placeholder,))
    return cursor.fetchall()

但是如果我需要调用这个函数时没有占位符，或者有一个以上的占位符，第二个参数应该是list还是*args？如果两个都是，我会得到错误。函数调用将是这样的，并且它是这样工作的：

person_id = 100
result = function_q('SELECT name FROM persons WHERE n_id = ?;', person_id)

但是如果我有不止一个占位符，这意味着函数调用将像这样：

person_id = 100
age = 30
result = function_q('SELECT name FROM persons WHERE n_id = ? and age = ?;', person_id, person_age)

sqlite

来源：https://stackoverflow.com/questions/75117763/how-to-create-a-function-for-multiple-queries-that-accepts-zero-one-or-more-pla

1条答案

按热度按时间

mzaanser1#

从placeholder变量cursor.execute(query, (placeholder,))中删除元组，并假设它是一个可迭代对象。

import sqlite3
db_path = ':memory:'

def function_q(query, placeholders):
    cur.execute(query, placeholders) # renamed cursor variable!
    return cur.fetchall()

# multiple placeholders
q1 = 'SELECT * FROM sample_table WHERE col1=? and col2=?;'
phs1 = ('x', 'y')

# single placeholders
q2 = 'SELECT * FROM sample_table WHERE col2=?;'
phs2 = ('yy',)

# no placeholders
q3 = 'SELECT * FROM sample_table;'
phs3 = tuple()

test = [(q1, phs1), (q2, phs2), (q3, phs3)]

# create and populate db
statement = """
CREATE TABLE sample_table (col1, col2);
INSERT INTO sample_table (col1, col2) VALUES ('x','y');
INSERT INTO sample_table (col1, col2) VALUES ('xx','yy');
"""
with sqlite3.connect(db_path) as con:
    # db initialisation
    cur = con.cursor()
    cur.executescript(statement)

    # run the test with different placeholders
    for q, phs in test:
        res = function_q(q, phs)
        print(*res)

    # execution multiple placeholders query
    q = 'SELECT * FROM sample_table WHERE col1=? and col2=?;'
    print(function_q(q, ('xx', 'yy'))) # notice the iterable object

#('x', 'y')
#('xx', 'yy')
#('x', 'y') ('xx', 'yy')
#[('xx', 'yy')]

赞(0）回复(0）举报 2023-01-17

我来回答

sqlite 如何为多个查询创建一个函数，该函数可以接受零个、一个或多个占位符？

1条答案

相关问题

热门标签

最新问答