如何使用 SQL 参数 [英] How to use SQL parameters
问题描述
我正在尝试为我正在制作的一个简单游戏创建一个数据库，但在查询玩家统计数据时遇到了问题.目前数据库是可以搜索和更新的，但是只有修改实际代码才能改变搜索词.
I'm trying to create a database for a simple game I'm making and having an issue with querying it for the players stats. So far the database can be searched and updated, but only by modifying the actual code can the search terms be changed.
到目前为止的整个代码(对任何缩进错误表示歉意):
The entire code so far (apologies for any indentation errors):
# Importing modules
import sqlite3
# Creating database
base_db = sqlite3.connect("D:\\FILEPATH\\base_db")
# Creating cursor object
global cur
cur = base_db.cursor()
# Creating players table with username etc. attributes
cur.execute("""
CREATE TABLE IF NOT EXISTS players
(username, password, score)
""")
base_db.commit()
# Inserts some example data
def populate_db():
print("Populating database")
example_data = [("John Smith", "Password", 10)]
cur.executemany("""
INSERT INTO players
VALUES (?, ?, ?)""",
example_data)
base_db.commit()
# Clears database
def clear_db():
print("Clearing database")
command = ("""
DELETE FROM players
""")
cur.execute(command)
base_db.commit()
# Function that (only) updates the database
def update_db(mode, name):
if mode is "score":
print("Updating scores")
print("Finding", name)
command = ("""
UPDATE players
SET score = '0'
WHERE username = 'John Smith'
""")
cur.execute(command)
base_db.commit()
# Function that (only) retrieves data from the database
def retrieve_db(mode, name):
# Returns value of player's score
if mode is "score":
print("Retrieving scores")
print("Finding", name)
command = ("""
SELECT score FROM players
WHERE username = 'John Smith'
""")
return cur.execute(command).fetchone()[0]
# Returns tuple of username and password
elif mode is "login":
print("Retrieving login details")
print("Finding", name)
command = ("""
SELECT username, password FROM players
WHERE username = 'John Smith'
""")
return cur.execute(command).fetchone()[0:2]
base_db.commit()
# Testing results
populate_db()
print(retrieve_db("score", "John Smith")) # Expected result is 10
print(retrieve_db("login", "John Smith")) # Expected result is ('John Smith, 'Password')
clear_db()
以及我正在尝试解决的部分:
And the section I'm trying to get a solution for:
# Function that (only) retrieves data from the database
def retrieve_db(mode, name):
# Returns value of player's score
if mode is "score":
print("Retrieving scores")
print("Finding", name)
command = ("""
SELECT score FROM players
WHERE username = 'John Smith'
""")
return cur.execute(command).fetchone()[0]
我想要一个将 name 参数传递给此命令的解决方案，而不是 WHERE username = 'John Smith'，因此无论 name 参数是什么，都会成为要搜索的术语.
Instead of WHERE username = 'John Smith', I would like a solution for passing the name parameter into this command instead, so whatever the name parameter is becomes the term being searched for.
我在这里找到了一个类似的例子(使用 Python 变量搜索 SQLite 数据库) 但我一直在思考如何将 (?), (variable) 想法实现到这个代码块中.
I found a similar example here (Searching SQLite Database with Python Variables) but I'm stuck on how to implement the (?), (variable) idea into this block of code.
到目前为止我尝试过的:
What I've tried so far:
command = ("""
SELECT score FROM players
WHERE username = VALUES (?)""", name)
command = ("""
SELECT score FROM players
WHERE username VALUES (?)""", name)
非常感谢您能提供的任何帮助.提前致谢.
Any help you could give is very much appreciated. Thanks in advance.
为了将来参考，我得到的最终工作版本是:
For future reference the final working version I got was:
command = ("""
SELECT score FROM players
WHERE username = ?
""")
return cur.execute(command, name).fetchone()[0]
当函数被调用时，名称被提供为 ['John Smith'].
Where the name was supplied as ['John Smith'] when the function was called.
推荐答案
VALUES 是 INSERT 的必需部分声明.
参数标记(?)只是替换值本身:
A parameter marker (?) just replaces the value itself:
command = ("""
SELECT score FROM players
WHERE username = ?
""")
params = ['John Smith']
return cur.execute(command, params).fetchone()[0]
这篇关于如何使用 SQL 参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
