Python-based MySQL database source code instance

I am using a MYSQL database operated by MySQLdb. Let's take a simple example:

12345678910

import MySQLdb  try:     conn=MySQLdb.connect(host='localhost',user='root',passwd='root',db='test',port=3306)     cur=conn.cursor()     cur.execute('select * from user')     cur.close()     conn.close() except MySQLdb.Error,e:      print "Mysql Error %d: %s" % (e.args[0], e.args[1])

Make sure to modify your database, host name, user name, and password.

The following example shows how to insert data, insert data in batches, and update data:

 

123456789101112131415161718192021222324252627

import MySQLdb  try:     conn=MySQLdb.connect(host='localhost',user='root',passwd='root',port=3306)     cur=conn.cursor()          cur.execute('create database if not exists python')     conn.select_db('python')     cur.execute('create table test(id int,info varchar(20))')          value=[1,'hi rollen']     cur.execute('insert into test values(%s,%s)',value)          values=[]     for i in range(20):         values.append((i,'hi rollen'+str(i)))              cur.executemany('insert into test values(%s,%s)',values)      cur.execute('update test set info="I am rollen" where id=3')      conn.commit()     cur.close()     conn.close()  except MySQLdb.Error,e:      print "Mysql Error %d: %s" % (e.args[0], e.args[1])

Be sure to haveConn. commit ()Submit the transaction,Otherwise, data cannot be inserted.

The results of my MySQL database cannot be obtained after running.

 

123456789101112131415161718192021222324252627282930313233

import MySQLdb  try:     conn=MySQLdb.connect(host='localhost',user='root',passwd='root',port=3306)     cur=conn.cursor()          conn.select_db('python')      count=cur.execute('select * from test')     print 'there has %s rows record' % count      result=cur.fetchone()     print result     print 'ID: %s info %s' % result      results=cur.fetchmany(5)     for r in results:         print r      print '=='*10    cur.scroll(0,mode='absolute')      results=cur.fetchall()     for r in results:         print r[1]           conn.commit()     cur.close()     conn.close()  except MySQLdb.Error,e:      print "Mysql Error %d: %s" % (e.args[0], e.args[1])

The running result will not be pasted, which is too long.

After the query, Chinese characters are correctly displayed, but garbled characters are displayed in the database. After searching through the Internet, I found that a property can be used:

In Python code

Conn = MySQLdb. Connect (host = 'localhost', user = 'root', passwd = 'root', db = 'python') adds an attribute:
Changed:
Conn = MySQLdb. Connect (host = 'localhost', user = 'root', passwd = 'root', db = 'python', charset = 'utf8 ′)
Charset is the same as the encoding of your database. If the database is gb2312, write charset = 'gb2312 ′.

 

The following describes common functions:

Then, this connection object also provides support for transaction operations, standard methods
Commit () Submit
Rollback () rollback

Cursor is used to execute commands:
Callproc (self, procname, args): used to execute a stored procedure. The received parameters are the stored procedure name and parameter list. The returned values are the number of affected rows.
Execute (self, query, args): executes a single SQL statement. The received parameters are the SQL statement itself and the list of parameters used. The returned values are the affected rows.
Executemany (self, query, args): executes a single-pick SQL statement, but repeats the parameters in the parameter list. The returned value is the number of affected rows.
Nextset (self): Move to the next result set

Cursor is used to receive the returned value:
Fetchall (self): receives all returned result rows.
Fetchmany (self, size = None): receives the size of returned results rows. If the size value is greater than the number of returned results rows, the returned cursor. arraysize data is returned.
Fetchone (self): returns a result line.
Scroll (self, value, mode = 'relative '): Move the pointer to a row. if mode = 'relative ', the value bar is moved from the current row. If mode = 'absolute', the value bar is moved from the first row of the result set.

References:

MySQLdb's user guide

Package MySQLdb