MySQLdb：Python 操作 MySQL 数据库

男娘i 2022-07-13 06:14 206阅读 0赞

# [MySQL for Python][]： #

https://sourceforge.net/projects/mysql-python/files/

Github:    https://github.com/farcepest/MySQLdb1

# MySQL Python tutorial #

This is MySQL Python programming tutorial. It covers the basics of MySQL programming with Python. It uses the `MySQLdb` module. The examples were created and tested on Ubuntu Linux.

There is a similar [PostgreSQL Python tutorial][], [MySQL Visual Basic tutorial][], or [MySQL PHP tutorial][]on ZetCode. [SQLAlchemy tutorial][] covers SQLAlchemy SQL Toolkit and Object Relational Mapper. If you need to refresh your knowledge of the Python language, there is a full [Python tutorial][]. You may also consider to look at the [MySQL tutorial][], too.

## About MySQL database ##

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one part of the very popular *LAMP* platform which consists of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS. Wikipedia and YouTube use MySQL. These sites manage millions of queries each day. MySQL comes in two versions: MySQL server system and MySQL embedded system.

## Before we start ##

We need to install several packages to execute the examples in this tutorial.

If you do not already have MySQL installed, we must install it.

$ sudo apt-get install mysql-server

This command installs the MySQL server and various other packages. While installing the package, we are prompted to enter a password for the MySQL root account.

$ apt-cache search MySQLdb
    python-mysqldb - A Python interface to MySQL
    python-mysqldb-dbg - A Python interface to MySQL (debug extension)
    bibus - bibliographic database
    eikazo - graphical frontend for SANE designed for mass-scanning

We do not know the package name for the MySQLdb module. We use the `apt-cache` command to figure it out.

$ sudo apt-get install python-mysqldb

Here we install the Python interface to the MySQL database. Both `_mysql` and `MySQL` modules.

Next, we are going to create a new database user and a new database. We use the `mysql` client.

$ mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 30
    Server version: 5.0.67-0ubuntu6 (Ubuntu)
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema | 
    | mysql              | 
    +--------------------+
    2 rows in set (0.00 sec)

We connect to the database using the root account. We show all available databases with the `SHOW DATABASES` statement.

mysql> CREATE DATABASE testdb;
    Query OK, 1 row affected (0.02 sec)

We create a new `testdb` database. We will use this database throughout the tutorial.

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> USE testdb;
    Database changed
    
    mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> quit;
    Bye

We create a new database user. We grant all privileges to this user for all tables of the `testdb`database.

## \_mysql module ##

The `_mysql` module implements the MySQL C API directly. It is not compatible with the Python DB API interface. Generally, the programmers prefer the object oriented `MySQLdb` module. We will concern ourself with the latter module. Here we present only one small example with the `_mysql`module.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import _mysql
    import sys
    
    try:
        con = _mysql.connect('localhost', 'testuser', 'test623', 'testdb')
            
        con.query("SELECT VERSION()")
        result = con.use_result()
        
        print "MySQL version: %s" % \
            result.fetch_row()[0]
        
    except _mysql.Error, e:
      
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)
    
    finally:
        
        if con:
            con.close()

The example will get and print the version of the MySQL database. For this, we use the `SELECT VERSION()` SQL statement.

## MySQLdb module ##

`MySQLdb` is a thin Python wrapper around `_mysql`. It is compatible with the Python DB API, which makes the code more portable. Using this model is the preferred way of working with the MySQL.

## First example ##

In the first example, we will get the version of the MySQL database.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb
    import sys
    
    try:
        con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');
    
        cur = con.cursor()
        cur.execute("SELECT VERSION()")
    
        ver = cur.fetchone()
        
        print "Database version : %s " % ver
        
    except mdb.Error, e:
      
        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)
        
    finally:    
            
        if con:    
            con.close()

In this script, we connect to the testdb database and execute the `SELECT VERSION()` statement. This will return the current version of the MySQL database. We print it to the console.

import MySQLdb as mdb

We import the `MySQLdb` module.

con = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

We connect to the database. The `connect()` method has four parameters. The first parameter is the host, where the MySQL database is located. In our case it is a localhost, e.g. our computer. The second parameter is the database user name. It is followed by the user's account password. The final parameter is the database name.

cur = con.cursor()
    cur.execute("SELECT VERSION()")

From the connection, we get the cursor object. The cursor is used to traverse the records from the result set. We call the `execute()` method of the cursor and execute the SQL statement.

