Flask-sqlalchemy configuration, handling objects-relationships, one-to-many, many-to-many

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-SQLAlchemyThere are very well documented.

Flask-SQLAlchemyIs 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 Import SQLAlchemy  from Import  = Flask (__name__) app.config['sqlalchemy_database_uri' " Mysql://root:[email protected]/test "  = SQLAlchemy (APP)

Or

 from Import SQLAlchemy  from Import  = SQLAlchemy ()def= Flask (__name__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. is Flask-SQLAlchemy 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__tablename__'students'# Specifies the table name sno = db. Column (db. String (Ten), primary_key== db. Column (db. String (Ten= 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. ColumnSpecifies 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__tablename__'deptments'= db. Column (db. Integer, primary_key== Sname = db. Column (db. String (Ten), index=true)

Colleges and students are a one-to-many relationship. Flask-SQLAlchemyis 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.relationsthe 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 broken down into a one-to-many relationship, such as: Student selection, the relationship between students and the curriculum:

sc = db. Table ('SC', Db. Column ('Sno'Db. String (Ten), DB. ForeignKey ('Students.sno') ) db. Column ('CNO'Db. String (Ten), DB. ForeignKey ('Courses.cno')) Class Course (db). Model):__tablename__='Courses'CNO= db. Column (db. String (Ten), primary_key=True) CNAME= db. Column (db. String (Ten), index=True) Students= Db.relationship ('Student', secondary=Sc,backref=db.backref ('Course', lazy='Dynamic'), Lazy='Dynamic')

scTable 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. lazyrefers 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:

Filters

Filter	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 configuration, handling objects-relationships, one-to-many, many-to-many