SQLAlchemy基础
1 简介
SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射(ORM)工具,使用MIT许可证发行。
SQLAlchemy“采用简单的Python语言,为高效和高性能的数据库访问设计,实现了完整的企业级持久模型”。
SQLAlchemy首次发行于2006年2月,并迅速地在Python社区中最广泛使用的ORM工具之一,不亚于Django的ORM框架。
Flask-SQLAlchemy是在Flask框架的一个扩展,其对SQLAlchemy进行了封装,目的于简化在 Flask 中 SQLAlchemy 的 使用,提供了有用的默认值和额外的助手来更简单地完成日常任务。
2 安装
安装Flask-SQLAlchemy
pip install flask-sqlalchemy
如果使用的是MySQL数据库,还需要安装MySQL的Python客户端库
pip install mysqlclient
3 数据库连接设置
在Flask中使用Flask-SQLAlchemy需要进行配置,主要配置以下几项:
-
SQLALCHEMY_DATABASE_URI
数据库的连接信息-
Postgres:
postgresql://user:password@localhost/mydatabase
-
MySQL:
mysql://user:password@localhost/mydatabase
-
Oracle:
oracle://user:password@127.0.0.1:1521/sidname
-
SQLite (注意开头的四个斜线):
sqlite:////absolute/path/to/foo.db
-
-
SQLALCHEMY_TRACK_MODIFICATIONS
在Flask中是否追踪数据修改 -
SQLALCHEMY_ECHO
显示生成的SQL语句,可用于调试
这些配置参数需要放在Flask的应用配置(app.config
)中。
from flask import Flask app = Flask(__name__) class Config(object): SQLALCHEMY_DATABASE_URI = 'mysql://root:mysql@127.0.0.1:3306/toutiao' SQLALCHEMY_TRACK_MODIFICATIONS = False SQLALCHEMY_ECHO = True app.config.from_object(Config)
其他配置参考如下:
名字 | 备注 |
---|---|
SQLALCHEMY_DATABASE_URI | 用于连接的数据库 URI 。例如:sqlite:////tmp/test.dbmysql://username:password@server/db |
SQLALCHEMY_BINDS | 一个映射 binds 到连接 URI 的字典。更多 binds 的信息见用 Binds 操作多个数据库。 |
SQLALCHEMY_ECHO | 如果设置为Ture, SQLAlchemy 会记录所有 发给 stderr 的语句,这对调试有用。(打印sql语句) |
SQLALCHEMY_RECORD_QUERIES | 可以用于显式地禁用或启用查询记录。查询记录 在调试或测试模式自动启用。更多信息见get_debug_queries()。 |
SQLALCHEMY_NATIVE_UNICODE | 可以用于显式禁用原生 unicode 支持。当使用 不合适的指定无编码的数据库默认值时,这对于 一些数据库适配器是必须的(比如 Ubuntu 上 某些版本的 PostgreSQL )。 |
SQLALCHEMY_POOL_SIZE | 数据库连接池的大小。默认是引擎默认值(通常 是 5 ) |
SQLALCHEMY_POOL_TIMEOUT | 设定连接池的连接超时时间。默认是 10 。 |
SQLALCHEMY_POOL_RECYCLE | 多少秒后自动回收连接。这对 MySQL 是必要的, 它默认移除闲置多于 8 小时的连接。注意如果 使用了 MySQL , Flask-SQLALchemy 自动设定 这个值为 2 小时。 |
4 模型类字段与选项
字段类型
类型名 | python中类型 | 说明 |
---|---|---|
Integer | int | 普通整数,一般是32位 |
SmallInteger | int | 取值范围小的整数,一般是16位 |
BigInteger | int或long | 不限制精度的整数 |
Float | float | 浮点数 |
Numeric | decimal.Decimal | 普通整数,一般是32位 |
String | str | 变长字符串 |
Text | str | 变长字符串,对较长或不限长度的字符串做了优化 |
Unicode | unicode | 变长Unicode字符串 |
UnicodeText | unicode | 变长Unicode字符串,对较长或不限长度的字符串做了优化 |
Boolean | bool | 布尔值 |
Date | datetime.date | 时间 |
Time | datetime.datetime | 日期和时间 |
LargeBinary | str | 二进制文件 |
列选项
选项名 | 说明 |
---|---|
primary_key | 如果为True,代表表的主键 |
unique | 如果为True,代表这列不允许出现重复的值 |
index | 如果为True,为这列创建索引,提高查询效率 |
nullable | 如果为True,允许有空值,如果为False,不允许有空值 |
default | 为这列定义默认值 |
关系选项
选项名 | 说明 |
---|---|
backref | 在关系的另一模型中添加反向引用 |
primary join | 明确指定两个模型之间使用的联结条件 |
uselist | 如果为False,不使用列表,而使用标量值 |
order_by | 指定关系中记录的排序方式 |
secondary | 指定多对多关系中关系表的名字 |
secondary join | 在SQLAlchemy中无法自行决定时,指定多对多关系中的二级联结条件 |
5 构建模型类映射
利用一下三张表构建模型类映射
CREATE TABLE `user_basic` ( `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID', `account` varchar(20) COMMENT '账号', `email` varchar(20) COMMENT '邮箱', `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态,是否可用,0-不可用,1-可用', `mobile` char(11) NOT NULL COMMENT '手机号', `password` varchar(93) NULL COMMENT '密码', `user_name` varchar(32) NOT NULL COMMENT '昵称', `profile_photo` varchar(128) NULL COMMENT '头像', `last_login` datetime NULL COMMENT '最后登录时间', `is_media` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是自媒体,0-不是,1-是', `is_verified` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否实名认证,0-不是,1-是', `introduction` varchar(50) NULL COMMENT '简介', `certificate` varchar(30) NULL COMMENT '认证', `article_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '发文章数', `following_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '关注的人数', `fans_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '被关注的人数', `like_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计点赞人数', `read_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计阅读人数', PRIMARY KEY (`user_id`), UNIQUE KEY `mobile` (`mobile`), UNIQUE KEY `user_name` (`user_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户基本信息表'; CREATE TABLE `user_profile` ( `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID', `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别,0-男,1-女', `birthday` date NULL COMMENT '生日', `real_name` varchar(32) NULL COMMENT '真实姓名', `id_number` varchar(20) NULL COMMENT '身份证号', `id_card_front` varchar(128) NULL COMMENT '身份证正面', `id_card_back` varchar(128) NULL COMMENT '身份证背面', `id_card_handheld` varchar(128) NULL COMMENT '手持身份证', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `register_media_time` datetime NULL COMMENT '注册自媒体时间', `area` varchar(20) COMMENT '地区', `company` varchar(20) COMMENT '公司', `career` varchar(20) COMMENT '职业', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户资料表'; CREATE TABLE `user_relation` ( `relation_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id', `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID', `target_user_id` bigint(20) unsigned NOT NULL COMMENT '目标用户ID', `relation` tinyint(1) NOT NULL DEFAULT '0' COMMENT '关系,0-取消,1-关注,2-拉黑', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`relation_id`), UNIQUE KEY `user_target` (`user_id`, `target_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户关系表';
首先需要创建SQLAlchemy对象:
-
方式一:
db = SQLAlchemy(app)
-
方式二:
db = SQLAlchemy() db.init_app(app)
注意此方式在单独运行调试时,对数据库操作需要在Flask的应用上下文中进行,即
with app.app_context(): User.query.all()
python中定义模型类如下:
from flask import Flask from flask_sqlalchemy import SQLAlchemy from datetime import datetime app = Flask(__name__) db = SQLAlchemy(app) class Config(object): SQLALCHEMY_DATABASE_URI = 'mysql://root:root@127.0.0.1:3306/toutiaobackend' SQLALCHEMY_TRACK_MODIFICATIONS = False #在Flask中是否追踪数据修改 SQLALCHEMY_ECHO = True #显示生成的SQL语句,可用于调试 class User(db.Model): """ 用户的基本信息表 """ __tablename__ = "user_basic" class STATUS: ENABLE = 1 DISABLE = 0 id = db.Column("user_id",db.Integer,primary_key=True,doc="用户id") account = db.Column(db.String,doc="账号") email = db.Column(db.String,doc="邮箱") status = db.Column(db.Integer,default=1,doc="是否可用") mobile = db.Column(db.String,doc="手机号") password = db.Column(db.String,doc="密码") name = db.Column('user_name', db.String, doc='昵称') photo=db.Column("profile_photo",db.String,doc="头像") last_login = db.Column(db.DateTime,doc="最后一次登录时间") is_media= db.Column(db.Boolean,default=0,doc="是否是自媒体") is_verified= db.Column(db.Boolean,default=0,doc="是否认证") introduction = db.Column(db.String,doc="简介") certificate = db.Column(db.String,doc="认证") article_count = db.Column(db.Integer,default=0,doc="文章数量") following_count = db.Column(db.Integer,default=0,doc="关注的人数") like_count = db.Column(db.Integer,default=0,doc="累计点赞数") read_count = db.Column(db.Integer,default=0,doc="阅读数量") class UserProfile(db.Model): """ 用户资料表 """ __tablename__ = 'user_profile' class GENDER: MALE = 0 FEMALE = 1 id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID') gender = db.Column(db.Integer, default=0, doc='性别') birthday = db.Column(db.Date, doc='生日') real_name = db.Column(db.String, doc='真实姓名') id_number = db.Column(db.String, doc='身份证号') id_card_front = db.Column(db.String, doc='身份证正面') id_card_back = db.Column(db.String, doc='身份证背面') id_card_handheld = db.Column(db.String, doc='手持身份证') ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间') utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间') register_media_time = db.Column(db.DateTime, doc='注册自媒体时间') area = db.Column(db.String, doc='地区') company = db.Column(db.String, doc='公司') career = db.Column(db.String, doc='职业') class Relation(db.Model): """ 用户关系表 """ __tablename__ = 'user_relation' class RELATION: DELETE = 0 FOLLOW = 1 BLACKLIST = 2 id = db.Column('relation_id', db.Integer, primary_key=True, doc='主键ID') user_id = db.Column(db.Integer, doc='用户ID') target_user_id = db.Column(db.Integer, doc='目标用户ID') relation = db.Column(db.Integer, doc='关系') ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间') utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间') app.config.from_object(Config)