CODEWITHSUNDEEP
pythonsqlsqlite
user13669275
user13669275

Reputation: 23

sqlite3.OperationalError: no such column: Python and Sqllite3

My Code:

From Python 3.8

def email_address_grab(email_list):

""" This function takes in a list of emails and puts them into a sql database"""


#import module
import sqlite3 as sql


#Setup sql
#create connection for sql
connection = sql.connect("emailList.db")

#create cursor
crsr = connection.cursor()

 #create sql table
cmd = """CREATE TABLE emails (
email_handle TEXT,
email_domain VARCHAR(20));"""
crsr.execute(cmd)


#iterate through email list
index = 0
for email in email_list:
    #split email with a delimiter of "@"
    email_list[index] = email.split('@')
    index += 1

#while loop to put all data into table
ct = 0
while ct <= (len(email_list) - 1):
    for i in range(0, len(email_list)):
        for j in range(0, len(email_list)):
            email_address_1 = email_list[i]
            email_address_2 = email_list[j]
            cmd = f"""INSERT INTO emails (email_handle, email_domain) VALUES ({email_address_1}, {email_address_2});"""
            crsr.execute(cmd)
    ct += 1


#get the contents of the table
crsr.execute("SELECT * FROM emails;")

#store contents in a variable
email_address_list = crsr.fetchall()

#save changes to sql table
connection.commit()

#close connection
connection.close()


#return print statement for data
return print(email_address_list)

Error:

Traceback (most recent call last):

File "c:/Users/USER/Desktop/email grabber.py", line 79, in email_address_grab(["[email protected]"])

File "c:/Users/USER/Desktop/email grabber.py", line 58, in email_address_grab crsr.execute(cmd)

sqlite3.OperationalError: no such column: 'testemail123', 'gmail.com'

Upvotes: 0

Views: 716

Answers (1)

M Z
M Z

Reputation: 4799

Your issue is because this is your final command string:

"""INSERT INTO emails (email_handle, email_domain) VALUES (testemail123, gmail.com);"""

while what is a valid string is:

"""INSERT INTO emails (email_handle, email_domain) VALUES ("testemail123", "gmail.com");"""

So you should be using something like:

cmd = f"""INSERT INTO emails (email_handle, email_domain) VALUES ('{email_address_1}', '{email_address_2}');"""

Although, with sqlite3, you should be passing in parameters with the execute call. This is to help prevent sql injection attacks because the way that you are using formatted strings can result in catastrophic attacks.

You should pass parameters to sqlite3 instances like this:

cmd = """INSERT INTO emails (email_handle, email_domain) VALUES (?, ?);"""
crsr.execute(cmd, (email_address_1, email_address_2))

Upvotes: 1

Related Questions