当前位置:威尼斯 > 数据库 > 这种设计可用于自定义列表查询,因此database表示

这种设计可用于自定义列表查询,因此database表示

文章作者:数据库 上传时间:2019-11-08

数据库中为了实现表格数据的自由设置,我们经常设计纵表,或者列定义的表(如下KeyValue),定义一个列超级多的表中每个字段的意义。

Flask-SQLAlchemy是一个Flask的一个扩展,简化了在Flask程序中使用SQLAlchemy的操作难度。SQLAlchemy是一个很强大的关系型数据库框架,支持多种数据库后台。SQLAlchemy提供了高层ORM,也提供了使用数据库原生SQL的低层功能。而Flask-Migrate扩展是对SQLAlchemy的主力开发人员编写的Alembic迁移框架的一个轻量级包装,并集成到了Flask-Script中。

但是在设计时简单的东西却很容易被人们忘记,如下一个简单但是很松散的列表查询。

使用pip安装Flask的扩展

(flask)$ pip install flask-sqlalchemy
(flask)$ pip install flask-migrate

 

使用flask-sqlalchemy管理数据库

Flask-Sqlalchemy数据库URL

数据库引擎 URL
MySQL mysql://username:password@hostname/database
Postgres postgresql://username:password@hostname/database
SQLite(Unix) sqlite:////absolute/path/database
SQLite(Windows)

在这些URL中,hostname表示MySQL服务器所在的主机,可以是本地主机(localhost),也可以是远程服务器。数据服务器上可以托管多个数据库,因此database表示要是使用的数据库名。如果数据库需要进行认证,username和password表示数据库用户和密码。

程序中使用的数据库URL必须保存到Flask配置对象config.py的SQLALCHEMY_DATABASE_URI键中。另外一个很有用的选项,即SQLALCHEMY_COMMIT_ON_TEARDOWN 键,将其设置为True时,每次请求结束后都会自动提交数据库中的变动。

--CREATE TABLE KeyValue
--(
--[key] NVARCHAR(50) ,
--[value] NVARCHAR(50)
--)

CREATE TABLE TableNumber
(
Name NVARCHAR(50),
Filed1 NVARCHAR(50),
Filed2 NVARCHAR(50),
Filed3 NVARCHAR(50),
Filed4 NVARCHAR(50),
Filed5 NVARCHAR(50),
)
INSERT INTO dbo.TableNumber
        ( Name ,
          Filed1 ,
          Filed2 ,
          Filed3 ,
          Filed4 ,
          Filed5
        )
VALUES  ( N'张三2' , -- Name - nvarchar(50)
          N'中国2' , -- Filed1 - nvarchar(50)
          N'河北2' , -- Filed2 - nvarchar(50)
          N'唐山2' , -- Filed3 - nvarchar(50)
          N'' , -- Filed4 - nvarchar(50)
          N''  -- Filed5 - nvarchar(50)
        )

INSERT INTO KeyValue VALUES('Filed1','国家')
INSERT INTO KeyValue VALUES('Filed2','省')
INSERT INTO KeyValue VALUES('Filed3','市')


DECLARE @msg NVARCHAR(max)=''
SELECT @msg=@msg+','+[key]+' as '+value  FROM KeyValue 

SET @msg='Name as 名称'+@msg;

DECLARE @sql NVARCHAR(max);
SET @sql='select '+@msg +' from TableNumber'

EXEC( @sql)

定义数据模型

和所有的应用一样,我们先设计简单的用户和权限模型。模型这个术语表示程序中使用的持久化实体。在 ORM 中,模型一般是一个 Python 类,类中的属性对应数据库表中的列。
示例 2-1 app/models.py: 定义 Role 和 User 模型

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)

    def __repr__(self):
        return '<Role %r>' % self.name

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, index=True)
    def __repr__(self):
        return '<User %r>' % self.username

类变量tablename定义在数据库中使用的表名。其余的类变量都是该模型的属性,被定义为db.Column类的示例。

db.Column类构造函数的第一个参数是数据库列和模型属性的类型。下表列出了一些最常用的列类型以及在模型中使用的Python类型。

最常用的AQLAlchemy列类型

类型名 Python类型 说明
Integer int 普通整数,一般是32位
SmallInteger int 取值范围小的整数,一般是16位
BigInteger int或long 不限制精度的整数
Float float 浮点数
Numeric decimal.Decimal 定点数
String str 变长字符串
Text str 变长字符串,对较长或不限长度的字符串做了优化
Unicode unicode 变长Unicode字符串
UnicodeText unicode 变长Unicode字符串,对较长或不限长度的字符床做了优化
Boolean bool 布尔值
Date datetime.date 日期
Time datetime.time 时间
DateTime datetime.datetime 日期和时间
Interval datetime.timedeta 时间间隔
Enum str 一组字符串
PickleType 任何Python对象 自动化使用Pickle序列化
LargeBinary str 二进制文件

最常用的SQLAlchemy列选项

选项名 说明
primary_key 如果设为True,这列就是表的主键
unique 如果设为True,这列不允许出现重复
index 如果设为True,为这列创建索引,提升查询效率
nullable 如果设为True,这列允许使用空值;如果设为False,这列不允许使用空值
default 为这列定义默认值

图片 1

关系

app/models.py 一对多关系模型

class Role(db.Model):
    # ...
    users = db.relationship('User', backref='role', lazy='dynamic')

class User(UserMixin, db.Model):
    # ...
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

常用的SQLAlchemy关系选项