ver = cur.fetchone()

We fetch the data. Since we retrieve only one record, we call the `fetchone()` method.

print "Database version : %s " % ver

We print the data that we have retrieved to the console.

except mdb.Error, e:
      
        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)

We check for errors. This is important, since working with databases is error prone.

finally:    
            
        if con:    
            con.close()

In the final step, we release the resources.

$ ./version.py
    Database version : 5.5.9

The output might look like the above.

## Creating and populating a table ##

We create a table and populate it with some data.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');
    
    with con:
        
        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Writers")
        cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                     Name VARCHAR(25))")
        cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

We create a `Writers` table and add five authors to it.

with con:

With the with keyword, the Python interpreter automatically releases the resources. It also provides error handling.

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                    Name VARCHAR(25))")

This SQL statement creates a new database table called `Writers`. It has two columns: `Id` and `Name`.

cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    ...

We use the `INSERT` statement to insert authors to the table. Here we add two rows.

mysql> SELECT * FROM Writers;
    +----+-------------------+
    | Id | Name              |
    +----+-------------------+
    |  1 | Jack London       |
    |  2 | Honore de Balzac  |
    |  3 | Lion Feuchtwanger |
    |  4 | Emile Zola        |
    |  5 | Truman Capote     |
    +----+-------------------+
    5 rows in set (0.00 sec)

After executing the script, we use the `mysql` client tool to select all data from the Writers table.

## Retrieving data ##

Now that we have inserted some data into the database, we want to get it back.

In this example, we retrieve all data from the `Writers` table.

cur.execute("SELECT * FROM Writers")

This SQL statement selects all data from the Writers table.

rows = cur.fetchall()

The `fetchall()` method gets all records. It returns a result set. Technically, it is a tuple of tuples. Each of the inner tuples represent a row in the table.

for row in rows:
        print row

We print the data to the console, row by row.

$ ./retrieve.py
    (1L, 'Jack London')
    (2L, 'Honore de Balzac')
    (3L, 'Lion Feuchtwanger')
    (4L, 'Emile Zola')
    (5L, 'Truman Capote')

This is the output of the example.

Returning all data at a time may not be feasible. We can fetch rows one by one.

We again print the data from the `Writers` table to the console. This time, we fetch the rows one by one.

for i in range(cur.rowcount):
        
        row = cur.fetchone()
        print row[0], row[1]

We fetch the rows one by one using the `fetchone()` method. The rowcount property gives the number of rows returned by the SQL statement.

$ ./retrieve2.py
    1 Jack London
    2 Honore de Balzac
    3 Lion Feuchtwanger
    4 Emile Zola
    5 Truman Capote

Output of the example.

## The dictionary cursor ##

There are multiple cursor types in the `MySQLdb` module. The default cursor returns the data in a tuple of tuples. When we use a dictionary cursor, the data is sent in a form of Python dictionaries. This way we can refer to the data by their column names.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
    
    with con:
    
        cur = con.cursor(mdb.cursors.DictCursor)
        cur.execute("SELECT * FROM Writers LIMIT 4")
    
        rows = cur.fetchall()
    
        for row in rows:
            print row["Id"], row["Name"]

In this example, we get the first four rows of the `Writers` table using the dictionary cursor.

cur = con.cursor(mdb.cursors.DictCursor)

We use the `DictCursor` dictionary cursor.

cur.execute("SELECT * FROM Writers LIMIT 4")

We fetch four rows from the `Writers` table.

for row in rows:
        print row["Id"], row["Name"]

We refer to the data by column names of the `Writers` table.

$ ./dictcur.py
    1 Jack London
    2 Honore de Balzac
    3 Lion Feuchtwanger
    4 Emile Zola

Example output.

## Column headers ##

Next we will show, how to print column headers with the data from the database table.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
    
    with con:
    
        cur = con.cursor()
        cur.execute("SELECT * FROM Writers LIMIT 5")
    
        rows = cur.fetchall()
    
        desc = cur.description
    
        print "%s %3s" % (desc[0][0], desc[1][0])
    
        for row in rows:    
            print "%2s %3s" % row

Again, we print the contents of the `Writers` table to the console. Now, we include the names of the columns too. The column names are considered to be the 'meta data'. It is obtained from the cursor object.

desc = cur.description

A description attribute of the cursor returns information about each of the result columns of a query.

print "%s %3s" % (desc[0][0], desc[1][0])

