Initialize and configure
ORM
(Object relational Mapper) objects relational mappings. Refers to a relationship object that maps the method that faces the object to a database.
Flask-sqlalchemy is a Flask extension, able to support a variety of database background, we can not need to care about SQL processing details, operations database, a basic relationship corresponding to a class, and an entity corresponding to the class instance object, by invoking the method to manipulate the database. Flask-SQLAlchemy
There are very well documented.
Flask-SQLAlchemy
Is the connection information for the database specified by the URL.
The two methods of initialization are as follows (for example, to connect a MySQL database):
from flask_sqlalchemy import SQLAlchemyfrom flask import FLaskapp = Flask(__name__)app.config[‘SQLALCHEMY_DATABASE_URI‘] = "mysql://root:[email protected]/test"db = SQLAlchemy(app)
Or
from flask_sqlalchemy import SQLAlchemyfrom flask import FLaskdb = SQLAlchemy()def create_app(): app = Flask(__name__) db.init_app(app) return app
The difference between the two is that the first one does not need to start flask, app_context
but the second method is needed because it is possible to create multiple flask applications, but my understanding is that when developing in general, flask instances are deferred because it is difficult to modify configuration information at run time, which is consistent with this approach.
Flask-SQLAlchemy
is required to be Flask.config
declared in. More details need to be checked for configuration. For example, the configuration information indicates that SQLAlchemy
multiple database engines can be bound. Another example: when developing a personal blog on the Sina SAE cloud platform, you will gone away
need to add information to this issue SQLALCHEMY_POOL_RECYCLE
, as described in the Sina developer documentation.
SQLAlchemy processing objects--relationships
How does SQLAlchemy handle object-to-relationship? Examples come from the introduction of database systems.
Simple example
Create a Student students table
class Student(db.Model): __tablename__ = ‘students‘ #指定表名 sno = db.Column(db.String(10), primary_key=True) sname = db.Column(db.String(10)) sage = db.Column(db.Integer)
The API documentation explains that creating an object requires inheriting the db.Model类
associated data table entry, db.Model类
inheriting the Query类
data query method, __tablename__
and specifying the database's table name, which Flask-SQLAlchemy
can be saved in. Column
Specifies the table field.
SQLAlchemy supported field types are:
type name |
python type |
Description |
Integer |
Int |
Normal integer, 32-bit |
Float |
Float |
Floating point number |
String |
Str |
Variable length string |
Text |
Str |
Variable length string, optimized for longer strings |
Boolean |
bool |
Boolean value |
Pickletype |
Any Python object |
Automatic use of pickle serialization |
From simple example,flask web development has more detailed content.
The remaining parameters specify the configuration options for the properties, and the common configuration options are as follows:
Option Name |
Description |
PrimaryKey |
If set to True, indicates the primary key |
Unique |
If set to TRUE, this column does not repeat |
Index |
If set to True, create indexes to improve query efficiency |
Nullable |
If set to True, null values are allowed |
Default |
Define default values for this column |
Use the default
default Time property as follows:
time = db.Column(db.Date, default=datetime.utcnow)
Indicates that default
an expression can be accepted lambda
.
One-to-many
Create a college deptment table by creating a single-sheet method
class Deptment(db.Model): __tablename__ = ‘deptments‘ dno = db.Column(db.Integer, primary_key=True) dname = Sname = db.Column(db.String(10),index=True)
Colleges and students are a one-to-many relationship. Flask-SQLAlchemy
is by db.relationship()
solving a one-to-many relationship. To add a property to dept, the code is as follows:
class Deptment(db.Model): ... students = db.relationship(‘Student‘, backref=‘dept‘) class Student(db.Model): ... dept_no = db.Column(db.Integer, db.ForeignKey(‘deptments.dno‘))
The foreign key of the table db.ForeignKey
is specified, and the passed in parameter is the field of the table. db.relations
the attribute it declares is not a table field, the first parameter is the name of the associated class, and backref
is a proxy for the reverse identity, which is equivalent to adding Dept properties to the student class. For example, there are deptment instance dept and student Instance Stu. dept.students.count()
the number of students will be returned to the college; stu.dept.first()
An instance of the Deptment class that will return the student's college information. Generally db.relationship()
it will be on 一
this side.
Many-to-many
Many-to-many relationships can be decomposed into a one-to-many relationship, such as: Student selection, student-to-curriculum relationships:
SC = db. Table ( ' SC ', db. Column ( ' Sno ', db. String (10), DB. ForeignKey ( ' Students.sno ')) db. Column ( ' CNO ', db. String (10), DB. ForeignKey ( ' courses.cno '))) class Course (db. Model): __tablename__ = ' courses ' CNO = db. Column (db. String (10), Primary_key=true) CNAME = db. Column (db. String (10), Index=true) students = db.relationship ( ' Student ', SECONDARY=SC, Backref=db.backref ( ' course ', Lazy=
sc
Table by the db.Table
declaration, we do not need to care about this table, because this table will be SQLAlchemy
taken over, its only function is as students table and Courses Table Association table, so the db.relationship()
sencondary
correlation table parameter must be indicated in. lazy
refers to the lazy evaluation method at the time of query, here is a detailed parameter description, but is the declaration of the db.backref
reverse identity proxy, where the lazy
parameters are to indicate the inverse query lazy evaluation method, SQLAlchemy
encourage this way to declare a many-to-many relationship.
However, if you have custom fields in the associated table, such as adding a score field to the SC table, you need to change the way the table is declared, sc
change to the inherited object, and db.Model
set sc:courses = 1:n
sc:student = 1:n
the relationship.
SQLAlchemy Handling Relationships-objects
Detailed instructions are available in the Flask-sqlalchemy query. How do I query to an object after I create the relationship?
SQLAlchemy has a query filter as follows:
Filter | Filters
Description |
Filter () |
Add filter to original query, return new query |
Filter_by () |
Add the equivalent filter to the original query and return to the new query |
Limit () |
Use the specified value to limit the number of results returned by the original query, and return the new query |
Offset () |
Offsets the result returned by the original query and returns the new query |
Order_by () |
Sort returns results, returns new query |
GroupBy () |
Original Query group, return new query |
These filters return the result is a new query, I understand that these queries are actually generated SQL statements, lazy
the lazy evaluation method is also reflected in the query, and these statements can not generate the object needs to query, you need to call other methods to generate the object.
SQL query execution function:
Method |
Description |
All () |
Return results as a list |
First () |
Returns the first result if none is returned |
first_or_404 () |
Returns the first result if no 404 exception is thrown |
Get () |
Returns the primary key corresponding to the record, none returns none |
get_or_404 () |
Returns the primary key corresponding to the record, if no 404 exception is thrown |
Count () |
Returns the number of query results |
Paginate () |
Returns a Paginate object that is used for paging |
Flask-sqlalchemy Study Summary