Flask-数据库

pip install -U Flask-SQLAlchemy

Flask-SQLAlchemy: Quickstart — Flask-SQLAlchemy Documentation (2.x)

Flask-SQLAlchemy 主要是对 SQLAlchemy 的封装,所以详细的教程还是要看:

SQLAlchemy - The Database Toolkit for Python

一、使用

1.创建连接

主要参数就是下面三个:

SQLALCHEMY_DATABASE_URI = "postgresql+psycopg2://username:password@ip:5432/db"
SQLALCHEMY_POOL_SIZE = 3
SQLALCHEMY_POOL_RECYCLE = 900

SQLALCHEMY_DATABASE_URI: 应用于连接的数据库 URI

SQLALCHEMY_POOL_SIZE:数据库池的大小。

SQLALCHEMY_POOL_RECYCLE: 超过此小时后将自动回收连接的秒数。

URI 的格式为:

dialect+driver://username:password@host:port/database

dialect是数据库类型: 例如 mysql、postgresql

driver是驱动类型: 例如postgresql有psycopg2(默认), mysql有mysqldb(默认)、pymysql


Engine Configuration — SQLAlchemy 1.4 Documentation

初始化:

# 加载配置
app = Flask(__name__)
app.config.from_object('config.config')


# 初始化db
db = SQLAlchemy()
db.init_app(app)

2.创建模型类

import db


class User(db.Model):
 

    id = db.Column(db.String(255), primary_key=True)
    username= db.Column(db.String(255))
    email= db.Column(db.String(255))
 

字段类型:

Integer	an integer
String(size)	a string with a maximum length (optional in some databases, e.g. PostgreSQL)
Text	some longer unicode text
DateTime	date and time expressed as Python datetime object.
Float	stores floating point values
Boolean	stores a boolean value
PickleType	stores a pickled Python object
LargeBinary	stores large arbitrary binary data

后面的字段设置再源码中有介绍,这些设置有的是在建表时有有效(模型可以去数据库里建表),有的是生成对象时有效

3.增删改查

Flask-SQLAlchemy 提供的增删改查

import User


me = User('1', 'zhangsan', 'admin@example.com')
db.session.add(me)
db.session.commit()



db.session.delete(me)
db.session.commit()

查询:

id	username	email
1	admin	admin@example.com
2	peter	peter@example.org
3	guest	guest@example.com

>>> peter = User.query.filter_by(username='peter').first()
>>> peter.id
2
>>> peter.email
u'peter@example.org'


>>> missing = User.query.filter_by(username='missing').first()
>>> missing is None
True


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


>>> User.query.order_by(User.username).all()
[<User u'admin'>, <User u'guest'>, <User u'peter'>]



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


>>> User.query.get(1)
<User u'admin'>

这里的session不需要手动关闭,只需要手动提交即可, 其他丰富一点的api用法都在 SQLAlcherm里面:

ORM Querying Guide — SQLAlchemy 1.4 Documentation

​​​​​​https://docs.sqlalchemy.org/en/14/orm/session_basics.html#querying-1-x-style

SQLAlcherm中增删改查

SQLAlcherm有两种风格的查询方式,1.4版本都出现的第二种风格

增

import User

u1 = User('1','z','z')
u2 = User('2','z','z')
u3 = User('3','z','z')



# 添加一条记录
session.add([u1 , u2 , u3 ])
session.commit()

# 批量插入
session.add_all([u1 , u2 , u3 ])
session.commit()

删

常规删除:

import User

u1 = User('1','z','z')

session.delete(u1)
session.commit()

根据查询删除:

# 风格一的删除

session.query(User).filter(User.name == "squidward").
    delete(synchronize_session="fetch")

session.commit()

# 风格二的删除
from sqlalchemy import delete

stmt = delete(User).where(User.name == "squidward").execution_options(synchronize_session="fetch")

session.execute(stmt)
session.commit()

改

# 风格一的删除

session.query(User).filter(User.name == "squidward").
    update(synchronize_session="fetch")

session.commit()


# 风格二的删除
from sqlalchemy import update

stmt = update(User).where(User.name == "squidward").execution_options(synchronize_session="fetch")

session.execute(stmt)
session.commit()

查

# 样式一

# query from a class
results = session.query(User).filter_by(name='ed').all()

# query with multiple classes, returns tuples
results = session.query(User, Address).join('addresses').filter_by(name='ed').all()

# query using orm-columns, also returns tuples
results = session.query(User.name, User.fullname).all()





# 样式二
from sqlalchemy import select

# query from a class
statement = select(User).filter_by(name="ed")

# list of first element of each row (i.e. User objects)
result = session.execute(statement).all()

# query with multiple classes
statement = select(User, Address).join('addresses').filter_by(name='ed')

# list of tuples
result = session.execute(statement).all()

# query with ORM columns
statement = select(User.name, User.fullname)

# list of tuples
result = session.execute(statement).all()

返回的数据要么是模型类要么就是元祖

4.执行原生sql

db.session.execute("select * FROM  \"scheam\".\"table_name\" WHERE id= :id",
                       {'id': 1}).all()

返回的是一个元祖列表

其他使用

pg数据库指定 schema 和 table_name

from py_app import db
from datetime import datetime

class Users(db.Model):
    # __tablename__ = 'users'
    __table_args__ = {
        'schema': 'myschema'
    }
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(255))  # 用户名
    password = db.Column(db.String(255))  # 密码明文
    create_time = db.Column(db.DateTime, default=datetime.now())
    create_by = db.Column(db.String(255))
    update_time = db.Column(db.DateTime, onupdate=datetime.now())
    update_by = db.Column(db.String(255))

关于字段默认值设置

from datetime import datetime   

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

这里设置的时间默认值和时间更新默认值是用的 python的datetime  , 换言之这个时间是这个模型类的实体对象生成时或者代码执行时的时间, 不等同于在数据库里设置默认值。

这篇文章有怎么设置数据库默认值: 

https://stackoverflow.com/questions/13370317/sqlalchemy-default-datetime