Using Python to manipulate the MySQL database

Use Python to operate MySQL, as long as you install the Python-mysql installation package, there are many ways to install it, you can install it using Easy_install or PIP or source code.

Here's how to use Python to manipulate MySQL, here are some simple examples:

(1). Use Python to connect to MySQL:

1 ImportMySQLdb2 3 Try:4conn = MySQLdb.connect (host="localhost",, user='Root', passwd="sina.com", db="MySQL", port=3307)5 exceptmysqldb.operationalerror,e:6     Print "The error msg is:"E

(2). Query MySQL data using python:

1 ImportMySQLdb2  3 Try:4Conn=mysqldb.connect (host='localhost', user='Root', passwd='Root', db='Test', port=3306)5Cur=conn.cursor ()6Cur.execute ('SELECT * from user')7 cur.close ()8 conn.close ()9 exceptmysqldb.error,e:TenPrint "Mysql Error%d:%s"% (E.args[0], e.args[1])

(3). Use Python to add and update MySQL data:

1 ImportMySQLdb2  3 Try:4Conn=mysqldb.connect (host='localhost', user='Root', passwd='Root', port=3306)5Cur=conn.cursor ()6      7Cur.execute ('CREATE database if not exists Python')8conn.select_db ('python')9Cur.execute ('CREATE TABLE Test (ID int,info varchar )')Ten       OneValue=[1,'Hi Rollen'] ACur.execute ('INSERT into test values (%s,%s)', value) -       -values=[] the forIinchRange (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') +   A Conn.commit () at cur.close () - conn.close () -   - exceptmysqldb.error,e: - Print "Mysql Error%d:%s"% (E.args[0], e.args[1])

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 ()PrintresultPrint 'ID:%s Info%s'%result Results=cur.fetchmany (5)     forRinchResults:PrintRPrint '=='*10Cur.scroll (0,mode='Absolute') Results=Cur.fetchall () forRinchResults:PrintR[1] Conn.commit () cur.close () conn.close ()exceptmysqldb.error,e:Print "Mysql Error%d:%s"% (E.args[0], e.args[1])

(4). Some commonly used API methods:

1 commit () Commit2 rollback () rollback3 4 cursor the method used to execute the command:5 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 affected6 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 affected7 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 affected8 Nextset (self): move to the next result set9 Ten the cursor is used to receive the return value of the method: One Fetchall (self): receives all the returned result rows. AFetchmany (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'): Move the pointer to a line. 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.

Using Python to manipulate the MySQL database