Python operation MySQL Database

Insist on learning a little every day, accumulate a little every day, as their own daily amateur harvest, this article is my meal during the writing, using their own scattered time to learn python operation MySQL, so tidy up a bit.

I am using the MySQL database of mysqldb operation. Let's start with a simple example:

12345678910

importMySQLdbtry:    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()exceptMySQLdb.Error,e:     print"Mysql Error %d: %s"%(e.args[0], e.args[1])

Please note that modify your database, hostname, username, password.

Here's an example of inserting data, inserting data in bulk, and updating data:

123456789101112131415161718192021222324252627

importMySQLdbtry:    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=[]    fori inrange(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()exceptMySQLdb.Error,e:     print"Mysql Error %d: %s" %(e.args[0], e.args[1])

Please note that you must have conn.commit () to commit the transaction , or you cannot actually insert the data.

After running my MySQL database will not be the result.

123456789101112131415161718192021222324252627282930313233

importMySQLdbtry:    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()    printresult    print‘ID: %s info %s‘%result    results=cur.fetchmany(5)    forr inresults:        printr    print ‘==‘*10    cur.scroll(0,mode=‘absolute‘)    results=cur.fetchall()    forr inresults:        printr[1]        conn.commit()    cur.close()    conn.close() exceptMySQLdb.Error,e:     print"Mysql Error %d: %s"%(e.args[0], e.args[1])

The result of the operation is not affixed, too long.

The Chinese will display correctly after query, but it is garbled in the database. After I looked up from the Internet, I found that using a property can be done:

In Python code

conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' python ') add a property:
Switch
conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' python ', charset= ' UTF8 ')
CharSet is to be the same as your database code, if the database is gb2312, then write charset= ' gb2312 '.

Here are some common functions:

The connection object is then provided with support for transactional operations, and the standard method
Commit () Commit
Rollback () rollback

Cursor the method used to execute the command:
Callproc (self, procname, args): Used to execute stored procedure, received parameter is stored procedure name and parameter list, return value is the number of rows affected
Execute (Self, query, args): Executes a single SQL statement, receives the parameters for the SQL statement itself and the parameter list used, and returns the number of rows affected
Executemany (self, Query, args): Executes a heads-up SQL statement, but repeats the parameters in the list of parameters, with the returned value being the number of rows affected
Nextset (self): move to the next result set

The cursor is used to receive the return value of the method:
Fetchall (self): receives all the returned result rows.
Fetchmany (self, Size=none): Receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, the cursor.arraysize data is returned.
Fetchone (self): Returns a result row.
Scroll (self, value, mode= ' relative '): Moves the pointer to a row. If mode= ' relative ', the value bar is moved from the current row, if mode= ' absolute ', Represents the move value bar from the first row of the result set.

Resources:

MySQLdb ' s User Guide

Package MySQLdb

Python operation MySQL Database