sqlalchemy数据库知识大全

sqlalchemy

是一个基于python的orm框架，可以让我们在python中可以使用sql操作数据库

flask 中没有orm框架 都是使用sqlalchemy作为操作数据库表的模块
fastapi 也是使用的sqlchemy


1.安装 pip3.8 install sqlalchemy

# sqlalchemy本身是无法操作数据库的，必须要使用pymysql一起

如何使用sqlalchemy写原生sql

# 第一步：导入
from sqlalchemy import create_engine

# 第二步：生成引擎对象
engine = create_engine(
    "mysql+pymysql://moongod:123@127.0.0.1:3306/FlowProps",
    # 连接mysql用户是moongod密码是123 连接的库名是FlowProps
    max_overflow=2,  # 超过连接池大小外最多创建的连接
    pool_size=10,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 第三步：使用引擎获取连接，操作数据库
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute('select * from app_props')
print(cursor.fetchall())

使用sqlalchemy创建表

# 第一步导入
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime
import datetime

# 第二步 生成基类
Base = declarative_base()

# 第三步 继承基类编写表 和 字段
class User(Base):
    id = Column(Integer, primary_key=True，autoincrement=True)
    name = Column(String(32), index=True, nullable=False)
    # nullable=False 不可以为空
    email = Column(String(36), unique=True)
    ctime = Column(DateTime, default=datetime.datetime.now)
    extra = Column(Text, nullable=True)

    __tablename__ = 'users'


# 第四步 生成数据库连接 首先要自己创好库
engine = create_engine(
    "mysql+pymysql://moongod:123@127.0.0.1:3306/text",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

# 第五步 执行表迁移 创建表  
# 这样数据库就会生成对应的表
Base.metadata.create_all(engine)



# 删除所有表的指令
Base.metadata.drop_all(engine)


 
参数：
primary_key=True  设置主键 主键自带非空唯一自增
Integer         整形
String(32)      字符串类型 最多32位
nullable=False  不可以为空
unique=True     唯一值索

单表的增删改查

增一个,增多个

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import User,Book
# 需要操作的表导过来

engine = create_engine(
    "mysql+pymysql://moongod:123@127.0.0.1:3306/text",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

Session = sessionmaker(bind=engine)
# 创建一个连接

session = Session()
# 连接生成一个对象

user = User(extra='hhh')
# 创建一条user表的数据 需要填写非空字段 nullable=False的字段都需要填写
session.add(user)
# 发送新增数据
session.commit()
# 确定执行
session.close()
# 关闭连接



也可以同时创建多个数据使用.add_all

user = User(name='MOON',phone='1110')
user1 = User(name='clivin',phone='2222')
book = User(name='西游记')

session.add_all([user,user1,book])
# 使用.add_all可以执行多个新增数据操作
# 包括其他表 

session.commit()
# 确定执行

这样就同步一次生成了 2条user表数据 1条book表数据

查

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import User

engine = create_engine(
    "mysql+pymysql://moongod:123@127.0.0.1:3306/text",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

Session = sessionmaker(bind=engine)
# 创建一个连接

session = Session()
# 连接生成一个对象


# .all()方法返回一个列表
user = session.query(User).filter(User.id > 2).all()
# user = session.query(User).filter(User.id == 2).all()
# 选择要查询的表User，filter查询方式 填写条件
# filter 过滤条件，必须写表达式 ==  >= <= != > <
# 如果没有找到则返回空


user = session.query(User).filter_by(id=3).first()
# 直接写等式，等级条件查找精确查找
print(user.name)

改

# 方式一：使用对象进行更改  先拿到需要修改的数据对象
user = session.query(User).filter_by(id=3).first()
user.name = 'alex3'
user_new = User(name='alex1')
# 对象属性进行更改后 再次使用add方法传入即可
# 该方法会自动判断是更新还是新增，根据主键判断，如果你的数据有主键，则是针对该数据修改
# 如果传入的对象没有主键，则数据新增
session.add_all([user_new,user])
session.commit()



# 方式二：直接使用update进行修改
user = session.query(User).filter_by(id=3).update({'name':'alax66'})
session.commit()
#使用 .update方法 传入键值对 更新该数据




字段运算
# 字符串相加
res = session.query(User).filter_by(id=1).update({User.name: User.name + '666'})
print(res)
session.commit()

# 数字的加减
res = session.query(User).filter_by(id=1).update({User.age: User.age + 10}, synchronize_session='evaluate')
session.commit()

删

user = session.query(User).filter_by(id=3).delete()
# 对于查询的数据进行直接删除
print(user)
# 返回值为1 代表删除成功 1是影响了几条数据
# 返回值为0 则代表删除失败，可能是由于没有该数据

session.commit()

scoped_session解决全局安全问题

from flask import Flask
from models import User
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker,scoped_session

engine = create_engine("mysql+pymysql://moongod:123@127.0.0.1:3306/text", )
app = Flask(__name__)
Session = sessionmaker(bind=engine)


db_session = scoped_session(Session)
'''
因为全局都使用同一个Session对象，会有线程并发安全问题
于是需要scoped_session来解决这个问题
db_session = scoped_session(Session) 这样就解决了
这样就可以全局使用这个db_session
'''

@app.route('/home')
def home():
    try:
        user = User(name='home2', email='99991@qq.com')
        db_session.add(user)
        db_session.commit()
    except Exception:
        return '添加失败'
    db_session.close()
    return '添加成功'


if __name__ == '__main__':
    app.run()
    
 

单表高级查询

查询个别字段，二次筛选，之间，in，取反, or， like，分页, 排序，聚合函数

1.只查询个别字段
原生sql：select name email from user;
# 只查询个别字段
res = session.query(User.name,User.email).all()
print(res)
# res 就是结果 [('moon','1122@qq.com'), ('moon', None), ('moon1', None)]


2.使用原生sql进行复杂的查询
select * from user where id <20 or name=moon
# 使用任意sql进行查询
res = session.query(User).filter(text("id<:value or name=:name")).params(value=10, name='moon').all()
# 查询表名是User的表，条件是 id小于10 or name=moon的所有数据



3.自定义查询（了解）
# from_statement 写纯原生sql

res=session.query(User).from_statement(text("SELECT * FROM users where email=:email")).params(email='3@qq.com').all()

# 直接在text中填写原生sql :email是占位符 .params(email='3@qq.com')是给占位符填写参数
# 直接会拿到一个列表 列表中有sql查询的结果


4.and条件连接
res = session.query(User).filter(User.id > 1, User.name == 'lqz099').all() 
# 直接使用,就行 默认是and关系


5.between在什么什么之间的数据
res = session.query(User).filter(User.id.between(1, 9), User.name == 'moon').all()
# 查询id在1-9直接的数据，并且还要满足name='moon'


6.in 在什么什么范围呢 
session.query(User).filter(User.email.in_(['3@qq.com','r@qq.com'])).all()
# 使用.in_查询数据是否符合在 ['3@qq.com','r@qq.com']中



7.取反，id不在那个范围 
session.query(User).filter(~User.id.in_([1,3,4])).all()
# ~ 除了条件之外


8.二次筛选
session.query(User).filter(~User.id.in_(session.query(User.id).filter_by(name='lqz099'))).all()


9.or
from sqlalchemy import or_
session.query(User).filter(or_(User.id ==3,User.name=='moon')).all()
# 查询id是3 或者姓名叫 moon的数据对象


10.通配符 like
res = session.query(User.name).filter(User.name.like('a%')).all()
# 查询user表中 姓名以a开头的数据
print(res)

11.分页
session.query(User)[2*5:2*5+2]
# 一页显示2条，查第5页的数据
# 也就是从第10条 到12条
# 【每页显示条数 * 页数 : 每页显示条数 * 页数 + 每页显示条数】


12.排序，降序desc，升序asc
session.query(Book).order_by(Book.price.desc()).all()
# 按照图书价格降序
session.query(Book).order_by(Book.price.asc()).all()
# 按照图书价格升序
进阶：
session.query(User).order_by(User.name.desc(), User.id.asc())
# 第一个条件重复后，再按第二个条件升序排
# 如果姓名相同 那按照id再排序



13.分组 聚合函数
from sqlalchemy.sql import func

# 分组之后取最大id，id之和，最小id  和分组的字段
res = session.query(
     User.extra,
    # 分组字段
    func.max(User.id),
     func.sum(User.id),
    func.min(User.id)).group_by(User.extra).all()




14. 分组查询后再次二次筛选 having
# select max(id),sum(id),min(id) from  user group by  user.extra   having id_max>2;
res = session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.extra).having(func.max(User.id) > 2)

使用原生sql

## 方式一：
# 第一步：导入
from sqlalchemy import create_engine
# 第二步：生成引擎对象
engine = create_engine(
    "mysql+pymysql://root@127.0.0.1:3306/cnblogs",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 第三步：使用引擎获取连接，操作数据库
conn = engine.raw_connection()
cursor=conn.cursor()
cursor.execute('select * from aritcle')
print(cursor.fetchall())




### 方式二：
from models import User, Book
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/aaa")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

# 2.0.9 版本需要使用text包裹一下，原来版本不需要
# cursor = session.execute(text('select * from users'))
# result = cursor.fetchall()
# print(result)

cursor = session.execute(text('insert into books(name) values(:name)'), params={"name": '红楼梦'})
# execute执行 ：name做占位符 params传参数
session.commit()
print(cursor.lastrowid)
# 拿到新增的数据 id号  insert into原生sql的新增数据

session.close()

django的orm中如何执行原生sql

res = Book.objects.raw('select * from app01_publish where id=1') 


    print(res[0])
    print(type(res[0]))
    print(res[0].name)
    # book 没有addr，但是也打印出来了
    print(res[0].addr)

    return HttpResponse('ok')
  

# 结果是根据sql的执行结果的  比如你用 book对象 执行 查询publish表的数据 也是可以的
# 返回的就是publish表的查询结果

一对多表创建

# 一对一：本身是一个表，拆成两个表，做一对一的关联；；；本质就是一对多，只不过关联字段唯一
# 一对多：关联字段写在多的一方
# 多对多：需要建立中间表；；本质也是一对多



# 一对多关系
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey
from sqlalchemy.orm import relationship

# 第二步：执行declarative_base，得到一个类
Base = declarative_base()


class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    # ForeignKey关联的是指的是tablename的名称 hobby
    # 关联字段写在多的一方，写在Person中，跟hobby表中id字段做外键关联
    hobby_id = Column(Integer, ForeignKey("hobby.id"))

    # relationship跟数据库无关，不会新增字段，只用于快速链表操作
    # 类名，backref用于反向查询
    hobby = relationship('Hobby', backref='person')
    # 如果有hobby对象，拿到所有 hobby.person就可以拿到

    def __repr__(self):
        return self.name


engine = create_engine("mysql+pymysql://moongod:123@127.0.0.1:3306/text", )

# 把表同步到数据库  （把被Base管理的所有表，都创建到数据库）
Base.metadata.create_all(engine)

# 把所有表删除
# Base.metadata.drop_all(engine)

一对多表的新增

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from models1 import Hobby, Person

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/aaa")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)




# 一对多新增 方式一：最基本的方法
hobby = Hobby(caption='乒乓球')
session.add(hobby)
person = Person(name='张三')
session.add(person)
# 先在两个表里都生成这个数据
hobby=session.query(Hobby).filter(Hobby.caption=='乒乓球').first()
# 然后再把这个数据拿出来
person = Person(name='王五',hobby_id=hobby.id)
session.add(person)
# 然后再更新外键关联




方式二：推荐使用
支持按对象的增加方式，必须加relationship 做关联
# 方式一 对表中已有数据做关联
hobby=session.query(Hobby).filter(Hobby.caption=='乒乓球').first()
person = Person(name='moon',hobby=hobby)
session.add_all([person])
session.commit()

# 方式二 表中没有数据 创建数据 然后做关联
hobby = Hobby(caption='羽毛球')  
person = Person(name='moon',hobby=hobby)
session.add_all([person, hobby])
session.commit()


## 基于对象的跨表查询  .
# 正向查询
# person=session.query(Person).filter(Person.name=='王五').first()
# # print(person.hobby_id)
# print(person.hobby)  # Hobby 的对象

# 反向查询
# hobby=session.query(Hobby).filter(Hobby.id==1).first()
# print(hobby.pers)

基于对象的跨表查询

表中需要设置relationship('Hobby', backref='pers')属性

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from models1 import Hobby, Person

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/aaa")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)


# 正向查询
person=session.query(Person).filter(Person.name=='王五').first()
print(person.hobby_id)
print(person.hobby) 
# person.hobby这样就可以直接拿到外键的hobby对象


# 反向查询 用户hobby对象反向获取person的数据
hobby=session.query(Hobby).filter(Hobby.id==1).first()
print(hobby.caption)
print(hobby.person[0].name)
# hobby.person拿到的是一个列表 因为是一对多的关系

多对多表创建

from sqlalchemy import create_engine
import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship

# 第二步：执行declarative_base，得到一个类
Base = declarative_base()



# 中间表 必须手动创建 用第三张表 关联两张表
class Boy2Girl(Base):
    __tablename__ = 'boy2girl'
    id = Column(Integer, primary_key=True)
    girl_id = Column(Integer, ForeignKey('girl.id'))
    boy_id = Column(Integer, ForeignKey('boy.id'))


class Girl(Base):
    __tablename__ = 'girl'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name


class Boy(Base):
    __tablename__ = 'boy'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    girls = relationship('Girl', secondary='boy2girl', backref='boys')
    # 一定要加 在boy或girl表中都可以，
    # 

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name

    
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/aaa", )


# 把表同步到数据库  （把被Base管理的所有表，都创建到数据库

if __name__ == '__main__':
	Base.metadata.create_all(engine)

多对多表数据的新增

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from models2 import Girl, Boy, Boy2Girl

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/aaa")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)


新增数据：纯手动 不推荐
girl=Girl(name='刘亦菲')
boy=Boy(name='彭于晏')
session.add_all([girl,boy])
session.add(Boy2Girl(girl_id=1,boy_id=1))
session.commit()


新增数据：推荐 使用对象快速新增
boy = Boy(name='moon')
boy.girls = [Girl(name='迪丽热巴'), Girl(name='景田')]
session.add(boy)
session.commit()
# 因为relationship在boy表里 所以可以直接这样创建
# 3张表里都会生成对应的数据

多对多的查询

# 基于对象的跨表查询
# 正向
boy = session.query(Boy).filter(Boy.id==2).first()
print(boy.girls)
# 返回的是一个列表
# 通过boy对象直接拿到关联的所有的girls对象

# 反向
girl = session.query(Girl).filter(Girl.id==2).first()
print(girl.boys)
# 返回的是一个列表
# 通过girl对象直接拿到关联的所有的boys对象

基于flask-sqlachemy模块创建表

from flask_sqlalchemy import SQLAlchemy
from src import app


db = SQLAlchemy()
# 生成一个对象
db.init_app(app)
# 注册到app中


'''
接下来的所有都使用db进行操作
'''

class text(db.Model):
    __tablename__ = 'text'
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))
    
    
    
在视图函数中直接使用 db.session就可以进行所有操作了
from models import Book   
@app.route('/')
def index():
    db.session.add(Book(name='xxx'))
    db.session.commit()
    return '增加成功'

在配置文件中 配置 链接的数据库

在flask对象app中注册sqlalchemy的配置

app.config.from_pyfile('settings.py')



SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root@127.0.0.1:3306/text?charset=utf8"
SQLALCHEMY_POOL_SIZE = 5
SQLALCHEMY_POOL_TIMEOUT = 30
SQLALCHEMY_POOL_RECYCLE = -1
# 追踪对象的修改并且发送信号
SQLALCHEMY_TRACK_MODIFICATIONS = False