Python how to manipulate MySQL database method sharing

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

The code is as follows:

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])

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

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

The code is as follows:

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])

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.

The code is as follows:

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

