MySQLdb：Python 操作 MySQL 数据库

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 tutorialon 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 MySQLdbpython-mysqldb - A Python interface to MySQLpython-mysqldb-dbg - A Python interface to MySQL (debug extension)bibus - bibliographic databaseeikazo - 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 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 30Server 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 changedmysql> 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 testdbdatabase.

_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 _mysqlmodule.

#!/usr/bin/python# -*- coding: utf-8 -*-import _mysqlimport systry:    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 mdbimport systry:    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.pyDatabase 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 mdbcon = 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.

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

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.

#!/usr/bin/python# -*- coding: utf-8 -*-import MySQLdb as mdbcon = mdb.connect('localhost', 'testuser', 'test623', 'testdb');with con:    cur = con.cursor()    cur.execute("SELECT * FROM Writers")    for i in range(cur.rowcount):                row = cur.fetchone()        print row[0], row[1]

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.py1 Jack London2 Honore de Balzac3 Lion Feuchtwanger4 Emile Zola5 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 mdbcon = 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.py1 Jack London2 Honore de Balzac3 Lion Feuchtwanger4 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 mdbcon = 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.pyId 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 mdbcon = 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.pyNumber 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 mdbdef 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 therollback() 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 mdbimport systry:    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 withstatement.

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 mdbcon = 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?

通过 apt 安装

$ sudo apt-get install python-mysqldb

模块 _mysql

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

使用 _mysql 的例子：

import _mysqlimport systry:    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 mdbimport systry:    con = mdb.connect('localhost', 'root', '******', 'test')    cur = con.cursor()    cur.execute('select version()')    ver = cur.fetchone()    print 'MySQL version: %s' % verexcept mdb.Error, e:    print 'Error %d: %s' % (e.args[0], e.args[1])    sys.exit(1)finally:    if con:        con.close()

创建表，插入数据

# coding: utf-8import MySQLdb as mdbcon = 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 mdbcon = 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 mdbcon = 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 mdbcon = 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 mdbcon = 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 mdbcon = 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-8import MySQLdb as mdbtry:    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_ciinit-connect='SET NAMES utf8'character-set-server = utf8