Why does the searching of an item in my sqlite table, using python, only work when the data is a number saved as text?

Question

I'm trying to create a contact book as a personal project. In the 'find_contact()' function, when I use the 'emaiL' variable to perform a query, it error message says that the data (I pre-recorded in the table) doesn't exist. But when I changed the variable to be used to query to 'phoneNum' (which is a number in text form) the query worked. How do I go about this please?

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()

The test run was:

Hello, would you like to save, search or update for a contact: 
search
Enter email: mine

The traceback:

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

Please show the exact error message you get including complete traceback. — mkrieger1, Jul 08 '21 at 19:29

score 0 · Answer 1 · edited Jul 08 '21 at 19:32

0

In the query query = f'''SELECT * FROM contacts WHERE last_name = {emaiL}''' is it not supposed to be f'''SELECT * FROM contacts WHERE email = {emaiL}'''?

edited Jul 08 '21 at 19:32

mkrieger1

19,194
5
54
65

answered Jul 08 '21 at 19:31

Chaitu Boggavarapu

151
1
5

It is, I used last_name to see if it was only email that had the problem and forgot to change it. Thank you. – Gloria Isedu Jul 08 '21 at 22:41

score 0 · Answer 2 · answered Jul 08 '21 at 23:14

It's probably because your {emaiL} doesn't have quotes for the email in the query, like this:

f'''SELECT * FROM contacts WHERE email = '{emaiL}' '''

If you print out your current query variable, you'll get SELECT * FROM contacts WHERE email = mine, which isn't valid. You want to get the string SELECT * FROM contacts WHERE email = 'mine'.

score 0 · Accepted Answer · answered Jul 08 '21 at 23:16

query = f'''SELECT * FROM contacts WHERE email = {emaiL}'''

If the value of the variable emaiL is the string 'mine', this creates the SQL statement

SELECT * FROM contacts WHERE email = mine

but in this statement, mine would be interpreted by SQLite as a column name. If you want it to be interpreted as a string, quotes would need to be added:

SELECT * FROM contacts WHERE email = "mine"

However, don't try to adjust the string formatting line, query = f'''...''' to add the quotes, instead use a parameterized statement with ? as a placeholder, like you did for the other SQL statements in your code.

query = 'SELECT * FROM contacts WHERE email = ?'
cur.execute(query, (emaiL,))

See How to use variables in SQL statement in Python? for reasons to do so.

Why does the searching of an item in my sqlite table, using python, only work when the data is a number saved as text?

3 Answers3