python mysql query error when string contains '

Question

I am having difficulties inserting a string in my mysql database when the string contains '

here is the string and the code, I don't want to get rid of this character or replace it with "

String

I heard Allah's Messenger saying, "The reward of deeds depends upon the intentions and every person will get the reward according to what he has intended. So whoever emigrated for worldly benefits or for a woman to marry, his emigration was for what he emigrated for."

CODE

#! /usr/bin/env python
from bs4 import BeautifulSoup                                                                                                                                 
import urllib2 
import lxml    
from lxml import etree                                                                                                                                           
import mysql.connector
import re
import time
# =============DATABASE SETTINGS===================

mysql_host                  = '192.168.0.15'
mysql_localhost_user        = 'admin'
mysql_localhost_password    = 'xxxxxxx'
mysql_localhost_database    = 'prayertime'
cnx = mysql.connector.connect(host=mysql_host, user=mysql_localhost_user, password=mysql_localhost_password, database=mysql_localhost_database)
topic = "Revelation"
arabic_hadith = "test"
english_hadith = "I heard Allah's Messenger saying, The reward of deeds depends upon the intentions and every person will get the reward according to what he has intended. So whoever emigrated for worldly benefits or for a woman to marry, his emigration was for what he emigrated for."
cursor = cnx.cursor()
cursor.execute("INSERT INTO " + mysql_localhost_database +".hadith" + " (hadith,translated_hadith,topic)" + " VALUES ('" + arabic_hadith+ "', '" + english_hadith+ "' , '" + topic+ "')" )
cnx.commit()
cursor.close()
cnx.close()

Ravinder Reddy · Accepted Answer · 2013-12-15 13:55:34Z

3

Update 2:

Comment: you can't parameterise the database name, so the ? in ?.hadith is wrong. – Luke Woodward

Please check the changed prepared query as below.

Update 1:

TypeError: execute() takes at most 4 arguments (6 given)

This new solution should be working.

sql_string = "INSERT INTO " + mysql_localhost_database + 
             ".hadith( hadith, translated_hadith, topic )" +  
             " VALUES ( ?, ?, ? )"

cursor.execute(  
  sql_string, ( arabic_hadith, english_hadith, topic )  
)

Refer to: Method MySQLCursor.execute(operation, params=None, multi=False)

OLD Answer:

Try this:

cursor.execute(  
  "INSERT INTO ?.hadith( hadith, translated_hadith, topic )" +  
  " VALUES ( ?, ?, ? )"  
  , mysql_localhost_database  
  , arabic_hadith
  , english_hadith
  , topic
)

References: Python ODBC library: Cursor API doc

edited Dec 15, 2013 at 13:55

answered Dec 15, 2013 at 7:51

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

5 Comments

Ossama Over a year ago

Thanks, I get the following error = cursor.execute("INSERT INTO ?.hadith (hadith, translated_hadith, topic) VALUES (?,?,?)" , mysql_localhost_database, arabic_hadith, english_hadith, topic) TypeError: execute() takes at most 4 arguments (6 given)

Ossama Over a year ago

I Passed additional arguments in a tuple but now I get error mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''prayertime'.hadith (hadith, translated_hadith, topic) VALUES ('حَدَّثَن' at line1

Ravinder Reddy Over a year ago

@Ossama Either put prayertime in single quotes or remove them. Error message shows that error is at or before prayertime'. You can see that prayertime' has ' suffix but not prefixed.

Luke Woodward Over a year ago

@Ravinder: you can't parameterise the database name, so the ? in ?.hadith is wrong.

Luke Woodward Over a year ago

@Ravinder: Another thing: the questioner is using MySQL Connector for Python, not PyODBC, so the link to PyODBC is misleading.

Luke Woodward · Accepted Answer · 2013-12-15 19:15:26Z

1

When using MySQL Connector for Python, use %s as a placeholder for string values

cursor.execute("INSERT INTO " + mysql_localhost_database +".hadith" +
                   " (hadith,translated_hadith,topic)" + " VALUES (%s, %s, %s)",
               (arabic_hadith, english_hadith, topic))

There are two things to note about this line:

There are no ' marks before and after each %s.
There is a comma after the string query, not a %.

Note that we are not simply using Python's string formatting with the % operator. We are passing separately the query string and the values to the MySQL Connector for Python. The connector then does any necessary escaping to ensure that there are no issues with ' characters or suchlike.

I know this approach works because I have run this against a MySQL database and successfully inserted your data.

edited Dec 15, 2013 at 19:15

answered Dec 15, 2013 at 14:12

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

2 Comments

Ravinder Reddy Over a year ago

I am aware of using %s or other place holders. Isn't it good to leave it to statement processor to decide the data type? I mean by just using ? place holders.

Luke Woodward Over a year ago

@Ravinder: the MySQL Connector for Python doesn't support ? marks; the documentation says that you should use %s instead, regardless of the type. I've also expanded my answer to explain that this use of %s placeholders is more than just string formatting.

Collectives™ on Stack Overflow

python mysql query error when string contains '

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related