时间戳

首页 > 解决方案 > 为什么使用 python 在我的 sqlite 表中搜索项目仅在数据是保存为文本的数字时才有效？

python - 为什么使用 python 在我的 sqlite 表中搜索项目仅在数据是保存为文本的数字时才有效？

问题描述

我正在尝试创建一个通讯录作为个人项目。在 '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:\Users\GLORIA\Desktop\MINE\db.py", line 60, in <module>
    find_contact()
  File "c:\Users\GLORIA\Desktop\MINE\db.py", line 33, in 
    find_contact
    cur.execute(query)
sqlite3.OperationalError: no such column: mine

标签： pythonsqlitevariables

解决方案

在查询query = f'''SELECT * FROM contacts WHERE last_name = {emaiL}'''中不应该是f'''SELECT * FROM contacts WHERE email = {emaiL}'''吗？

推荐阅读