1064, "You have an error in your SQL syntax" inserting(Another)

Question

I have the following error on my IDE:

MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2102@lionstate.edu', '88zlsj5j', 'Kristopher O'Connell', '21', 'F', 'CMPSC', '77' at line 1")

Here is a portion of my code that causes the error:

for a, b, c, d, e ,f, g, h in zip(df_stu['Email'], df_stu['Password'], df_stu['Full Name'], df_stu['Age'], df_stu['Gender'], df_stu['Major'], df_stu['Street'], df_stu['Zip']):
    cursor.execute("INSERT INTO LSU.Student (Semail, Spassword, Sname, Sage, Sgender, Smajor, Sstreet, Szipcode) "
                   "VALUES ('%s', '%s', '%s', '%d', '%s', '%s', '%s', '%d')" % (a, b, c, d, e, f, g, h))

And This is my CREATE TABLE:

cursor.execute("CREATE TABLE IF NOT EXISTS LSU.Student (Semail CHAR(50), Spassword CHAR(20), Sname CHAR(50), "
               "Sage INT, Sgender CHAR(5), Smajor CHAR(50), Sstreet CHAR(50), Szipcode INT, PRIMARY KEY (Semail))")

This looks right to me, but the IDE keep saying that there is a syntax error.

What is the period qualifying `LSU.`? is the name of the database? — Parfait, Apr 03 '19 at 19:47

score 0 · Answer 1 · answered Apr 03 '19 at 19:53

0

Can the single quote (') in 'Kristopher O'Connell' be interfering with the query?

answered Apr 03 '19 at 19:53

crosa

11
3

score 0 · Answer 2 · answered Apr 03 '19 at 20:10

Consider parameterization, the highly advised and recommended, industry best practice approach to avoid SQL injection by malicious users; quote enclosures and special characters that can break query execution; and unreadable/unmaintainable code as data mixes with code.

# PREPARED STATEMENT (ALL PLACEHOLDERS USING UNQUOTED %s PLACEHOLDERS, NO DATA)
sql = """INSERT INTO LSU.Student (Semail, Spassword, Sname, Sage, Sgender, Smajor, Sstreet, Szipcode)
         VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
      """

for a, b, c, d, e ,f, g, h in zip(df_stu['Email'], df_stu['Password'], df_stu['Full Name'], 
                                  df_stu['Age'], df_stu['Gender'], df_stu['Major'], 
                                  df_stu['Street'], df_stu['Zip']):    
    # QUERY EXECUTION
    cursor.execute(sql, (a, b, c, d, e, f, g, h))

Even consider executemany using pandas' DataFrame.values method as it appears you are iterating from a data frame. This avoids the for and zip loop:

# PREPARED STATEMENT
sql = """INSERT INTO LSU.Student (Semail, Spassword, Sname, Sage, Sgender, Smajor, Sstreet, Szipcode)
         VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
      """

# EXECUTE PARAMETERIZED QUERY
sql_cols = ['Email', 'Password', 'Full Name', 'Age', 'Gender', 'Major', 'Street', 'Zip']
cursor.executemany(sql, df_stu[sql_cols].values.tolist())   
conn.commit()

1064, "You have an error in your SQL syntax" inserting(Another)

2 Answers2