SQLAlchemy Is a relational database framework , It provides high-level ORM And the operation of the underlying native database .
flask-sqlalchemy It's a simplification SQLAlchemy Operation of the flask Expand .
ORM（Object-Relation Mapping Called objects - Relation mapping ）：
The main Realize the mapping of model objects to relational database data , such as : Map each record in the database table to a module Type object
ORM Provides a persistence mode , It can efficiently access the database . It can put the bottom RDBMS Encapsulated as business entity objects , Provided to the business logic layer . Programmers tend to focus on business logic , Rather than how the underlying database should be accessed , And how to write SQL Statement to obtain data, etc . use ORM, It can be transformed from the design level of database into object-oriented thinking .
Environmental Science ：window11+pycharm2020.1+Anaconda4.11.0 +python3.7
Flask-sqlalchemy2.5.1
Flask-Migrate：3.1.0
Source code ：https://github.com/sueleeyu/flask_api_ar
1. install pymysql, Open console , Input ：
pip install pymysql
2. install flask-sqlalchemy, Open console , Input ：
pip install flask-sqlalchemy
3. install Flask-Migrate, Open console , Input ：
pip install flask-migrate
solve Flask-migrate Installation and problems encountered ：Python introduction -Flask-migrate Installation and use ：
Python introduction -Flask-migrate Installation and use _suelee_hm The blog of -CSDN Blog
flask-sqlalchemy Operating the database , It's easy to use , Easy to operate . Common configurations are as follows ：
frequently-used SQLAlchemy Field type ：
Type name
python Type in the
explain
Integer
int
Normal integer , It's usually 32 position
SmallInteger
int
Integer with small value range , It's usually 16 position
BigInteger
int or long
Integer with unlimited precision
Float
float
Floating point numbers
Numeric
decimal.Decimal
Normal integer , It's usually 32 position
String
str
Variable length string
Text
str
Variable length string , Optimized for long or unlimited length strings
Unicode
unicode
Lengthening Unicode character string
UnicodeText
unicode
Lengthening Unicode character string , Optimized for long or unlimited length strings
Boolean
bool
Boolean value
Date
datetime.date
Time
Time
datetime.datetime
Date and time
LargeBinary
str
Binary
frequently-used SQLAlchemy Column options ：
Option name
explain
primary_key
If True, Represents the primary key of the table
unique
If True, Represents that duplicate values are not allowed in this column
index
If True, Index this column , Improve query efficiency
nullable
If True, Null value allowed , If False, No null values are allowed
default
Define default values for this column
frequently-used SQLAlchemy Relational options ：
Option name
explain
backref
Add reverse references to the first mock exam in another model of the relationship.
primary join
Explicitly specify the join conditions used between the two models
uselist
If False, Do not use list , Use scalar values instead
order_by
Specify how records in the relationship are sorted
secondary
Specifies the name of the relational table in a many to many relationship
secondary join
stay SQLAlchemy When China cannot make its own decision , Specifies the secondary join condition in a many to many relationship
1. establish db object . newly build exts.py file ：
# Deposit db Variable from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy()
2. create profile . newly build configs.py file ：
# -*- coding: UTF-8 -*- # The configuration file # database information HOST = 'xxx.cn' PORT = '3306' DATABASE = 'db' USERNAME = 'root' PASSWORD = 'root' DB_URI = 'mysql+mysqldb://{}:{}@{}:{}/{}'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE) # Database connection code DB_CHARSET = "utf8" SQLALCHEMY_DATABASE_URI = DB_URI SQLALCHEMY_TRACK_MODIFICATIONS = False SQLALCHEMY_ECHO = True3. binding app. newly build app.py, Write code to create app, Registered blueprint ：
# app.py # Lord app file , Run the file from flask import Flask from flask_migrate import Migrate from controller import ar import configs from exts import db def create_app(): app1 = Flask(__name__) # Registered blueprint app1.register_blueprint(ar, url_prefix='/ar') # Load profile app1.config.from_object(configs) # db binding app db.init_app(app1) # Must let Flask-Migrate Be able to manage app Database in , Need to use Migrate(app,db) To bind app And the database . If there are the following app file # binding app And the database migrate = Migrate(app=app1, db=db) return app1 app = create_app()
if __name__ == '__main__': #app = create_app() app.run()
4. Create table . newly build models.py, Write each database table class :
# models.py # Model file , Used to store all the models from exts import db """ The following table shows the relationship ： One user corresponds to multiple （ One to many ）
""" """ In a one-on-one relationship , Need to set up relationship Medium uselist=Flase, Other database operations are the same . In a one to many relationship , The foreign key is set on the side of more than one , Relationship （relationship） Can be set on either side . In many to many relationships , Need to establish a relationship table , Set up secondary= A relational table
""" if __name__ == '__main__': import app app1 = app.create_app() app1.run() # User table class ARUser(db.Model): # User The model name __tablename__ = 'ar_user' # Table name id = db.Column(db.BigInteger, primary_key=True, autoincrement=True) # id, The primary key increases automatically user_name = db.Column(db.String(20), index=True) # User name create_time = db.Column(db.TIMESTAMP) update_time = db.Column(db.TIMESTAMP) # relationship # 1. The first parameter is the name of the model , It must be consistent with the name of the model # 2.backref（bsck reference）： Represents a reverse reference , Field name when accessing me on behalf of the other party geospatials = db.relationship('GeoHistory', backref='ARUser', lazy='select') # Add relationships def __init__(self, name): self.user_name = name # Geospatial Anchor table class GeoHistory(db.Model): # GeoHistory The model name __tablename__ = 'ar_geo_history' # Table name id = db.Column(db.BigInteger, primary_key=True, autoincrement=True) # id, The primary key increases automatically bid = db.Column(db.String(20), index=True) # geo id # Foreign keys # 1. The data type of foreign key must depend on the type of field referenced , To be the same # 2. db.Foreignkey(" Table name . Field name ")fl # 3. Foreign keys belong to the database level , It's not recommended to go directly to ORM Use it directly uid = db.Column(db.BigInteger, db.ForeignKey('ar_user.id')) # user id, Set up foreign keys name = db.Column(db.String(20), index=True) # Anchor name latitude = db.Column(db.Float) longitude = db.Column(db.Float) altitude = db.Column(db.Float) heading = db.Column(db.Float) # The mobile camera faces state = db.Column(db.SmallInteger) # Anchor status 0 hide ,1 Show create_time = db.Column(db.TIMESTAMP) update_time = db.Column(db.TIMESTAMP) def __init__(self, bid, name, latitude, longitude, altitude, heading): self.bid = bid self.name = name self.latitude = latitude self.longitude = longitude self.altitude = altitude self.heading = heading db.relationship and backref Usefulness ：
5. The mapping table . After creating the table, you need to map it to the database , I need to use flask-migrate library .
app.py Medium Migrate Binding code :
# db binding app db.init_app(app) # Must let Flask-Migrate Be able to manage app Database in , Need to use Migrate(app,db) To bind app And the database . If there are the following app file # binding app And the database migrate = Migrate(app=app, db=db)
pycharm Access control ：
perform init command , Initialize a migration folder ：
flask db init
perform migrate command , Add the current model to the migration file ：
flask db migrate
perform update command , Operate the corresponding database in the migration file , Really map to the database :
flask db upgrade
completion of enforcement , And... Will be generated in the database models.py The corresponding table in ：
Request Request for GET and POST Two kinds of ,flask How to get parameters ：
route The decorator passes through methods Specify request method , The default is GET：
@app.route('/ar/add_anchor', methods=['POST'])GET： Parameter in url in , Parameters can be obtained in the following two ways ：
POST：
POST The request was sent through Content-Type Mark different data .
Different Content-Type Treatment mode ：
a. application/json：
request ：
receive ：
jdata = request.get_json() # perhaps request.json.get('content')b. application/x-www-form-urlencoded：
request ：
receive ：
jdata = request.values.get("content")c. multipart/form-data
request ：
receive ：
jdata = request.form.get('content')1. Insert ：
geoHistory = GeoHistory("100001", "bp1", 39.4632, 116.3679, 28.3135, 137.1354) db.session.add(geoHistory) db.session.commit()Batch insert ：
geoHistory = GeoHistory("100001", "bp1", 39.4632, 116.3679, 28.3135, 137.1354) geoHistory2 = GeoHistory("100001", "bp2", 39.4632, 116.3679, 28.3135, 137.1354) db.session.add(geoHistory) # Insert a db.session.commit() anchors = [geoHistory, geoHistory2] # Batch db.session.execute(GeoHistory.__table__.insert(), anchors) # SQLAlchemy Core db.session.commit() # Batch or db.session.add_all(anchors) db.session.commit()2. Inquire about
Filter To filter ,filter The internal condition is and.limit Limit data ：
latitude_min, latitude_max, longitude_min, longitude_max = utils.get_area(latitude, longitude, 1000) anchors = GeoHistory.query.filter( GeoHistory.latitude.between(latitude_min, latitude_max), GeoHistory.longitude.between(longitude_min, longitude_max)).limit( 20).all()
1. entrance
@app.route('/') def hello_world(): return 'Hello World!'2.app Internal registration blueprint ：
# Registered blueprint app1.register_blueprint(ar, url_prefix='/ar')
3. newly build controller.py, Create a blueprint , Writing interface :
from flask import Blueprint, request from R import R from service import ARService ar = Blueprint("ar", __name__) @ar.route('/nearby') def nearby(): latitude = float(request.args.get('latitude')) longitude = float(request.values.get('longitude')) result = ARService.query_histories(latitude, longitude) return R.ok(result) @ar.route('/add_anchors', methods=["GET", "POST"]) def add_anchor(): json_data = '' if request.method == "GET": json_data = request.args.get("content") if request.method == "POST": if request.content_type.startswith('application/json'): json_data = request.get_json() # application/json Get the original parameters , It is accepted that type yes 'bytes’ The object of , Such as ：b{'name':'lucy', 'age':22} # data = request.get_data() elif request.content_type.startswith('multipart/form-data'): json_data = request.form.get('content') else: json_data = request.values.get("content") anchors = json_data["collection"] ARService.add_anchor(anchors) return R.ok(data=None)4. newly build service.py, Implement data logic ：
import utils from models import GeoHistory from exts import db from utils import O2d class ARService(object): @staticmethod def query_histories(latitude, longitude): # Query all latitude_min, latitude_max, longitude_min, longitude_max = utils.get_area(latitude, longitude, 1000) anchors = GeoHistory.query.filter( GeoHistory.latitude.between(latitude_min, latitude_max), GeoHistory.longitude.between(longitude_min, longitude_max)).limit( 20).all() return O2d.obj_to_list(anchors) @staticmethod def add_anchor(anchors): db.session.execute(GeoHistory.__table__.insert(), anchors) # SQLAlchemy Core db.session.commit() if __name__ == '__main__': geoHistory = GeoHistory("100001", "bp1", 39.4632, 116.3679, 28.3135, 137.1354) geoHistory2 = GeoHistory("100001", "bp2", 39.4632, 116.3679, 28.3135, 137.1354)test Json data ：
{ "collection":[{ "bid":"100001","name":"bp1","serializedTime":"2022/7/6 16:43:42","latitude":39.90866127654739,"longitude":116.46562960310614,"altitude":29.459830418229104,"heading":137.04320866867753}]}
Content-Type：application/json
utils.py:
class O2d: @staticmethod def obj_to_dic(obj): ''' Will the incoming data Object to dictionary
''' result = {} for temp in obj.__dict__: if temp.startswith('_') or temp == 'metadata': continue result[temp] = getattr(obj, temp) return result @staticmethod def obj_to_list(list_obj): ''' Will the incoming data Conversion of objects List,list The elements in are dictionaries
''' result = [] for obj in list_obj: result.append(O2d.obj_to_dic(obj)) return result[1] flask-migrate file :
Flask-Migrate — Flask-Migrate documentation
[1] Source code :https://github.com/sueleeyu/flask_api_ar
