python : error when insert mysql database

Question

我正在使用 Ejabberd xmpp/jabber 服务器，我正在尝试使用 Python 将数据插入 mysql 数据库

我正在做的是:

username = "user1"
email = "user1@domain.tld"
vCard = ("<vCard xmlns='vcard-temp'><VERSION>3.0</VERSION><ADR><HOME/></ADR><ADR><WORK/></ADR><TEL><HOME/></TEL><TEL><WORK/></TEL><TEL><FAX/></TEL><TEL><CELL/></TEL><EMAIL><HOME/><USERID>%s</USERID></EMAIL><EMAIL><WORK/></EMAIL><JABBERID></JABBERID><ORG><ORGUNIT/></ORG><PRODID></PRODID></vCard>" % (email))

import MySQLdb as mdb
db = mdb.connect('localhost', 'test1', 'test1', 'test1')
try:
    cur = db.cursor()
    cur.execute("INSERT INTO vcard(username,vcard) VALUES('%s', '%s')" % (username, vCard))
except Exception as why: print why
db.close()

向我展示了这个错误:

(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 '<vCard xmlns='vcard-temp'><VERSION>3.0</VERSION><ADR><HOME/></ADR><ADR><WORK/></' at line 1")

但是当我尝试通过终端连接到 mysql 并插入它时，它可以 100% 工作

我在终端里做什么:

mysql -u test1 -p test1

INSERT INTO vcard(username,vcard) VALUES("user1", "<vCard xmlns='vcard-temp'><VERSION>3.0</VERSION><ADR><HOME/></ADR><ADR><WORK/></ADR><TEL><HOME/></TEL><TEL><WORK/></TEL><TEL><FAX/></TEL><TEL><CELL/></TEL><EMAIL><HOME/><USERID>user1@domain.tld</USERID></EMAIL><EMAIL><WORK/></EMAIL><JABBERID></JABBERID><ORG><ORGUNIT/></ORG><PRODID></PRODID></vCard>");

我该怎么做？

Answer 1

您不需要将 %s 放在引号内:

cur.execute("INSERT INTO vcard(username,vcard) VALUES(%s, %s)"%(username, vCard))

您还可以删除值元组开头的 % :

cur.execute("INSERT INTO vcard(username,vcard) VALUES(%s, %s)",(username, vCard))