ORM in flask

1. response 1. What is a response is the content that the server sends to the client. The response corresponds to a request and can be a common string, template or redirect return 'normal string' return render_template('xxx.html ') 2. Response object: encapsulate the response content into an object to complete more response behaviors (for example: add cookies ,..) in flask, use make_response () to construct a response object. 2. File Upload. 1. Note that if a file is uploaded in the problem form, the following two requirements must be followed: 1. The submission method must be the value of the enctype attribute of the post 2 form must be multipart/form-Data 2. The server side 1 passes the request. files get File Upload F = request. files ['file Box Name value'] 2 through F. save saves the file to the specified directory F. filename: get the file name f. save ('/static') # file path 3 model-Models 1 What is model, it is the framework of an attribute 2 Model in the class table created based on the structure of the table in the database. It corresponds to an ORM 1 table in the programming language) each table in the ing database to the programming class corresponds to a class in the programming language in the orm: allow Automatic Generation of a data table a class allows automatic generation of a Class A ing of the Data Type of Data Table 2 maps the fields and Data Types in the database table to the attributes of the class in the programming language in the orm: allow automatic ing of fields and data types in a table to a programming language or automatically ing attributes and data types in a class to a table. to the relationship between tables and classes in the relational database in the programming language. One-to-one foreign key, the primary key, which uniquely limits a data record in Table A and only one data record in table B. A piece of data is associated with two to multiple foreign keys. A primary key course can be associated with any number of data entries in table A taught by multiple instructors, one piece of data in Table B can only be associated with one piece of data in Table A. More than three pairs of data can be associated with one piece of data in Table A through the third joined table. data entries are associated, one piece of data in Table B can also be associated with any multiple pieces of data in table A. 3. Orm advantage 1 encapsulates all operations in the database, this greatly improves development efficiency. 2. You can omit the huge data access layer, perform crud operations on data without using SQL encoding. 3. Configure the ORM framework 1 Database and framework in Python and flask, the ORM framework used is-sqlalchemy. To use sqlalchemy in flask, install pip3 install sqlalchemy pip3 install flask-sqlalchemy 2 to create the database flask Fr. Om flask import flask from flask_sqlalchemy import sqlalchemy APP = flask (_ name _) app. config ['sqlalchemy _ database_uri '] = 'mysql: // username: password @ localhost: 3306/database name' DB = sqlalchemy (APP) app. config ['sqlalchemy _ database_uri '] = 'mysql + pymysql: // root: [email protected]: 3306/flask? Charset = utf8' app. config ['sqlalchemy _ commit_on_tearlow'] = true # specify to automatically submit the model after the operation is completed: 1 Define the model: the table in the database is embodied in the programming language, essentially, it is a python class (model class or entity class) Class modelmane (dB. models) _ tablename __= 'tablename' column_name = dB. column (dB. type, options) 1 modelmane: defines the name of the model class, Set 2 tablename according to the table name: ing to the name of the table in the database 3 column_name: attribute name, ing to the data table is the column name 4 type: data Type mapped to columns 5 Options: column option dB. type: Python type description integer int common integer, 32-bit samllin Teger int small integer, usually 16-bit biginteger Int or long unlimited precision Integer float floating point number numeric decimal. decimal fixed point string STR Variable Length string text STR Variable Length string, optimized Unicode Variable Length Unicode string unicodetext Unicode optimized Variable Length Unicode string 2 Date datetime. date time datetime. time datetime. datetime date and time options column option Name Description primary_key if set to true indicates that this column is the primary key unique if set to true indicates that this column value has a unique index if set to true indicates that this column will create an index if mullable is set to true, this column can be empty. defaul T indicates that the default value of this column has an error option [No modul name mysql_db] solution 1: Import pymysql. install_as_mysqldb () solution 2: 'mysql + pymysql: // root: [email protected]: 3306/flask? Charset = utf8' # Add ['+ pymysql'] 1 database operation after mysql-insert 1 create object 2 insert dB. session. add (object) dB. session. commit () 2 database operations -- Query 1 is based on DB. session to query 1 dB. session. query () This function returns a query object, type: basequery. This object contains all the data in the table corresponding to the object class. This function can receive one or more parameters, parameters indicate who the object to be queried is. 2. query the execution function. Objective: to obtain the final result syntax based on query (): DB. session. query (), query execution function () function description all () returns the first result of all queries in query () in the form of a list, if no result is returned, none first_or_404 () is returned. The first result in the query result. If there is no result, the system terminates and returns the 404 3 query filter function. The filter function filters some rows based on the query syntax: DB. session. query (). filter Function (). query execution function () filter function description filter () filters by specified conditions (multiple tables, single tables, positioning) filter_by () uses limit () When filtering by Equivalent Conditions () get order_by () by the number of restricted rows sort by specified conditions group_by () Grouping filter according to specified conditions function explanation 1 filter () Note: conditions must be composed of model classes, attribute Composition 1 query the information dB of the user older than 30. session. query (user ). filter (user. age> 30) 2. query the information dB of the user whose age is greater than 30 and whose ID is greater than 1. session. query (user ). filter (user. age> 30, user. id> 1) 3 Query Users whose age is greater than 30 or whose ID is greater than 1. Note: The query or operation requires the help of or _ dB. session. query (users ). filter (or _ (users. age> 30, users. id> 2) 4. to query information whose ID is equal to 2, you must use = 5 to query the users whose email contains 'W'. Note the following when you perform fuzzy search: the like () DB provided by attributes in the object class must be used for fuzzy query. session. query (users ). filter (users. email. like ('% W') 6. to query the value of the tip ID in [32, 33], you must use the In _ ([]) DB provided by the attribute in the object class. session. query (users ). filter (users. age. in _ ([32, 33]) 2. Aggregate Function dB. session. query (func. aggregate Function ('column name '). label ('Alias ') aggregate function: sum () Count () max () min () AVG () 3 filter_by () queries the information dB of users whose id = 5. session. query (users ). filter_by (ID = 5 ). all () 4 limit () dB. session. query (users ). limit (2) dB. session. query (users ). limit (2 ). offset (1) 5 order_by () # Sort the DB in descending order according to the value of the ID column. session. query (users ). order_by ('Id desc') # sort by age column values in descending order, and sort dB by ID in ascending order. session. query (users ). order_by ('Id DESC, Id ASC '). all () 2 query models based on the models class. query. query filter functions (condition parameters) and query execution functions () 3. Delete 1. query the object database to be deleted. session. query (models ). filter_by (XXX ). first () 2. Delete the database according to the provided deletion method. session. delete (u) Note: The actual deletion is not completed through the delete operation, but through the modified 4 modify 1 query to query the information to be modified 2 Change the object. Property = value 3 save dB. session. add (Object object) 2 link ing 1 one-to-many Syntax: DB 1 adds a column to the "multiple" object and references it from the primary key column of the '1' table, foreign key column name = dB. column () foreign key column name = dB. column (dB. integer, DB. foreignkey ('Primary key table. column ') 2. Add Association attributes and reverse references to the object of '1'. Et: Association attributes: In the course object, one attribute can be used to obtain all corresponding teacher association attributes. It is necessary to load the reverse reference in the course object class: In the student object, A corresponding course reverse reference link property can be obtained through an attribute. The link property and reverse reference relationship should be added to the entity class of Teacher: property name = dB. relationship ('multi-Table object classname', link option) the option name of The Link option indicates the reverse reference attribute name added by backref to another model of The Link (the attribute name of the object reference to "1" is to be added to the object of "multiple) lazy specifies how to load the current related record... select: loading record immediate upon first access: loading relevant data immediately after the source object is loaded subquery: the effect is the same as above. Loading record using subquery method (this subquery works better when there is a large amount of data) noload: Never load records dynamic: by default, records are not loaded, but uselist queries for Loading records are provided. If it is set to flase, the list is not used to indicate associated data, the scalar secondary is used to specify the name of the joined table in the Multi-to-Multi-link ing.

