SQLAlchemy

最新推荐文章于 2024-09-14 07:42:49 发布
最新推荐文章于 2024-09-14 07:42:49 发布
阅读量481 收藏 4
点赞数
分类专栏： python 文章标签： python 数据库 orm sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43079274/article/details/116403518
版权

文章目录

一、连接和初始化

掌握了SQLALchemy也就是掌握了定制化的flask-SQLALchemy插件

使用SQLAlchemy去连接数据库

使用SQLALchemy去连接数据库，需要使用一些配置信息，然后将他们组合成满足条件的字符串

HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'

# dialect+driver://username:password@host:port/database
DB_URI = "mysql+pymysql://f{username}:f{password}@f{host}:f{port}/f{db}?charset=utf8"

然后使用create_engine创建一个引擎engine，然后再调用这个引擎的connect方法，就可以得到这个对象，然后就可以通过这个对象对数据库进行操作了

from sqlalchemy import create_engine


username = 'root'
password = '123'
host = '127.0.0.1'
port = '3306'
db = 'iHome'

# dialect[+driver]://user:password@host/dbname[?key=value..]
DB_URI = f"mysql://{username}:{password}@{host}/{db}?charset=utf8"
# DB_URI = "mysql://root:123@127.0.0.1:3306/iHome"

engine = create_engine(DB_URI)
# 判断是否连接成功
conn = engine.connect()
result = conn.execute('select 1')
print(result.fetchone())
#查看打印输出的结果，如果控制台输出(1,) 则连接成功

二、模型与数据库的映射

将ORM模型映射到数据库中：

  1. 用declarative_base根据engine创建一个ORM基类

    from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
DB_URI = "mysql://root:123@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)

  2. 用这个Base类作为基类来写自己的ORM类。要定义__tablename__类属性，来指定这个模型映射到数据库中的表名

    class Person(Base):
        __tablename__ = 'person'

  3. 创建属性来映射到表中的字段，所有需要映射到表中的属性都应该为Column类型：

    class Person(Base):
    __tablename__ = 'person'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
    1. 使用Base.metadata.create_all()来将模型映射到数据库中
    2. 一旦使用Base.metadata.create_all()将模型映射到数据库中后，即使改变了模型的字段，也不会重新映射了

完整demo

import sqlalchemy
# from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.ext.declarative import declarative_base

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = sqlalchemy.create_engine(DB_URI)
Base = declarative_base(engine)

class Person(Base):
    __tablename__ = 'person'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)

Base.metadata.create_all()

三、SQLAlchemy常用数据类型

  1. Integer：整形，映射到数据库中是int类型

  2. Float：浮点类型，映射到数据库中是float类型。他占据的32位

  3. Double：双精度浮点类型，映射到数据库中是double类型，占据64位

  4. String：可变字符类型，映射到数据库中是varchar类型

  5. Boolean：布尔类型，映射到数据库中的是tinyint类型

  6. Text：存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型。

  7. LONGTEXT：长文本类型，映射到数据库中是longtext类型

  8. DECIMAL：定点类型。

    是专门为了解决浮点类型精度丢失的问题的。在存储金钱相关的字段的时候建议使用这个数据类型。并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位

  9. Enum：枚举类型

    指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举

    示例代码如下：

    class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    tag = Column(Enum("python", 'flask', 'django'))

  10. Date：存储时间，只能存储年月日。

    映射到数据库中是date类型。在Python代码中，可以使用datetime.date来指定。

    示例代码如下：

    class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    create_time = Column(Date)

article = Article(create_time=date(2017, 10, 10))

  11. DateTime：存储时间，可以存储年月日时分秒毫秒等。

    映射到数据库中也是datetime类型。在Python代码中，可以使用datetime.datetime来指定。

    示例代码如下：

    class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    create_time = Column(DateTime)

article = Article(create_time=datetime(2011, 11, 11, 11, 11, 11))

  12. Time：存储时间，可以存储时分秒。

    映射到数据库中也是time类型。在Python代码中，可以使用datetime.time来至此那个。

    示例代码如下：

    class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    create_time = Column(Time)

article = Article(create_time=time(hour=11, minute=11, second=11))

四、Column常用参数

  1. primary_key：设置某个字段为主键

  2. autoincrement：设置这个字段为自动增长的

  3. default：设置某个字段的默认值。在发表时间这些字段上面经常用

  4. nullable：指定某个字段是否为空。默认值是True，就是可以为空

  5. unique：指定某个字段的值是否唯一。默认是False

  6. onupdate：在数据更新的时候会调用这个参数指定的值或者函数。

    在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用的就是update_time（每次更新数据的时候都要更新的值）

    update_time = Column(DateTime,onupdate=datetime.now,default=datetime.now)

  7. name：指定ORM模型中某个属性映射到表中的字段名。

    如果不指定，那么会使用这个属性的名字来作为字段名。

    如果指定了，就会使用指定的这个值作为参数。

    这个参数也可以当作位置参数，在第1个参数来指定

    title = Column(String(50),name='title',nullable=False)
