Python connection MySQL

1. Install the driver

There are currently two mysq L drives, and we can choose one to install:

Mysql-python: Is the python driver that encapsulates the MySQL C driver; Mysql-connector-python: It's the official MySQL-only Python drive.

Mysql-python:

Installation Tutorial: http://www.cnblogs.com/jfl-xx/p/7299221.html

Mysql-connector-python:

Installation Tutorial: http://www.cnblogs.com/Bgod/p/6995601.html

2. Test the connection

The Mysql-python driver, the MySQLdb module, is used here.

test_connect.py

1 #!/usr/bin/python2 #-*-coding:utf-8-*-3 4 ImportMySQLdb5 6 #Open a database connection7db = MySQLdb.connect ("localhost","Root","123456","Test")8 9 #get an operation cursor using the cursor () methodTencursor =db.cursor () One  A #executing SQL statements using the Execute method -Cursor.execute ("SELECT VERSION ()") -  the #Use the Fetchone () method to get a database.  -data =Cursor.fetchone () -  - Print "Database version:%s"%Data +  - #To close a database connection +Db.close ()

The test results are as follows and the connection is successful:

3. Create a database table

After the test is successful, we can create a table for MySQL directly in Python:

create_table.py

1 #!/usr/bin/python2 #-*-coding:utf-8-*-3 4 ImportMySQLdb5 6 #Open a database connection7db = MySQLdb.connect ("localhost","Root","123456","Test")8 9 #get an operation cursor using the cursor () methodTencursor =db.cursor () One  A #If the data table already exists, use the Execute () method to delete the table.  -Cursor.execute ("DROP TABLE IF EXISTS EMPLOYEE") -  the #Create a data table SQL statement -sql ="""CREATE TABLE EMPLOYEE ( - first_name CHAR () not NULL, - last_name CHAR (+), + Age INT, - SEX CHAR (1), + INCOME FLOAT)""" A  at cursor.execute (SQL) -  - #To close a database connection -Db.close ()

The table results are as follows:

4. Manipulating Database tables

Note: The placeholder in MySQL is%s

Operate_table.js

1 #!/usr/bin/python2 #-*-coding:utf-8-*-3 4 ImportMySQLdb5 6 #Open a database connection7db = MySQLdb.connect ("localhost","Root","123456","Test")8 9 #get an operation cursor using the cursor () methodTencursor =db.cursor () One  A #SQL INSERT Statement -Ins_sql ="""INSERT into EMPLOYEE (first_name, - last_name, age, SEX, INCOME) the VALUES (' Yu ', ' Jie ', ' M ', ' 8000 ')""" -  -INS_SQL1 ='INSERT into employee (first_name, last_name, age, sex, income) values (%s,%s,%s,%s ,%s)' -  + #SQL query Statements -Sel_sql ='SELECT * FROM employee where first_name =%s' +  A #SQL UPDATE statement atUpd_sql ='Update employee Set age =%s where sex =%s' -  - #SQL DELETE statement -Del_sql ='Delete FROM employee where first_name =%s' -  - Try: in     #Execute SQL statement -     #Insert to Cursor.execute (ins_sql) +Cursor.execute (INS_SQL1, ('Xu','F', 20,'M', 8000)) -     #Select theCursor.execute (Sel_sql, ('Yu',)) *Values =Cursor.fetchall () $     PrintValuesPanax Notoginseng     #Update -Cursor.execute (Upd_sql, (24,'M',)) the     #Delete +Cursor.execute (Del_sql, ('Xu',)) A  the     #commit to database execution + Db.commit () - except: $     #Roll Back when an error occurs $ Db.rollback () -  - #To close a database connection theDb.close ()

Perform an insert operation

Perform a query operation

Perform an update operation

Perform a delete operation

Knowledge points for Query statements:

Python queries MySQL uses the Fetchone () method to get a single piece of data, using the Fetchall () method to get multiple data.

Fetchone (): This method gets the next query result set. The result set is an object

Fetchall (): receives all the returned result rows.

For example, this example:

1Sel_sql ='SELECT * FROM employee where first_name =%s'2Cursor.execute (Sel_sql, ('Yu',))3Results =Cursor.fetchall ()4      forRowinchResults:5FName =Row[0]6LName = row[1]7Age = Row[2]8Sex = row[3]9Income = Row[4]Ten         Print "fname=%s, lname=%s,age=%d,sex=%s,income=%d"% (fname, lname, age, sex, income)

The results are as follows:

Python connection MySQL