我正在尝试创建一个联系人簿作为个人项目。在"find_contact(("函数中，当我使用"emaiL"变量执行查询时，它的错误消息表明数据(我预先记录在表中(不存在。但是，当我将用于查询的变量更改为"phoneNum"(这是一个文本形式的数字(时，查询就起作用了。我该怎么办？
import sqlite3
conn = sqlite3.connect('contactBook.db')
cur = conn.cursor()
records = cur.fetchall()
#create table
cur.execute("""CREATE TABLE IF NOT EXISTS contacts (
first_name TEXT NOT NULL,
last_name TEXT,
phone_number TEXT NOT NULL PRIMARY KEY,
email,
address TEXT,
UNIQUE(phone_number, email)
)""")
#conn.close()
def save_contact():
save_contact.firstName = input("First name of contact: ")
lastName = input("Last name of contact: ")
phoneNumber = input("Phone number of contact: ")
email_ = input("Email of contact: ")
address_ = input("Address of contact: ")
cur.execute("INSERT OR IGNORE INTO contacts (first_name, last_name,phone_number,
email,address) VALUES (?, ?, ?, ?, ?)",
(save_contact.firstName, lastName, phoneNumber, email_, address_))
conn.commit()
def find_contact():
emaiL = input('Enter email: ')
query = f'''SELECT * FROM contacts WHERE email = {emaiL}'''
lua = f'''SELECT first_name, phone_number FROM contacts WHERE email = {emaiL}'''
#cur.execute("SELECT * FROM contacts (email) VALUES (?)", (email,))
cur.execute(query)
#conn.commit()
print(cur.execute(lua))
req = input("Hello, would you like to save or search for a contact: ")
if str.lower(req) == 'save':
save_contact()
x = save_contact.firstName
print("You have successfully saved " + x + "'s details")
elif str.lower(req) == 'search':
find_contact()
测试运行是：
Hello, would you like to save, search or update for a contact:
search
Enter email: mine
回溯：
Traceback (most recent call last):
File "c:UsersGLORIADesktopMINEdb.py", line 60, in <module>
find_contact()
File "c:UsersGLORIADesktopMINEdb.py", line 33, in
find_contact
cur.execute(query)
sqlite3.OperationalError: no such column: mine
query = f'''SELECT * FROM contacts WHERE email = {emaiL}'''
如果变量emaiL的值是字符串'mine'，则会创建SQL语句
SELECT * FROM contacts WHERE email = mine
但是在这个语句中，mine将被SQLite解释为列名。如果您想将其解释为字符串，则需要添加引号：
SELECT * FROM contacts WHERE email = "mine"
但是，不要试图调整字符串格式行query = f'''...'''以添加引号，而是使用带有?的参数化语句作为占位符，就像您对代码中的其他SQL语句所做的那样。
query = 'SELECT * FROM contacts WHERE email = ?'
cur.execute(query, (emaiL,))
请参阅如何在Python中的SQL语句中使用变量？
在查询query = f'''SELECT * FROM contacts WHERE last_name = {emaiL}'''中，它不应该是f'''SELECT * FROM contacts WHERE email = {emaiL}'''吗？
这可能是因为您的{emaiL}在查询中没有电子邮件的引号，如下所示：
f'''SELECT * FROM contacts WHERE email = '{emaiL}' '''
如果打印出当前查询变量，则会得到无效的SELECT * FROM contacts WHERE email = mine。您想要获得字符串SELECT * FROM contacts WHERE email = 'mine'。