sqlalchemy

 SQLalchemy 是python语言下的一款ORM框架,该框架建立在数据库API上,使用关系对象映射对数据库进行操作

面试关键 我们通常都是通过类生成数据库,

可是如果有数据库怎么生成对应的类呢? 

可以用下面这个代码

python manage.py inspectdb

生成的代码在屏幕中

安装

pip3 install SQLAlchemy

需要注意了：SQLAlchemy 自己无法操作数据库，必须结合 pymsql 等第三方插件，Dialect 用于和数据 API 进行交互，根据配置文件的不同调用不同的数据库 API，从而实现对数据库的操作，如：

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
   
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
   
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
   
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
   
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

 

注：SQLAlchemy无法修改表结构，如果需要可以使用SQLAlchemy开发者开源的另外一个软件Alembic来完成

1执行原生sql

sqlalchemy使用的原因

你可能会问我们为什么要用sqlalchemy执行原生sql我们直接用pymysql执行原生SQL不就完了,这里你忽略了一个重要的信息

sqlalchemy 还提供了连接池功能，SQLAlchemy提供了一个称为create_engine的函数来创建数据库连接，并可以配置连接池来管理连接的创建和重用

 

执行原生sql语句

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

注意:写原生sql的时候一定要用连接池,你可以用SQLAlchemy的连接池,也可以用DBUtiles +pymysql的连接池

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
  
  
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (name) VALUES ('小红')"
# )
  
# 新插入行自增ID
# cur.lastrowid
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
  
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
#     host='1.1.1.99', color_id=3
# )
  
# 执行SQL
# cur = engine.execute('select * from hosts')
# 获取第一行数据
# cur.fetchone()
# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()

 

2 使用orm执行语句

创建表

 

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import  declarative_base
#生成一个sqlorm基类,创建表必须继承它
Base=declarative_base()

class Student(Base): #这个student并不是一个表的名字,只是一个类名
    "定义类"
    __tablename__="students8"#这个才是你创建表的名字
    id= Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(30),)
    def __str__(self):
        return self.name
#创建数据库引擎，惰性连接
engine=create_engine("mysql+pymysql://root:123456@localhost:3306/oldboy?charset=utf8") #注意utf8这里没有引号,有引号,会报错,
#创建表#注意这里只用一次,用完要立刻注释掉
Base.metadata.create_all(engine)

注意默认值这里有坑：

default不生效，要使用server_default，但是server_default对于布尔值不生效可以这样设置

from sqlalchemy import Column, Integer, String, Numeric, TEXT, Boolean, SmallInteger,text


class Student(Base):
    __tablename__ = "tb_student"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255))
    sex = Column(Boolean)
    age = Column(SmallInteger)
    class_name = Column("class", String(255))
    description = Column(TEXT)
    is_delete = Column(Boolean, server_default=text('False'))  #这里是关键

    def __repr__(self):
        return f"<{self.name} {self.class_name.__name__}>"

 

 

 

删除表

#删除表

Base.metadata.drop_all(engine)

 

添加数据

from sqlalchemy_demo import Student,engine
from sqlalchemy.orm import sessionmaker
#创建会话工厂，用来绑定数据库引擎
Session=sessionmaker(bind=engine)
#创建会话实例
session=Session()
obj1=Student(name="alex")
obj2=Student(name="小花")
#添加单条数据
session.add(obj1)
#添加多条数据
session.add_all([obj1,obj2])
#提交到数据库
session.commit()

一个会话实例并不等同于一个 MySQL 连接。在 SQLAlchemy 中，会话（Session）是建立在数据库连接之上的抽象层，用于执行数据库操作和管理对象的状态。

通常情况下，会话会使用一个底层的 MySQL 连接来执行数据库操作。当您创建一个会话实例时，会话会自动获取一个连接（从连接池中或者根据配置建立新连接），并在会话的生命周期内重复使用该连接。

