CODEWITHSUNDEEP
pythonsqlitecharacter-encoding
rmonraz
rmonraz

Reputation: 1

sqlite3.OperationalError: near ";": syntax error when Inserting

I keep getting a sqlite3.OperationalError: near ";", can't figure out why...

What am I doing wrong? I am trying to insert records into a database from a list of employee tuples.

Have read other similar errors but not quite the same as this one.

connection = sqlite3.connect("company.db")
cursor = connection.cursor()



cursor.execute("""DROP TABLE employee;""")

staff_data = [ ("William", "Shakespeare", "m", "19611025"),
("Frank", "Schiller", "m", "19550817"), ("Jane", "Wall", "f", "1989-03-14")]


# Define an SQL command
sql_command = """
CREATE TABLE employee (
staff_number INTEGER PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(30),
gender CHAR(1),
joining DATE,
birth_date DATE);
"""

# Execute command
cursor.execute(sql_command)

# Insert commands
sql_command = """ INSERT INTO employee (staff_number, fname, lname, gender,
birth_date) VALUES (NULL, "William", "Shakespeare", "m", "1961-10-25");"""

cursor.execute(sql_command)
print(sql_command)

for p in staff_data:
    format_str = """ INSERT INTO employee (staff_number, fname, lname, gender, birth_date) VALUES (NULL, "{first}", "{last}", "{gendera}", "{birthdate}"); """
    sql_command = format_str.format(first=p[0], last=p[1], gendera=p[2], birthdate=p[3])


print(sql_command)
cursor.execute(sql_command)



# DONT FORGET THIS TO SAVE CHANGES
connection.commit()

connection.close()

This is the error message:

 INSERT INTO employee (staff_number, fname, lname, gender,
birth_date) VALUES (NULL, "William", "Shakespeare", "m", "1961-10-25");
 INSERT INTO employee (staff_number, fname, lname, gender, birth_date) VALUES (NULL, "Jane", "Wall", "f", "1989-03-14"); 
Traceback (most recent call last):
  File "sqliteuse0.py", line 53, in <module>
    cursor.execute(sql_command)
sqlite3.OperationalError: near ";": syntax error

Upvotes: 0

Views: 1989

Answers (2)

rmonraz
rmonraz

Reputation: 1

I used difflib.ndiff to compare the typed string and the one generated by formatting and it said format_str had a missing ;. I deleted and retyped it and it solved the issue. I believe this was due to copying directly from a pdf with the example code.

Issue solved

Upvotes: 0

Jacob Krall
Jacob Krall

Reputation: 28825

    format_str = """ INSERT INTO employee (staff_number, fname, lname, gender, birth_date) VALUES (NULL, "{first}", "{last}", "{gendera}", "{birthdate}"); """

That's not a semicolon (;). It's U+037E GREEK QUESTION MARK (;).

Replace it with a semicolon and try again.

Upvotes: 1

Related Questions