选项名 说明
backref 在关系的另一个模型中添加反向引用
primaryjoin 明确指定两个模型之间使用的联结条件。只在模棱两可的关系中需要指定
lazy 指定如何加载相关记录。可选值有select(首次访问时按需加载)、immediate(源对象加载后加载)、joined(加载记录,但使用联结)、subquery(立即加载,但使用子查询),noload(永不加载)和dynamic(不加载记录,但提供加载记录的查询)
uselist 如果设为False,不使用列表,而使用标量值
order_by 指定关系中记录的排序方式
secondary 指定多对多关系表的名字
secondaryjoin SQLAlchemy无法自行决定时,指定多对多关系中的二级联结条件

一对多 关系之外,还有几种其它的关系类型。一对一 关系可以用前面介绍的 一对多 关系表示,但调用db.relationship()时要把uselist设为False,把“多”变成“一”。 多对一 关系也可使用 一对多 表示,对调两个表即可,或者把外键和db.relationship()都放在“多”这一侧。最复杂的关系是 多对多 ,需要用到第三张表,这个表称为 关系表

 

创建迁移仓库

定义好数据模型之后,就应该设计数据库了,不管你用mysql还是sqlite,flask-SQLAlchemy和flask-Migrate会根据数据模型自动创建数据库。
为了导出数据库迁移命令,flask-Migrate提供了一个MigrateCommand类,可附加在flask-script的manage对象上。设置manage.py

...
from flask.ext.migrate import Migrate, MigrateCommand

...

migrate = Migrate(app=app, db=db)
manager.add_command('db', MigrateCommand)

使用一下命令创建数据库及迁移仓库

(flask)$ python manage.py db init
(flask)$ python manage.py db migrate -m "initial migration"
(flask)$ python manage.py db upgrade

注意事项,虽然flask-migrate提供了downgrade()函数,可以将改动删除。但是建议大家不要随便使用。如果你觉得数据库设计得有问题,建议你删除相关数据库设计,重新再来。

这种设计可用于自定义列表查询,EXCEL导出,很方便,使用起来比在C#中写更便捷,不过缺点就是依赖数据库。

数据库操作

插入数据

user = User(username=u'张三')
db.session.add(user)  # 插入数据
db.session.commit()  # 只有提交事务了,才可以获取(user.id)数据的ID值。

查询数据

a. 用主键获取数据:

User.query.get(1)
<User u'张三'>

b. 通过一个精确参数进行反查:

peter=User.query.filter_by(username='peter').first()  #注意:精确查询函数query.filter_by(),是通过传递参数进行查询;其他增强型查询函数是query.filter(),通过传递表达式进行查询。
print(peter.id)  #如果数据不存在则返回None

c. 模糊查询:

User.query.filter(User.email.endswith('@example.com')).all()
[<User u'admin'>, <User u'guest'>]

c1. 逻辑非1:

peter = User.query.filter(User.username != 'peter').first()
print(peter.id)

c2. 逻辑非2:

from sqlalchemy import not_
peter = User.query.filter(not_(User.username=='peter')).first()
print(peter.id)

c3. 逻辑与:

from sqlalchemy import and_
peter = User.query.filter(and_(User.username=='peter', User.email.endswith('@example.com'))).first()
print(peter.id)

c4. 逻辑或:

from sqlalchemy import or_
peter = User.query.filter(or_(User.username != 'peter', User.email.endswith('@example.com'))).first()
print(peter.id)

d. 查询数据加工

d1. 排序:

User.query.order_by(User.username)  #嘿嘿,你用哪个字段作为排序参考呢?
[<User u'admin'>, <User u'guest'>, <User u'peter'>]

d2. 限制返回的数目:

User.query.limit(1).all()
[<User u'admin'>]

e. 查询数据返回

e1. 返回查询到的第一个对象:

r = User.query.first()
print(r)

e2. 返回所有查询到的对象:

r = User.query.all()
print(r)

常用的SQLAlchemy查询过滤器

过滤器 说明
filter() 把过滤器添加到原查询上,返回一个新查询
filter_by() 把等值过滤器添加到原查询上,返回一个新查询
limit() 使用指定的值限制原查询返回的结果数量,返回一个新查询
offset() 偏移原查询返回的结果,返回一个新查询
order_by() 根据指定条件对原查询结果进行排序,返回一个新查询
group_by() 根据指定条件对原查询进行分组,返回一个新查询

在查询上应用指定的过滤器后,通过调用all()执行查询,以列表形式返回结果。除了all(),还有其它方法能触发查询执行。

最常用的SQLAlchemy查询执行函数

方法 说明
all() 以列表形式返回查询的所有结果
first() 返回查询的第一个结果,如果没有结果,返回None
first_or_404() 返回查询的第一个结果,如果没有结果,则终止请求,返回404错误响应
get() 返回指定主键对应的行,如果没有对应的行,返回None
get_or_404() 返回指定主键对应的行,如果没有对应的行,则终止请求,返回404错误响应
count() 返回查询结果的数量
paginate() 返回一个Paginate对象,它包含指定范围内的结果。

删除数据

u = User.query.first()
db.session.delete(u)  #删除数据和插入数据一样简单,但必须是通过查询返回的对象。
db.session.commit()

更新数据

u = User.query.first()
u.username = 'guest'  #更新数据和变量赋值那么简单,但必须是通过查询返回的对象。
db.session.commit()

本文由威尼斯发布于数据库,转载请注明出处:这种设计可用于自定义列表查询,因此database表示

关键词: