Python link MySQL database detailed

Learn some basic python things, always want to write a program, but write programs do not use the database, appear too low-end, then the Python link MySQL how to operate it? Here is a detailed introduction to the next

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

Importmysqldb 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 () 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:

Importmysqldb 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 () ')         value=[1, ' Hi Rollen ']    cur.execute (' INSERT into test values (%s,%s ) ', value '         values=[]    fori inrange:        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.

Importmysqldb 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 have%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 Chinese will display correctly after query, but it is garbled in the database. Note here to add a parameter charset:

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 '.

Note: Python MySQL links common functions

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.

