SQLite查询参数化：避免VALUES语法错误与正确传递单元素元组参数

INSERT INTO MyTable (column1, column2) VALUES ('value1', 'value2');

# 错误的SQL查询字符串
get_all_parking_by_type = 'SELECT * FROM Parking WHERE type = ? and user_id = 0 VALUES (?)'
# 执行时会抛出 sqlite3.OperationalError: near "VALUES": syntax error

# 正确的SQL查询字符串
get_all_parking_by_type = 'SELECT * FROM Parking WHERE type = ? and user_id = 0'

# 尝试传递参数，但 ('guest') 实际上是一个字符串，不是元组
cursor.execute(get_all_parking_by_type, ('guest'))

# 正确传递单元素元组参数
cursor.execute(get_all_parking_by_type, ('guest', ))

import sqlite3

# 假设我们有一个名为 'parking.db' 的数据库
conn = sqlite3.connect('parking.db')
cursor = conn.cursor()

try:
    # 创建一个示例表（如果不存在）
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS Parking (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            type TEXT NOT NULL,
            user_id INTEGER NOT NULL
        )
    ''')
    conn.commit()

    # 插入一些示例数据
    cursor.execute("INSERT INTO Parking (type, user_id) VALUES (?, ?)", ('guest', 0))
    cursor.execute("INSERT INTO Parking (type, user_id) VALUES (?, ?)", ('member', 1))
    cursor.execute("INSERT INTO Parking (type, user_id) VALUES (?, ?)", ('guest', 0))
    conn.commit()

    # 正确的SQL查询字符串，移除了 VALUES
    get_all_parking_by_type = 'SELECT * FROM Parking WHERE type = ? AND user_id = 0'

    # 正确传递单元素元组参数 ('guest', )
    cursor.execute(get_all_parking_by_type, ('guest', ))
    guests = cursor.fetchall()

    print("所有user_id为0的guest停车记录：")
    for guest_record in guests:
        print(guest_record)

except sqlite3.Error as e:
    print(f"数据库操作错误: {e}")
    conn.rollback() # 发生错误时回滚事务
finally:
    # 清理资源
    conn.close()

所有user_id为0的guest停车记录：
(1, 'guest', 0)
(3, 'guest', 0)