Here we print and format the table column names.

for row in rows:    
        print "%2s %3s" % row

And here, we traverse and print the data.

$ ./columnheaders.py
    Id Name
     1 Jack London
     2 Honore de Balzac
     3 Lion Feuchtwanger
     4 Emile Zola
     5 Truman Capote

Ouput of the script.

## Prepared statements ##

Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance. The Python DB API specification suggests 5 different ways how to build prepared statements. The `MySQLdb` module supports one of them, the ANSI `printf` format codes.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
        
    with con:    
    
        cur = con.cursor()
            
        cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
            ("Guy de Maupasant", "4"))        
        
        print "Number of rows updated:",  cur.rowcount

We change the name of an author on the fourth row.

cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Guy de Maupasant", "4"))

We use the two placeholders identified by the `%s` markers. Before the SQL statement is executed, the values are bound to their placeholders.

$ ./prepared.py
    Number of rows updated: 1

We have updated one row.

mysql> SELECT Name FROM Writers WHERE Id=4;
    +------------------+
    | Name             |
    +------------------+
    | Guy de Maupasant |
    +------------------+
    1 row in set (0.00 sec)

The author on the fourth row was successfully changed.

## Inserting images ##

People often look for ways to insert images into databases. We will show how it can be done in SQLite and Python. Note that some people do not recommend to put images into databases. Images are binary data. MySQL database has a special data type to store binary data called `BLOB` (Binary Large Object). `TINYBLOB`, `BLOB`, `MEDIUMBLOB` and `LONGBLOB` are variants of the binary object type.

mysql> CREATE TABLE Images(Id INT PRIMARY KEY, Data MEDIUMBLOB);
    Query OK, 0 rows affected (0.08 sec)

For this example, we create a new table called `Images`.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb
    
    
    def read_image():
        
        fin = open("woman.jpg")    
        img = fin.read()
        
        return img
        
    
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
     
    with con:
        
        cur = con.cursor()
        data = read_image()
        cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))

In the above script, we read a JPG image from the disk and insert it into the `Images` table.

def read_image():
        
        fin = open("woman.jpg")    
        img = fin.read()
        
        return img

The `read_image()` method reads binary data from the JPG file, located in the current working directory.

cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))

We insert the image data into the `Images` table.

## Reading images ##

In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb 
    
    def writeImage(data):
        
        fout = open('woman2.jpg', 'wb')
        
        with fout:
            
            fout.write(data)
    
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
    
    with con:
    
        cur = con.cursor()
    
        cur.execute("SELECT Data FROM Images WHERE Id=1")
        data = cur.fetchone()[0]
        writeImage(data)

We read one image from the `Images` table.

cur.execute("SELECT Data FROM Images WHERE Id=1")

We select one record from the table.

fout = open('woman2.jpg', 'wb')

We open a writable binary file.

fout.write(data)

We write the data to the disk.

Now we should have an image called `woman2.jpg` in our current directory. We can check if it is the same image that we have inserted into the table.

## Transaction support ##

A *transaction* is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

For databases that support transactions, the Python interface silently starts a transaction when the cursor is created. The `commit()` method commits the updates made using that cursor, and the`rollback()` method discards them. Each method starts a new transaction.

The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. Since MySQL 5.5, InnoDB becomes the default storage engine. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. The `commit()` and `rollback()` methods are not implemented. They do nothing. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb
    import sys
    
    
    try:
        con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
    
        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Writers")
        cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                     Name VARCHAR(25)) ENGINE=INNODB")
        cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")
        
        con.commit()
    
        
    except mdb.Error, e:
      
        if con:
            con.rollback()
            
        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)
        
    finally:    
                
        if con:    
            con.close()

We recreate the `Writers` table. We explicitly work with transactions.

cur = con.cursor()

In Python DB API, we do not call the BEGIN statement to start a transaction. A transaction is started when the cursor is created.

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                    Name VARCHAR(25)) ENGINE=INNODB")

We are dealing with a InnoDB table type. For older MySQL versions (<5.5), we need to specify the engine type with the `ENGINE=INNODB` option.

con.commit()

We must end a transaction with either a `commit()` or a `rollback()` method. If we comment this line, the table is created but the data is not written to the table.

In this tutorial, we have been working with transactions without explicitly stating it. We used context managers. The context manager handles the entry and the exit from the desired runtime context for the execution of the block of code. Context managers are normally invoked using the `with`statement.

