当字符串包含'时python mysql查询错误

Question

当字符串包含 ' 时，我无法在我的 mysql 数据库中插入该字符串

这是字符串和代码，我不想删除这个字符或替换它为"

字符串

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."

代码

#! /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() 

Answer 1

更新 2:

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

请检查更改后的准备查询，如下所示。

---

更新 1:

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

这个新解决方案应该有效。

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

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

引用:Method MySQLCursor.execute(operation, params=None, multi=False)

---

旧答案:

试试这个:

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

引用:Python ODBC library: Cursor API doc