python mysql数据库编程

来自个人博客：http://ningning.today/2014/12/19/python/python-mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BC%96%E7%A8%8B/

安装mysql和MySQLdb模块

安装mysql数据库:

sudo apt-get install mysql-server

安装python MySQLdb模块:

sudo apt-get install python-mysqldb

配置mysql

首先我们建立一个新的数据库用户和数据库。

$ mysql -u root -p

mysql> SHOW DATABASES;

显示数据库信息:

+——————————+

| Database |

+——————————+

| information_schema |

| mysql |

+——————————+

建立一个数据库testdb用来实验:

mysql> CREATE DATABASE testdb

Query OK, 1 row affected (0.02 sec)

mysql> CREATE USER ‘estuser‘@‘localhost‘ IDENTIFIED BY ‘test623‘

使用testdb数据库:

mysql> USE testdb;

更改权限:

mysql> GRANT ALL ON testdb.* TO ‘testuser‘@‘localhost‘;

退出:

mysql> quit;

MySQLdb模块操作

0. 获取数据库版本(version.py)

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb
import sys

try:

    #连接数据库函数connect(host, username, password, dbname)
    con = mdb.connect(‘localhost‘, ‘testuser‘, ‘test623‘, ‘testdb‘);

    #cursor对象遍历记录
    cur = con.cursor()

    #execute()函数执行sql语句
    cur.execute("SELECT VERSION()")

    #fetchone()获取一个记录
    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()

执行

python version.py

或者

chmod a+x version.py

./version

可以看到输出mysql版本号:

MySQL version: 5.5.40-0ubuntu0.14.04.1

1. 创建和更新数据库表(create_add.py)

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb

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

 #python解释器碰到with关键字会自动释放资源和错误处理

with con:

    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS Writers")

    #创建作者表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‘)")

执行python create_add.py然后进入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)

2. 检索数据(retrieve.py)

#!/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")

    #fetchall()得到所有记录，返回一个二维tuple，每个inner tuples代表数据库表的一行
    rows = cur.fetchall()

    for row in rows:
        print row
```

执行`python retrieve.py`:

(1L, ‘Jack London‘)
(2L, ‘Honore de Balzac‘)
(3L, ‘Lion Feuchtwanger‘)
(4L, ‘Emile Zola‘)
(5L, ‘Truman Capote‘)

3. 字典游标(dictcur.py)

之前cursor返回的是一个tuple的tuple，也可以使用列名作为字典的下标获得数据

#!/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"]

执行下看看:

python ./dictcur.py

1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola

4. 获取列信息Column headerscolumnheaders.py)

#!/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()

    #cursor对象的description返回一个查询的列信息
    desc = cur.description

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

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

python columnheaders.py

Id Name
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

5. 预处理语句Prepared statements(prepared.py)

使用占位符而不是直接输出值

#!/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

python ./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)

6. 事务支持Transaction support

一个事务Transaction是指在一个或多个数据库间的原子操作，在一个Transaction中的数据库语句可以全部提交(commit())或者回滚(rollback())。

注意老版本的mysql使用的存储引擎是MyISAM(不支持Transaction)，从MySQL5.5以后使用InnoDB作为默认存储引擎，在安全性和速度上做了权衡。

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb
import sys

try:
    con = mdb.connect(‘localhost‘, ‘testuser‘, ‘test623‘, ‘testdb‘)
    # A transaction is started when the cursor is created.
    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‘)")

    #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.
    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()

实际上之前的代码示例已经通过with语句使用了Transaction而没有显示声明它。with语句适用于对资源进行访问的场合，确保不管使用过程中是否发生异常都会执行必要的“清理”操作，释放资源，比如文件使用后自动关闭、线程中锁的自动获取和释放等。在这里with自动commits()或者rollback()，很方便。

Reference

MySQL Python tutorial
浅谈 Python 的 with 语句