title = Column('my_title',String(50),nullable=False)

五、数据的增删改查操作

用session做数据的增删改查

  1. 构建session对象：所有和数据库的ORM操作都必须通过一个叫做session的会话对象来实现

    通过以下代码来获取会话对象:

    from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
session = sessionmaker(engine)()

  2. 首先添加对象,也即创建一条数据

    p = Person(name='zhiliao',age=18,country='china')

    • 将这个对象添加到session会话对象中：

      • session.add(p)

    • 将session中的对象做commit操作（提交）：

      • session.commit()

    • 一次性添加多条数据：

      • p1 = Person(name='zhiliao1', age=19, country='china')
p2 = Person(name='zhiliao2', age=20, country='china')
session.add_all([p1, p2])
session.commit()

  3. 查找对象

    # 查找某个模型对应的那个表中所有的数据：
all_person = session.query(Person).all()
# 使用filter_by来做条件查询
all_person = session.query(Person).filter_by(name='zhiliao').all()
# 使用filter来做条件查询
all_person = session.query(Person).filter(Person.name=='zhiliao').all()
# 使用get方法查找数据，get方法是根据id来查找的，只会返回一条数据或者None
person = session.query(Person).get(primary_key)
# 使用first方法获取结果集中的第一条数据
person = session.query(Person).first()

  4. 修改对象：首先从数据库中查找对象，然后将这条数据修改为你想要的数据，最后做commit操作就可以修改数据了

    person = session.query(Person).first()
person.name = 'ketang'
session.commit()

  5. 删除对象：将需要删除的数据从数据库中查找出来，然后使用session.delete方法将这条数据从session中删除，最后做commit操作就可以了

    person = session.query(Person).first()
session.delete(person)
session.commit()

demo

from sqlalchemy import create_engine,Column,Integer,String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)

Base = declarative_base(engine)

# Session = sessionmaker(engine)
# session = Session()

session = sessionmaker(engine)()


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    country = Column(String(50))

    def __str__(self):
        return "<Person(name:%s,age:%s,country:%s)>" % (self.name,self.age,self.country)


# 增
def add_data():
    p1 = Person(name='zhiliao1',age=19,country='china')
    p2 = Person(name='zhiliao2',age=20,country='china')
    session.add_all([p1,p2])
    session.commit()

# 查
def search_data():
    all_person = session.query(Person).all()
    for p in all_person:
        print(p)
    # all_person = session.query(Person).filter_by(name='zhiliao').all()
    # for x in all_person:
    #     print(x)
    # all_person = session.query(Person).filter(Person.name=='zhiliao').all()
    # for x in all_person:
    #     print(x)
    # person = session.query(Person).first()
    # print(person)

# 改
def update_data():
    person = session.query(Person).first()
    person.name = 'ketang'
    session.commit()

# 删
def delete_data():
    person = session.query(Person).first()
    session.delete(person)
    session.commit()

if __name__ == '__main__':
    # add_data()
    search_data()
    # update_data()
    # delete_data()

六、query可用参数

  1. 模型对象。指定查找这个模型中所有的对象

  2. 模型中的属性。可以指定只查找某个模型的其中几个属性

  3. 聚合函数

    1. func.count：统计行的数量
    2. func.avg：求平均值
    3. func.max：求最大值
    4. func.min：求最小值
    5. func.sum：求和

    事实上，在SQLALchemy的 func上，其实没有任何聚合函数。但是因为他底层做了一些魔术，只要mysql中有的聚合函数，都可以通过func调用

练习demo

from sqlalchemy import create_engine, Column, Integer, Float,String,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import random



DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()



class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    price = Column(Float, nullable=False)

    def __repr__(self):
        return "<Article(title:%s)>" % self.title


# 在SQLALchemy中设立模型，__repr__可以打印列表，而__str__则只能一个个便历的时候打印出来

# Base.metadata.drop_all()
# Base.metadata.create_all()


for x in range(6):
    article = Article(title='title%s' % x, price=random.randint(50, 100))
    session.add(article)
session.commit()

# 模型对象
articles = session.query(Article).all()
print(articles)

# 模型中的属性
articles = session.query(Article.title, Article.price).all()
print(articles)

# 聚合函数
result = session.query(func.count(Article.id)).first()
print(result)
result = session.query(func.avg(Article.price)).first()
print(result)
result = session.query(func.max(Article.price)).first()
print(result)
result = session.query(func.min(Article.price)).first()
print(result)
result = session.query(func.sum(Article.price)).first()
print(result)
print(func.sum(Article.price))
# select sum(price) from article