From flask import flaskfrom flask_sqlalchemy import sqlalchemyapp = flask (_ name _) dB = sqlalchemy (APP) app. config ['sqlalchemy _ database_uri '] = 'mysql + pymysql: // root: [email protected]: 3306/flask' app. config ['sqlalchemy _ commit_on_tearlow'] = trueclass course (dB. model): _ tablename __= 'Course' id = dB. column (dB. integer, primary_key = true) cname = dB. column (dB. string (30), nullable = true) # Add Association attributes and reverse references # Association attributes In the course object, which attribute can be used to obtain all corresponding teacher # reverse reference relationships: In the teacher object, which attribute can be used to find the corresponding course teachers = dB. relationship ('teacher', backref = 'Course', lazy = 'dynamic ') def _ init _ (self, name): Self. cname = Name def _ repr _ (Self): Return '<coure: % R>' % self. cnameclass teacher (dB. model): _ tablename __= 'teacher' id = dB. column (dB. integer, primary_key = true) tname = dB. column (dB. string (30), nullable = true) Tage = dB. column (dB. int Eger) course_id = dB. column (dB. integer, DB. foreignkey ('course. id ') def _ repr _ (Self): Return' <teacher % R> '% self. namedb. create_all () @ app. route ('/') def hello_world (): Return 'Hello world! '@ App. route ('/01-addcourse') def add_course (): course1 = course ('python basics ') course2 = course ('python advanced') course3 = course ('data basics ') DB. session. add (course1) dB. session. add (course2) dB. session. add (course3) return '1' @ app. route ('/02-register') def register_teacher (): Teacher = teacher () teacher. tname = 'Mr Lu 'teacher. tage = 28 course = course. query. filter_by (ID = 3 ). first () # teacher. course = course # assign a value to teacher by associating the course object. course_id = 1 dB. session. add (teacher) return '1' @ app. route ('/03-query-teacher') def query_teacher (): # search for all instructors through course # Find course objects whose course_id is 1 # course = course. query. filter_by (ID = 1 ). first () # print (course. cname) # for tea in course. teachers. all (): # print (tea. tname) # Use teacher to find the corresponding course teacher = teacher. query. filter_by (ID = 1 ). first () print ('instructor name', teacher. tname) course = teacher. course print (course. cname) passif _ name _ = '_ main _': app. run (DEBUG = true)

View code -- reverse ing and query

 

2-to-one
1. What is one-to-one
One record in Table A can only be associated with one record in table B.
One record in Table B can only be associated with one record in table.
2 In sqlalchemy
1. Add any class
Foreign key column name = dB. Column (db. integer, DB. foreignkey ('Primary key table. Primary Key column '))
2. Add it to another class.
Link property and reverse reference link Property
Property = dB. relationship ('associated object class', backref = 'reverse reference property name', uselist = false)
One row in the database: entity integrity: the entity in the table cannot be completely repeated-primary key

 

ORM in flask