Python+mysql Study Notes (i)

Python can actually manipulate the database, think if you can write SQL statements in Python, and thus directly achieve the purpose of manipulating the database, how cool!

Python's standard database interface is Python DB-API, which installs different modules for different databases, where MYSQLDB is the interface for linking MySQL databases.

Install the MYSQLDB, which can be http://sourceforge.net/projects/mysql-python on Locate the installation package, download the installation, and no unnecessary settings.

The use process is as follows:

1. Import MYSQLDB Module
2. Get a connection to a database
3. Execute the SQL statement while refreshing the database
4. To close a database connection

Next, record database connections, create database tables, database insert operations, query Operations, update operations, delete operations, and error handling

1. Database connection

   # import Module Import MySQLdb # Database connection db = MySQLdb.connect (host= "localhost", user= ' * * * * *, passwd= ' * * * *, db= ' * * * *)

The database connection is simple and the format is fixed, note that before connecting, ensure that the user has permission to manipulate a database

1. Create a database table

   #-*-Coding:utf-8-*- Import MySQLdb db = MySQLdb.connect (host = ' localhost ', user = ' Wpy ', passwd = ' ", db = ' TestDB ') # get cursor cursor = Db.cursor () # Cursor's Execute method can execute SQL # Delete If Table employee is present Cursor.execute (' DROP TABLE IF EXISTS EMPLOYEE ') sql = ' CREATE TABLE EMPLOYEE (\ NAME VARCHAR (PRIMARY key,\) Age Int,\ SEX VARCHAR (1)) ' Cursor.execute (SQL) # Close Cursors Cursor.close () # Close the database connection Db.close ()

   View in PowerShell:

   No problem!

2. Database Insert Operations

   #-*-Coding:utf-8-*- Import MySQLdb db = MySQLdb.connect (host = ' localhost ', user = ' Wpy ', passwd = ' ", db = ' TestDB ') # get cursor cursor = Db.cursor () # Insert a record sql = "INSERT into EMPLOYEE (NAME, age, SEX) VALUES (' Simon ', ' M ') ' Try Cursor.execute (SQL) # Be sure to commit () the database will not be updated Db.commit () Except Db.rollback () # Insert more than one record values = [(' Lucy ', +, ' W '), (' Tom ', +, ' M ')] # must be%s, without quotation marks,%d is not possible sql = "INSERT into EMPLOYEE (NAME, age, SEX) VALUES (%s,%s,%s)" Try Cursor.executemany (SQL, values) Db.commit () Except Db.rollback () # Close Cursors Cursor.close () # Close the database connection Db.close ()

   The results are as follows:

3. database Query Operations

   #-*-Coding:utf-8-*- Import MySQLdb db = MySQLdb.connect (host = ' localhost ', user = ' Wpy ', passwd = ' ", db = ' TestDB ') # get cursor cursor = Db.cursor () # SQL query Statement sql = "SELECT *" from the EMPLOYEE WHERE age >= '%d ' "% (20) Try Cursor.execute (SQL) # Get All Records rows = Cursor.fetchall () For row in rows: print ' Name: ', row[0], ' Age: ', row[1], ' Sex: ', row[2] Except print ' Error, return nothing. ' # Close Cursors Cursor.close () # Close the database connection Db.close ()

   The results are as follows:

   Cursor also has two methods, Fetchone () and Fetchmany (size=)

   Fetchmany (size=) is relatively simple and can control the number of rows returned

   The meaning of Fetchone () is that it is obtained by line-by-row, because the position of the cursor changes as each fetch is used, and the result is visible, but that is exactly what the cursor means.

4. Database Update Operations

   #-*-Coding:utf-8-*- Import MySQLdb db = MySQLdb.connect (host = ' localhost ', user = ' Wpy ', passwd = ' ", db = ' TestDB ') # get cursor cursor = Db.cursor () # SQL UPDATE statement sql = "UPDATE EMPLOYEE SET age = age + 1 WHERE age >= '%d '"% (20) Try Cursor.execute (SQL) Db.commit () Except Db.rollback () # Close Cursors Cursor.close () # Close the database connection Db.close ()

   Database update operation is relatively simple, take care not to forget the Db.commit ()

5. Database Delete operations

   #-*-Coding:utf-8-*- Import MySQLdb db = MySQLdb.connect (host = ' localhost ', user = ' Wpy ', passwd = ' ", db = ' TestDB ') # get cursor cursor = Db.cursor () # SQL DELETE statement sql = "DELETE from the EMPLOYEE WHERE age < '%d '"% (20) Try Cursor.execute (SQL) Db.commit () Except Db.rollback () # Close Cursors Cursor.close () # Close the database connection Db.close ()

   Relatively simple, not much to say

   Summary: The syntax of MYSQLDB is relatively simple, the steps are very single, in addition to the SQL is different, the overall framework not much has changed in general. Or that sentence, SQL is the root, only a good command of SQL to perform more complex operations.

   in fact, MySQLdb grammar far more than these, but these are enough, if you want to learn more, you can refer to official website http://dev.mysql.com/doc/connector-python/en/

   Reference: http://www.runoob.com/python/python-mysql.html

   Http://www.cnblogs.com/coser/archive/2012/01/12/2320741.html

   http://blog.csdn.net/lengyue318/article/details/7913427

Python+mysql Study Notes (i)