The flask-sqlalchemy,flask-migrate of Flask learning record

The Flask-sqlalchemy library makes it easier for Flask to use SQLAlchemy, a powerful relational database framework that can either manipulate the database using ORM or use the original SQL commands.

Flask-migrate is a data migration framework that needs to be manipulated through the Flask-script library.

I. Configuration Flask-sqlalchemy

The database address used by the program needs to be configured in Sqlalchemy_database_uri, SQLAlchemy supports multiple databases, and the configuration format is as follows:

Postgres:

Postgresql://scott:[email Protected]/mydatabase

Mysql:

Mysql://scott:[email Protected]/mydatabase

Oracle:

Oracle://scott:[email Protected]:1521/sidname

Sqlite:

Sqlite:////absolute/path/to/foo.db

DB is an instance of the SQLAlchemy class that represents the database used by the program, providing the user with all the functionality of the Flask-sqlalchemy

 fromFlaskImportFlask fromFlask.ext.sqlalchemyImportSqlalchemyapp= Flask (__name__)#Configure database Addressapp.config['Sqlalchemy_database_uri'] ='sqlite:////tmp/test.db'#This configuration is true, and the commit database changes automatically at the end of each requestapp.config['Sqlalchemy_commit_on_teardown'] =Truedb=SQLAlchemy (APP)#can also be db = SQLAlchemy () Db.init_app (APP)

Two. Defining the Model

Flask-sqlalchemy uses classes that inherit to Db.model to define the model, such as:

 class  User (db. Model, usermixin): #   Usermixin is required in the Flask-login library for  __tablename__  =   users       #   ID = db. Column (db. Integer,primary_key=true) Username  = db. Column (db. String (Unique=
    
     true) 
     
 Password = db. Column (db. String (+)) 
     
 
     def  __repr__  " Span style= "color: #000000;" > (self):  return   " <user%r>  % self.username 

Define the need to import the db in the Python shell, call Db.create_all () to create the database

(1) characters commonly used segment options:

Primary_key Setting the primary key

Unique is the only

Index whether to create indexes

Whether the nullable is allowed to be empty

Default setting defaults, which can pass in a reference to a function, such as an incoming Datetime.datetime.utcnow, is the latest time each time it is created

Three. Additions and deletions to check and change

(1) Insert data:

 from  app.models import   User  from  app import   db  #   Create a new user  u = User () u.username  =  " abc   '  

(2) Find data:

#returns all users saved to listUser_list =User.query.all ()#find the first user username for ABC, return the user instanceU = User.query.filter_by (username='ABC'). First ()#Blur Find all users with a user name ending in CUser_list = User.query.filter (Username.endswith ('C') . All ()#find a user with a user name other than ABCU = User.query.filter (username! ='ABC'). First ()

(3) Delete data:

user = User.query.first () db.session.delete (user) Db.session.commit ()

(4) Modify the data:

u ='sb'db.session.commit ()

Four. One-to-many relationships

classPerson (db. Model): ID= db. Column (db. Integer, primary_key=True) name= db. Column (db. String (50))    #using the relationship definition on many sides, Backref will create a person reference named persons in the Address table, which can then be used by everyone who address.persons access to the address.Addresses = Db.relationship ('Address', backref='Persons', Lazy='Dynamic')classAddress (db. Model): ID= db. Column (db. Integer, primary_key=True) Email= db. Column (db. String (50))    #use DB on one side. ForeignKey declaring foreign keysperson_id = db. Column (db. Integer, Db. ForeignKey ('person.id'))

I. Configuration Flask-migrate

 from Import  = Migrate (app,db)# The first argument is an instance of flask, and the second argument is the SQLAlchemy db instance manager.add_ Command ('db', Migratecommand)#Manaer is an instance of Flask-script , this statement adds a DB command to the Flask-script

Two. Using Flask-migrate

In the command prompt line

Enter Python manager.py DB init to create the migration warehouse,

Enter Python manager.py db migrate-m ' initial migration ' to create a migration script that creates a migration script after the database structure changes

Enter Python manager.py DB upgrade to update the database

Flask-sqlalchemy,flask-migrate of Flask Learning records