4.Flask-SQLAlchemy

Flask-SQLAlchemy

本章完成以下内容：

1. 最小的应用
2. 配置项
3. Models

因为 SQLAlchemy 是一个常用的数据库抽象层和数据库关系映射包(ORM)，并且需要一点点设置才可以使用，因此存在一个 Flask 扩展帮助您操作它。如果您想要快速开始使用，那么我们建议您使用这种方法。

一、最小的应用

from flask import Flaskfrom flask\_sqlalchemy import SQLAlchemyapp = Flask(\_\_name\_\_)app.config['SQLALCHEMY\_DATABASE\_URI'] = 'sqlite:////tmp/test.db'db = SQLAlchemy(app)class User(db.Model):    id = db.Column(db.Integer, primary\_key=True)    username = db.Column(db.String(80), unique=True)    email = db.Column(db.String(120), unique=True)    def \_\_init\_\_(self, username, email):        self.username = username        self.email = email    def \_\_repr\_\_(self):        return '<User %r>' % self.username

初始化数据库： db.create\_all()
增加一些数据：

admin = User('admin', '[e-mail]')guest = User('guest', '[e-mail]')db.session.add(admin)db.session.add(guest)db.session.commit()

访问数据：

users = User.query.all() # 访问所有的数据  admin = User.query.filter\_by(username='admin').first() # 访问特定的数据

外键关系：

from datetime import datetimeclass Post(db.Model):    id = db.Column(db.Integer, primary\_key=True)    title = db.Column(db.String(80))    body = db.Column(db.Text)    pub\_date = db.Column(db.DateTime)    category\_id = db.Column(db.Integer, db.ForeignKey('category.id'))    category = db.relationship('Category',        backref=db.backref('posts', lazy='dynamic'))    def \_\_init\_\_(self, title, body, category, pub\_date=None):        self.title = title        self.body = body        if pub\_date is None:            pub\_date = datetime.utcnow()        self.pub\_date = pub\_date        self.category = category    def \_\_repr\_\_(self):        return '<Post %r>' % self.titleclass Category(db.Model):    id = db.Column(db.Integer, primary\_key=True)    name = db.Column(db.String(50))    def \_\_init\_\_(self, name):        self.name = name    def \_\_repr\_\_(self):        return '<Category %r>' % self.name# 使用的时候这样py = Category('Python')p = Post('Hello Python!', 'Python is pretty cool', py)db.session.add(py)db.session.add(p)

二、配置项

• SQLALCHEMY_DATABASE_URI
  The database URI that should be used for the connection. Examples:
  sqlite:////tmp/test.db or mysql://username:password@server/db

• SQLALCHEMY_BINDS
  A dictionary that maps bind keys to SQLAlchemy connection URIs. For more information about binds see Multiple Databases with Binds.

• SQLALCHEMY_ECHO
  If set to True SQLAlchemy will log all the statements issued to stderr which can be useful for debugging.

• SQLALCHEMY_RECORD_QUERIES
  Can be used to explicitly disable or enable query recording. Query recording automatically happens in debug or testing mode. See get_debug_queries() for more information.

• SQLALCHEMY_NATIVE_UNICODE
  Can be used to explicitly disable native unicode support. This is required for some database adapters (like PostgreSQL on some Ubuntu versions) when used with improper database defaults that specify encoding-less databases.

• SQLALCHEMY_POOL_SIZE
  The size of the database pool. Defaults to the engine’s default (usually 5)

• SQLALCHEMY_POOL_TIMEOUT
  Specifies the connection timeout for the pool. Defaults to 10.

• SQLALCHEMY_POOL_RECYCLE
  Number of seconds after which a connection is automatically recycled. This is required for MySQL, which removes connections after 8 hours idle by default. Note that Flask-SQLAlchemy automatically sets this to 2 hours if MySQL is used.

• SQLALCHEMY_MAX_OVERFLOW
  Controls the number of connections that can be created after the pool reached its maximum size. When those additional connections are returned to the pool, they are disconnected and discarded.

• SQLALCHEMY_TRACK_MODIFICATIONS
  If set to True, Flask-SQLAlchemy will track modifications of objects and emit signals. The default is None, which enables tracking but issues a warning that it will be disabled by default in the future. This requires extra memory and should be disabled if not needed.

URL的格式
dialect+driver://username:password@host:port/database

Ex:

• Postgres: postgresql://scott:tiger@localhost/mydatabase
• MySQL:mysql://scott:tiger@localhost/mydatabase
• Oracle:oracle://scott:tiger@127.0.0.1:1521/sidname
• SQLite (note the four leading slashes): sqlite:////absolute/path/to/foo.db

三、Models

常见API查询

类型 解释 Integer an integer String(size) a string with a maximum length 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