Database operations using
SQLAlchemy module installation (need to install mysql-python/python-devel) pip install Flask-sqlalchemy
Directory structure
├──app│├──__init__.py│└──models.py├──config.py
Configuring the Environment
Vim Config.py#coding:utf-8sqlalchemy_database_uri = ' mysql://mysqltest:[email protected]/mysqltest ' SQLALCHEMY_ Track_modifications = True #python版本低报出警告
#初始化vim app/__init__.pyfrom flask import flaskfrom flask_sqlalchemy import sqlalchemyapp = flask (__name__) app.config.from_object (' Config ') db = sqlalchemy (app) From app import models
#定义表模型 (for example) Vim App/models.pyfrom app import Dbclass User (db. Model): id = db. Column (db. Integer,primary_key = True) nickname = db. Column (db. String (UP), Index=true,unique = True) email = db. Column (db. String (+), Index=true,unique = True) def __repr__ (self): return ' <user%r> '% (self.nickname)
Introduction to the use of shell mode
#创建数据模型/delete data Model >>> from app import db>> > from app.models import User #导入User模型 >>> Db.create_all () #创建数据库模型 >>> db.drop_all () >>> db.create_all ()
#添加数据 # How to submit a single data >>> name_admin = user (Nickname= ' Admin ', email= ' [email protected] ') #赋值数据 >>> db.session.add (name_admin ) #添加数据 >>> db.session.commit () #提交数据 # How to submit multiple data > >> name_user1 = user (nickname= ' user1 ', email= ' [email protected] ') >>> Name_user2 = user (nickname= ' user2 ', email= ' [email protected] ') >>> db.session.add_ All ([Name_user1,name_user2]) #提交的是一个列表 >>> db.session.commit ()
Mysql> SELECT * from user;+----+----------+-----------------+| ID | Nickname | Email |+----+----------+-----------------+| 1 | admin | [Email protected] | | 2 | User1 | [Email protected] | | 3 | User2 | [Email protected] |+----+----------+-----------------+
Querying data
#查询所有数据all =user.query.all () #过滤查询 (Filter_by ()), query nickname=user1 rows >>> user1 = User.query.filter_by (id=2). First () >>> user2 = User.query.filter_by (id=3). First () >>> user1<user u ' user1 ' >>>> User2<user u ' user2 ' > #查询该行数据的各个字段, view the value of the nickname field >>> User1.nicknameu ' user1 ' >>> User2.nicknameu ' User2 ' #结果显示限制 (limit), showing two query results >>> result = User.query.limit (2). All () >>> result[ <user u ' admin ';, <user u ' user001 ';]
modifying data
#修改数据, modify the value of the corresponding field and submit it directly # Modify the value of the Use1 nickname field to User001user1.nickname = ' user001 ' Db.session.add (user1) Db.session.commit ()
Delete Data Db.session.delete (user1) Db.session.commit ()
#常见查询过滤器filter () Add the filter to the original query and return a new query filter_by () adds the equivalent filter to the original query, returns a new query limit () limits the number of results returned by the original query using the specified value, and returns a new query offset () Offsets the result returned by the original query, returns a new query order_by () sorts the results of the original query according to the specified criteria, and returns a new query group_by () groups The original query results according to the specified criteria, returning a new query
#常见查询执行函数all () returns all the results of a query as a list first () returns a query with no result, returns NONEFIRST_OR_404 () returns the first result of the query, or, if there is no result, terminates the request, returns a 404 error response get () returns the row for the specified primary key, or, if there is no corresponding row, returns NONEGET_OR_404 () returns the row for the specified primary key, terminates the request if the specified primary key is not found, returns a 404 error Response count () returns the number of query results paginate () returns a A Paginate object that contains the results in the specified range
Flask Learning Database Operation One