I am new to Python and MySQL so I am practicing with a readonly account. I am trying to create an executable script that basically asks for a first name, last name, then inserts them into the query and returns the results. Below is what I have and the error I get:
cur = conn.cursor()
user_input_fname = raw_input("Enter Trainer First Name: ")
user_input_lname = raw_input("Enter Trainer Last Name: ")
cur.execute(" SELECT concat(u.firstname, ' ', u.lastname) AS clientName, us.sessionid AS sessionID, convert_tz(s.starttime, '+00:00', '+04:00') AS sessionTimeEST, concat(t.firstname, ' ', t.lastname) AS trainerName FROM usersessions us LEFT JOIN users u ON us.userid = u.id INNER JOIN sessions s ON us.sessionid = s.id INNER JOIN trainers t ON s.trainerid = t.id WHERE u.firstname = %s AND u.lastname =%s ORDER BY s.starttime;' " %(user_input_fname, user_input_lname))
data = cur.fetchall()
cur.close()
conn.close()
ERROR: pymysql.err.InternalError: (1054, u"Unknown column 'ethan' in 'where clause'")
I feel like it's a ' or " missing somewhere but I can't figure out my syntactical mistake.
The string literals within sql must be enclosed by ' or ". You are providing names in the where clause, so those should be enclosed by one of the speechmarks:
...WHERE u.firstname = '%s' AND u.lastname ='%s'...
There is also a trailing single quote just before the end of the sql statement. If that's truly in your code, then it must be removed.