Connection objects in `MySQLdb` module can be used as context managers. They automatically commit or rollback transactions. Connection context managers clean up code by factoring out `try`, `except`, and `finally` statements.

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
    
    with con:
        
        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Writers")
        cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                     Name VARCHAR(25))")
        cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")

In our code example, the context manager deals with all the work necessary for error handling. It automatically commits or rolls back a transaction.

参考：http://zetcode.com/db/mysqlpython/

官方简介

> MySQLdb is an thread-compatible interface to the popular MySQLdatabase server that provides the Python database API.

## 安装 ##

### 通过 pip 安装 ###

$ apt-get install python-dev libmysqlclient-dev $ pip install MySQL-python

详见：[How to install Python MySQLdb module using pip?][How to install Python MySQLdb module using pip]

### 通过 apt 安装 ###

$ sudo apt-get install python-mysqldb

## 模块 \_mysql ##

MySQLdb 安装好后，有两个模块或方式可用。模块 `_mysql` 提供的是类似于 MySQL C 接口的 API，而模块 `MySQLdb` 在 `_mysql` 基础上又做了进一步封装，使之符合 Python 的数据库 API 规范。推荐使用后者。

使用 `_mysql` 的例子：

import _mysql
    import sys
    
    try:
        con = _mysql.connect('localhost', 'root', '******', 'test')
    
        con.query('select version()')
        result = con.use_result()
    
        print 'MySQL version: %s' % result.fetch_row()[0]
    
    except _mysql.Error, e:
        print 'Error %d: %s' % (e.args[0], e.args[1])
        sys.exit(1)
    
    finally:
        if con:
            con.close()

改用 `MySQLdb`：

import MySQLdb as mdb
    import sys
    
    try:
        con = mdb.connect('localhost', 'root', '******', 'test')
    
        cur = con.cursor()
        cur.execute('select version()')
    
        ver = cur.fetchone()
    
        print 'MySQL version: %s' % ver
    
    except mdb.Error, e:
        print 'Error %d: %s' % (e.args[0], e.args[1])
        sys.exit(1)
    
    finally:
        if con:
            con.close()

## 创建表，插入数据 ##

# coding: utf-8
    
    import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'root', '******', 'test')
    
    with con:
        cur = con.cursor()
        cur.execute('drop table if exists writers')
        cur.execute('create table writers(id int primary key auto_increment,\ name varchar(25)) default charset utf8')
        cur.execute('insert into writers(name) values("Jack London")')
        cur.execute('insert into writers(name) values("Honore de Balzac")')
        cur.execute('insert into writers(name) values("Lion Feuchtwanger")')
        cur.execute('insert into writers(name) values("Emile Zola")')
        cur.execute('insert into writers(name) values("Truman Capote")')
        cur.execute('insert into writers(name) values("曹雪芹")')

## 查询 ##

### 一次取回所有结果：`fetchall` ###

import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'root', '******', 'test')
    
    with con:
        cur = con.cursor()
        cur.execute('select * from writers')
    
        # 结果集 rows 为元组（tuple）的元组，每一个元组代表了表中的一行。
        rows = cur.fetchall()
        for row in rows:
            print row

### 挨个取回结果：`fetchone` ###

import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'root', '******', 'test')
    
    with con:
        cur = con.cursor()
        cur.execute('select * from writers')
    
        for i in range(cur.rowcount):
            row = cur.fetchone()
            print row

### 使用字典 Cursor ###

import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'root', '******', 'test')
    
    def test_dict_cursor():
        with con:
            cur = con.cursor(mdb.cursors.DictCursor) # 字典 cursor
            cur.execute('select * from writers limit 4')
    
            # rows 为字典的元组
            rows = cur.fetchall()
            for row in rows:
                print row['id'], row['name'] # 通过列名访问结果

### 打印列名 ###

import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'root', '******', 'test')
    
    with con:
        cur = con.cursor()
        cur.execute('select * from writers limit 4')
    
        rows = cur.fetchall()
            
        # 元组的元组，每一个元组对应一个结果列，元组的第一个元素为列名。
        desc = cur.description
    
        # 打印前两个结果列的列名。
        print '%s %3s' % (desc[0][0], desc[1][0])
    
        for row in rows:
            print '%2s %3s' % row

## Prepared Statements ##

Prepared Statements 可以提高安全性和性能，特别是对于多次重复执行的查询。Python 的数据库 API 规范建议了 5 种不同的方式来构造 Prepared Statements，MySQLdb 只支持其中的一种，代码类似于 `ANSI printf` 的格式化操作。

