Python学习

第二十八天

Python与MySQL交互(二)

1、准备工作、建表、添加数据

#部门表
create table Department(
id int,
name varchar(20) 
);
#员工表
create table Employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
# 添加数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200);

注意：dep_id 这里并没有直接加foreign key约束 只是逻辑外键。

2、执行SQL查询

这里我们以多表查询-查询员工以及对应的部门信息

2.1、创建连接

import pymysql
#创建连接
db = pymysql.connect(host='localhost',port=3306,user='root',
                     passwd='root',database='python_db')

#对数据的增删改查操作在这里

# 关闭连接
db.close()

其中各个参数:
1、host、连接的MySQL主机，若是本地则为localhost；
2、port、连接的MySQL主机端口，默认3306；
3、user、连接的用户名；
4、passwd、连接的密码；
5、database、数据库名字；
6、charset、编码方式，建议utf-8

2.2、编写SQL语句

查询员工信息以及对应的部门名称。

select a.`name`,a.sex,a.age,b.`name` from employee a inner join department b on a.dep_id = b.id

2.3、创建游标、执行查询

cursor = db.cursor()
sql = 'select a.`name`,a.sex,a.age,b.`name` from employee a inner join department b on a.dep_id = b.id'
cursor.execute(sql)
data = cursor.fetchall()
if data:
    for row in data:
        name = row[0]
        sex = '男' if row[1] == 'male' else '女'
        age = row[2]
        dname = row[3]+'部'
        print('姓名:%s,性别:%s,年龄:%d,部门:%s' %(name,sex,age,dname))

游标常用的方法：
1、fetchone(): 该方法获取下一个查询结果集。结果集是一个对象；
2、fetchall():接收全部的返回结果集；
3、rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数；

2.4、关闭连接、资源释放

cursor.close()
db.close()

3、事务

我们对数据库进行增加、删除、修改操作的时候，在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。commit()方法游标的所有更新操作，rollback（）方法回滚当前游标的所有操作。删除为例：

#删除
import pymysql
#创建连接
db = pymysql.connect(host='localhost',port=3306,user='root',
                     passwd='root',database='python_db')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
#创建SQL语句 cursor.execute执行
sql = 'delete from Student where id = %d' %(3)
try:
    #执行SQL语句
    cursor.execute(sql)
    # 提交执行
    db.commit()
except:
    #发生异常回滚
    db.rollback()
# 关闭游标与数据库连接
cursor.close()
db.close()

4、游标cursor

python数据库编程中，cursor是一个实现了迭代器和生成器的MySQLdb对象，初建游标的时候cursor中还没有数据，只有等到fetchone()或fetchall()的时候才返回一个元组tuple，才支持len()和index()操作，这也是它是迭代器的原因。但同时为什么说它是生成器呢？因为cursor只能用一次，即每用完一次之后记录其位置，等到下次再取的时候是从游标处再取而不是从头再来，而且fetch完所有的数据之后，这个cursor将不再有使用价值了，即不再能fetch到数据了。