Flask Study Notes-database (Python3.6), flaskpython3.6

Flask Study Notes-database (Python3.6), flaskpython3.6
Python database framework

Factors for selecting a database framework:

Project Structure Construction

Application

|____ Application web directory

|____ Manage. py script data

|____ Runserver. py start the server

| ______ Init _. py module Export File

| |____ App. conf configuration file

| |____ Models. py Data Model

| |____ Views. py View

| |____ Static files

| |____ Templates Page Template

| |____ Base.html

| |____ Index.html

| |____ Login.html

 

1. Flask installation and framework (demonstration on the official website)

Flask Official Website: http://flask-sqlalchemy.pocoo.org/

Chinese http://www.pythondoc.com/flask-sqlalchemy/

Flask installation: pip3 install Flask-SQLAlchemy

PyMySQL: pip3 install Flask-PYMy

2. ORM Introduction

 

Object Relational ing (ORM) is a technology designed to solve the mismatch between objects and Relational databases. In essence, it converts data from one form to another;

Objects and relational data are two forms of business entities. business entities are represented as objects in the memory and relational data in the database;

There are associations and inheritance relationships between objects in the memory, and relational data in the database cannot directly express multi-associations and inheritance relationships;

The object-link ing system generally exists in the form of middleware, which mainly maps program objects to relational database data;

Both the business logic layer and the user interface layer are object-oriented. When the object information changes, you need to save the object information in the relational database.

 

3. Database switching/Initialization

3.1 Use a URL to connect to a database

Database Engine	URL
MySQL	Mysql: // username: password @ hostname/database
Postgres	Postgresql: // username: password @ hostname/database
SQLite (Unix)	Sqlite: // absolute/path/to/database
SQLite (Windows)	Sqlite: // c:/absolute/path/to/database

SQLite data library does not need to use server, because hostname, username, and password are not used. The database in the URL is the file name on the hard disk. 3.2 Configuration

1. SQLALCHEMY_DATABASE_URI -- connection data database

For example, SQLALCHEMY_DATABASE_URI = 'mysql: // root: 1234567890 @ localhost: 3306/test ';

2. SQLALCHEMY_ECHO

If it is set to True, SQLAlchemy records all statements sent to the standard output, which is helpful for Skip;

3. SQLALCHEMY_RECORD_QUERIES -- explicitly disable or enable query records

Query records are automatically enabled in debug or test mode;

4. SQLALCHEMY_NATIVE_UNICODE -- explicitly disable native unicode

5. SQLALCHEMY_TRACK_MODIFICATIONS

If set to True, SQLAlchemy modifies and sends signals to the tracing object. The default value is None;

Example: configure the module export module, import the SQLAlchemy extension, create a Flask application, load the configuration, create the SQLAlchemy object, and pass the Flask application as a parameter.

1 #__init__.py2 3 from flask import Flask4 from flask_sqlalchemy import SQLAlchemy 5 6 app = Flask(__name__)7 app.config.from_pyfile('app.conf')8 9 db = SQLAlchemy(app)

1 #app.conf2 3 SQLALCHEMY_DATABASE_URI = 'mysql://root:1234567890@localhost:3306/test'4 SQLALCHEMY_TRACK_MODIFICATIONS = True5 SQLALCHEMY_ECHO = False6 SQLALCHEMY_NATIVE_UNICODE = True7 SQLALCHEMY_RECORD_QUERIES = False

3.3 Data Model

1. There are many classes in the program and many tables in the database. SQLAlchemy combines classes with tables;

2. db. Model specifies the class to be associated with the database. The base class of all models is db. Model, which is stored in the required SQLAlchemy instance;

3. All Model definitions must inherit the db. Model class!

4. Define a Column with Column. The Column name is the name assigned to the variable;

5. The Column type is the first parameter of Column and can be provided directly. The common types are as follows:

Type name	Python type	Description
Integer	Int	Ordinary integer, usually 32-bit
SmallInteger	Int	An integer with a small value range, usually 16 bits.
BigInteger	Int or long	Integer With no Precision Limit
Float	Float	Floating Point Number
Numeric	Decimal. Decimal	Number of points
String	Str	Variable-length string
Text	Str	Variable-length string, optimized for long or unlimited-length strings
Unicode	Unicode	Variable-length Unicode string
UnicodeText	Unicode	Variable-length Unicode string, optimized for long or unlimited-length strings
Boolean	Bool	Boolean Value
Date	Datetime. date	Date
Time	Datetime. time	Time
DateTime	Datetime. datetime	Date and Time
Interval	Datetime. timedelta	Interval
Enum	Str	A group of strings
PickleType	Any Python object	Automatic Pickle serialization
LargeBinary	Str	Binary files

The most commonly used SQLAlchemy column option

Option name	Description
Primary_key	If set to True, this column is the primary key of the table.
Unique	If set to True, duplicate values are not allowed in this column.
Index	If set to True, create an index for this column to improve query efficiency.
Nullable	If set to True, null values are allowed in this column. If set to False, null values are not allowed in this column.
Default	Define default values for this column

Relationship expression

Relational databases use relationships to associate rows in different tables.

1.One-to-multiple relationship

One-party relationship is represented by db. relationship (). The first parameter is a string of multiple-party class names. For other parameters, see:

