How to create a function for multiple queries that accepts zero, one or more placeholders?-CodePudding

Because I have multiple queries to different tables, with different placeholders, I would like to create a function for this. But I can't decide on parameters of this function

For example

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

But then if I need to call this function with no placeholder, or with more than 1 placeholder, second parameter should be a list or *args ? With both I get errors. Function call will be like this and this works:

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

But if I have more then one placeholder, meaning the function call would be like:

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

CodePudding user response：

Remove the tuple from the placeholder variable cursor.execute(query, (placeholder,)) and assumed it is an iterable object.

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')]