python 十三 PyMySQL模块、SQLAIchemy基础、SQLAIchemy进阶

最新推荐文章于 2024-07-01 21:10:22 发布

尘埃落定2000

最新推荐文章于 2024-07-01 21:10:22 发布

阅读量370

点赞数

文章标签： python 十三

本文链接：https://blog.csdn.net/weixin_41072205/article/details/95875358

版权

一、PyMySQL模块
pypi 即python package index，是python语言的软件仓库，官方站点为https://pypi.python.org
1.1 安装依赖包
]# yum -y install gcc
1.2 本地安装
#pip3 install PyMySQL-0.8.0.tsr.gz

1.3 在线安装
#pip3 install pymysql
配置pip安装时,采用国内镜像站点加快下载：
#mkdir ~/.pip/
#vim ~/.pip/pip.conf
[global]
index-url=http://pypi.douban.com/simple/
[install]
truted-host=pypi.douban.com
1.4 安装mariadb-server
]# yum -y install mariadb-server
]# mysqladmin password tedu.cn
1.5 规划数据库tedu.cn
1）确定字段
姓名、性别、出生日期、部门、职位、联系方式、基本工资、奖金、工资总和、发工资日期。

2）分析字段，（数据库的范式，百度查）
1，根据第一范式，要把一部分字段进一步拆分
所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
联系方式继续拆分成：电话号码、Email
2、只有满足了1NF才能进行到2NF）
在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
员工表：员工编号、姓名、性别、出生日期、部门ID、电话号码、email
部门表：部门ID、部门名称
工资表：auto_id、员工编号、日期、基本工资、奖金、工资总和
3、3NF:在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
工资总和是基本工资加奖金、所以不能出现在表中

3）创建数据库
1、建库
]# mysql -uroot -ptedu.cn
[(none)]> show database
[(none)]> CREATE DATABASE tedu DEFAULT CHARSET ‘utf8’;
[(none)]> USE tedu;
2、创建部门表
[tedu]> create table departments(dep_id INT PRIMARY KEY,dep_name VARCHAR(20));
3、创建员工表
[tedu]> CREATE TABLE employees(emp_id INT PRIMARY KEY,enmp_name VARCHAR(20) NOT NULL,birth_date DATE,phone CHAR(11),email VARCHAR(50),dep_id INT, FOREIGN KEY(dep_id)REFERENCES departments(dep_id));
4、创建工资表
[tedu]> CREATE TABLE salary(auto_id INT AUTO_INCREMENT PRIMARY KEY,date DATE,emp_id INT,basic INT,awards INT,FOREIGN KEY(emp_id) REFERENCES employees(emp_id));
[tedu]> show tables;

4）连接数据库（pycharm上操作创建insert_data.py）
1、创建连接是访问数据库的第一步：
import pymysql
conn = pymysql.connect(
host=‘127.0.0.1’,
port=3306,
user=‘root’,
passwd=‘tedu.cn’,
db=‘tedu’,
charset=‘utf8’
)
2、游标 cursor 就是游动的标识
（一条sql取出来对应n条结果资源的接口/句柄，就是游标，沿着游标可以一次取出一行）
cursor = conn.cursor() # 创建游标
3、插入数据
对数据库表做修改操作，必须要commit
insert1 = “INSERT INTO departments(dep_id, dep_name) VALUES(%s, %s)”
result = cursor.execute(insert1, (1, ‘人事部’))
conn.commit()
cursor.close()
conn.close()
[tedu]> select * from departments; #查询表内容
]# python3 insert_data.py
[tedu]> select * from departments; #数据已经插入了。
创建inset_data2.py ,复制insert_data.py内容，去掉result行，然后在insert1下添加：
insert1 = “INSERT INTO departments(dep_id, dep_name) VALUES(%s, %s)”
data = [(2, ‘运维部’), (3, ‘开发部’)]
cursor.executemany(insert1, data)
[tedu]> select * from departments; #查询一下

