Python basics-using Mysql and python Basics

Python basics-using Mysql and python Basics

Python is very convenient to operate Mysql. The basic operations of MySQLdb are as follows:

 

Query:

 1 try: 2     conn = MySQLdb.connect(host=self.ip, user=self.username,passwd=self.password, db=self.dbname, port=self.port) 3     cur = conn.cursor() 4     cur.execute(sql) 5     rows = cur.fetchall() 6     data = rows 7 except MySQLdb.Error, e: 8     print str(e) 9     print "Connet mysql db error..."10     sys.exit()

 

Insert data:

try:    conn = MySQLdb.connect(host=self.ip, user=self.username, passwd=self.password, db=self.dbname, port=self.port)    cur = conn.cursor()    cur.execute(sql, value)    conn.commit()    conn.close()    cur.close()except MySQLdb.Error, e:    print str(e)    print "Execute mysql db error..."    sys.exit()

 

We encountered Encoding Problems during use. We used UTF-8 to solve the encoding problem:

conn.set_character_set('utf8')cur.execute('SET NAMES utf8;')cur.execute('SET CHARACTER SET utf8;')cur.execute('SET character_set_connection=utf8;')

 

There is also a backslash problem. mysql escapes the backslash by default. My solution is to replace the backslash with the double backslash:

datapath = datapath.replace('\\', '\\\\')

 

The mysql statement needs to format the string. The queried SQL string must use % to represent the placeholder of the variable. However, python seems to have to use % s.

 

Executeplugin also supports Simultaneous Insertion of multiple data records, but I didn't use this because it is convenient to add loop processing outside.