数据库
ORM
ORM全称 Object Relational Mapping对象关系映射
通过 ORM 可以不用关心后台是使用的哪种数据库，只需要按照 ORM 所提供的语法规则去书写相应的代码， ORM 就会自动的转换成对应对应数据库的 SQL 语句
SQLAlchemy
安装： mysql
安装python包： pymysql、sqlalchemy
pip install -i https://pypi.douban.com/simple pymysql
pip install -i https://pypi.douban.com/simple sqlalchemy
1 连接数据库(connect.py)
from sqlalchemy import create_engine # 连接数据库的方法
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'mydb'
USERNAME = 'admin'
PASSWORD = 'Root110qwe'
Db_Uri = 'mysql+pymysql://{}:{}@{}/{}?charset=utf8'.format(
USERNAME,PASSWORD,HOSTNAME,DATABASE) # 连接数据库的uri
# mysql+pymysql://{用户名}:{密码}@{host}:{port}/{数据库}?charset=utf8
engine = create_engine(Db_Uri) # 连接数据库
if __name__=='__main__':
connection = engine.connect() # 类似与pymysql中的定义游标
result = connection.execute('select 1') print(result.fetchone())
```
2 数据库建模<br>
对象关系型映射，数据库中的表与python中的类相对应<br>
在connect.py生成一个基类
```python
"""
在connet.py文件里面
"""
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(engine) # 生成Base类 用于建模
```
建模(modules.py)
```python
from connect import Base
from sqlalchemy import Column, Integer, String, DateTime
# __tablename__ 表名
# Column 创建字段
# Interger 数据库中的int
# String 字符串类型
# DateTime 时间类型
# Boolean 布尔型
calss User(Base):
"""
建立表模型，相当于在数据库中建立一张表
注意：必须继承在connet.py文件里面生成的Base类
字段名 = Column(数据类型, 约束条件)
"""
__tablename__ = "user"
id = Column(Interger, primary_key=True, autoincrement=True)
username = Column(String(10))
password = Column(String(20))
def __repr__(self):
result = "<User(id={id}, username={username}, password={password})>".format(id=self.id, username=self.username, password=self.password)
if __name__ = "__nmain__"
Base.metadata.create_all() # 创建表
```
3 sqlalchemy对数据库的增删查改<br>
在connect.py文件里添加以下代码
```python
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session() # 生成会话类，用于操纵数据
```
数据增删改查(test_user.py)
```python
from connect import session
from modules import User
def add_user():
"""
添加数据 add() add_all([])
默认开启事务
session.add(要添加的数据) # 添加一条数据
session.commit() # 提交事务
session.add_all(要添加数据的列表)
session.commit()
"""
person = User(username='XXX', password='****')
session.add(person) # 添加一条数据
session.add_all(
[
User(username='XXX', password='***'),
User(username='XXX', password='***'),
]) # 添加多条数据
session.commit() # 提交事务
def query_user():
"""
查看数据query()
session.query(表模型)
"""
result = session.query(User).all() # 查看所有数据，
# 返回的是对象，重写User的__repr__方法重构输出内容
print(result)
result = session.query(User).first() # 查看第一条数据
def update_user():
"""
更新数据 update({})
filter(条件) 返回一个序列类型对象。本身为原生sql语句
session.query(表模型).filter(条件).update(修改数据[字典格式])
session.commit()
"""
session.query(User).filter(User.username=="XXX").update({User.username: 'xxx'})
session.commit()
def delete_user():
"""
删除数据 delete()
result = session.query(表模型).fiter(条件)[第几条数据]
session.delete(result)
session.commit()
"""
result = session.query(User).filter(User.username=="XXX").first()
session.delete(result)
session.commit()
if __name__ == "__main__":
add_user()
```
## 查询精讲
1 结果查询<br>
[1] 整表查询<br>
i) 查看所有数据<br>
```python
session.query(表模型).all()
```
ii) 查看第一条数据<br>
```python
1. session.query(表模型).first()
2. session.query(表模型)[0]
```
iii) 两种方式可以取到具体的数据值
```python
1. getattr(rs[第几条数据], 'username')
2. rs[第几条数据].username
```
[2] 查询某个表的字段<br>
i) 查看所有数据<br>
```python
session.query(表模型.字段).all()
```
ii) 查看第一条数据<br>
```python
1. session.query(表模型.字段).first()
2. session.query(表模型.字段)[0]
```
2 条件查询
[1] 过滤函数 <br>
i) filter
```python
session.query(表模型).filter(条件1, 条件2)
session.query(User).filter(User.username=='budong').all()
```
filter 是一个过滤函数，过滤条件都可以书写在此函数中，不同的条件之间用 逗号 分隔
ii) filter_by <br>
```python
session.query(表模型).filter_by(条件)
# 例如：session.query(User).filter_by(username='budong').all()
```
filter 和 filter_by 过滤函数，但是使用有如下差别：
1. filter 中需要添加 类对象，filter_by不需要
2. filter_by 中只能添加等于的条件，不能添加 不等于、大于小于等条件，filter没有这个限制
[2] 模糊查询 [在filter里面使用] <br>
i) like 和 notlike <br>
例如：<br>
```python
session.query(User).filter(User.username.like('杜%'))
session.query(User).filter(User.username.notlike('杜%'))
```
ii) is_ 和 isnot <br>
例如：<br>
```python
session.query(User).filter(User.username.is_(None))<br>
session.query(User).filter(User.username.isnot(None))
```
iii) in_ 和 notin
例如：<br>
```python
session.query(User).filter(User.username.in_(['XXX', 'xxx']))<br>
session.query(User).filter(User.username.notin(['XXX', 'xxx']))
```
[3] 查询结果数<br>
i) 限制查看几条数据 limit<br>
```python
session.query(表模型).limit(限制查看数)
```
ii) 偏移几条数据查看 offset<br>
```python
session.query(表模型).offset(偏移量)
```
iii) 从左下标开始查看(右下标-左下标)个数据 slice<br>
```python
session.query(表模型).slice(左下标, 右下标)
```
iv) 查一条数据 one<br>
```python
session.query(表模型).filter(条件).one()
# <br>注意: 只能查看一条数据，如果有多条数据，报错
```
[4] 排序 order_by<br>
```python
session.query(表模型).order_by(用于排序的字段)
# 例如
session.query(User).order_by(User.id)
'''注意: order_by默认排序为顺序排列'''
# 倒序排列：
from sqlalchemy import desc<br>
session.query(User).order_by(desc(User.id)).all() <br>
"""注意：倒序排列时要导入 desc 才能使用"""
```
[5] 函数<br>
==**注意：**== 使用函数是必须先导入函数
```python
from sqlalchemy import func
```
i) func.count() 统计相同值出现的次数<br> group_by() 分组 <br> having()
```python
session.query(表模型.字段名,func.count(表模型.被统计字段)).group_by(表模型.字段名).all()
"""
注意：
如果查询的什么字段，那么分组的也必须为被查询字段
"""
session.query(表模型.字段名,func.count(表模型.被统计字段)).group_by(表模型.字段).having(条件).all()
```
ii) func.min() 求最小值<br>
```python
session.query(User.password, func.min(User.id)).group_by(User.password).all()
"""
查询usr表里面的password并按照password分组，在找出每个分组中id的最小值
"""
```
iii) func.max() 求最大值<br>
```python
session.query(User.password, func.max(User.id)).group_by(User.password).all()
"""
查询usr表里面的password并按照password分组，在找出每个分组中id的最大值
"""
```
iv) func.sum() 求和 <br>
```python
session.query(User.password, func.sum(User.id)).group_by(User.password).all()
"""
查询usr表里面的password并按照password分组，再对每个分组中的id求和
"""
```
v) extract 提取<br> label 取别名
```python
from sqlalchemy import extract # 注意使用时要导入
session.query(extract('minute',User.creatime).label('minute'), func.count(User.id)).group_by('minute').all()
```
[6] 选择条件<br>
```python
from sqlalchemy import or_
session.query(User.username).filter(or_(User.username.isnot(None), User.password=='qwe123')).all()
"""
查询出user表中username不为空的或者password为‘qwe123’的数据
"""
```
[7] 多表查询
i) 笛卡尔链接(cross join)<br>
例如：
```python
session.query(UserDetails, User).filter(UserDetails.id==User.id).all()
"""
查询出user_details表与user表中id相等的数据
"""
```
ii) 内连接(inner join)<br>
例如：
```python
session.query(User.username, UserDetails.lost_login).join(UserDetails,UserDetails.id==User.id)
```
iii) 外连接(outerjoin)<br>
```python
session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id==User.id)
# 通过上面打印的 SQL 来看， 上面这种方式采用的是left join
```
iv) union
```python
q1 = session.query(User.id)
q2 = session.query(UserDetails.id)
q1.union(q2).all()
"""
去重
"""
```
iv) 子查询
```python
sql_0 = session.query(UserDetails).subquery() # subquery 声明子表
session.query(User, sql_0.c.lost_login).all()
```
3 表关系<br>
i) 一对一<br>
relationship("被关联的表模型", backref="在被关联表中添加一个属性", uselist="默认一对多[True] 一对一[False]", cascade='自动处理关系[常用"all"]')
在user_modules中进行如下修改<br>
```python
# 导入
from sqlalchemy.orm import relationship
# 在 UserDetails 中添加如下代码：
userdetail = relationship('User',backref='details',uselist=False,cascade='all')
```
查询
```python
from connect import session
from user_modules import User
from user_modules import UserDetails
rs = session.query(UserDetails).get(2)
print(rs)
print(rs.userdetail)
rs = session.query(User).get(2)
print(rs)
print(rs.details)
```
ii) 多对多<br>
relationship('表模型', backref='在被关联表模型中添加属性', secondary=中间表表名)
创建中间表
```python
# 首先导入Table用于创建中间表
from sqlalchemy import Table
# 中间表
user_article = Table('user_article', Base.metadata,
Column('user_id', Integer, ForeignKey('user.id'), primary_key=True),
Column('article_id', Integer, ForeignKey('article.id'), primary_key=True) # 联合主键
)
class Article(Base):
__tablename__ = 'article'
id = Column(Integer, primary_key=True, autoincrement=True)
content = Column(String(500), nullable=True)
create_time = Column(DateTime, default=datetime.now)
article_user = relationship('User', backref='article', secondary=user_article) # 表关系 多对多
def __repr__(self):
return 'Article(id=%s, content=%s, creat_time=%s)' % (self.id,
self.content,
self.create_time
)
```