session.query(xxx)得到的是一个query对象，上面说过很多后面可以接上的方法，实际上还有更多如下：

　　one()　　如果返回行数不为1，那么就报错；若刚好返回结果就一条就返回这条记录的对象

　　limit(n)　　最多只返回n条结果

　　offset(n)　　直接跳过前n条记录，从n+1条开始返回

　　order_by(Table.attribute 或者 'attribute')　　返回结果按照给出的字段排序。

　　order_by(User.name.desc()) 或者 order_by('name desc')

　　filter(condition1).filter(condition2)　　多个拼接的filter就相当于and_(condition1,condition2...)

　　请注意以上所有方法都要在all()之前调用，all出来已经是一个列表了，不能在作为调用方调用这些方法了。

　　还可以些数据库自带的函数，在用之前记得from sqlalchemy import func，就可以通过func来调用了。这些函数不是放在调用链中，大多数时候都是放在query方法的参数位置，

　　比如func.count()　　统计一共有多少条记录作为结果等等

计数：
可以直接使用 .count()计算有多少行：
session.query(User).filter(User.name.like('%ed')).count()

当需要将指定的东西进行计数，就使用func.count()：
session.query(func.count(User.id)).scalar()

使用query.join()结合两张表来查询数据表数据：


result = session.query(AlchemyTest).join(AlchemyTestError).filter(AlchemyTestError.errorname == 'cuowu').first() #查询AlchemyTest表内的数据，看哪些user有这个error
if result is not None:
    print(result.TestName)

因为这两张表只有一个外键，所以query.join()知道怎么关联这两张表，但是如果没有或者不止一个外键的时候，使用其他的形式：
result = session.query(AlchemyTest).join((AlchemyTestError, AlchemyTest.TestName==AlchemyTestError.TestName)).first()

这里有一个join的reminder：

outerjoin: query.outerjoin(User.addresses) # LEFT OUTER JOIN

一、课程简介

1.1 实验内容

本课程带领大家使用 SQLAlchemy 连接 MySQL 数据库，创建一个博客应用所需要的数据表，并介绍了使用 SQLAlchemy 进行简单了 CURD 操作及使用 Faker 生成测试数据。

1.2课程知识点

学会用 SQLALchemy 连接数据库(MySQL, SQLite, PostgreSQL), 创建数据表；
掌握表数据之间一对一，一对多及多对多的关系并能转化为对应 SQLAlchemy 描述；
掌握使用 SQLAlchemy 进行 CURD 操作；
学会使用 Faker 生成测试数据

学习本课程需要你对 Python 与 MySQL 都有基本的掌握。

二、ORM 与 SQLAlchemy 简介

ORM 全称 Object Relational Mapping, 翻译过来叫对象关系映射。简单的说，ORM 将数据库中的表与面向对象语言中的类建立了一种对应关系。这样，我们要操作数据库，数据库中的表或者表中的一条记录就可以直接通过操作类或者类实例来完成。

SQLAlchemy 是Python 社区最知名的 ORM 工具之一，为高效和高性能的数据库访问设计，实现了完整的企业级持久模型。

接下来我们将使用 SQLAlchemy 和 MySQL 构建一个博客应用的实验库。

先安装 SQLAlchemy:

sudo pip install sqlalchemy

三、连接与创建

实验楼环境已经为我们安装了 MySQL，但还没有启动, 在启动 MySQL 之前，我们需要进行一些配置，将 MySQL 默认的 latin1 编码改成 utf8 。

sudo vim /etc/mysql/my.cnf

通过上面的命令打开 MySQL 的配置文件，添加下面几个配置：


[client]
default-character-set = utf8
 
[mysqld]
character-set-server = utf8
 
[mysql]
default-character-set = utf8

保存退出。现在我们可以启动 MySQL 服务了：

sudo service mysql start

在命令行下输入下面命令启动 MySQL：

mysql -uroot -p

看到上面的内容就说明我们的 MySQL 可以正常启动了（注意，上面的密码不需要输入内容，直接回车就行）, 并且我们我们通过命令：

> create database blog;

创建一个名为 blog 的数据库为下面的使用作准备。

另外，我们需要安装一个 Python 与 MySQL 之间的驱动程序：

sudo apt-get install python-mysqldb

3.1 连接数据库

我们在 Code 下新建个 Python 文件，叫什么名字就随你便了，这里我们叫 db.py，写入下面的内容：


# coding: utf-8
 
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog')
print(engine)

在上面的程序中，我们连接了默认运行在 3306 端口的 MySQL 中的 blog 数据库。

运行下这个程序，看到下面的信息说明我们已经连接成功了：

3.2 描述表结构

要使用 ORM, 我们需要将数据表的结构用 ORM 的语言描述出来。SQLAlchmey 提供了一套 Declarative 系统来完成这个任务。我们以创建一个 users 表为例，看看它是怎么用 SQLAlchemy 的语言来描述的：