Prepared Statements 在 ORM 库（比如 SQLAlchemy）中应该会有更完善的支持。

注（2016-01-10）：  
这里的 Prepared Statements 只是客户端的模拟，跟 MySQL Server 的 Prepared Statements 是两码事，所以并不能提高性能或安全性。（详见 [C API Prepared Statements][]）

import MySQLdb as mdb
    
    con = mdb.connect('localhost', 'root', '******', 'test')
    
    with con:
        cur = con.cursor()
    
        cur.execute("update writers set name = %s where id = %s",
                ("Guy de Maupasant", "4"))
    
        print "Number of rows updated:", cur.rowcount

## 事务 ##

前面的例子一直使用 `with` 语句来管理链接 (connection) 对象，避免了 `commit` 的直接调用。

一旦 cursor 创建，一个事务也就开始，结束时必须调用 `commit` 或 `rollback`。`commit` 提交修改，`rollback` 回滚。如果结合`with` 语句使用的话，`commit` 和 `rollback` 都将自动完成，因为 MySQLdb 的链接对象可以当作 context manager 使用。

# coding: utf-8
    
    import MySQLdb as mdb
    
    try:
        con = mdb.connect('localhost', 'root', '******', 'test')
    
        # Cursor 创建，事务开始。
        cur = con.cursor()
    
        cur.execute('drop table if exists writers')
        # MyISAM doesn't support transaction.
        cur.execute('create table writers(id int primary key auto_increment,\ name varchar(25)) engine=innodb')
    
        cur.execute('insert into writers(name) values("Jack London")')
        cur.execute('insert into writers(name) values("Honore de Balzac")')
        cur.execute('insert into writers(name) values("Lion Feuchtwanger")')
        cur.execute('insert into writers(name) values("Emile Zola")')
        cur.execute('insert into writers(name) values("Truman Capote")')
    
        # 显式地调用 commit 来结束一个事务。
        con.commit()
    
    except mdb.Error, e:
        # 异常发生时，调用 rollback 进行回滚。
        if con:
            con.rollback()
    
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)
    
    finally:
        if con:
            con.close()

## Cursor 有必要 close 吗？ ##

原则上讲，不需要显式地调用 cursor 对象的 `close` 方法，因为当 cursor 对象生命期结束时，`close` 方法会被自动调用。源码如下：

class BaseCursor(object):
        def __del__(self):
            self.close()
            self.errorhandler = None
            self._result = None

不过，还是建议主动调用 `close`，这样至少代码的行为更加明显。

参考：https://segmentfault.com/a/1190000004123103

**python下的MySQLdb使用**

下面开始操作的demo:

Python代码

# -*- coding: utf-8 -*-     
        #mysqldb    
        import time, MySQLdb    
           
        #连接    
        conn=MySQLdb.connect(host="localhost",user="root",passwd="",db="test",charset="utf8")  
        cursor = conn.cursor()    
           
        #写入    
        sql = "insert into user(name,created) values(%s,%s)"   
        param = ("aaa",int(time.time()))    
        n = cursor.execute(sql,param)    
        print n    
           
        #更新    
        sql = "update user set name=%s where id=3"   
        param = ("bbb")    
        n = cursor.execute(sql,param)    
        print n    
           
        #查询    
        n = cursor.execute("select * from user")    
        for row in cursor.fetchall():    
            for r in row:    
                print r    
           
        #删除    
        sql = "delete from user where name=%s"   
        param =("aaa")    
        n = cursor.execute(sql,param)    
        print n    
        cursor.close()    
           
        #关闭    
        conn.close()

基本的使用如上，还是很简单的，进一步使用还没操作，先从网上找点资料放上来，以备后续查看

**1.引入MySQLdb库**

import MySQLdb   
  
**2.和数据库建立连接**   
conn=MySQLdb.connect(host="localhost",user="root",passwd="sa",db="mytable",charset="utf8")   
提供的connect方法用来和数据库建立连接,接收数个参数,返回连接对象.   
  
比较常用的参数包括   
host:数据库主机名.默认是用本地主机.   
user:数据库登陆名.默认是当前用户.   
passwd:数据库登陆的秘密.默认为空.   
db:要使用的数据库名.没有默认值.   
port:MySQL服务使用的TCP端口.默认是3306.  
charset:数据库编码.