七、filter过滤的总结

过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行解释，并且这些过滤条件都是只能通过filter方法实现的：

  1. equals

    article = session.query(Article).filter(Article.title == "title0").first()
print(article)

  2. not equals

    query.filter(User.name != 'ed')

  3. in

    query.filter(User.name.in_(['ed','wendy','jack']))
# 同时，in也可以作用于一个Query
query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))))

  4. not in的两种办法

    articles = session.query(Article).filter(~Article.title.in_(['title1','title2'])).all()
print(articles)
articles = session.query(Article).filter(Article.title.notin_(['title1','title2'])).all()
print(articles)

  5. is null

    query.filter(User.name == None)
# 或者是
query.filter(User.name.is_(None))

  6. is not null

    query.filter(User.name != None)
# 或者是
query.filter(User.name.isnot(None))

  7. and

    # 引入的是and_
from sqlalchemy import and_

query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
# 或者是传递多个参数
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
# 或者是通过多次filter操作
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

  8. or

    # 引入的是or_
from sqlalchemy import or_

query.filter(or_(User.name == 'ed', User.name == 'wendy'))

  9. like

    query.filter(User.name.like('%ed%'))

如果想查看orm底层转换的sql语句，可以在filter方法后面不要再执行任何方法直接打印就可以看到了,比django要好很多

articles = session.query(Article).filter(or_(Article.title=='abc',Article.content=='abc'))
print(articles)

练习demo

from sqlalchemy import create_engine, Column, Integer, Float, String, Text, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    price = Column(Float, nullable=False)
    content = Column(Text)

    def __repr__(self):
        return "<Article(title:%s)>" % self.title


# session.query(Article).filter(Article.id == 1)
# session.query(Article).filter_by(id = 1)

# 1. equal
article = session.query(Article).filter(Article.title == "title0").first()
print(article)

# 2. not equal
articles = session.query(Article).filter(Article.title != 'title0').all()
print(articles)

# 3. like & ilike（不区分大小写）
articles = session.query(Article).filter(Article.title.ilike('title%')).all()
print(articles)

# 4. in：
# for xxx in xxx
# def _in()
articles = session.query(Article).filter(Article.title.in_(['title1', 'title2'])).all()
print(articles)

# not in
articles = session.query(Article).filter(~Article.title.in_(['title1', 'title2'])).all()
print(articles)
articles = session.query(Article).filter(Article.title.notin_(['title1', 'title2'])).all()
print(articles)

# is null
articles = session.query(Article).filter(Article.content == None).all()
print(articles)

# is not null
articles = session.query(Article).filter(Article.content != None).all()
print(articles)

# and
articles = session.query(Article).filter(Article.title == 'abc', Article.content == 'abc').all()
print(articles)

# or
articles = session.query(Article).filter(or_(Article.title == 'abc', Article.content == 'abc'))
print(articles)

八、数据库层面的外键约束

使用SQLAlchemy创建外键非常简单。在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和父表的主键字段类型保持一致。

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)

    uid = Column(Integer,ForeignKey("user.id",ondelete='SET NULL'))

外键约束有以下几项

在ForeignKey语句的ondelete字段进行定制

  1. RESTRICT：父表数据被删除，会阻止删除。默认就是这一项

    uid = Column(Integer,ForeignKey("user.id"))

  2. NO ACTION：在MySQL中，同RESTRICT

  3. .CASCADE：级联删除。 删除主表，次表也变为空

    uid = Column(Integer, ForeignKey("user.id", ondelete='CASCADE'))

  4. SET NULL：父表数据被删除，子表相关联的数据会设置为NULL

    uid = Column(Integer,ForeignKey("user.id",ondelete='SET NULL'))

    顺便提示一下，不可以有如下这种自相矛盾的写法，创建表会直接报错

    uid = Column(Integer,ForeignKey("user.id",ondelete='SET NULL',nullable=False))

ORM层面的外键约束

ORM层面删除数据，会无视mysql级别的外键约束。直接会从代码层级将对应的数据删除，然后将从表中的那个外键设置为NULL。如果想要避免这种行为，应该将从表中的外键的nullable=False

简单来说就是，如果你用sqlalchemy映射生成到数据库，然后你在数据库里直球删除数据，会按照数据库默认的各种操作来进去，这时候，我们默认设置的4个外键约束字段会起作用. 但是如果你在orm的代码上对一对多的父表数据（例如“作者-文章”一对多外键关系中的作者表即为父表）操作删除行为，会把子表中相关联的外键设置为null，并不会出现数据库层面操作的各类问题。当然，如果你在代码字段里添加了nullable=False，就会享受到代码报错的待遇了

uid = Column(Integer,ForeignKey("user.id",nullable=False))

这种在子表的外键写法，删了父表会直接报错

九、一对一

在sqlalchemy中，如果想要将两个模型映射成一对一的关系，那么应该在父模型中，指定引用的时候，要传递一个uselist=False这个参数进去。就是告诉父模型，以后引用这个从模型的时候，不再是一个列表了，而是一个对象了

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

    extend = relationship("UserExtend",uselist=False)

    def __repr__(self):
        return "<User(username:%s)>" % self.username

class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer,ForeignKey("user.id"))

    user = relationship("User",backref="extend")

当然，也可以借助sqlalchemy.orm.backref函数来简化代码

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

    # extend = relationship("UserExtend",uselist=False)

    def __repr__(self):
        return "<User(username:%s)>" % self.username

class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer,ForeignKey("user.id"))

    user = relationship("User",backref=backref("extend",uselist=False))

十、一对多

mysql级别的外键，还不够ORM，必须拿到一个表的外键，然后通过这个外键再去另外一张表中查找，这样太麻烦了。SQLAlchemy提供了一个relationship，这个类可以定义属性，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。

from sqlalchemy import create_engine, Column, Integer, String, Text,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()
 

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

    #在没有写backref语句的时候，可以两张表各建立一个relationship字段,
    # 一个模型里的relationship加上backref字段等效于2个relationship
    # articles = relationship("Article")   
    def __repr__(self):
        return "<User(username:%s)>" % self.username

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)
    uid = Column(Integer,ForeignKey("user.id"))

    author = relationship("User",backref="articles")

另外，可以通过backref来指定反向访问的属性名称。articles是有多个。他们之间的关系是一个一对多的关系

如果不加入relationship字段我们只能通过如下的方式间接的寻找跨表操作，一点也不orm

article = session.query(Article).first()
uid = article.uid
print(article)
user = session.query(User).get(uid)
print(user.username)

在加入relationship字段后，可以通过如下的写法

article = session.query(Article).first()
print(article.author.username)

十一、多对多

  1. 多对多的关系需要通过一张中间表来绑定他们之间的关系
  2. 先把两个需要做多对多的模型定义出来
  3. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”，避免出现重复关联的情况
  4. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary=中间表
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()

#复合主键
article_tag = Table(
    "article_tag",
    Base.metadata,
    Column("article_id",Integer,ForeignKey("article.id"),primary_key=True),
    Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True)
)


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)

    # tags = relationship("Tag",backref="articles",secondary=article_tag)

    def __repr__(self):
        return "<Article(title:%s)>" % self.title


class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)

    articles = relationship("Article",backref="tags",secondary=article_tag)

    def __repr__(self):
        return "<Tag(name:%s)>" % self.name

# 1. 先把两个需要做多对多的模型定义出来
# 2. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”。
# 3. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary=中间表。

# Base.metadata.drop_all()
# Base.metadata.create_all()
# article1 = Article(title="article1")
# article2 = Article(title="article2")
# tag1 = Tag(name='tag1')
# tag2 = Tag(name='tag2')
# article1.tags.append(tag1)
# article1.tags.append(tag2)
# article2.tags.append(tag1)
# article2.tags.append(tag2)
# session.add(article1)
# session.add(article2)
# session.commit()
# article = session.query(Article).first()
# print(article.tags)

tag = session.query(Tag).first()
print(tag.articles)

十二、ORM层面的外键约束

在SQLAlchemy，只要将一个数据添加到session中，和他相关联的数据都可以一起存入到数据库中了。这些是怎么设置的呢？其实是通过relationship的时候，有一个关键字参数cascade可以设置这些属性：

  1. save-update

    默认选项。在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。这种行为就是save-update属性影响的。

    如果写成author = relationship("User",backref="articles",cascade="")则自动添加的魔术就会消失，即使程序上运行了添加，但在数据库层面上，相关外键字段还会是NULL

  2. delete

    表示当删除某一个模型中的数据的时候，是否也删掉使用relationship和他关联的数据

    例如在article表中有外键关系
    author = relationship("User",backref="articles",cascade="save-update,delete")
    删除了文章，相应的作者表中的user也删除。。。 如果没有delete字段，作者表中的user还会存在着

  3. delete-orphan

    表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型中的relationship中，增加一个single_parent=True的参数。 示例代码如下:
    author = relationship("User",backref=backref("articles",cascade="save-update,delete,delete-orphan"),cascade="save-update",single_parent=True)

  4. merge

    默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作

    user = User(username='雨晨')  
session.merge(user)    
session.commit()

  5. expunge

    移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除

  6. all

    是对save-update, merge, refresh-expire(刷新过期时间), expunge, delete几种的缩写

  7. 将两张表中的relationship写法融合在一起的写法

    author = relationship("User",backref=backref("articles",cascade="save-update,delete"),cascade="save-update,delete")
#这种写法是user和article两张表中，任何一个表里的字段被删除，
#那么另一个表里的字段也被删除。比如删了作者的一篇文章，作者消失；
# 删了作者的账号，作者文章也消失。

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    uid = Column(Integer,ForeignKey("user.id"),nullable=False)

    author = relationship("User",backref=backref("articles",cascade="all"),cascade="save-update",single_parent=True,ondelete='')

class Comment(Base):
    __tablename__ = 'comment'
    id = Column(Integer, primary_key=True, autoincrement=True)
    content = Column(Text,nullable=False)
    uid = Column(Integer,ForeignKey("user.id"))

    author = relationship("User",backref="comments")

def my_init_db():
    Base.metadata.drop_all()
    Base.metadata.create_all()
    user = User(username='zhiliao')
    article = Article(title='title one')
    article.author = user
    comment = Comment(content='xxx')
    comment.author = user
    session.add(comment)
    session.add(article)
    session.commit()

def operation():
    user = User(username='ketang')
    article = Article(title='abvc')
    article.author = user
    session.add(user)
    session.add(article)
    session.expunge(user)
    session.commit()

if __name__ == '__main__':
    # my_init_db()
    operation()

十三、排序

  1. order_by

    可以指定根据这个表中的某个字段进行排序，如果在前面加了一个-，代表的是降序排序。
    articles=session.query(Article).order_by(Article.create_time).all()

  2. 在模型定义的时候指定默认排序：

有些时候，不想每次在查询的时候都指定排序的方式，可以在定义模型的时候就指定排序的方式。

有以下两种方式：

  • relationship的order_by参数
 在指定relationship的时候，传递order_by参数来指定排序的字段

 ```python
 author = relationship("User",backref= backref("articles",order_by=create_time.desc()))
 ```
  • 在模型定义中，添加以下代码: __mapper_args__ = { "order_by": title }即可让文章使用标题来进行排序
  1. 正序排序与倒序排序：

默认是使用正序排序。如果需要使用倒序排序，那么可以使用这个字段的desc()方法，或者是在排序的时候使用这个字段的字符串名字，然后在前面加一个负号，以下两种方法都可以达到同样的效果

articles=session.query(Article).order_by(Article.create_time.desc()).all()
articles=session.query(Article).order_by(-'create_time').all()

demo

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50),nullable=False)

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    create_time = Column(DateTime,nullable=False,default=datetime.now)
    uid = Column(Integer,ForeignKey("user.id"))

    author = relationship("User",backref=backref("articles",order_by=create_time.desc()))
    __mapper_args__ = {
        "order_by": create_time.desc()
    }

    def __repr__(self):
        return "<Article(title:%s,create_time:%s)>" % (self.title,self.create_time)

十四、limit、offset和切片

  1. limit：可以限制每次查询的时候只查询几条数据

  2. offset：可以限制查找数据的时候过滤掉前面多少条

articles = session.query(Article).order_by(Article.id.desc()).offset(10).limit(10).all()
  1. 切片：前两条的结合，可以对Query对象使用切片操作，来获取想要的数据。可以使用slice(start,stop)方法来做切片操作。也可以使用[start:stop]的方式来进行切片操作。一般在实际开发中，中括号的形式是用得比较多的
articles = session.query(Article).order_by(Article.id.desc()).slice(10,20)
articles = session.query(Article).order_by(Article.id.desc())[10:20]

十五、group_by与having查询

SQLALchemy很多语法和原生sql高度一致

group_by：根据某个字段进行分组

比如想要根据性别进行分组，来统计每个分组分别有多少人，那么可以使用以下代码来完成：

session.query(User.gender,func.count(User.id)).group_by(User.gender).all()

having：having是对查找结果进一步过滤

比如只想要看未成年人的数量，那么可以首先对年龄进行分组统计人数，然后再对分组进行having过滤。示例代码如下：

result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age >= 18).all()

demo

from sqlalchemy import create_engine, Column, Integer, String, func, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)
    age = Column(Integer,default=0)
    gender = Column(Enum("male","female","secret"),default="male")


# Base.metadata.drop_all()
# Base.metadata.create_all()
# user0 = User(username='钱二',age=16,gender='male')
# user1 = User(username='王武',age=17,gender='male')
# user2 = User(username='赵四',age=17,gender='male')
# user3 = User(username="张三",age=18,gender='female')
# user4 = User(username="张伟",age=19,gender='female')
# user5 = User(username="知了",age=20,gender='female')
#
# session.add_all([user0,user1,user2,user3,user4,user5])
# session.commit()


# 每个年龄的人数
# from sqlalchemy.orm.query import Query
result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age < 18).all()
print(result)

#打印[(16,1),(17,2)]

十六、join的使用

