Flask Study Notes (-database)

Python Database Framework

Most database engines have Python packages, including open source packages and commercial packages. Flask does not restrict what type of database package you use, so you can choose to use MySQL, Postgres, SQLite, Redis, MongoDB, or CouchDB, depending on your preference.

If none of this satisfies the requirements, there are some database abstraction layer code packages to choose from, such as SQLAlchemy and mongoengine. Instead of working with database entities such as tables, documents, or query languages, you can use these abstract packages to work directly with high-level Python objects.

Factors for choosing a database framework:

• Ease of use. The abstraction layer, also known as Object Relational Mapping (Object-relational mapper,orm) or Object document mapping (Object-document MAPPER,ODM), transforms the high-level object-oriented operations into lower database directives without the user's awareness.
• Performance. ORM and ODM transform the object business into the database business will have a certain loss. The real key point is how to choose an abstraction layer that can manipulate the lower database directly, in case the specific operation needs to use the database native instruction optimization directly.
• Portability. You must consider whether it can be used in your development platform and production platform.
• Flask Integration

Flask-sqlalchemy Management Database

• Installation

install flask-sqlalchemy

Making a database using a URL

Database Engine	URL
Mysql	Mysql://username:[email Protected]/database
Postgres	Postgresql://username:[email Protected]/database
SQLite (Unix)	Sqlite:////absolute/path/to/database
SQLite (Windows)	Sqlite:///c:/absolute/path/to/database

The SQLite database does not require a server, so you do not have to specify hostname, username, and password. The database in the URL is the file name of the file on your hard disk.

• Configuration
  The database URL used by the program must be saved to the Sqlalchemy_database_uri key of the Flask configuration object

There is also a useful option in the configuration object, that is, the Sqlalchemy_commit_on_teardown key, which, when set to True, will automatically commit changes in the database after each request is completed

fromimport SQLAlchemybasedir = os.path.abspath(os.path.dirname(__file__))app = Flask(__name__)app.config[‘SQLALCHEMY_DATABASE_URI‘] =‘sqlite:///‘‘data.sqlite‘)app.config[‘SQLALCHEMY_COMMIT_ON_TEARDOWN‘Truedb = SQLAlchemy(app)

• Defining the Model

 class Role(db. Model):__tablename__ =' Roles '#__tablename__ Define the name of the table used in the databaseID = db. Column (db. Integer, primary_key=True)#primary_key如果设为 True, this column is the primary key for the table. If __tablename__ is not defined, SQLAlchemy will use a default nameName = db. Column (db. String ( -), unique=True) def __repr__(self):        return ' <role% r> '% Self.name class User(db. Model):__tablename__ =' users 'ID = db. Column (db. Integer, primary_key=True) Username = db. Column (db. String ( -), unique=True, index=True) def __repr__(self):        return ' <user% r> '% Self.username

__repr__&__str__

The most common types of sqlalchemy columns

type name	python type	Description
Integer	Int	Ordinary integers, typically 32-bit
Smallinteger	Int	An integer with a small range of values, typically 16 bits
BigInteger	int or Long	Integer with no limit to precision
Float	Float	Floating point number
Numeric	Decimal. Decimal	Fixed-point number
String	Str	Variable length string
Text	Str	Variable length string, optimized for longer or unlimited lengths of strings
Unicode	Unicode	Variable-length Unicode string
UnicodeText	Unicode	Variable-length Unicode string optimized for longer 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	Time interval
Enum	Str	A set of strings
Pickletype	Any Python object	Automatic use of Pickle serialization
Largebinary	Str	binary files

Most commonly used sqlalchemy column options

Option Name	Description
Primary_key	If set to True, this column is the primary key of the table
Unique	If set to True, this column does not allow duplicate values
Index	If set to True, create an index for this column to improve query efficiency
Nullable	If set to True, this column allows null values, and if set to False, this column does not allow null values to be used
Default	Define default values for this column

Relationship expression

Relational databases use relationships to relate rows in different tables.

• One-to-many

 class Role(db. Model):    # ...Users = Db.relationship (' User ', backref=' role ')The users attribute in #添加到 Role model represents the object-oriented perspective of the relationship. For an instance of a role class, its Users property returns a list of the users associated with the role. The first parameter table of Db.relationship (), if the model class has not yet been defined, can be specified using a string. The Backref parameter in db.relationship () adds a role attribute to the User model to define the inverse relationship. This property replaces the ROLE_ID access role model, when you get a model object class User(db. Model):    # ...role_id = db. Column (db. Integer, Db. ForeignKey (' Roles.id '))The foreign keys in the #关系使用 users table are connected to two rows. The role_id column added to the USER model is defined as a foreign key, which is the foreign key that establishes the relationship. Passed to DB. The parameter ' roles.id ' of ForeignKey () indicates that the value of this column is the ID value of the row in the roles table. 

Db.relationship () can find a foreign key in a relationship on its own, but sometimes it is not possible to decide which column to use as a foreign key. If two or more columns in the USER model are defined as foreign keys to the Role model, SQLAlchemy does not know which column to use. If the foreign key cannot be determined, you will need to provide additional parameters for db.relationship () to determine the foreign key used

Common SQLAlchemy Relationship Options

Option Name	Description
Backref	To add a reverse reference to another model of a relationship
Primaryjoin	Explicitly specify the junction conditions used between the two models. You need to specify only in ambiguous relationships.
Lazy	Specifies how related records are loaded. Optional values are select (Load on demand on first visit), immediate (load after source object is loaded), joined (load record but use junction), subquery (load now but use subquery), noload (never load) and dynamic (does not load record , but provides a query to load the record)
Uselist	If set to Fales, do not use a list, but use a scalar value
Order_by	Specify how records are sorted in a relationship
Secondary	Specify the name of the relationship table in a many-to-many relationship
Secondaryjoin	SQLAlchemy a two-level join condition in a many-to-many relationship is not available at its discretion

