SQLAlchemy的简单使用

1.简述

最近在学习tornado，发现没有Orm模型。其自带的torndb模块不支持python3，学习一下SQLAlchemy供以后工作中使用。

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

1.1 知识要点

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

2. ORM 与 SQLAlchemy 简单介绍

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

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

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

3. 连接与创建

安装SQLAlchemy

pip install sqlalchemy

数据库我们采用Mysql，安装过程这里省略。可参考我的lnmp安装步骤
http://www.jianshu.com/p/1e51985b46dd

启动mysql服务

systemctl start mysqld

进入数据库命令行

mysql

更改数据库授权，远程主机可访问

update mysql.user set Host='%' where HOST='localhost' and User='root';

接下来我们使用图形化数据库操作工具（Navicat Premium）来操作数据库
创建一个blog的数据库

安装数据库驱动

pip install pymysql

3.1 连接数据库

新建一个db.py的文件，写入下面的内容：

#!/usr/bin/env python
# _*_ coding:utf-8 _*_
__author__ = 'junxi'

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('mysql+pymysql://blog:123456@localhost:3306/blog?charset=utf8')
Base = declarative_base()

print(engine)

运行：

Engine(mysql+pymysql://blog:***@localhost:3306/blog?charset=utf8)

3.2 描述表结构

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

编辑db.py:

#!/usr/bin/env python
# _*_ coding:utf-8 _*_
__author__ = 'junxi'

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


engine = create_engine('mysql+pymysql://blog:123456@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)


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

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

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

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

运行 db.py

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

C:\Windows\system32>mysql -ublog -p123456

mysql> use blog;
Database changed

mysql> show create table users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_users_username` (`username`),
  KEY `ix_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4. 关系定义

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, name="标题")
    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')
    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 ArticleTag(Base):
    __tablename__ = 'article_tag'

    id = Column(Integer, primary_key=True)
    article_id = Column(Integer, ForeignKey('articles.id'))
    tag_id = Column(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 映射到数据库

#!/usr/bin/env python
# _*_ coding:utf-8 _*_
__author__ = 'junxi'

"""
mysql://username:password@hostname/database
postgresql://username:password@hostname/database
sqlite:////absolute/path/to/database
sqlite:///c:/absolute/path/to/database
"""

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

engine = create_engine('mysql+pymysql://blog:123456@localhost:3306/blog?charset=utf8')
Base = declarative_base()

# print(engine)


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')
    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"))
    author = relationship('User')

    def __repr__(self):
        return "%s(%r)" % (self.__class__.__name__, self.title)


"""
# 这是创建表的另一种写法
article_tag = Table(
    'article_tag', Base.metadata,
    Column('article_id', Integer, ForeignKey('articles.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
)
"""


class ArticleTag(Base):
    __tablename__ = 'article_tag'

    id = Column(Integer, primary_key=True)
    article_id = Column(Integer, ForeignKey('articles.id'))
    tag_id = Column(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)

进入MySQL查看:

mysql> use blog;
Database changed
mysql> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| article_tag    |
| articles       |
| tags           |
| userinfos      |
| users          |
+----------------+
5 rows in set (0.00 sec)

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