4、用python语句查询数据
可以取出表中、多条、或者全部记录。
创建select_data.py(连接数据库、创建游标、以及关闭游标、关闭连接跟insert_data.py都是一样的。）
import pymysql
conn = pymysql.connect(
host=‘127.0.0.1’,
port=3306,
user=‘root’,
passwd=‘tedu.cn’,
db=‘tedu’,
charset=‘utf8’
)
cursor = conn.cursor() # 创建游标
select1 = “select * from departments”
cursor.execute(select1)
result = cursor.fetchone()
print(result)
print(’-’ * 30)
result = cursor.fetchmany(2)
print(result)
print(’-’ * 30)
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()
]# python3 select_data.py
5、移动游标
默认是相对当前位置移动。
import pymysql
conn = pymysql.connect(
host=‘127.0.0.1’,
port=3306,
user=‘root’,
passwd=‘tedu.cn’,
db=‘tedu’,
charset=‘utf8’
)
cursor = conn.cursor() # 创建游标
select1 = “select * from departments”
cursor.execute(select1)
cursor.scroll(2) # 默认相对当前位置移动
print(cursor.fetchall())
print(’-’ * 30)
cursor.scroll(0, mode=‘absolute’)
print(cursor.fetchall())
cursor.close()
conn.close()
6、修改数据
import pymysql
conn = pymysql.connect(
host=‘127.0.0.1’,
port=3306,
user=‘root’,
passwd=‘tedu.cn’,
db=‘tedu’,
charset=‘utf8’
)
cursor = conn.cursor() # 创建游标
update1 = “UPDATE departments SET dep_name=%s WHERE dep_name=%s” ###修改数据
cursor.execute(update1, (‘人力资源部’, ‘人事部’))
delete1 = ‘DELETE FROM departments WHERE dep_id=%s’ ##删除数据
cursor.execute(delete1, 3)
conn.commit()
cursor.close()
conn.close()

二、SQLAIchemy基础
2.1 安装 SQLAIchemy
]# pip3 install sqlalchemy

SQLAIchemy概述：
SQLAIchemy 是python编程语下的一款开源软件。提供SQL工具包及对象关系映射（ORM）工具，使用MIT许可证发行。
sqlAlchemy是python中最著名的ORM(Object Relationship Mapping)框架。
什么是ORM？——ORM即对象关系映射，一种可以把model中的模型和数据库中的一条数据相互转换的工具。
SQLAlchemy的理念是，SQL数据库的量级和性能重要于对象集合；而对象集合的抽象又重要于表和行。SQLAlchemy 的一个目标是提供能兼容众多数据库（如 SQLite、MySQL、Postgres、Oracle、MS-SQL、SQLServer 和 Firebird）的企业级持久性模型。

2.2 数据库对象管理
1）为sqlalchemy创建数据库
MariaDB [tedu]> CREATE DATABASE tarena DEFAULT CHARSET ‘utf8’;

2）连接mysql
通过create_engine 实现数据库连接
——初始化数据库连接：
create_engine()函数中的字符串的意义是，数据库+数据库连接框架://用户名:密码@IP地址:端口号/数据库名称。

3）声明映射
当使用ORM的时候，配置过程从描述数据库开始，通过自定义类映射相应的表，通过声明系统实现类映射。
首先通过声明系统，定义基类（父类）；
创建映射类，就可以创建自定义映射类；
通过表的映射类，在数据库中创建表；
from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.ext.declarative import declarative_base
#创建连接到数据库的引擎
engine =create_engine(
‘mysql+pymysql://root:tedu.cn@localhost/tarena?charset=utf8’, （‘’？chrset=utf8，没有这个中文会报错）
encoding=‘utf8’,
echo=True
)
Base = declarative_base() #生成ORM影射所需的基类

class Departments(Base): #必须继承于Base
tablename = ‘departments’ #库中的表名
#每个属性都是一个表中的一个字段，是类属性
dep_id = Column(Integer,primary_key=True)
dep_name = Column(String(20),nullable=False,unique=True)
def str(self):
return ‘[部门ID:%s,部门名称：%s]’%(self.dep_id,self.dep_name)

if name == ‘main’:
#在数据库中创建表，如果库中已有同名的表，将不会创建
Base.metadata.create_all(engine)
]# python3 new05.py
[tedu]> use tarena;
[tarena]> show tables;
[tarena]> show create table departments;

