Python : error when insert mysql database

Question

I'm using Ejabberd xmpp/jabber server and I'm trying to insert data into mysql database using Python

What I'm doing is :

username = "user1"
email = "[email protected]"
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()

showed to me this error :

(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")

but when I'm trying to connect to mysql via terminal and insert it, it's work 100%

what I'm doing in terminal :

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>[email protected]</USERID></EMAIL><EMAIL><WORK/></EMAIL><JABBERID></JABBERID><ORG><ORGUNIT/></ORG><PRODID></PRODID></vCard>");

How can I do it?

Answer 1

You don't need to put %s within quotations :

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

You can also remove the % at the leading of your values tuple :

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

• emzemzx: showed this error : (1136, "Column count doesn't match value count at row 1")
• emzemzx: sorry i forgot vcard in vcard(username), working 100% thnxxxx.