join其实和SQL语句里的JOIN用法一致

  1. join分为left join（左外连接）和right join（右外连接）以及内连接（等值连接）

  2. 在sqlalchemy中，使用join来完成内连接。在写join的时候，如果不写join的条件，那么默认将使用外键来作为条件连接

  3. query查找出来什么值，不会取决于join后面的东西，而是取决于query方法中传了什么参数。

就跟原生sql中的select 后面那一个一样

比如现在要实现一个功能，要查找所有用户，按照发表文章的数量来进行排序

示例代码如下：

result = session.query(User,func.count(Article.id)).join(Article).group_by(User.id).order_by(func.count(Article.id).desc()).all()
result = session.query(User,func.count(Article.id)).join(Article,User.id==Article.uid).group_by(User.id).order_by(func.count(Article.id).desc()).all()

demo

import datetime

from sqlalchemy import create_engine, Column, Integer, String, func, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

    def __repr__(self):
        return "<User(username: %s)>" % self.username

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    create_time = Column(DateTime, nullable=False, default=datetime.now)
    uid = Column(Integer,ForeignKey("user.id"))

    author = relationship("User",backref="articles")

    def __repr__(self):
        return "<Article(title: %s)>" % self.title

# Base.metadata.drop_all()
# Base.metadata.create_all()
#
# user1 = User(username='yuchen')
# user2 = User(username='yawn')
#
# for x in range(1):
#     article = Article(title='title %s' % x)
#     article.author = user1
#     session.add(article)
# session.commit()
#
#
# for x in range(1,3):
#     article = Article(title='title %s' % x)
#     article.author = user2
#     session.add(article)
# session.commit()

# 找到所有的用户，按照发表的文章数量进行排序
result = session.query(User.username,func.count(Article.id)).join(Article).group_by(User.id).order_by(func.count(Article.id).desc()).all()
print(result)
#打印结果为[('yuchen',2),('yawen',1)]

# select user.username,count(article.id) from user join article on user.id=article.uid group by user.id order by count(article.id) desc;

十七、subquery查询

子查询可以让多个查询变成一个查询，只要查找一次数据库，性能相对来讲更加高效一点。不用写多个sql语句就可以实现一些复杂的查询。那么在sqlalchemy中，要实现一个子查询，应该使用以下几个步骤：

  1. 将子查询按照传统的方式写好查询代码，然后在query对象后面执行subquery方法，将这个查询变成一个子查询

  2. 在子查询中，将以后需要用到的字段通过label方法，取个别名

  3. 在父查询中，如果想要使用子查询的字段，那么可以通过子查询的返回值上的c属性拿到。 示例代码如下

stmt = session.query(User.city.label("city"),User.age.label("age")).filter(User.username=='李A').subquery()
result = session.query(User).filter(User.city==stmt.c.city,User.age==stmt.c.age).all()

如果不使用子查询，则sqlalchemy写法如下：

# 寻找和李A这个人在同一个城市，并且是同年龄的人
user = session.query(User).filter(User.username=='李A').first()
users = session.query(User).filter(User.city==user.city,User.age==user.age).all()
print(users)

SQL原生子查询写法如下：

select user.id,user.username,user.city,user.age from user,(select user.city,user.age from user where user.username="李A") as li_a where age=li_a.age and user.city=li_a.city,

demo

import datetime

from sqlalchemy import create_engine, Column, Integer, String, func, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

DB_URI = "mysql://root:491521@127.0.0.1:3306/iHome"
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)
    city = Column(String(50),nullable=False)
    age =  Column(Integer,default=0)

    def __repr__(self):
        return "<User(username: %s)>" % self.username


# Base.metadata.drop_all()
# Base.metadata.create_all()
#
# user1 = User(username='李A',city="长沙",age=18)
# user2 = User(username='王B',city="长沙",age=18)
# user3 = User(username='赵C',city="北京",age=18)
# user4 = User(username='张D',city="长沙",age=20)
#
# session.add_all([user1,user2,user3,user4])
# session.commit()

# 寻找和李A这个人在同一个城市，并且是同年龄的人
# user = session.query(User).filter(User.username=='李A').first()
# users = session.query(User).filter(User.city==user.city,User.age==user.age).all()
# print(users)

stmt = session.query(User.city.label("city"),User.age.label("age")).filter(User.username=='李A').subquery()
result = session.query(User).filter(User.city==stmt.c.city,User.age==stmt.c.age).all()
print(result)
#打印结果为[<User(username:李A)>,<User(username:王b)>]

十八、懒加载

