Error with Mysql Syntax in Python Script when Inserting into Database

Question

I'm very new to python and mysql also and am currently trying to "INSERT data into a table, events for which I have set up on a WAMP server on my machine. The data I need to store into the database are stored within various arrays within my python program and these have all been stored correctly. I am able to successfully connect to the localhost database via my python program however I am having issues with my mysql syntax when writing to the database.

I hope someone is able to help me - I've basically set up a for loop to write elements stored in an array to the database that I have set up locally on my machine (localhost).

length = range(len(event_ids))
imageid = 0 

for z in length:
    cur.execute("""INSERT INTO events (event_id, event_title, start_time, stop_time, venue_id, image_id, all_day, venue_name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s""" %(event_ids[z], event_names1[z], start_times1[z], stop_times1[z], venue_ids1[z], imageid, all_day_list1[z], venue_names1[z]))
    db.commit()

cursor.close()
db.close()

The errors that I receive includes the below information. The elements that are displayed here are reading from the arrays that I have specified successfully "@2013042020, Prodijig Dublin, 2013-04-20 20:00:00, 2013-04-20 23:59:00, V0-001-0:". I just do not know why I receive SQL syntax errors. I'd really appreciate it if anyone can shed any information on where I may be going wrong.

ERRORS:

cur.execute("""INSERT INTO events (event_id, event_title, start_time, stop_time, venue_id, image_id, all_day, venue_name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s""" %(event_ids[z], event_names1[z], start_times1[z], stop_times1[z], venue_ids1[z], imageid, all_day_list1[z], venue_names1[z]))
File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute
self.errorhandler(self, exc, value)
File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in  defaulterrorhandler
raise errorclass, errorvalue
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 '@2013042020, Prodijig Dublin, 2013-04-20 20:00:00, 2013-04-20 23:59:00, V0-001-0' at line 1")

EDIT: I have a fix for this code after below discussions however I am now receiving an "IndexError: list index out of range" error for some reason I cannot explain at present and my python program terminates after 35 iterations (when it should iterate and insert elements to database 50 times). If anyone could help me with this too I'd be very appreciative. It might not have to do with this statement however - I am unsure just now.

My fixed mysql insert into code anyway is:

cur.execute('INSERT INTO events (event_id, event_title, start_time, stop_time, venue_id, image_id, all_day, venue_name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)' , (event_ids[z], event_names1[z], start_times1[z], stop_times1[z], venue_ids1[z], imageid, all_day_list1[z], venue_names1[z]))

Barmar · Accepted Answer · 2013-04-20 17:50:24Z

1

You need quotes around all the string values being inserted:

cur.execute("""INSERT INTO events (event_id, event_title, start_time, stop_time, venue_id, image_id, all_day, venue_name) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'""" %(event_ids[z], event_names1[z], start_times1[z], stop_times1[z], venue_ids1[z], imageid, all_day_list1[z], venue_names1[z]))

answered Apr 20, 2013 at 17:50

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Karen Over a year ago

Hi @Barmar I have tried this line of code and receive this following error: 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 '' at line 1")

Karen Over a year ago

could it be anything to do with a " ' " in the strings that I am writing to the database? I can't think of anything else! Thank you for your answer though!

Barmar Over a year ago

Single quotes are allowed in mysql queries. Can you print out the query string?

Karen Over a year ago

Well the strings stored within the array in which I am inserting into the database. One such element in the array reads: "South William Space'". This is just a guess though as I feel I have tried so many tutorials and looked through so many answers on stackoverflow that I can't find any other solution.

Barmar Over a year ago

That sounds like some of the arrays have fewer elements than event_ids does.

|

Hazzit · Accepted Answer · 2013-04-20 17:45:27Z

The query you are running is this:

INSERT INTO events (
    event_id, 
    event_title, 
    start_time, 
    stop_time, 
    venue_id, 
    image_id, 
    all_day, 
    venue_name) 
VALUES (
    @2013042020, 
    Prodijig Dublin, 
    2013-04-20 20:00:00, 
    2013-04-20 23:59:00, 
    V0-001-0  (rest of query is missing)

MySQL is complaining that @2013042020 is not a valid value. I assume event_id is an integer column, so the value should not contain an @ symbol. Once you fix that you will notice that MySQL has more to complain about your query:

Prodijig Dublin     should be a string: 'Prodijig Dublin'
2013-04-20 20:00:00 should be a date  : #2013-04-20 20:00:00# (quotes also work)
2013-04-20 23:59:00 should be a date  : #2013-04-20 23:59:00#
V0-001-0            should be as tring: 'V-0-001-0'

Collectives™ on Stack Overflow

Error with Mysql Syntax in Python Script when Inserting into Database

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related