Option name	Description
Backref	In order to establish a two-way connection, set reverse reference and declare a new attribute on the class where multiple parties are located;
Primaryjoin	Specify the join conditions used between two models. You only need to specify
Lazy	Specify how to load related records. Optional values include select (loaded on demand upon first access), immediate (loaded after the source object is loaded), joined (loaded records, but joined), subquery (loaded immediately, but use subquery), noload (never load) and dynamic (do not load records, but provide query for loading Records)
Uselist	If it is set to Fales, the scalar value is used instead of the list.
Order_by	Sorting method of records in a specified Link
Secondary	Name of a relational table in multiple-to-multiple relationships
Secondaryjoin	When SQLAlchemy cannot decide on its own, it specifies the Level 2 join condition in the many-to-many relationship.

If a User has multiple images, a one-to-multiple relationship is defined;

Because the backref parameter is set, attributes of User table (1) can be obtained for Image tables (multiple tables), as shown in figure

 1 #models.py 2  3 class User(db.Model):   4     id = db.Column(db.Integer, primary_key=True, autoincrement=True)   5     username = db.Column(db.String(80), unique=True)   6     password = db.Column(db.String(32))   7     head_url = db.Column(db.String(256)) 8     images = db.relationship('Image', backref = 'user', lazy = 'dynamic') 9     10 class Image(db.Model):  11     id = db.Column(db.Integer, primary_key = True, autoincrement = True)  12     url = db.Column(db.String(512))  13     user_id = db.Column(db.Integer, db.ForeignKey('user.id'))  14     created_date = db.Column(db.DateTime)  

2. One-to-one relationship
The one-to-one relationship can be expressed using the one-to-multiple relationship described above. However, when calling db. relationship (), set uselist to False and change "multiple" to "one"

3. many-to-many relationships

For example, the relationship between tags and logs in a blog;

Define a secondary table for relational use. We do not recommend that you use a model for a secondary table, but use an actual table;

 1 tags = db.Table('tags', 2     db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')), 3     db.Column('page_id', db.Integer, db.ForeignKey('page.id')) 4 ) 5  6 class Page(db.Model): 7     id = db.Column(db.Integer, primary_key=True) 8     tags = db.relationship('Tag', secondary=tags, backref=db.backref('pages', lazy='dynamic')) 9 10 class Tag(db.Model):11     id = db.Column(db.Integer, primary_key=True)

4. Database Operations

• Create a table

1 python manage.py shell2 >>> from manage.py import db3 >>> db.create_all()

• Delete table

1 db.drop_all()

• Insert row

Insert data into the database in three steps:

1. Create an object

2. Pass it as a parameter and add it to the session

3. Submit the session (if not submitted, the database will not change)

Note: The session here is a SQLAlchemy session;

1 user = User('Alice', '123456', 'http://images.***.com/head/1223/m.png') 
2 db.session.add(user) 
3 db.session.commit()

• Delete row

You can delete data from a database in three steps:

1. query data

2. delete a session

3. Submit a session

1 user = User. query. get (1) 2 user. delete () # delete method 1 3 db. session. delete (user) # delete method 2 4 db. session. commit ()

• Update

Update the data in the database in three steps:

1. query data

2. Update Data

3. Submit a session

1 user = User. query. filter_by (id> 5) 2 user. username = '[newname]' # update method 1 3 user. update ({'username': '[newname]'}) # update method 2 4 db. session. commit ()

• Query

1. query all. Role. query. all ()

2. Conditional query (using a filter ). User. query. filter_by (role = user_role). all ()

Common Filters

Filter	Description
Filter ()	Add the filter to the original query and return a new query.
Filter_by ()	Add the equivalent filter to the original query and return a new query.
Limit ()	Use the specified value to limit the number of results returned by the original query and return a new query.
Offset ()	Offset the result returned by the original query and return a new query.
Order_by ()	Sorts the original query results based on the specified conditions and returns a new query.
Group_by ()	Groups the original query results based on the specified conditions and returns a new query.

　　 The most commonly used SQLAlchemy query execution function

Method	Description
All ()	Returns all query results in the form of a list.
First ()	Returns the first result of the query. If no result is returned, None is returned.
First_or_404 ()	Returns the first result of the query. If no result is returned, the request is terminated and the 404 error response is returned.
Get ()	Returns the row corresponding to the specified primary key. If no corresponding row exists, None is returned.
Get_or_404 ()	Returns the row corresponding to the specified primary key. If the specified primary key is not found, the request is terminated and a 404 error response is returned.
Count ()	Number of returned query results
Paginate ()	Returns a Paginate object that contains results within the specified range.

• Query categories and Examples

1. Simple Query

Query. all (): returns all data of the list type.

2 Conditional Query

Query. filter_by (name = 'twc'). all ()

Query. filter_by (id = 5). first ()

Query. filter (User. username. endswith ('0'). all ()

3. Sort Query

Query. order_by (User. id. desc (). offset (1). limit (2). all ()

4. query groups

Query. get (ident): returns the specified tuples Based on the id attribute of the primary key.

5. Combined Query

Query. filter (and _ (User. id> 57, User. id <59). all ()

Query. filter (or _ (User. id = 57, User. id = 59). all ()

6 paging Query

Paginate (page = None, per_page = None, error_out = True) returns the pagination object. The first parameter indicates the current page, and the second parameter table indicates the number displayed on each page, error_out = True indicates that Error 404 is returned if no content is specified. Otherwise, an empty list is returned;

Common Methods for pagination objects are as follows:

Items: returns the content list of the current page.

Has_next: whether there is a next page

Has_prev: whether the previous page exists

Next (error_out = False): returns the Pagination object of the next page.

Prev (error_out = False): returns the Pagination object of the previous page.

Page: page number of the current page (starting from 1)

Pages: Total number of pages

Per_page: number of entries displayed on each page

Prev_num: Number of previous page numbers

Next_num: Number of next pages

Query: returns the query object for creating the Pagination object.

Total: total number of records returned by the query