Python 操作 MySQL数据库

孤独雪鹰

一、安装 MySQL
　　可以直接从MySQL官方网站下载最新版本。MySQL是跨平台的，选择对应的平台下载安装文件，安装即可。 如果是Windows用户，那么安装过程非常简单，直接根据向导一步一步操作即可。 如果是 Linux 用户，安装过程也是相当简单的。
## Ubuntu / Debian　　
$ sudo apt-get install mysql-server
　　
$ sudo apt-get install mysql-client
　　

　　
## CentOS / RHEL
　　
# yum install -y mysql mysql-server mysql-devel
　　基础设置：
## 启动 mysql　　
# service mysqld start
　　

　　
## 初始化
　　
# /usr/bin/mysql_secure_installation
　　

　　
## 编辑 my.cnf ，修改默认编码设置
　　
# vi /etc/my.cnf
　　
[client]
　　
default-character-set = utf8
　　

　　
[mysqld]
　　
default-storage-engine = INNODB
　　
character-set-server = utf8
　　
collation-server = utf8_general_ci
　　

　　
## 验证
　　
# service mysqld restart
　　
# mysql -u root -p
　　

　　
mysql> show variables like '%char%';
　　
+--------------------------+----------------------------+
　　
| Variable_name            | Value                      |
　　
+--------------------------+----------------------------+
　　
| character_set_client     | utf8                       |
　　
| character_set_connection | utf8                       |
　　
| character_set_database   | utf8                       |
　　
| character_set_filesystem | binary                     |
　　
| character_set_results    | utf8                       |
　　
| character_set_server     | utf8                       |
　　
| character_set_system     | utf8                       |
　　
| character_sets_dir       | /usr/share/mysql/charsets/ |
　　
+--------------------------+----------------------------+
　　
8 rows in set (0.00 sec)
二、安装 MySQL 驱动
　　由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以，需要支持Python的MySQL驱动来连接到MySQL服务器。
　　目前，有两个MySQL驱动：

• 　　mysql-connector-python：是MySQL官方的纯Python驱动；
  
• 　　MySQL-python：是封装了MySQL C驱动的Python驱动。
  

　　MySQL-python

　　MySQL-3.23 through 5.5 and Python-2.4 through 2.7 are currently supported. Python-3.0 will be supported in a future>　　方式一：（推荐）
# pip install MySQL-python　　方式二：（推荐）
## Ubuntu　　
$ sudo apt-get install python-mysqldb
　　

　　
## CentOS
　　
# yum install -y MySQL-python
　　方式三：
　　下载地址： https://pypi.python.org/pypi/MySQL-python/
　　下载完成MySQL-python-1.2.5.zip 文件之后直接解压。进入MySQL-python-1.2.5目录：
# unzip MySQL-python-1.2.5.zip　　
# cd MySQL-python-1.2.5
　　
# python setup.py install
　　
　　测试是否安装成功
　　测试非常简单，检查 MySQLdb 模块是否可以正常导入即可。
[root@localhost ~]# python　　
Python 2.6.6 (r266:84292, Jul 23 2015, 15:22:56)
　　
[GCC 4.4.7 20120313 (Red Hat 4.4.7-11)] on linux2
　　
Type "help", "copyright", "credits" or "license" for more information.
　　
>>> import MySQLdb
　　没有报错提示MySQLdb模块找不到，说明安装OK 。
三、数据库基本操作回顾
mysql> show databases;                 // 查看当前所有的数据库　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| mysql              |
　　
+--------------------+
　　
2 rows in set (0.15 sec)
　　

　　
mysql> CREATE DATABASE test;            // 创建数据库
　　
Query OK, 1 row affected (0.17 sec)
　　

　　
mysql> use test;
　　
Database changed
　　
mysql> show tables;                     // 查看数据库表
　　
Empty set (0.00 sec)
　　

　　
// 创建名为 user 的表
　　
mysql> CREATE TABLE user
　　
(
　　
    id INT UNSIGNED AUTO_INCREMENT,
　　
    name VARCHAR(20),
　　
    password VARCHAR(20),
　　
    PRIMARY KEY(id)
　　
) engine=innodb default
　　
Query OK, 0 rows affected (0.16 sec)
　　

　　
// 向表中插入几条数据
　　
mysql> INSERT INTO user(name, password) VALUES('Tom', '1321'),('Alen', '7875'),('Jack', '6759');
　　
Query OK, 3 rows affected (0.07 sec)
　　
Records: 3  Duplicates: 0  Warnings: 0
　　

　　
// 查看 user 表数据
　　
mysql> SELECT * FROM user;
　　
+----+------+----------+
　　
| id | name | password |
　　
+----+------+----------+
　　
|  1 | Tom  | 1321     |
　　
|  2 | Alen | 7875     |
　　
|  3 | Jack | 6759     |
　　
+----+------+----------+
　　
3 rows in set (0.13 sec)
四、Python 操作数据库
　　先来个简单的示例程序：
# -*- coding: utf-8 -*-　　

　　
import MySQLdb
　　

　　
try:
　　
    conn = MySQLdb.connect(
　　
        host = 'localhost',
　　
        user = 'root',
　　
        passwd = '123456',
　　
        db = 'test',
　　
        port = 3306
　　
        )
　　
    cur = conn.cursor()
　　

　　
    # 执行SQL
　　
    cur.execute ("""
　　
    CREATE TABLE python
　　
    (
　　
        ID INT,
　　
        COL1 VARCHAR(40),
　　
        COL2 VARCHAR(40),
　　
        COL3 VARCHAR(40)
　　
    )
　　
    """)
　　

　　
    cur.execute ("""
　　
    INSERT INTO TEST (ID, COL1, COL2, COL3)
　　
    VALUES
　　
        (1, 'a', 'b', 'c'),
　　
        (2, 'aa', 'bb', 'cc'),
　　
        (3, 'aaa', 'bbb', 'ccc')
　　
    """)
　　

　　
    cur.execute('select * from python')
　　

　　
    cur.close()
　　
    conn.commit()
　　
    conn.close()
　　
except MySQLdb.Error,e:
　　
     print "Mysql Error %d: %s" % (e.args[0], e.args[1])
　　请注意修改你的数据库，主机名，用户名，密码。 连接数据库时要注意数据库编码，不然一堆编码问题很愁人的～最好统一使用utf8编码。为了避免乱码，可修改一个参数：
　　conn = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test') 中加一个属性：
　　改为：
　　conn = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test', charset='utf8')
　　charset是要跟你数据库的编码一样，如果是数据库是gb2312 ,则写charset='gb2312'。
　　1、增
　　
# -*- coding: utf-8 -*-　　

　　
import MySQLdb
　　

　　
conn = MySQLdb.connect(
　　host = 'localhost',
　　port = 3306,
　　user = 'root',
　　passwd = '123456',
　　db = 'test',
　　charset = 'utf8'
　　
)
　　

　　
cur = conn.cursor()
　　

　　
cur.execute("INSERT INTO user(name, password) VALUES('python', '8901')")
　　

　　
cur.close()
　　
conn.commit()
　　
conn.close()
　　通过上面execute()方法中写入纯的sql语句来插入数据并不方便。如：
　　>>> cur.execute("insert into user(name, password)  values('python', '8901')")
　　我要想插入新的数据，必须要对这条语句中的值做修改。我们可以做如下修改：
# -*- coding: utf-8 -*-　　

　　
import MySQLdb
　　

　　
conn = MySQLdb.connect(
　　host = 'localhost',
　　port = 3306,
　　user = 'root',
　　passwd = '123456',
　　db = 'test',
　　charset = 'utf8'
　　
)
　　

　　
cur = conn.cursor()
　　

　　
sqli = "INSERT INTO user(name, password) VALUES(%s, %s)"
　　
cur.execute(sqli, ('chrome', '1903'))
　　

　　
cur.close()
　　
conn.commit()
　　
conn.close()
　　executemany()方法可以一次插入多条记录
sqli = "INSERT INTO user(name, password) VALUES(%s, %s)"　　
cur.executemany(sqli, [
　　
        ('IE', '1021'),
　　
        ('Firefox', '9012'),
　　
        ('windows', '6213')
　　
    ]
　　
)
　　最佳实践：
　　如果是插入数据，要执行commit操作，不然数据不会被写入数据库。最好是执行完所有的sql语句之后再commit，实践证明，这样会带来很大的速度提升。
　　cur.execute(sqli, (value1, value2))  , 如果value的值存在不确定性，那么上面两条语句存在sql注入风险。
　　例如，如果value是一段html代码，html代码中如果存在引号（单引号或者双引号），如果不加处理，那么sql语句就将被截断，将会引发插入错误。
　　如果value中存在英文逗号，将会造成前面的key和后面的value不对应，也不能正确插入数据。
　　如果value中存在反斜线 \，那么在python中反斜线后面的数据将被转义，如果不处理插入的话将会造成字符丢失或者其他不可遇见的结果。
　　因为在 SQL 中和在 Python 中有好多含有特殊含义的符号，如果不能很好的处理的话将会出现好多问题，幸好MYSQLdb模块给我们提供了一个escape_string方法，用这个方法能自动处理好上面的问题，特别方便。
value = MySQLdb.escape_string(value)     # 该函数阻止 python字符串以及SQL转义　　2、查
　　也许你已经尝试了在python中通过
　　>>>cur.execute("select  *  from  user")
　　来查询数据表中的数据，但它并没有把表中的数据打印出来。
　　来看看这条语句获得的是什么
　　>>>aa=cur.execute("select * from user")
　　>>>print aa
　　5
　　它获得的只是我们的表中有多少条数据。那怎样才能获得表中的数据呢？
# -*- coding: utf-8 -*-　　

　　
import MySQLdb
　　

　　
conn = MySQLdb.connect(
　　host = 'localhost',
　　port = 3306,
　　user = 'root',
　　passwd = '123456',
　　db = 'test',
　　charset = 'utf8'
　　
)
　　

　　
cur = conn.cursor()
　　

　　
# 获取表中共有多少条记录
　　
sqls = "SELECT * FROM user"
　　
num = cur.execute(sqls)
　　
print num
　　

　　
# 打印
　　
info = cur.fetchmany(num)
　　
for line in info:
　　print line
　　

　　
cur.close()
　　
conn.commit()
　　
conn.close()
　　查询通常有两种方式：
　　一种是使用cursor.fetchall（）获取所有查询结果，然后再一行一行的迭代；
　　另一种每次通过cursor.fetchone()获取一条记录，直到获取的结果为空为止。看一下下面的例子：
import MySQLdb　　

　　
try:
　　
    conn = MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306)
　　
    cur = conn.cursor()
　　

　　
    conn.select_db('test')
　　

　　
    sqls = "SELECT id, name, password FROM user"
　　
    cur.execute(sqls)
　　
    rows = cur.fetchall()
　　
    for row in rows:
　　print '%s, %s, %s' % (row[0], row[1], row[2])
　　
    print 'Number of rows returned: %s' % (cur.rowcount)
　　

　　
    ##############
　　

　　
    sqls = "SELECT id, name, password FROM user"
　　
    cur.execute(sqls)
　　
    while True:
　　
        row = cur.fetchone()
　　
        if row == None:
　　
                break
　　
        print '%s, %s, %s' % (row[0], row[1], row[2])
　　

　　
    print 'Number of rows returned: %s' % (cur.rowcount)
　　

　　
    #############
　　

　　
    cur.scroll(0,mode = 'absolute')    # 移动游标
　　
    results = cur.fetchmany(5)
　　
    for r in results:
　　
        print r
　　

　　
    conn.commit()
　　
    cur.close()
　　
    conn.close()
　　

　　
except MySQLdb.Error,e:
　　
     print "Mysql Error %d: %s" % (e.args[0], e.args[1])
　　下面贴一下常用的函数：
　　conn连接对象也提供了对事务操作的支持,标准的方法
　　commit() 提交
　　rollback() 回滚
　　cursor用来执行命令的方法:
　　callproc(self, procname, args):用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数
　　execute(self, query, args):执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数
　　executemany(self, query, args):执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数
　　nextset(self):移动到下一个结果集
　　cursor用来接收返回值的方法:
　　fetchall(self):接收全部的返回结果行.

　　fetchmany(self,>　　fetchone(self):返回一条结果行.
　　scroll(self, value, mode='relative'):移动指针到某一行.如果mode='relative',则表示从当前所在行移动value条,如果 mode='absolute',则表示从结果集的第一行移动value条.