时间戳

首页 > 解决方案 > SQLite 在插入查询中使用变量

python - SQLite 在插入查询中使用变量

问题描述

我正在尝试制作一个 python SQLite GPA Calculator 项目，我希望能够在其中使用用户输入并将用户给出的值插入到 SQLite 表中。我已经想出了如何插入查询，但我想知道如何在 SQLite 插入中使用 python3 中的变量。我似乎找不到任何适用于我的代码的解决方案，因此非常感谢您的帮助！

import sqlite3

try:
    sqliteConnection = sqlite3.connect("SQLite_Python_Test.db")
    sqlite_query_create_table = '''CREATE TABLE gpa_calc_users_test(
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        gpa_sem1 REAL,
        gpa_sem2 REAL);'''

    sqlite_query_insert = '''
    INSERT INTO gpa_calc_users_test
        (id, username, password, gpa_sem1, gpa_sem2)
        VALUES
        (1,'test', 'blablabla', 3.7, 3.5 )
    '''

    cursor = sqliteConnection.cursor()
    sqliteConnection.commit()
    print("Connected to sqlite Successfully")


    cursor.execute(sqlite_query_create_table)
    sqliteConnection.commit()
    print("Table created Successfully")

    cursor.execute(sqlite_query_insert)
    sqliteConnection.commit()
    print("Query inserted Successfully")
    cursor.close()

except sqlite3.Error as error:
    print("Error while creating a table")

finally:
    if(sqliteConnection):
        sqliteConnection.close()
        print("SQLite connection closed")

标签： pythonsqlite

解决方案

You may use a prepared statement for your insert, and then bind variables to it:

params = (1, 'test', 'blablabla', 3.7, 3.5,)
sql = """INSERT INTO gpa_calc_users_test
         (id, username, password, gpa_sem1, gpa_sem2)
         VALUES (?, ?, ?, ?, ?)"""
cursor.execute(sql, params)

推荐阅读