Implementation of a one-to-many relationship in flask SQLAlchemy

SQLAlchemy is an excellent ORM framework in Python that defines the correspondence of various database tables in the SQLAlchemy.

One-to-many is a more common relationship. Using flask SQLAlchemy to achieve a one-to-many relationship is as follows:

1. Building a Database model

Three tables were created in this experiment: User, phone, atttr. User and phone, phone and atttr are a one-to-many relationship. The implementation is as follows:

#!/usr/bin/env python#-*-coding:utf-8-*- fromFlaskImportFlask fromFlask.ext.sqlalchemyImportSqlalchemyapp= Flask (__name__)#Configuration Databaseapp.config['Sqlalchemy_database_uri'] =     "mysql://root: [Email Protected]/test?charset=utf8"DB=SQLAlchemy (APP)#Create user TableclassUSR (db. Model):__tablename__='usr'ID= db. Column (db. Integer, primary_key=True) Usrname= db. Column (db. String (unique=),True) Email= db. Column (db. String (+), unique=True) Phone= Db.relationship ('Phone', backref='User', lazy='Dynamic')    def __init__(self, username, email): Self.usrname=username Self.email=EmailclassPhone (db. Model): ID= db. Column (db. Integer, primary_key=True) name= db. Column (db. String (20)) Factory= db. Column (db. String (20) UserId= db. Column (db. Integer, Db. ForeignKey ('usr.id')) attr=Db.relationship ('atttr', backref='Phone', lazy='Dynamic')    def __init__(self, name, Factory, userId): Self.name=name Self.factory=Factory Self.userid=userIdclassatttr (db. Model): ID= db. Column (db. Integer, primary_key=True) Color= db. Column (db. String (20)) Price= db. Column (db. String (20)) MacId= db. Column (db. Integer, Db. ForeignKey ('phone.id'))    def __init__(self, color, price, macId): Self.color=Color Self.price=Price Self.macid=Maciddb.create_all ()

A one-to-many relationship between the user table and the phone table is determined by the following statement:

Phone = db.relationship ('phone', backref='user', lazy='  dynamic')

Where Rrealtionship describes the relationship between user and phone. In this article, the first parameter is the corresponding reference of the class "Phone";

The second parameter backref the method of declaring the new property for the class phone; the third parameter, lazy, determines when sqlalchemy from

The data is loaded in the database.

#!/usr/bin/env python#-*-coding:utf-8-*- fromFlaskImportFlask fromFlask.ext.sqlalchemyImportSqlalchemyapp= Flask (__name__)#Configuration Databaseapp.config['Sqlalchemy_database_uri'] =     "mysql://root: [Email Protected]/test?charset=utf8"DB=SQLAlchemy (APP)#Create user TableclassUSR (db. Model):__tablename__='usr'ID= db. Column (db. Integer, primary_key=True) Usrname= db. Column (db. String (unique=),True) Email= db. Column (db. String (+), unique=True) Phone= Db.relationship ('Phone', backref='User', lazy='Dynamic')    def __init__(self, username, email): Self.usrname=username Self.email=EmailclassPhone (db. Model): ID= db. Column (db. Integer, primary_key=True) name= db. Column (db. String (20)) Factory= db. Column (db. String (20) UserId= db. Column (db. Integer, Db. ForeignKey ('usr.id')) attr=Db.relationship ('atttr', backref='Phone', lazy='Dynamic')    def __init__(self, name, Factory, userId): Self.name=name Self.factory=Factory Self.userid=userIdclassatttr (db. Model): ID= db. Column (db. Integer, primary_key=True) Color= db. Column (db. String (20)) Price= db. Column (db. String (20)) MacId= db. Column (db. Integer, Db. ForeignKey ('phone.id'))    def __init__(self, color, price, macId): Self.color=Color Self.price=Price Self.macid=Maciddb.create_all ()

2. Construct API to get data resources

Note: Write test data to the database before you get the resource

#!usr/bin/env Pyhton#-*-coding:utf-8-*- fromFlaskImportFlask fromFlask.ext.restfulImportReqparse, Api, Resource fromModelImport*app= Flask (__name__) API=Api (APP)defTo_json (model):"""Returns A JSON representation of an sqlalchemy-backed object."""JSON= {}    #json[' fields '] = {}    #json[' pk '] = getattr (model, ' ID ')     forAo.inchModel._sa_class_manager.mapper.mapped_table.columns:#json[' Fields '][col.name] = getattr (model, col.name)Json[col.name] =getattr (model, col.name)#return dumps ([JSON])    returnJSONdefto_json_list (model_list): Json_list= []     forModelinchModel_list:json_list.append (To_json (model))returnjson_listdefmessage (record):ifRecord:returnTo_json (record), 200return{"message":"Not exit"}, 400classUserresource (Resource):defget (self, id): Record= Usr.query.filter_by (id=ID). First ()returnto_json_list (Record.phone.first (). Phone)defput (self, id): Parser=Reqparse. Requestparser () parser.add_argument ('username', type=str) args= Parser.parse_args (strict=True) Record= Usr.query.filter_by (id=ID). First ()ifRecord:record.username= args['username'] Db.session.commit ()return{"Status":"Updated"}, 201return{"message":"Not exit"}, 400defDelete (self, id): Record= Usr.query.filter_by (id=ID). First ()ifRecord:db.session.delete (record) Db.session.commit ()return{"Status":"deleted"}, 204return{"message":"Not exit"}, 400

Class Atrrresource (Resource):

def get (self, id):
Record = Atttr.query.filter_by (Id=id). First ()
Return Message (Record.phone.user)

Api.add_resource (Userresource, '/v1.0/user/<id> ')
Api.add_resource (usrlist, '/v1.0/user ', '/v1.0/user/')

Api.add_resource (Atrrresource, '/v1.0/atrr/<id> ')

If ' __name__ ' = = ' __main__ ':

App.run (Debug=true)

By entering URL:127.0.0.1:5000/V1.0/USER/1 in a browser, you can get all the properties of a certain type of phone for a user.

It is primarily a get function, which is accessed through three layers to obtain relevant information. Of course, you can also get the color of a phone by querying its

The user's information, as shown in Atttr get.

Resources:

[1] Http://docs.jinkan.org/docs/flask-sqlalchemy/models.html#id3

Implementation of a one-to-many relationship in flask SQLAlchemy