但是，会话的职责不仅仅局限于一个 MySQL 连接。会话还负责管理事务、缓存查询结果、跟踪对象的变化等。它提供了一种高级的接口来执行数据库操作，隐藏了底层连接的复杂性，并提供了更高级的功能，如事务管理、对象关联等。

一个会话实例可以执行多个数据库操作，这些操作可能涉及多个 MySQL 连接。当您调用会话的方法来执行数据库操作时，会话会负责从连接池获取连接，执行操作，然后将连接返回到连接池中供下一次使用。

因此，一个会话实例可以重复使用多个连接，而不是与一个连接绑定。连接的具体分配和释放是由 SQLAlchemy 的连接池管理机制来处理的，会话对象可以透明地使用这些连接，而无需直接操作连接。

总结起来，一个会话实例是建立在一个或多个 MySQL 连接之上的抽象层，用于执行数据库操作和管理对象的状态。它不是一个直接对应一个连接的概念，而是提供了更高级的功能和接口来处理数据库交互。

 

删除数据

#删除
session.query(Student).filter(Student.id>1).delete()
#提交到数据库
session.commit()

改数据

#改动
#第一种方式 session.query(Student).filter(Student.id==1).update({'name':"alex123"}) #这里是一个字典,且修改数据不能用get查询
#第二种方式

session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False) #synchronize_session=False 表示是字符串的拼接
#第三种方式 

session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")# synchronize_session="evaluate" 表示内部可以进行计算

#提交到数据库
session.commit()

查询数据

 

import models
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine,text
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/oldboy?charset=utf8")
Session=sessionmaker(bind=engine)
session=Session()
#查询
#第一 select * from classes
result=session.query(models.Classes).all() #得到列表对象
# 第二select name from classes
result1=session.query(models.Classes.name.label('name1')).all() #得到列表套元组对象可以用.属性[('全栈1期099',), ('全栈2期099',)], label 给name起别名相当于as
#第三种 filter传入的是一个表达式
r3 = session.query(models.Classes).filter(models.Classes.name == "全栈2期099").all()
#第四种 filter_by 传入的才是参数,
r4 = session.query(models.Classes).filter_by(name='全栈2期099').all()
#第五种 传查第一个
r5 = session.query(models.Classes).filter_by(name='全栈2期099').first()
#第六种 动态传参,把条件放到text中,但是这个text要导入,把字符串中要传入的参数放到params参数中.
from sqlalchemy import text
r6 = session.query(models.Classes).filter(text("id>:value and name=:name")).params(value=1, name='全栈2期').order_by(models.Classes.id).all()  #查询里面如果有动态传参的时候
把它包在text里面，
:value,:name这样的是占位符相当于%s,后面用.params来进行传值


#第7种:使用SQL字符串语句查询 ,把sql的字符串语句放到text中,

r7 = session.query(models.Classes).from_statement(text("SELECT * FROM classes where name=:name")).params(name='全栈2期').all() 

for item in r7:
    print(item.name)

#7子查询语句
result = session.query(Class).filter(Class.id in (session.query(Class.id).filter_by(name= 'alex'))).all()

session.close()

 

 跨表查询

models.py

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

Base=declarative_base()

class Classes(Base):
    __tablename__="classes"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(32),nullable=False,unique=True)

class Student(Base):
    __tablename__='student'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(32), nullable=False, index=True) #index普通索引 加速查找的作用
    password = Column(String(64), nullable=False)
    ctime=Column(DateTime,default=datetime.datetime.now)#这里不加括号,加括号会出现大问题,数据库中创建新的信息的时间还是刚开始运行程序的时间
    #####一对多
    class_id=Column(Integer,ForeignKey("classes.id")) #注意这里连接的不是类,而是表名.id
    cls=relationship("Classes",backref='stus') #这列不影响数据库表,不添加例,只帮助我们做关联查询,backref 就是我们反向查询时要点的名字,这个relation要重新导入
    # 多对多
    hobbys = relationship('Hobby', secondary='student2hobby', backref='hobby_to_student')  # secondary用来指定第三张表的名字

　　
