Python---SQLAlchemy

一、安装SQLAlchemy

pip install sqlalchemy

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，从而实现对数据库的操作，如：

pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

目标：通过类或对象进行操作----->sql语句-(pymysql/mysqldb)---->在数据库执行  所以要有这两个模块。

二、使用SQLAlchemy

执行原生的sql语句

# 基本写法不常见

from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/lesson??charset=utf-8",
                      max_overflow=0, # 超过连接池外最大连接的个数
                      pool_size=5, #连接池大小
                      pool_timeout=10, # 池中没有线程最多等待的时间
                      pool_recycle=-1) # 多久时间对连接池的线程进行回收（重置）


def task():
    conn = engine.raw_connection()  # 创建一个连接
    cursor = conn.cursor()   # 创建游标
    cursor.execute("select * from lab")   # 执行sql语句
    result = cursor.fetchall()   # 查询所有语句
    cursor.close()   # 关闭游标
    conn.close()   # 关闭连接
    return result

print(task())

orm进行数据库表的创建

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

Base = declarative_base()

# 创建单表
class User(Base):  # 需要继承Base
    __tablename__ = "lab233"  # 定义数据库的表名
    id = Column(Integer,primary_key=True, autoincrement=True)  #  主键自增
    name = Column(String(32), nullable=True, index=True) # name列 非空 索引
    ctime = Column(DateTime, default=datetime.datetime.now)   # 这里不加括号,加上括号就相当于写死了，当不加括号每次执行都会自动加上括号执行
    __table_args__ = (
        UniqueConstraint("id","name",name="uix_id_name"),  # 联合唯一索引  id和name创建联合唯一索引
        Index("ix_id_name", "name","email"),  # 联合索引 name和Email
    )  # 给这个表创建一些参数
--------------------------------------------------------------------------
# 创建一对多表
class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    hobby_id = Column(Integer, ForeignKey("hobby.id"))  # 创建外建  bobby是表名而不是类名

    # 与生成表结构无关，仅用于查询方便
    hobby = relationship("Hobby", backref='pers')
---------------------------------------------------------------------------
# 创建多对多的表 
class Server2Group(Base):  # 第三张表包含两个外键
    __tablename__ = 'server2group'
    id = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))


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

    # 与生成表结构无关，仅用于查询方便
    servers = relationship('Server', secondary='server2group', backref='groups')


class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)

if __name__ =="__main__":  # 这里只需要执行一次
    engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/lesson??charset=utf-8", max_overflow=0, pool_size=5, pool_timeout=10, pool_recycle=-1) 
    Base.metadata.create_all(engine)  # 帮助你创建表，不加这句话不会自动创建表
    # Base.metadata.drop_all(engine)  # 帮助你删除表

操作数据表进行增删改查

第一种连接模式

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from tpp import User  # 导入表


# 创建连接池
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/lesson??charset=utf-8", max_overflow=0,pool_size=5, pool_timeout=10,pool_recycle=-1) 

Session = sessionmaker(bind=engine)
#从连接池中获取连接
session = Session() # 每次执行操作都要创建Session  第一种获取连接的方式

# 进行orm的操作  像极了java的orm操作
obj1 = User(name="pp")  # 向User表中添加一行数据
session.add(obj1)   # 添加到会话中

session.commit() # 提交数据
session.close() # 关闭session  将连接返回连接池

第二种连接模式, 推荐这种模式

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from tpp import User   # 导入表


# 创建连接池
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/lesson??charset=utf-8", max_overflow=0,pool_size=5,pool_timeout=10,pool_recycle=-1) 
Session = sessionmaker(bind=engine)

#从连接池中获取连接
session = scoped_session(Session)  # 第二种获取连接的方式为每个线程单独创建session

# 进行orm的操作  像极了java的orm操作
obj1 = User(name="saosan")
session.add(obj1)

session.commit() # 提交数据
session.close() # 关闭session  将连接返回连接池

scoped_session虽然没有add、commit这些方法但是也可以运行这些方法，但是在scoped_session中并能没有看到这些方法的影子，所以猜想这两个类有继承的关系，但是事实并不是这样.....

源码流程解读

__all__ = ['scoped_session']   #  当一个py文件写了一个__all__方法就只能导出__all__后面的东西

class scoped_session(object):
    session_factory = None

    def __init__(self, session_factory, scopefunc=None):  # session_factory是我们传进来的Session类
        self.session_factory = session_factory
        if scopefunc:   # scopefunc=None执行else
            self.registry = ScopedRegistry(session_factory, scopefunc)
        else:
            self.registry = ThreadLocalRegistry(session_factory)
-----------------------------------------------------------------------------
ThreadLocalRegistry的__init__方法

class ThreadLocalRegistry(ScopedRegistry):

    def __init__(self, createfunc):  # createfunc就是Session类
        self.createfunc = createfunc
        self.registry = threading.local()  # 创建threading.local
-----------------------------------------------------------------------------
此时self.registry封装了两个值self.createfunc、self.registry 

我们回到scoped_session继续往下看

ScopedSession = scoped_session  # 其实ScopedSession和scoped_session两个是等价的

for meth in Session.public_methods:  # 通过 遍历+反射 的方式为scoped_session增加Session类的方法
    setattr(scoped_session, meth, instrument(meth))  # 设置属性
----------------------------------------------------------------------
Session.public_methods
public_methods = (
        '__contains__', '__iter__', 'add', 'add_all', 'begin', 'begin_nested',
        'close', 'commit', 'connection', 'delete', 'execute', 'expire',
        'expire_all', 'expunge', 'expunge_all', 'flush', 'get_bind',
        'is_modified', 'bulk_save_objects', 'bulk_insert_mappings',
        'bulk_update_mappings',
        'merge', 'query', 'refresh', 'rollback',
        'scalar')  # Session类所有的方法都在这
----------------------------------------------------------------------
def instrument(name):
    def do(self, *args, **kwargs):
        return getattr(self.registry(), name)(*args, **kwargs)  # 通过反射获取函数的属性
    return do
----------------------------------------------------------------------
执行self.registry的__call__方法

def __call__(self):
    try:
        return self.registry.value  # 第一次进来没有这个属性就报错
    except AttributeError:
        val = self.registry.value = self.createfunc()  # 在这里实例化了Session类
        return val  # 返回Session类的对象

这就很好的解释了为什么在scoped_session中仍然能用Session中的方法。

如何增、删、改、查？

删除：

session.query(User).filter(User.id>3).delete() # 删除User表id>3的数据

增加：

obj1 = User(name="saosan")  # 创建一个User对象
session.add(obj1)  # 添加一行记录到User表

obj1 = User(name="qq")
obj2 = User(name="uu")
obj3 = User(name="ii")
session.add_all([obj1,obj2,obj3]) # 批量增加，列表里放一堆对象

修改：

session.query(User).filter(User.id==3).update({"name":"lee"})  # 修改时后面是字典

session.query(User).filter(User.id==3).update({User.name:User.name+"sin"},synchronize_session=False)  # 也可以完成F操作,需要加上synchronize_session=False这个参数

session.query(User).filter(User.id==3).update({User.name:User.name+999},synchronize_session="evaluate")  # 数字的话，需要加上synchronize_session这个参数

查询：

session.query(User).all()  # 查询User表所有的数据, query()放类名, 可以通过遍历获取每个元素

for i in session.query(User).all():  # 获得具体值
    print(i.name)

session.query(User.name).all()  # 表示只取User.name一列数据

session.query(User.id.label("nid"),User.name).all()  # 查询id和name两列数据，并为id列起了别名 

for i in session.query(User.id.label("nid"),User.name):  # label相当于取别名
    print(i.nid)   # 可以通过别名获取属性

session.query(User.name).filter_by(name="ttp").all()  # 获取name="ttp"的全部数据

session.query(User.name).filter_by(name="ttp").first()  # 获取name="ttp"的第一条数据

session.query(User).filter(User.id>1, User.name=="saosan").all()   #  查询id>1并且name为saosan的数据, ','代表且的意思

session.query(User).filter(User.id.between(1,2)).all()  # 取大于等于1小于等于2的数据,两边都取得到

session.query(User).filter(User.id.in_([1,2])).all()  # 查询id在[1,2]的数据  in后面要下划线

session.query(User).filter(~User.id.in_([1,2])).all()  # ~代表非的意思,取反

session.query(User).filter(User.id.in_(session.query(User.id).filter(User.name=="ttp"))).all()  # 进行嵌套查询,和sql嵌套查询类似


from sqlalchemy.sql import text
session.query(User).filter(text("id=:id and name=:name")).params(id=1,name="ttp").order_by(User.id).all()  # :id只是占位符,通过后面的params传参数

session.query(User).from_statement(text("SELECT * FROM user where name=:name")).params(name='ttp').all()  # 直接写原生的sql语句



from sqlalchemy import and_, or_
session.query(User).filter(and_(User.name=="ttp", User.id == 1))  # 且条件

session.query(User).filter(or_(User.name=="ttp", User.id == 1))   # 或条件

※： filter和filter_by的区别 ： filter里面传的是表达式 而 filter_by里面传的是参数,只能等于, 不能大于小于。

其他常见用法：

通配符

session.query(User).filter(User.name.like("t%")).all()  # 查询t开头的数据

session.query(User).filter(~User.name.like("t%")).all()   # ~代表非的意思，取反

limit限制数据

session.query(User).all()[1:2] # 直接用切片

排序

session.query(User).order_by(User.name.desc(), User.id.asc()).all()  # desc降序,asc升序

分组

session.query(User).group_by(User.id).all()   # 通过id进行分组

session.query(func.max(User.id)).group_by(User.id).all() # 通过id分组后，取最大的id

session.query(func.min(User.id)).group_by(User.id).all() # 通过id分组后，取最小的id

session.query(func.sum(User.id)).group_by(User.id).all()  # 通过id分组后，取和

session.query(User).group_by(User.id).having(User.id>2).all()   # having 再加条件

连表
session.query(User,User).filter(User.id == User.id).all()  # filter 里面是连接条件

session.query(User).join(User).all()   #  第二种  默认是inner join

session.query(User).join(User,isouter=True).all()  # 加上isouter=True就是外连接

session.query(User).join(User,User.id == User.id).all()   # 连表的条件自己可以定义

# 也可以在on后面增加多个连表条件
session.query(User).join(User,and_(User.id == User.id,User.name == User.name)).all()

组合
from sqlalchemy import union,union_all

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

写原生sql
cursor = session.execute("select * from user")  # 通过session执行sql语句
result = cursor.fetchall()  # 查询全部数据

cursor = session.execute("insert into lab233(name) values(:name)",params={'name':"lol"})
print(cursor.lastrowid)   # 打印最后插入数据的id

一对多查询

表结构：

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


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    hobby_id = Column(Integer, ForeignKey("hobby.id"))  # 创建外建  bobby是表名而不是类名

    # 与生成表结构无关，仅用于查询方便,backref 是反向查找使用到的
    hobby = relationship("Hobby", backref='pers')

查询：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func
from sqlalchemy.orm import scoped_session,relationship
from tpp import Server2Group,Group,Server

# 创建连接池
engine = create_engine("mysql+pymysql://root:zyz521@127.0.0.1:3306/lesson??charset=utf-8", max_overflow=0,pool_size=5,pool_timeout=10,pool_recycle=-1)
Session = sessionmaker(bind=engine)
#从连接池中获取连接
session = Session() 
# orm操作

# 一对多查询
# 法一
result = session.query(Person,Hobby).all()
for row in result:
    print(row[0].name, row[1].caption)  # row[0]是Person, row[1]是Hobby

# 法二
result = session.query(Person.name,Hobby.caption).join(Hobby,isouter=True).all()
for i in result:
    print(i.name,i.caption)

# 反向查找  通过backref参数
result = session.query(Hobby).filter(Hobby.id == 1).first()  # 只有单个对象才有反向查找
for i in result.pers:
    print(i.name)

# 反向增加  通过backref参数
obj = Hobby(caption="sing")
obj.pers = [Person(name="lff"),Person(name="dzw")]
session.add(obj)

session.commit() 
session.close() 

多对多查询

表结构:

class Server2Group(Base):
    __tablename__ = 'server2group'
    id = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))

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

    # 与生成表结构无关，仅用于查询方便   这个外键写到那张表里都没关系, secondary 是第三张表
    servers = relationship('Server', secondary='server2group', backref='groups')

class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)

查询：

# 多表查询

# 通过外键添加数据不仅会在两张表中添加数据，而且还会在第三张表内添加数据
# 正向添加
obj = Group(id=1,name="fangshan")
obj.servers = [Server(hostname="qqqqq"), Server(hostname="wwwwwww")]
session.add(obj)

# 反向添加
obj = Server(hostname="rrrrrr")
obj.groups = [Group(id=2,name="fengtai"),Group(id=3,name="haidian")]
session.add(obj)

# 反向查询
obj = session.query(Server).first()
print(obj.groups)

#关联子查询 select id,name,(select count(id) from teacher where teacher.id = user.id) as nid from user  括号里的select必须保证这里只能有一个结果  这种就是再加一列的情况
# 先写子查询，filter是条件过滤, correlate 放的是要关联的表
subquery = session.query(func.count(Server.id)).filter(Server.id == Group.id).correlate(Group).as_scalar()
# 主查询中放子查询和关联的了类
print(session.query(Group,subquery).all())

session.commit() 
session.close()