Python DB-API使用流程:
-
引入API模块
-
获取与数据库的连接
-
执行SQL语句和存储过程
-
关闭数据库连接
一、接口包安装
1）windows环境
下载地址：https://pypi.python.org/pypi/MySQL-python/1.2.5
比如：MySQL-python-1.2.5.win32-py2.7.exe
安装完毕之后查看接口目录：
注：在导入模块时，如果报错，则需要加一下系统环境变量
2）Linux环境
源码下载地址：https://pypi.python.org/pypi/MySQL-python/1.2.5
比如：MySQL-python-1.2.5.zip
安装：
yum install -y python-devel mysql-devel gcc
unzip MySQL-python-1.2.5.zip
cd MySQL-python-1.2.5
python setup.py build & python setup.py install
二、连接数据库
1）首先给mysql建库、授权
create database python;
grant all privileges on *.* to 'root'@'%' identified by '123456';
flush privileges;
2）定义连接方法
MySQLdb提供了与数据库的连接方法，如下：
conn=MySQLdb.connect(host="192.168.2.230",user="root",passwd="123456",db="python",charset="utf8")
为了规范代码，先定义mysql连接信息，将配置信息写入字典中，后期再调用，便于扩展，如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
import MySQLdb
def connect_mysql():
db_config = {
"host": "192.168.2.230",
"port": 3306,
"user": "root",
"passwd": "123456",
"db": "python",
"charset": "utf8"
}
try:
cnx = MySQLdb.connect(**db_config)
except Exception as e:
raise e
return cnx
|
常用参数：
host: 数据库主机名，默认为本地主机
user: 数据库登陆名，默认为当前用户
passwd: 数据库登陆的秘密，默认为空
db: 数据库名，无默认值
port: 数据库端口，默认是3306，数字类型
charset: 数据库编码
三、mysql事务
MySQL中，Innodb引擎的数据库或表才支持事务，它用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
事务必须满足的4个条件：
1）原子性：一组事务，要么成功，要么撤回
2）稳定性：有非法数据（外键约束之类），事务撤回
3）隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100% 隔离，需要牺牲速度
4）可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项决定把事务保存到日志里的时间
1）查看事务自动提交是否开启，默认开启
mysql> show variables like 'auto%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
+--------------------------+-------+
4 rows in set (0.01 sec)
注：autocommit，即开启自动提交；如关闭则每次执行select、insert、date、delete，都需手动提交
2）mysql事务的方法
commit(): 提交当前事务，如果支持事务的数据库执行了增删改后，没有commit则数据库，则默认回滚
rollback(): 取消当前事务
四、游标
游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。用户使用SQL语句逐一从游标中获取记录，赋给主变量，交由python进一步处理，一组主变量一次只能存放一条记录。
特点：
提供了一种对表中检索出的数据进行操作的灵活手段
总是与一条SQL 选择语句相关联，因为它由结果集和结果集中指向特定记录的游标位置组成
当决定对结果集进行处理时，必须声明一个指向该结果集的游标
常用方法:
cursor(): 创建游标对象
close(): 关闭游标对象
fetchone(): 得到结果集的下一行
fetchmany([size = cursor.arraysize]): 得到结果集的下几行
fetchall(): 得到结果集中剩下的所有行
excute(sql[, args]): 执行一个数据库查询或命令
executemany (sql, args): 执行多个数据库查询或命令
五、mysql操作数据
例子1：连接操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
import MySQLdb
def connect_mysql():
db_config = {
"host": "192.168.2.230",
"port": 3306,
"user": "root",
"passwd": "123456",
"db": "python",
"charset": "utf8"
}
try:
cnx = MySQLdb.connect(**db_config)
except Exception as e:
raise e
return cnx
if __name__ == "__main__":
sql = "create table test(id int not null);insert into test(id) values(100);"
cnx = connect_mysql()
cus = cnx.cursor()
try:
cus.execute(sql)
cus.close()
cnx.commit()
except Exception as e:
raise e
cnx.rollback()
finally:
cnx.close()
|
结果：
mysql> show tables;
+------------------+
| Tables_in_python |
+------------------+
| test |
+------------------+
1 row in set (0.00 sec)
mysql> select * from test;
+-----+
| id |
+-----+
| 100 |
+-----+
1 row in set (0.08 sec)
例子2：游标操作
1）创建数据表employees
use python
create table employees (
emp_no int not null auto_increment,
emp_name varchar(16) not null,
gender enum('M', 'F') not null,
hire_date date not null,
primary key (emp_no)
);
2）插入数据
insert into employees(emp_no, emp_name, gender, hire_date) values(01, 'huangzhenping', 'M', '2017-11-21');
insert into employees(emp_no, emp_name, gender, hire_date) values(02, 'zhaoxiang', 'M', '2016-11-10');
insert into employees(emp_no, emp_name, gender, hire_date) values(03, 'lichao', 'M', '2016-08-20');
3）查看数据
mysql> select * from employees;
+--------+---------------+--------+------------+
| emp_no | emp_name | gender | hire_date |
+--------+---------------+--------+------------+
| 1 | huangzhenping | M | 2017-11-21 |
| 2 | zhaoxiang | M | 2016-11-10 |
| 3 | lichao | M | 2016-08-20 |
+--------+---------------+--------+------------+
3 rows in set (0.00 sec)
4）游标操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
import MySQLdb
def connect_mysql():
db_config = {
"host": "192.168.2.230",
"port": 3306,
"user": "root",
"passwd": "123456",
"db": "python",
"charset": "utf8"
}
try:
cnx = MySQLdb.connect(**db_config)
except Exception as e:
raise e
return cnx
if __name__ == "__main__":
sql = "select * from employees;"
cnx = connect_mysql()
cus = cnx.cursor()
try:
cus.execute(sql)
result1 = cus.fetchone()
print("result1:")
print(result1)
result2 = cus.fetchmany(1)
print("result2:")
print(result2)
result3 = cus.fetchall()
print("result3:")
print(result3)
cus.close()
cnx.commit()
except Exception as e:
print("error")
cnx.rollback()
raise e
finally:
cnx.close()
|
结果：
result1:
(1L, u'huangzhenping', u'M', datetime.date(2017, 11, 21))
result2:
((2L, u'zhaoxiang', u'M', datetime.date(2016, 11, 10)),)
result3:
((3L, u'lichao', u'M', datetime.date(2016, 8, 20)),)
六、mysql连接池
每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的
python的数据库连接池包 DBUtils：
DBUtils是一套Python数据库连接池包，并允许对非线程安全的数据库接口进行线程安全包装。
DBUtils提供两种外部接口：
* PersistentDB ：提供线程专用的数据库连接，并自动管理连接。
* PooledDB ：提供线程间可共享的数据库连接，并自动管理连接。
1）下载接口包
下载地址：https://pypi.python.org/pypi/DBUtils/
如：DBUtils-1.2.tar.gz
2）安装
tar -zxvf DBUtils-1.2.tar.gz
cd DBUtils-1.2
python setup.py install
注： 或者 pip install DBUtils安装
3）连接池操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
import MySQLdb
from DBUtils.PooledDB import PooledDB
db_config = {
"host": "192.168.2.230",
"port": 3306,
"user": "root",
"passwd": "123456",
"db": "python",
"charset": "utf8"
}
pool = PooledDB(MySQLdb, 5, **db_config)
conn = pool.connection()
cur = conn.cursor()
SQL = "select * from employees;"
r = cur.execute(SQL)
r = cur.fetchall()
print(r)
cur.close()
conn.close()
|
结果：
((1L, u'huangzhenping', u'M', datetime.date(2017, 11, 21)), (2L, u'zhaoxiang', u'M', datetime.date(2016, 11, 10)), (3L, u'lichao', u'M', datetime.date(2016, 8, 20)))