# coding: utf-8
 
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer
 
 
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog?charset=utf8')
Base = declarative_base()
 
 
class User(Base):
 
    __tablename__ = 'users'
 
    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)
 
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.username)

我们看到，在 User 类中，用 __tablename__ 指定在 MySQL 中表的名字。我们创建了三个基本字段，类中的每一个 Column 代表数据库中的一列，在 Colunm中，指定该列的一些配置。第一个字段代表类的数据类型，上面我们使用 String, Integer 俩个最常用的类型，其他常用的包括：


Text
Boolean
SmallInteger
DateTime

nullable=False 代表这一列不可以为空，index=True 表示在该列创建索引。

另外定义 __repr__ 是为了方便调试，你可以不定义，也可以定义的更详细一些。

python db.py

运行程序，我们在 MySQL 中看看表是如何创建的：

四、关系定义

4.1 一对多关系

对于一个普通的博客应用来说，用户和文章显然是一个一对多的关系，一篇文章属于一个用户，一个用户可以写很多篇文章，那么他们之间的关系可以这样定义：


class User(Base):
 
    __tablename__ = 'users'
 
    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)
    articles = relationship('Article')
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.username)
 
 
class Article(Base):
 
    __tablename__ = 'articles'
 
    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False, index=True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
    author = relationship('User')
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.title)

每篇文章有一个外键指向 users 表中的主键 id，而在 User 中使用 SQLAlchemy 提供的 relationship 描述关系。而用户与文章的之间的这个关系是双向的，所以我们看到上面的两张表中都定义了 relationship。

SQLAlchemy 提供了 backref 让我们可以只需要定义一个关系：

articles = relationship('Article', backref='author')

添加了这个就可以不用再在 Article 中定义 relationship 了！

4.2 一对一关系

在 User 中我们只定义了几个必须的字段，但通常用户还有很多其他信息，但这些信息可能不是必须填写的，我们可以把它们放到另一张 UserInfo 表中，这样User 和 UserInfo 就形成了一对一的关系。你可能会奇怪一对一关系为什么不在一对多关系前面？那是因为一对一关系是基于一对多定义的：


class User(Base):
 
    __tablename__ = 'users'
 
    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)
    articles = relationship('Article', backref='author')
    userinfo = relationship('UserInfo', backref='user', uselist=False)
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.username)
 
 
class UserInfo(Base):
 
    __tablename__ = 'userinfos'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    qq = Column(String(11))
    phone = Column(String(11))
    link = Column(String(64))
    user_id = Column(Integer, ForeignKey('users.id'))

定义方法和一对多相同，只是需要添加 userlist=False 。

4.3 多对多关系

一遍博客通常有一个分类，好几个标签。标签与博客之间就是一个多对多的关系。多对多关系不能直接定义，需要分解成俩个一对多的关系，为此，需要一张额外的表来协助完成：


