Flask-sqlalchemy is a Flask extension that adds SQLALchemy support to your app. SQLALchemy is a Python-language SQL Toolkit and Object-relational mapping (ORM) tool that uses the MIT license release to provide compatibility with many databases such as SQLite, MySQL, Postgres, Oracle, ms-sql, SQL Server, and Firebird) Enterprise-class persistence model.
First, load the flask-sqlalchemy extension for your flask app
Code Example:
123456 |
from flask import flask Code class= "python keyword" >from flask.ext.sqlalchemy import sqlalchemy app Code class= "python keyword" >= flask (__name__) app.config[ ' Sqlalchemy_database_uri ' ] = ' sqlite:////tmp/test.db ' db = sqlalchemy (APP) #这个就是你以后操作数据库的对象实例了 |
Sqlalchemy_database_uri Format instance:
Postgresql://scott:[email Protected]/mydatabase
Mysql://scott:[email Protected]/mydatabase
Oracle://scott:[email Protected]:1521/sidname
Sqlite:////absolute/path/to/foo.db #注意: 3 slash + Path
Second, the establishment of database model and initialization database
To create a database model:
Code Example:
12345678910111213141516 |
import hashlib
from app
import db
#在数据库模型文件中导入上面建立的db对象
class User(db.Model):
id = db.Column(db.Integer, primary_key
=
True
)
# id
username
= db.Column(db.String(
80
), unique
=
True
)
email
= db.Column(db.String(
320
), unique
=
True
)
password
= db.Column(db.String(
32
), nullable
=
False
)
def __init__(
self
, username, email, password):
self
.username
= username
self
.email
= email
self
.password
= hashlib.md5(password)
#呵呵,这样在插入数据自动给密码哈希了!
def __repr__(
self
):
return "<User ‘{:s}‘>"
.
format
(
self
.username)
|
Initializing the database is also very simple, just call the Db.create_all () function.
Code Example:
12 |
if __name__ = = ‘__main__‘ : db.create_all() |
Third, insert data
Code Example:
123 |
u = User(username = ‘peter‘ , email = ‘[email protected]‘ , password = ‘123456‘ ) db.session.add(u) #插入数据 db.session.commit() #只有提交事务了,才可以获取(u.id)数据的ID值。 |
Iv. Querying data
Get the data with the primary key:
Code Example:
12 |
User.query.get( 1 ) <User u ‘admin‘ > |
To counter-check with an exact parameter:
Code Example:
12 |
peter = User.query.filter_by(username = ‘peter‘ ).first() #注意:精确查询函数query.filter_by(),是通过传递参数进行查询;其他增强型查询函数是query.filter(),通过传递表达式进行查询。 print (peter. id ) #如果数据不存在则返回None |
Fuzzy query:
Code Example:
12 |
User.query. filter (User.email.endswith( ‘@example.com‘ )). all () [<User u ‘admin‘ >, <User u ‘guest‘ >] |
Logic not 1:
Code Example:
12 |
peter = User.query. filter (User.username ! = ‘peter‘ ).first() print (peter. id ) |
Logic not 2:
Code Example:
123 |
from sqlalchemy import not_ peter = User.query. filter (not_(User.username = = ‘peter‘ )).first() print (peter. id ) |
Logic with:
Code Example:
123 |
from sqlalchemy import and_ peter = User.query. filter (and_(User.username = = ‘peter‘ , User.email.endswith( ‘@example.com‘ ))).first() print (peter. id ) |
Logical OR:
Code Example:
123 |
from sqlalchemy import or_ peter = User.query. filter (or_(User.username ! = ‘peter‘ , User.email.endswith( ‘@example.com‘ ))).first() print (peter. id ) |
Six, query data processing
The sort and limit functions can be followed by query or filter.
Sort:
Code Example:
12 |
User.query.order_by(User.username) #嘿嘿,你用哪个字段作为排序参考呢? [<User u ‘admin‘ >, <User u ‘guest‘ >, <User u ‘peter‘ >] |
Limit the number of returns:
Code Example:
12 |
User.query.limit( 1 ). all () [<User u ‘admin‘ >] |
Vi. Query Data return
Returns the first object queried to:
Code Example:
12 |
r = User.query.first() print (r) |
Returns all queries to the object:
Code Example:
12 |
r = User.query. all () print (r) |
Vii. deletion of data
Code Example:
123 |
u = User.query.first() db.session.delete(u) #删除数据和插入数据一样简单,但必须是通过查询返回的对象。 db.session.commit() |
Viii. Updating data
Code Example:
123 |
u = user.query.first () u.username = ' guest ' #更新数据和变量赋值那么简单, but must be the object returned by the query. db.session.commit () |