在一对多，或者多对多的时候，如果想要获取多的这一部分的数据的时候，往往能通过一个属性就可以全部获取了。比如有一个作者，想要或者这个作者的所有文章，那么可以通过user.articles就可以获取所有的。但有时候我们不想获取所有的数据，比如只想获取这个作者今天发表的文章，那么这时候我们可以给relationship传递一个lazy=‘dynamic’，以后通过user.articles获取到的就不是一个列表，而是一个AppenderQuery对象了。这样就可以对这个对象再进行一层过滤和排序等操作。 通过lazy='dynamic'，获取出来的多的那一部分的数据，就是一个AppenderQuery对象了。这种对象既可以添加新数据，也可以跟Query一样，可以再进行一层过滤。 总而言之一句话：如果你在获取数据的时候，想要对多的那一边的数据再进行一层过滤，那么这时候就可以考虑使用lazy='dynamic'

lazy可用的选项，最主要的还是前两种

  1. select

    这个是默认选项。还是拿user.articles的例子来讲。如果你没有访问user.articles这个属性，那么sqlalchemy就不会从数据库中查找文章。一旦你访问了这个属性，那么sqlalchemy就会立马从数据库中查找所有的文章，并把查找出来的数据组装成一个列表返回。这也是懒加载。

  2. dynamic

    这个就是我们刚刚讲的。就是在访问user.articles的时候返回回来的不是一个列表，而是AppenderQuery对象

  3. immediate

    只要把user对象从数据库查出来，便会吧所有articles数据查询出来，使用很少，会造成性能浪费

  4. Joined

    查找结果通过JOIN方法加载进来

  5. subquery

    通过子查询到方式加载进来

demo

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50),nullable=False)


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    create_time = Column(DateTime,nullable=False,default=datetime.now)
    uid = Column(Integer,ForeignKey("user.id"))

    author = relationship("User",backref=backref("articles",lazy="dynamic"))

    def __repr__(self):
        return "<Article(title: %s)>" % self.title

首先是一对多而言，所以author = relationship("User",backref=backref("articles",lazy="dynamic"))，lazy写在里面而非外面。
author可以有多本articles，但因为articles只能有一个author，放在外面反而没意义了。其次，

user = session.query(User).first()

如果没写lazy="dynamic"这句话，那么type(user)是InstrumentedList类型（from sqlalchemy.orm.collections import InstrumentedList），list的一种，可以append，但无法使用fillter等方法，但可以便历后逐条的进行筛选

如果写了lazy="dynamic"这句话，那么type(user)是AppenderQuery类型（from sqlalchemy.orm.dynamic import AppenderQuery）,继承自query和AppenderMixin，可以使用fillter等query方法，完全是一个query，而且还可以append。
只有使用了lazy="dynamic"，以下操作才可以实现：