class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')
   # 多对多
    students = relationship('Student', secondary='student2hobby', backref='student_to_hobby')

####多对多的第三张表,需要自己写,
class Student2Hobby(Base):
    __tablename__ = 'student2hobby'
    id = Column(Integer, primary_key=True, autoincrement=True)
    student_id = Column(Integer, ForeignKey('student.id'))
    hobby_id = Column(Integer, ForeignKey('hobby.id'))
　　
    ###联合唯一索引
    __table_args__ = (
        UniqueConstraint('student_id', 'hobby_id', name='uix_student_id_hobby_id'),
        ##普通联合索引
        # Index('ix_id_name', 'name', 'extra'),
    )

def init_db():
    # 数据库连接相关
    engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/oldboy?charset=utf8")
    # 创建表
    Base.metadata.create_all(engine)
def drop_db():
    engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/oldboy?charset=utf8")
    # 删除表
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    # drop_db()
    init_db()

models.py

 

注意:Student表的一对多的class_id字段,你不能用这个字段找到班级的名称,只能用这个字段找到班级的id,因为,这个字段是interger类型,它不像django中的orm似的可以点出班级的name,

解决这个问题的方法:

1. 用子查询,即查询里边套查询,方法太low了
2. 用连表查询join

   1. objs = session.query(Student).join(Classes,isouter=True).all() #isouter表示外连接,相当于left join 
      
      for obj in objs:
          print (obj.id,obj.class_id.name)
      
      #你就可以查class的所有的内容了

       

3. 在表中添加一个伪字段cls,该字段用relationship函数,这个字段并不会出现在我们的表结构中,只是为了帮助我们连表查询和反向查询,实例见上边的数据库表,和下边的方法三和反向查询

跨表查询所在的班级名称

#方法一:
import models

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/oldboy?charset=utf8")
Session=sessionmaker(bind=engine)
session=Session()

obj=session.query(models.Student).filter(models.Student.username=="李白").first()
obj2=session.query(models.Classes).filter(models.Classes.id==obj.class_id).first()
print(obj.username,obj2.name)

 

#方法二连表查询

objs=session.query(models.Student.username,models.Classes.name).filter(models.Student.username=="李白").join(models.Classes,isouter=True).all()#isouter 外链接,
相当于leftjoin
for obj in objs:
  print(obj.username,obj.name)

 

#方法三添加字段relationship
 objs=session.query(models.Student).all()
 for item in objs:
     print(item.id,item.username,item.cls.name)

 

反向查询:

#反向查询 全栈1期099对应的学生名字
obj=session.query(models.Classes).filter(models.Classes.name=="全栈1期099").first()
student_objs=obj.stus #得到学生的列表对象
for student_obj in student_objs:
    print(student_obj.username)

 relationship属性

出现的背景: 在不用子查询和连表查询的情况下,外键字段不能用于连表查询,这里并不能像Django一样通过外键可以直接查到外键关联的对象,这在sqlalchemy中是办不到的,为了解决这个问题,为了使连表查询更方便,出现了relationship这个字段,这个字段并不会在数据库中出现只是为了方便连表查询罢了.

作用:

1. 连表查询,有了relationship就可以用外键字段做连表查询了,即点属性了
2. 反向查询,通过backref属性,同时back_populates和backref作用是一样的,只不过,官方想用back_populates来代替backref,但是backref一直能用.
3. 添加数据,这样说可能比较难以理解,举个例子.
1. 　　当是一对多的情况下

   我们有两张表,一张book表,另一张publish表, 一对多的关系,
   现在我们有这么一个情况,要添加一本新书,但是这本新书的出版社并没有在publish表中,那么我们怎么才能在添加新书的同时,也添加新的出版社呢,relationship提供了这么一个方法
   
   #假设relationship对应的字段名为publish_re
   
   session.add(Book(name="江湖",publish_re =Publish('title' = "上海出版社"))
   
   session.commit 
   
   #这样我们就可以了

　　　　　　2.当是多对多的情况下:

　　　　　　

　　　　　　　　　　class User2Hobby(Base):
                    __tablename__ = 'user2hobby'
                    id = Column(Integer, primary_key=True, autoincrement=True)
                    hobby_id = Column(Integer, ForeignKey('hobby.id'))
                    user_id = Column(Integer, ForeignKey('user.id'))


                class Hobby(Base):
                    __tablename__ = 'hobby'
                    id = Column(Integer, primary_key=True)
                    title = Column(String(64), unique=True, nullable=False)

                    # 与生成表结构无关，仅用于查询方便
                    users = relationship('User', secondary='user2hobby', backref='hbs') #secondary 用于连接第三张表


                class User(Base):
                    __tablename__ = 'user'

                    id = Column(Integer, primary_key=True, autoincrement=True)
                    name = Column(String(64), unique=True, nullable=False)

我们要添加数据通常我们这样做

    session.add(Hobby(title='篮球'))
    session.commit()
                    
    session.add(User(name='小红'))
    session.commit()
    #第三张表            
    session.add(User2Hobby(hobby_id=1,user_id=1))
    session.commit()
                    

有了relationship我们这样添加

                    #正向添加用字段
                    obj = Hobby(title='篮球')
                    obj.users = [User(name='小王'),User(name='小红')]
                    session.add(obj)
                    session.commit()
                    
                    #反向添加,用backref
                    obj = User(title='俊杰')
                    obj.hbs = [Hobby(title='篮球'),Hobby(title='足球')]
                    session.add(obj)

 我们可以这样查询数据:

obj1 = session.query(Student).filter_by(username='张飞').first()
for i in obj1.hobbys:
    print(i.caption)

在relationship中还有一个属性需要我们特别的注意:

lazy: 这个属性决定了当查询该外键关联的属性是以什么方式进行加载出来.主要有以下三个值:

#1.select  当访问该属性的时候直接加载出来,生成一个列表.不访问它就不会加载到内存中.
#2.dynamic  动态加载属性,生成的是一个APPenderQuery对象,你可以使用方法把它里边的内容取出来
# 3. noload  永不加载该属性,当访问该属性时,就是一个空值

其中lazy=dynamic 这里要注意一下,该属性不能作用在一对一和一对多的字段,可以用在多对多的字段,在一对一和多对一的字段中应该这样用

classes = relationship("Classes", backref=backref("student_back", lazy='dynamic'))

在多对多中可以这样写

hobbys = relationship('Hobby', secondary='student2hobby', backref='hobby_to_student',
                          lazy='dynamic')

# 查询
obj1 = session.query(Student).filter_by(username='张飞').first()
print(type(obj1.hobbys),obj1.hobbys.first().caption) #这里不能用点操作,要用方法把值取出来
#结果: <class 'sqlalchemy.orm.dynamic.AppenderQuery'>    篮球

 

默认 lazy = select

obj1 = session.query(Student).filter_by(username='张飞').first()
print(type(obj1.hobbys))

#结果

<class 'sqlalchemy.orm.collections.InstrumentedList'>

 

常用操作

#　条件
ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
# and ,or 
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() #and 
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all() #%匹配所有,_匹配一个字符
ret = session.query(Users).filter(~Users.name.like('e%')).all() #非

# 限制
ret = session.query(Users)[1:2] #分页

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all() #默认找外键,默认是inner join

ret = session.query(Person).join(Favor, isouter=True).all()  #left join 没有right join


# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all() # 自动去重

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all() #保留,不去重

常用操作

 

SqlAlchemy两种创建session(sqlalchemy)的方式

主要考虑在多线程的情况下

当需要并发的创建session的时候:

方式1: 此方法注意一定再把session创建在每个线程内, 如果你把session = xxxx() 创建了函数外边,程序就会报错

报错原因是共用一个session,一个线程执行完会关闭session,其他线程就不能再用session了.

import models
from threading import Thread
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine =create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s8day128db?charset=utf8",pool_size=2,max_overflow=0)
XXXXXX = sessionmaker(bind=engine)

def task():
    from sqlalchemy.orm.session import Session
    session = XXXXXX() 

    data = session.query(models.Classes).all()
    print(data)

    session.close()

for i in range(10):
    t = Thread(target=task)
    t.start()

方式二: 

其实本质上还是在用threding.local 来保存隔离每个线程的session,推荐方式二:flask_sqlalchemy组件默认用的这种方式

import models
from threading import Thread
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session

engine =create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s8day128db?charset=utf8",pool_size=2,max_overflow=0)
XXXXXX = sessionmaker(bind=engine)
# 原来：session=XXXXXX()

"""
源码分析
session = scoped_session对象 {
    session_factory = XXXXXX,
    registry = ThreadLocalRegistry{
        createfunc=XXXXXX, 
        registry=threading.local()
    }
}

class  scoped_session:
    def add(self, *args, **kwargs):
        return getattr(self.registry(), 'add')(*args, **kwargs)
        
    def add_all(self, *args, **kwargs):
        return getattr(self.registry(), 'add_all')(*args, **kwargs)
    
    def commit(self, *args, **kwargs):
        return getattr(self.registry(), 'commit')(*args, **kwargs)
        
    def query(self, *args, **kwargs):
        return getattr(self.registry(), 'query')(*args, **kwargs)
"""
session = scoped_session(XXXXXX)
def task():

    # 1. 原来的session对象 = 执行session.registry()
    # 2. 原来session对象.query
    data = session.query(models.Classes).all()
    print(data)
    session.remove()


for i in range(10):
    t = Thread(target=task)
    t.start()

在这里用到了一个以前没有见过的面向对象的额知识,一个对象如果能.出某个方法,那么说明这个方法在他的类或继承类当中. 但是在query() 并没有在scoped_session这个类中,而是看源码

#这个类中有这么一段代码
for meth in Session.public_methods:#public_methods包含了query方法名
    setattr(scoped_session, meth, instrument(meth)) #meth就是query方法名

#给这scoped_session 赋值了一个方法


#接着来看这段代码
def instrument(name):
    def do(self, *args, **kwargs):
        return getattr(self.registry(), name)(*args, **kwargs)#这里registry加括号了调用__call__方法
    return do
####接着看

        if scopefunc:
            self.registry = ScopedRegistry(session_factory, scopefunc)
        else:
            self.registry = ThreadLocalRegistry(session_factory)
###接着看
 def __call__(self):
        try:
            return self.registry.value#这里没有值,走else
        except AttributeError:
            val = self.registry.value = self.createfunc() #就是我们定义的xxx函数
            return val

注意 flask-sqlalchemy默认也是使用的第二种方式

多进程使用sqlalchemy连接池

QLAlchemy 的连接池是基于线程的。连接池的目的是为了重用数据库连接，提高数据库访问的性能。在多线程环境中，每个线程都可以从连接池中获取一个连接，并在使用完毕后将连接归还给连接池，而不是每次都重新创建和销毁连接。

SQLAlchemy 的连接池使用线程本地存储（Thread-local Storage）来维护每个线程的连接。这意味着每个线程都有自己的连接，线程之间的连接是相互隔离的。当一个线程从连接池中获取连接时，它会将连接与当前线程关联起来，这样在同一个线程中的多个数据库操作可以共享同一个连接。

线程本地存储是一种线程级别的存储机制，它为每个线程提供了一个独立的存储空间，可以在整个线程的生命周期内保存数据。SQLAlchemy 使用线程本地存储来管理每个线程的数据库连接，以确保连接的安全性和一致性。

通过使用线程本地存储，SQLAlchemy 的连接池能够有效地处理多个线程同时访问数据库的情况，而不会出现线程间的混乱或竞争条件。每个线程都可以独立地获取和释放连接，而不会与其他线程产生干扰。

需要注意的是，连接池仅在同一个线程内部有效，不同线程之间的连接无法共享。如果需要在多进程环境下共享连接池，每个进程都应该创建自己的连接池对象，以确保连接的独立性和安全性。