• One
  A one-to-one relationship can be represented by a one-to-many relationship described earlier, but when calling Db.relationship (), set the uselist to False to turn "many" into "a".

• Many-to-many

tags = db. Table (' tags 'Db. Column (' tag_id 'Db. Integer, Db. ForeignKey (' Tag.id ')), Db. Column (' page_id 'Db. Integer, Db. ForeignKey (' Page.id '))) class Page(db. Model):ID = db. Column (db. Integer, primary_key=True) tags = db.relationship (' Tag ', Secondary=tags, Backref=db.backref (' pages ', lazy=' Dynamic ')) class Tag(db. Model):ID = db. Column (db. Integer, primary_key=True)

Database operations

• Create a table

python hello.py shell>>> fromimport db>>> db.create_all()

• Delete a table

db.drop_all()

• Insert Row

#创建对象, the model's constructor accepts parameters that are the initial value of the model property specified using the keyword parameter. Admin_role = Role (name=' Admin ') User_role = Role (name=' User ') User_susan = User (username=' Susan ', Role=user_role)#role property can also be used, although it is not a true database column, but it is a high-level representation of a one-to-many relationship. User_john = User (username=' John ', Role=admin_role)The #这些新建对象的 id attribute is not explicitly set because the primary key is managed by Flask-sqlalchemy. Print (admin_role.id)#None#通过数据库会话管理对数据库所做的改动, in Flask-sqlalchemy, the session is represented by Db.session. # #首先, add object to sessionDb.session.add (Admin_role) db.session.add (user_role) db.session.add (User_susan) db.session.add (User_john)#简写: Db.session.add_all ([Admin_role, User_role, User_john, User_susan])# #通过提交会话 (Transactions), writing objects to the databaseDb.session.commit ()

Session Submission :

Database sessions can guarantee the consistency of the database. The commit operation uses atomic methods to write all the objects in the session to the database. If an error occurs during a write session, the entire session will be invalidated.
Database sessions can also be rolled back. When Db.session.rollback () is called, all objects added to the database session are restored to their state when they are in the database.

• Modify rows

‘Administrator‘db.session.add(admin_role)session.commit()

• Delete Row

db.session.delete(mod_role)session.commit()

• Query rows

  • Query all. Role.query.all ()
  • Conditional query (using filters). User.query.filter_by (Role=user_role). All ()

  user_role = Role.query.filter_by(name=‘User‘).first()#filter_by() 等过滤器在 query 对象上调用,返回一个更精确的 query 对象。

  Common filters

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 ()	Uses the specified value to limit the number of results returned by the original query, returning a new query
Offset ()	Offsets the result returned by the original query and returns a new query
Order_by ()	Sorts the original query results according to the specified criteria, returning a new query
Group_by ()	Groups the original query results according to the specified criteria, returning a new query

The most commonly used SQLAlchemy query execution function

Method	Description
All ()	Return all results of a query as a list
First ()	Returns the first result of a query, or none if there is no result
first_or_404 ()	Returns the first result of the query and, if there is no result, terminates the request, returning a 404 error response
Get ()	Returns the row for the specified primary key, or none if there are no corresponding rows
get_or_404 ()	Returns the row for the specified primary key, terminates the request, returns a 404 error response If the specified primary key is not found
Count ()	Returns the number of query results
Paginate ()	Returns a Paginate object that contains the results in the specified range

• Relationship Query

  #执行 user_role.users 表达式时,隐含的查询会调用 all() 返回一个用户列表。 query 对象是隐藏的,因此无法指定更精确的查询过滤器。users = user_role.users#修改了关系的设置,加入了 lazy = ‘dynamic‘ 参数,从而禁止自动执行查询class Role(db.Model):users = db.relationship(‘User‘, backref=‘role‘, lazy=‘dynamic‘)#顺序排列user_role.users.order_by(User.username).all()

Manipulating the database in a view function

@app. Route ('/', methods=[' GET ', ' POST ') def index():form = Nameform ()ifForm.validate_on_submit (): User = User.query.filter_by (username=form.name.data). First ()ifUser is None: User = User (username = form.name.data) db.session.add (user) session[' known '] =False        Else: session[' known '] =Truesession[' name '] = Form.name.data Form.name.data ="'        returnRedirect (Url_for (' index '))returnRender_template (' index.html ', form = form, name = Session.get (' name '), known = Session.get (' known ',False))

Modify a template

{% extends "base.html"%} {% import "bootstrap/wtf.html" as WTF%} {% block title%} flasky{% endblock%} {% block page_content%}<div class="Page-header">        <H1>Hello, {% If name%}{{name}}{% Else%}stranger{% ENDIF%}!</H1>{% if not known%}<p>Pleased to meet you!</P>{% Else%}<p>Happy to see you again!</P>{% ENDIF%}</div>{{wtf.quick_form (form)}} {% Endblock%}

Integrated Python Shell

Let Flask-script's shell command automatically import specific objects

fromimport Shelldef make_shell_context():    return dict(app=app, db=db, User=User, Role=Role)  manager.add_command("shell", Shell(make_context=make_shell_context))

The Make_shell_context () function registers programs, database instances, and models, so these objects can be imported directly into the shell

Creating a migration warehouse with Flask-migrate for database migrations

install flask-migrate

Configuration

fromimport Migrate, MigrateCommand# ...migrate = Migrate(app, db)manager.add_command(‘db‘, MigrateCommand)

To create a migration warehouse with the INIT subcommand before maintaining the database migration

python hello.py db init

Create a migration script

"initial migration"

Update Database

python hello.py db upgrade

Flask Study Notes (-database)