user = session.query(User).first()
print(user.articles.filter(Article.id > 50).all())
老杨玩python
关注
专栏目录
Python SQLAlchemy基本操作和常用技巧（包含大量实例,非常好）
12-24
首先说下，由于最新的 0.8 版还是开发版本，因此我使用的是 0.79 版，API 也许会有些不同。因为我是搭配 MySQL InnoDB 使用，所以使用其他数据库的也不能完全照搬本文。 接着就从安装开始介绍吧，以 Debian/Ubuntu 为例（请确保有管理员权限）：1.MySQL复制代码 代码如下:apt-get install mysql-serverapt-get install mysql-clientapt-get install libmysqlclient15-dev2.python-mysqldb复制代码 代码如下:apt-get install python-mysqld
python常用库之数据库orm框架之SQLAlchemy
西京刀客
10-08 1万+
SQLAlchemy是一个基于Python实现的SQL工具包和ORM框架,提供了高层抽象来管理数据库交互。
SQLAlchemy PickleType
最新发布
Flask Web
09-14 326
在SQLAlchemy中，PickleType是一个数据类型，它允许你将Python对象序列化为二进制格式，并将这些二进制数据存储在数据库中。当你需要将复杂的Python对象存储在数据库中，并且希望能够在以后检索并恢复这些对象时，PickleType是非常有用的。当我们检索记录时，序列化的对象会被反序列化，恢复为原始的Python对象，我们可以访问其属性和方法。然后，在Data类中，我们定义了一个obj字段，其类型为PickleType，用于存储MyObject实例。
SQLAlchemy简明教程
热门推荐
王敏的专栏
01-20 5万+
SQLAlchemy 是 Python 著名的 ORM 工具包。通过 ORM，可以用面向对象的方式来操作数据库，不再需要编写 SQL 语句。本篇不解释为什么要使用 ORM，主要讲解 SQLAlchemy 的用法。SQLAlchemy 支持多种数据库，除 sqlite 外，其它数据库需要安装第三方驱动，不同数据库最主要区别在连接数据库 url 不同。本篇以 sqlite 数据库为例进行说明。 建立到数据库的连接 Engine 对象是使用 sqlalchemy 的起点，根据 sqlalchemy documen
SQLAlchemy 教程 —— 基础入门篇
weixin_33713707的博客
02-11 1331
SQLAlchemy 教程 —— 基础入门篇 一、课程简介 1.1 实验内容 本课程带领大家使用 SQLAlchemy 连接 MySQL 数据库，创建一个博客应用所需要的数据表，并介绍了使用 SQLAlchemy 进行简单了 CURD 操作及使用 Faker 生成测试数据。 1.2课程知识点 学会用 SQLALchemy 连接数据库(MySQL, SQLite, PostgreSQL), 创建数...
sqlalchemy
04-09
SQLAlchemy是一个功能强大的Python库，用于与关系型数据库进行交互，其最突出的特点是具有对象关系映射（ORM）功能。它提供了完善的工具集，让开发者能够以面向对象的方式操作数据库，而不是写原始SQL代码，极大地...
sqlalchemy的基本操作大全
03-09
SQLAlchemy 是一个强大的 Python 数据库 ORM（对象关系映射）框架，它允许开发者使用面向对象的方式来操作数据库，而无需直接编写 SQL 语句。ORM 的核心思想是将数据库中的表映射为 Python 类，表中的行映射为类的...
FlaskSQLAlchemy下载及安装
02-28
**Flask-SQLAlchemy**是Python微框架Flask的一个扩展，它使得在Flask应用中集成和使用SQLAlchemy变得更加方便。SQLAlchemy是Python的一个强大的ORM（对象关系映射）库，能够将数据库操作转化为面向对象的编程方式。...
教大家使用Python SqlAlchemy
09-21
Python SQLAlchemy 是一个强大的ORM（对象关系映射）库，它允许开发者使用Python对象来操作数据库，简化了Python应用与SQL数据库之间的交互。以下是对标题和描述中提到的使用Python SQLAlchemy的关键知识点的详细...
sqlalchemy的
02-12
`SQLAlchemy`是Python编程语言中的一个流行数据库 ORM（对象关系映射）工具，它允许开发者用Python类和对象的方式操作数据库，而无需编写大量的SQL语句。在Python的Web开发中，SQLAlchemy通常作为数据库操作的核心库...
SQLAlchemy详细教程
07-07
SQLAlchemy 详细教程 SQLAlchemy 是一个 Python 的关系型数据库工具包，提供了一个高级的对象关系映射（Object-Relational Mapping，ORM）系统，允许开发者使用 Python 对象来交互数据库，而不需要直接使用 SQL ...
SQLalchemy挑战
02-14
SQLAlchemy家庭作业-冲浪！ 在你开始之前 为此项目创建一个新的存储库，称为sqlalchemy-challenge 。 不要将此作业添加到现有存储库中。 将新的存储库克隆到您的计算机。 将Jupyter笔记本和app.py添加到此文件夹。...
sqlalchemy挑战
02-13
SQLAlchemy家庭作业-冲浪！ 在你开始之前 为此项目创建一个新的存储库，称为sqlalchemy-challenge 。 不要将此作业添加到现有存储库中。 将新的存储库克隆到您的计算机。 将Jupyter笔记本和app.py添加到此文件夹。...
sqlalchemy文档
01-28
SQLAlchemy 是一个强大的 Python SQL 工具包和对象关系映射器，它为数据库操作提供了高级接口。在深入了解 SQLAlchemy 的核心概念之前，我们先来理解什么是 ORM（对象关系映射）。ORM 是一种技术，用于将数据库操作...
python对象操作数据库—SQLAlchemy的基本介绍与简单使用
weixin_66339667的博客
01-25 3895
SQLAlchemy是一个用于Python的SQL工具和对象关系映射（ORM）库。它允许你通过Python代码来与关系型数据库交互，而不必直接编写SQL语句。
简单易用，灵活强大：用SQLAlchemy实现Python操作数据库
weixin_62650212的博客
04-18 8720
SQLAlchemy是一个Python的SQL工具和ORM框架，可以通过Python代码直接操作关系型数据库，也可以使用ORM模型进行对象关系映射。它支持多种数据库，并提供了强大的SQL表达式和查询API。SQLAlchemy可以分为两个部分：Core和ORM。Core：提供了底层的SQL表达式和查询API，支持多种数据库的可移植操作，例如连接管理、事务管理、对象关系映射、元数据管理等。
SQLAlchemy完全入门
虚幻私塾
01-10 1万+
Python微信订餐小程序课程视频 https://edu.csdn.net/course/detail/36074 Python实战量化交易理财系统 https://edu.csdn.net/course/detail/35475 最近想要学习SQLAlchemy, 发现网上的中文文档大多是机翻的, 读起来特别变扭, 因此对照着最新的英文文档梳理了一遍, 写下来记录一下 目前SQLAlchemy的版本为1.4.x, 风格处于1.x过渡到2.0的时代. 为了尽量让这篇文章的兼容之后的版本, 本文将讲述1.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值