how do I insert a row to a MySQL table in Python?

Question

I've created a table with a name defined by a variable, and have then processed some text that I would like to insert into the table. I'm having a tough time finding the correct syntax, even when following this StackOverflow example. Here is a snippet of the code:

# Open database connection
db = MySQLdb.connect("localhost","root","menagerie","haiku_archive" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Create table using execute() method.

sql = "CREATE TABLE IF NOT EXISTS " + table_name  + """
     (haiku_text VARCHAR(120),
     date_written CHAR(22))"""

cursor.execute(sql)
.
.

# SQL query to INSERT a haiku into the selected table
cursor.execute('''INSERT into ' + table_name + '(haiku_text, date_written) values (%s, %s)''', (haiku_text, date_written))

# Commit your changes in the database
db.commit()

# disconnect from server
db.close()

I'm deliberately not including the processing, which basically involves opening a text file and doing assorted strips, rstrips, joins, etc., to edit the text into the desired format before inserting into the table. I can include it if it would be helpful.

The error I'm getting isn't particularly helpful - at least to me:

_mysql_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 '' + table_name + '(haiku_text, date_written) values ('the old pond<br>a frog ' at line 1")

suggestion --> check what is passed to db --> try -->sql_state = '''INSERT into ' + table_name + '(haiku_text, date_written) values (%s, %s)''', (haiku_text, date_written) --> print sql_state --> see if the statement is correct — user3570335
– user3570335, Commented May 8, 2014 at 1:55

Daniel Roseman · Accepted Answer · 2014-05-07 22:29:05Z

0

By surrounding the statement in triple quotes, you're ensuring that the single quote after "into" does not close the string. That means that the whole text is treated as one literal string, which is why you're seeing that in the error message.

There's no reason to use triple quotes here at all. Just use normal single quotes throughout:

cursor.execute('INSERT INTO ' + table_name + ' (haiku_text, date_written) VALUES (%s, %s)', (haiku_text, date_written))

(Also note I've added a space before the opening paren of the column names.)

answered May 7, 2014 at 22:29

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

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

2 Comments

meda Over a year ago

hi daniel, I am currently learning python, does %s need to be quoted? in php it would need it

Daniel Roseman Over a year ago

No, the db API parameter substitution automatically works out if they need quotes or not.

meda · Accepted Answer · 2014-05-07 22:29:21Z

0

I believe you need a space after table name:

table_name + ' (haiku_text, date_written)
       -------^

answered May 7, 2014 at 22:29

meda

45.5k14 gold badges97 silver badges123 bronze badges

Collectives™ on Stack Overflow

how do I insert a row to a MySQL table in Python?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related