创建映射类的实例
创建实例时，并不会真正在表中添加记录。
创建会话类，ORM访问数据库的句柄被称作Session。会话类的实例对象用于绑定到数据库，实例化类的对象，并不打开任何连接，当实例化初次使用，它将从Engine维护的连接池中获取一个连接。当所有的事务均被commit或会话对象被关闭时，连接结束。
session =Session()
session.add(dep_dev)
session.commit()
print(str(dep_dev.dep_id))
session.close()
可以创建多个实例，批量添加记录，如果有中文件，注意engine的参数。
from dbconn import Departments,Session
hr = Departments(dep_id=1,dep_name=‘hr’)
ops = Departments(dep_id=2,dep_name=‘operations’)
dev = Departments(dep_id=3,dep_name=‘development’)
finance =Departments(dep_id=4,dep_name=‘财务部’)
deps = [ops,dev]
session =Session()
session.add(hr)
session.add_all(deps)
session.add(finance)
session.commit()
session.close()
]# python3 add_department.py
[tarena]> select * from departments;

5）创建员工表、工资表，使用外键关联
rom sqlalchemy import create_engine, Column, String, Integer, ForeignKey, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

#创建连接到数据库的引擎
engine = create_engine(
‘mysql+pymysql://root:tedu.cn@localhost/tarena?charset=utf8’,
encoding=‘utf8’,
echo=True
)
Base = declarative_base() # 生成ORM映射所需的基类
Session = sessionmaker(bind=engine)
class Departments(Base): # 必须继承于Base
tablename = ‘departments’ # 库中的表名
# 每个属性都是表中的一个字段，是类属性
dep_id = Column(Integer, primary_key=True)
dep_name = Column(String(20), nullable=False, unique=True)
def str(self):
return ‘[部门ID：%s, 部门名称：%s]’ % (self.dep_id, self.dep_name)
class Employees(Base):
tablename = ‘employees’
emp_id = Column(Integer, primary_key=True)
name = Column(String(20), nullable=False)
gender = Column(String(6))
phone = Column(String(11))
email = Column(String(50))
dep_id = Column(Integer, ForeignKey(‘departments.dep_id’))
def str(self):
return ‘员工：%s’ % self.name
class Salary(Base):
tablename = ‘salary’
auto_id = Column(Integer, primary_key=True)
date = Column(Date)
emp_id = Column(Integer, ForeignKey(‘employees.emp_id’))
basic = Column(Integer)
awards = Column(Integer)
if name == ‘main’:
# 在数据库中创建表，如果库中已有同名的表，将不会创建
Base.metadata.create_all(engine)
_________________________________________________________
添加表记录，创建映射类的实例
from dbconn import Employees, Session
ltd = Employees(emp_id=, name=‘李通达’, gender=‘男’, phone=‘13378904567’, email=‘ltd@163.com’, dep_id=2)
wxy = Employees(emp_id=2, name=‘王秀燕’, gender=‘女’, phone=‘15098765432’, email=‘wxy@tedu.cn’, dep_id=2)
gq = Employees(emp_id=3, name=‘高琦’, gender=‘女’, phone=‘15876543212’, email=‘gq@tarena.com’, dep_id=1)
wzf = Employees(emp_id=4, name=‘王召飞’, gender=‘男’, phone=‘15609871234’, email=‘wzf@sohu.com’, dep_id=1)
emps = [ltd, wxy, gq, wzf]
session = Session()
session.add_all(emps)
session.commit()
session.close()

from dbconn import Salary, Session
jan2018_1 = Salary(date=‘2018-01-10’,emp_id=4,basic=11000,awards=3000)
jan2018_2 = Salary(date=‘2018-01-10’,emp_id=1,basic=13000,awards=2000)
jan2018_3 = Salary(date=‘2018-01-10’,emp_id=6,basic=15000,awards=3000)
jan2018_4 = Salary(date=‘2018-01-10’,emp_id=7,basic=9000,awards=3000)
session = Session()
sals = [jan2018_1, jan2018_2, jan2018_3,jan2018_4]
session.add_all(sals)
session.commit()
session.close()

基本查询
通过作用于session的query()函数创建查询对象，query（）函数可以接收多种参数。
通过order_by() 函数可以实现指定字段排序
from dbconn import Departments, Employees, Salary, Session
session = Session()
qset = session.query(Departments).order_by(Departments.dep_id)
print(qset) # qset此时只是一条SQL语句
for dep in qset: # 向qset取值时，得到一个个实例
print("%s: %s" % (dep.dep_id, dep.dep_name))
session.close()

使用ORM描述符进行查询
使用ORM描述符进行查询
返回值是元组。
qset = session.query(Employees.name, Employees.phone)
print(qset)
for name, phone in qset: # qset执行后返回的是元组
print(’%s: %s’ % (name, phone))

8）使用命名元组
查询对象返回的是一个命名元组
名称是类的名字，或是类中的属性的名字。

修改显示字段名
显示的字段名可以通过label() 函数进行修改。
qset = session.query(Departments.dep_name.label(‘部门’))
print(qset)
for row in qset:
print(row.部门)

提取部分数据
通过“切片”的方式，实现部分数据的提取
qset = session.query(Employees.name, Employees.email).
order_by(Employees.emp_id)[2:4]
print(qset) # qset因为切片的原因，已经是元组组成的列表了，不再是SQL语句
11）结果过滤
通过filter() 函数实现结果过滤
qset = session.query(Employees.name).
filter(Employees.dep_id2).filter(Employees.gender’女’)
print(qset)
for name in qset:
print(name)

常用的过滤操作符
相等：
**query.filter(Employees.name == 'john')**
不相等
**query.filter(Employees.name!='john')**
模糊查询
**query.filter(Employees.name.like('%j'))**  #数据库里面 %匹配多个字符 ，下划线_ 匹配一个字符

qset = session.query(Employees.name).
filter(Employees.name.like(‘王%’))
print(qset)
for name in qset:
print(name)
qset = session.query(Employees.name).
filter(Employees.name.in_([‘吴伟超’, ‘李通达’, ‘董枝俊’])) ## in
print(qset)
for name in qset:
print(name)
qset = session.query(Employees.name).
filter(~Employees.name.in_([‘吴伟超’, ‘李通达’, ‘董枝俊’])) ## 波浪线 ~ 表示取反，即为not in
print(qset)
for name in qset:
print(name)

filter(Employees.name.is(None)) ####字段为空
filter(Employees.name.isnot(None)) ####字段不为空

多重条件 and、多重条件or
from sqlalchemy import and_, or_ ##需要导入功能
filter(and_(Employees.dep_id2,Employees.gender’女’))
filter(or_(Employees.dep_id2, Employees.gender’女’))

查询对象返回值
all() 返回列表
first() 返回结果中的第一条记录
one() 取出所有的记录，如果不是一条记录则抛出异常
scalar() 调用one()，返回第一列的值。
qset = session.query(Employees.name, Employees.phone)
print(qset.all()) # 返回列表
print(qset.first()) # 返回满足条件的第一个值
qset = session.query(Employees.name, Employees.phone).
filter(Employees.emp_id==1)
print(qset.one()) # 查询必须只有一项，否则报错
print(qset.scalar()) # 调用one，返回第一列的值

聚会
通过count() 方法，统计行数
qset = session.query(Employees)
print(qset.count())
即等同于MariaDB [tarena]> select count(*) from employees;

多表查询
通过join() 方法实现多表查询
qset = session.query(Employees.name, Departments.dep_name).
join(Departments, Employees.dep_id==Departments.dep_id)
print(qset.all())
类似MariaDB
[tarena]> select e.name,d.dep_name from employees as e join departments as d where e.dep_id =d.dep_id ;

更新数据
通过会话的update()方法更新，或者通过会话的字段赋值更新
from dbconn import Departments, Session
session = Session()
ops = session.query(Departments).filter(Departments.dep_id==2)
ops.update({Departments.dep_name: ‘运维部’})
dev = session.query(Departments).get(3) # 返回主键是3的条目
dev.dep_name = ‘开发部’
session.commit()
session.close()

删除记录：delete
xz = session.query(Departments).get(5)
session.delete(xz)