article_tag = Table(
    'article_tag', Base.metadata,
    Column('article_id', Integer, ForeignKey('articles.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
)
 
 
class Tag(Base):
 
    __tablename__ = 'tags'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.name)

4.4 映射到数据

表已经描述好了，在文件末尾使用下面的命令在我们连接的数据库中创建对应的表：


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

进入 MySQL 看看：

所有的表都已经创建好了！

五、简单 CURD

当你想打电话给朋友时，你是否得用手机拨通他的号码才能建立起一个会话？同样的，你想和 MySQL 交谈也得先通过 SQLAlchemy 建立一个会话：


from sqlalchemy.orm import sessionmaker
 
Session = sessionmaker(bind=engine)
session = Session()

你可以把 sessionmaker 想象成一个手机，engine 当做 MySQL 的号码，拨通这个“号码”我们就创建了一个 Session 类，下面就可以通过这个类的实例与 MySQL 愉快的交谈了！

5.1 Create

如果你玩过LOL, 我想你一定知道Faker。而在 Python的世界中，Faker 是用来生成虚假数据的库。安装它：

sudo pip install faker

下面结合 Faker 库创建一些测试数据：


    faker = Factory.create()
    Session = sessionmaker(bind=engine)
    session = Session()
 
    faker_users = [User(
        username=faker.name(),
        password=faker.word(),
        email=faker.email(),
    ) for i in range(10)]
    session.add_all(faker_users)
 
    faker_categories = [Category(name=faker.word()) for i in range(5)]
    session.add_all(faker_categories)
 
    faker_tags= [Tag(name=faker.word()) for i in range(20)]
    session.add_all(faker_tags)
 
    for i in range(100):
        article = Article(
            title=faker.sentence(),
            content=' '.join(faker.sentences(nb=random.randint(10, 20))),
            author=random.choice(faker_users),
            category=random.choice(faker_categories)
        )
        for tag in random.sample(faker_tags, random.randint(2, 5)):
            article.tags.append(tag)
        session.add(article)
 
    session.commit()

在上面的代码中我们创建了10个用户，5个分类，20个标签，100篇文章，并且为每篇文章随机选择了2~5个标签。

使用 SQLAlchemy 往数据库中添加数据，我们只需要创建相关类的实例，调用 session.add() 添加一个，或者 session.add_all() 一次添加多个，最后 session.commit() 就可以了。

5.3 Update

更新一个字段：


>>> a = session.query(Article).get(10)
>>> a.title = 'My test blog post'
>>> session.add(a)
>>> session.commit()

添加一个标签：


>>> a = session.query(Article).get(10)
>>> a.tags.append(Tag(name='python'))
>>> session.add(a)
>>> session.commit()

5.4 Delete


>>> a = session.query(Article).get(10)
>>> session.delete(a)
>>> session.commit()

删除直接调用 delete 删除获取到的对象，提交 session 即可。

Text类型与String类型区别

在SQLAlchemy中，Text和String都是用于在数据库中存储字符串的类型，但它们之间有一些区别。

存储需求：String类型是变长字符串，它存储的是字符数据的最大长度，而Text类型则是可变长度的字符串，它存储的是数据的实际长度。因此，如果存储的字符串长度固定，使用String更有效率，而如果存储的字符串长度不固定，使用Text更合适。
性能考虑：由于Text类型存储的是实际长度，所以在某些数据库中，它可能比String类型在性能上更占优势。尤其是在处理大量数据时，使用Text类型可能会提高查询效率。
适用场景：通常情况下，使用String类型就足够了，因为它适用于大多数常见的字符串场景。但是，如果你需要存储大量文本数据，例如文章、描述等，使用Text类型可能更适合。

总之，在选择Text和String类型时，需要考虑你的实际需求和数据库性能。在大多数情况下，使用String类型是足够的，但如果你需要存储大量可变长度的文本数据，使用Text类型可能更有优势。

完整代码


# coding: utf-8
 
import random
from faker import Factory
 
from sqlalchemy import create_engine, Table
from sqlalchemy.ext.declarative import declarative_base #提取声明
from sqlalchemy import ForeignKey   #外键
from sqlalchemy import Column, String, Integer, Text
from sqlalchemy.orm import sessionmaker, relationship  #创建会议、关系
 
 
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog?charset=utf8')
Base = declarative_base()
 
 
class User(Base):
 
    __tablename__ = 'users'
 
    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)
    articles = relationship('Article', backref='author')
    userinfo = relationship('UserInfo', backref='user', uselist=False)
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.username)
 
 
class UserInfo(Base):
 
    __tablename__ = 'userinfos'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    qq = Column(String(11))
    phone = Column(String(11))
    link = Column(String(64))
    user_id = Column(Integer, ForeignKey('users.id'))
 
 
class Article(Base):
 
    __tablename__ = 'articles'
 
    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False, index=True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
    cate_id = Column(Integer, ForeignKey('categories.id'))
    tags = relationship('Tag', secondary='article_tag', backref='articles')
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.title)
 
 
class Category(Base):
 
    __tablename__ = 'categories'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)
    articles = relationship('Article', backref='category')
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.name)
 
 
article_tag = Table(
    'article_tag', Base.metadata,
    Column('article_id', Integer, ForeignKey('articles.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
)
 
 
class Tag(Base):
 
    __tablename__ = 'tags'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)
 
    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.name)
 
 
if __name__ == '__main__':
    Base.metadata.create_all(engine)
 
    faker = Factory.create()
    Session = sessionmaker(bind=engine)
    session = Session()
 
    faker_users = [User(
        username=faker.name(),
        password=faker.word(),
        email=faker.email(),
    ) for i in range(10)]
    session.add_all(faker_users)
 
    faker_categories = [Category(name=faker.word()) for i in range(5)]
    session.add_all(faker_categories)
 
    faker_tags= [Tag(name=faker.word()) for i in range(20)]
    session.add_all(faker_tags)
 
    for i in range(100):
        article = Article(
            title=faker.sentence(),
            content=' '.join(faker.sentences(nb=random.randint(10, 20))),
            author=random.choice(faker_users),
            category=random.choice(faker_categories)
        )
        for tag in random.sample(faker_tags, random.randint(2, 5)):
            article.tags.append(tag)
        session.add(article)
 
    session.commit()

六、总结与习题

本篇教程带领大家使用 SQLAlchemy 创建了一个博客应用的数据库及相关表，再次过程中，介绍了使用 SQLAlchemy 定义一对一、一对多及多对多关系，CURD 及使用 Faker 生成测试数据。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/605700