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

Question

当字符串包含'时，在mysql数据库中插入字符串时遇到困难。

下面是字符串和代码，我不想去掉这个字符，或者用"替换 it

字符串

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

当对MySQL使用%s连接器时，使用%s作为字符串值的占位符

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

关于这一行有两件事要注意：

• 在每个'之前和之后都没有%s标记。
• 字符串查询后有逗号，而不是%。

请注意，我们并不是简单地在%操作符中使用Python的字符串格式。我们将查询字符串和值分别传递给MySQL连接器。然后，连接器执行任何必要的转义，以确保'字符或类似的字符没有问题。

我知道这种方法是可行的，因为我已经在MySQL数据库上运行了这个方法，并成功地插入了您的数据。

Answer 2

更新2：

注释：您不能参数化数据库名，所以呢？在?.hadith是错误的。-卢克·伍德沃德

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

更新1：

TypeError: execute()最多接受4个参数(6个给定)

这个新的解决方案应该是可行的。

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

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

指：方法MySQLCursor.execute(操作，params=None，multi=False)

旧答案：

试试这个：

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

引用：Python库:游标API文档