更多关于参数的信息可以查这里   
http://mysql-python.sourceforge.net/MySQLdb.html   
  
然后,这个连接对象也提供了对事务操作的支持,标准的方法   
commit() 提交   
rollback() 回滚   
  
**3.执行sql语句和接收返回值**   
cursor=conn.cursor()   
n=cursor.execute(sql,param)   
首先,我们用使用连接对象获得一个cursor对象,接下来,我们会使用cursor提供的方法来进行工作.这些方法包括两大类:1.执行命令,2.接收返回值   
  
cursor用来执行命令的方法:   
callproc(self, procname, args):用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数   
execute(self, query, args):执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数   
executemany(self, query, args):执行单条sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数   
nextset(self):移动到下一个结果集   
  
cursor用来接收返回值的方法:   
fetchall(self):接收全部的返回结果行.   
fetchmany(self, size=None):接收size条返回结果行.如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据.   
fetchone(self):返回一条结果行.   
scroll(self, value, mode='relative'):移动指针到某一行.如果mode='relative',则表示从当前所在行移动value条,如果mode='absolute',则表示从结果集的第一行移动value条.   
  
下面的代码是一个完整的例子.   
\#使用sql语句,这里要接收的参数都用%s占位符.要注意的是,无论你要插入的数据是什么类型,占位符永远都要用%s   
sql="insert into cdinfo values(%s,%s,%s,%s,%s)"   
\#param应该为tuple或者list   
param=(title,singer,imgurl,url,alpha)   
\#执行,如果成功,n的值为1   
n=cursor.execute(sql,param)   
  
\#再来执行一个查询的操作   
cursor.execute("select \* from cdinfo")   
\#我们使用了fetchall这个方法.这样,cds里保存的将会是查询返回的全部结果.每条结果都是一个tuple类型的数据,这些tuple组成了一个tuple   
cds=cursor.fetchall()   
\#因为是tuple,所以可以这样使用结果集   
print cds\[0\]\[3\]   
\#或者直接显示出来,看看结果集的真实样子   
print cds   
  
\#如果需要批量的插入数据,就这样做   
sql="insert into cdinfo values(0,%s,%s,%s,%s,%s)"   
\#每个值的集合为一个tuple,整个参数集组成一个tuple,或者list   
param=((title,singer,imgurl,url,alpha),(title2,singer2,imgurl2,url2,alpha2))   
\#使用executemany方法来批量的插入数据.这真是一个很酷的方法!   
n=cursor.executemany(sql,param)   
  
**4.关闭数据库连接**   
需要分别的关闭指针对象和连接对象.他们有名字相同的方法   
cursor.close()   
conn.close()   
  
四步完成,基本的数据库操作就是这样了.下面是两个有用的连接   
MySQLdb用户指南: http://mysql-python.sourceforge.net/MySQLdb.html   
MySQLdb文档: http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html

**5 编码（防止乱码）**

需要注意的点：

**    1 Python文件设置编码 utf-8 （文件前面加上 \#encoding=utf-8)**  
**    2 MySQL****数据库charset=utf-8 **  
**    3 Python****连接MySQL是加上参数 charset=utf8 **

#encoding=utf-8 
        import sys 
        import MySQLdb 
         
        db=MySQLdb.connect(user='root',charset='utf8')

注：MySQL的配置文件设置也必须配置成utf8

打开`/etc/mysql/my.cnf`文件，为\[mysqld\]添加如下设置：

[mysqld]
    collation-server = utf8_unicode_ci
    init-connect='SET NAMES utf8'
    character-set-server = utf8

[MySQL for Python]: https://sourceforge.net/projects/mysql-python/
[PostgreSQL Python tutorial]: http://zetcode.com/db/postgresqlpythontutorial/
[MySQL Visual Basic tutorial]: http://zetcode.com/db/mysqlvb/
[MySQL PHP tutorial]: http://zetcode.com/databases/mysqlphptutorial/
[SQLAlchemy tutorial]: http://zetcode.com/db/sqlalchemy/
[Python tutorial]: http://zetcode.com/lang/python/
[MySQL tutorial]: http://zetcode.com/databases/mysqltutorial/
[How to install Python MySQLdb module using pip]: http://stackoverflow.com/questions/25865270/how-to-install-python-mysqldb-module-using-pip
[C API Prepared Statements]: http://dev.mysql.com/doc/refman/5.6/en/c-api-prepared-statements.html