Python DB-API使用流程:
引入API模块
获取与数据库的连接
执行SQL语句和存储过程
关闭数据库连接
一、接口包安装
1)windows环境
比如:MySQL-python-1.2.5.win32-py2.7.exe
安装完毕之后查看接口目录:
<a href="https://s3.51cto.com/oss/201711/20/f5abcc3c5a42df05dfff11bc5830b636.png" target="_blank"></a>
注:在导入模块时,如果报错,则需要加一下系统环境变量
<a href="https://s4.51cto.com/oss/201711/20/8a4ff2f12336284694ffb672f58e934d.png" target="_blank"></a>
2)Linux环境
安装:
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连接信息,将配置信息写入字典中,后期再调用,便于扩展,如下:
<code>import</code> <code>MySQLdb</code>
<code>def</code> <code>connect_mysql():</code>
<code> </code><code>db_config </code><code>=</code> <code>{</code>
<code> </code><code>"host"</code><code>: </code><code>"192.168.2.230"</code><code>,</code>
<code> </code><code>"port"</code><code>: </code><code>3306</code><code>,</code>
<code> </code><code>"user"</code><code>: </code><code>"root"</code><code>,</code>
<code> </code><code>"passwd"</code><code>: </code><code>"123456"</code><code>,</code>
<code> </code><code>"db"</code><code>: </code><code>"python"</code><code>,</code>
<code> </code><code>"charset"</code><code>: </code><code>"utf8"</code>
<code> </code><code>}</code>
<code> </code><code>try</code><code>:</code>
<code> </code><code>cnx </code><code>=</code> <code>MySQLdb.connect(</code><code>*</code><code>*</code><code>db_config)</code>
<code> </code><code>except</code> <code>Exception as e:</code>
<code> </code><code>raise</code> <code>e</code>
<code> </code><code>return</code> <code>cnx</code>
常用参数:
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:连接操作
<code>if</code> <code>__name__ </code><code>=</code><code>=</code> <code>"__main__"</code><code>: </code><code># __name__为主函数</code>
<code> </code><code>sql </code><code>=</code> <code>"create table test(id int not null);insert into test(id) values(100);"</code> <code># mysql语句</code>
<code> </code><code>cnx </code><code>=</code> <code>connect_mysql() </code><code># 创建mysql连接</code>
<code> </code><code>cus </code><code>=</code> <code>cnx.cursor() </code><code># 创建一个游标对象</code>
<code> </code><code>cus.execute(sql) </code><code># 执行mysql连接和语句</code>
<code> </code><code>cus.close() </code><code># 关闭游标对象</code>
<code> </code><code>cnx.commit() </code><code># 提交事务,否则执行的语句会回滚,从而不生效</code>
<code> </code><code>except</code> <code>Exception as e: </code><code># 如报错,则抛出,并将操作回滚</code>
<code> </code><code>raise</code> <code>e </code>
<code> </code><code>cnx.rollback()</code>
<code> </code><code>finally</code><code>:</code>
<code> </code><code>cnx.close() </code><code># 关闭连接</code>
结果:
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)游标操作
<code>if</code> <code>__name__ </code><code>=</code><code>=</code> <code>"__main__"</code><code>:</code>
<code> </code><code>sql </code><code>=</code> <code>"select * from employees;"</code>
<code> </code><code>cnx </code><code>=</code> <code>connect_mysql()</code>
<code> </code><code>cus </code><code>=</code> <code>cnx.cursor()</code>
<code> </code><code>cus.execute(sql)</code>
<code> </code><code>result1 </code><code>=</code> <code>cus.fetchone()</code>
<code> </code><code>print</code><code>(</code><code>"result1:"</code><code>)</code>
<code> </code><code>print</code><code>(result1)</code>
<code> </code><code>result2 </code><code>=</code> <code>cus.fetchmany(</code><code>1</code><code>)</code>
<code> </code><code>print</code><code>(</code><code>"result2:"</code><code>)</code>
<code> </code><code>print</code><code>(result2)</code>
<code> </code><code>result3 </code><code>=</code> <code>cus.fetchall()</code>
<code> </code><code>print</code><code>(</code><code>"result3:"</code><code>)</code>
<code> </code><code>print</code><code>(result3)</code>
<code> </code><code>cus.close()</code>
<code> </code><code>cnx.commit()</code>
<code> </code><code>print</code><code>(</code><code>"error"</code><code>)</code>
<code> </code><code>cnx.close()</code>
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的性能会产生较大的影响。因此,实际使用中,通常会使用数据库的连接池技术,来访问数据库达到资源复用的目的
<a href="https://s5.51cto.com/oss/201711/21/8c5d576129edc404d85777252074855a.png" target="_blank"></a>
python的数据库连接池包 DBUtils:
DBUtils是一套Python数据库连接池包,并允许对非线程安全的数据库接口进行线程安全包装。
DBUtils提供两种外部接口:
* PersistentDB :提供线程专用的数据库连接,并自动管理连接。
* PooledDB :提供线程间可共享的数据库连接,并自动管理连接。
1)下载接口包
如: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)连接池操作
<code>from</code> <code>DBUtils.PooledDB </code><code>import</code> <code>PooledDB</code>
<code>db_config </code><code>=</code> <code>{</code>
<code>"host"</code><code>: </code><code>"192.168.2.230"</code><code>,</code>
<code>"port"</code><code>: </code><code>3306</code><code>,</code>
<code>"user"</code><code>: </code><code>"root"</code><code>,</code>
<code>"passwd"</code><code>: </code><code>"123456"</code><code>,</code>
<code>"db"</code><code>: </code><code>"python"</code><code>,</code>
<code>"charset"</code><code>: </code><code>"utf8"</code>
<code>}</code>
<code>pool </code><code>=</code> <code>PooledDB(MySQLdb, </code><code>5</code><code>, </code><code>*</code><code>*</code><code>db_config) </code><code># 连接池最小连接数为5</code>
<code>conn </code><code>=</code> <code>pool.connection() </code><code># 定义一个连接池连接,方便后面调用 </code>
<code>cur </code><code>=</code> <code>conn.cursor()</code>
<code>SQL </code><code>=</code> <code>"select * from employees;"</code>
<code>r </code><code>=</code> <code>cur.execute(SQL)</code>
<code>r </code><code>=</code> <code>cur.fetchall()</code>
<code>print</code><code>(r)</code>
<code>cur.close()</code>
<code>conn.close()</code>
((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)))
本文转自 huangzp168 51CTO博客,原文链接:http://blog.51cto.com/huangzp/1983608,如需转载请自行联系原作者