Those years we learn flask-sqlalchemy, to achieve database operations, paging and other functions

All those years we learned flask-sqlalchemy

implementation of database operations, paging and other functions  

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

From flask import Flaskfrom flask.ext.sqlalchemy Import Sqlalchemyapp = Flask (__name__) #配置数据库地址app. config[' Sqlalchemy_ Database_uri '] = ' mysql://username:[email Protected]:3306/db_name?charset=utf8 ' #该配置为True, The COMMIT database changes automatically at the end of each request app.config[' sqlalchemy_commit_on_teardown ' = Truedb = SQLALCHEMY (APP) #也可以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是Flask required in the-login library __tablename__ = ' users ' #每个属性定义一个字段 id = db. Column (db. Integer,primary_key=true) Username = db. Column (db. String (UP), unique=true) password = db. Column (db. String (+)) #定制显示的格式 def __repr__ (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 userfrom app import db# Create a new user U = User () U.username = ' abc ' U.password = ' abc ' #将用户添加到数据库会话中db. session . Add (U) #将数据库会话中的变动提交到数据库中, if not commit, there is no change in the database Db.commit ()

(2) Find data:

#返回所有用户保存到list中user_list = User.query.all () #查找username为abc的第一个用户, returns the user instance U = User.query.filter_by (username= ' abc '). First () #模糊查找用户名以c结尾的所有用户user_list = User.query.filter (Username.endswith (' C ')). All () #查找用户名不是abc的用户u = 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 = User.query.first () u.username = ' Mackie ' Db.session.commit ()

four. One-to-many relationships

My understanding is: The foreign key is defined on many sides, and the relathonship () function is used to establish the relationship, it can be defined only on one side, or it can be used on either side (only when using the BACKREF option with the same side)

Class person (db. Model):        __tablename__ =  ' persons '              id = db. Column (db. Integer, primary_key=true)     name = db. Column (db. String (a)          #backref将在Address表中创建个名为persons的Person引用, You can then use the address.persons          #访问这个地址的所有人      Addresses = db.relationship (' Address ',  backref= ' persons ', lazy= ' dynamic ') class address (db. Model):        __tablename__ =  ' address '              id = db. Column (db. Integer, primary_key=true)     email = db. Column (db. String ()          #在多的一边使用db. ForeignKey declaring foreign Key     pErson_id = db. Column (db. Integer, db. ForeignKey (' person.id '))

Five. Many-to-many relationships

A many-to-many relationship can be decomposed into two many-to-one relationships between the original table and the associated table, and the following code establishes the relationship between the student and the selected course:

#创建关联表, the foreign keys of the two fields are another two tables, one student corresponds to multiple related tables, and an association table corresponds to multiple course registrations = db. Table (' Registrations ',                          db. Column (' student_id ', db. Integer,db. ForeignKey (' students.id ')),                          db. Column (' class_id ', db. Integer,db. ForeignKey (' classes.id '))                           ) class student (db. Model):    __tablename__ =  ' students '     id = db. Column (db. Integer,primary_key=true,)     name = db. Column (db. String)     classes = db.relationship (' Class ',                                secondary = registrations,  #关联表, you just need to build a relationship in one table, SQLAlchemy will take care of the other table.                                backref =  Db.backref (' Students ', lazy= ' dynamic '),                                lazy =  ' dynamic ') class class (db. Model):    __tablename__ =  ' classes '     id = db. Column (db. Integer, primary_key=true)     name = db. Column (db. String)

Many-to-many use:

#学生1增加一门选课
Student1.classes.append (Class1)
#学生1退选class1
Student1.classes.remove (Class1)
#学生1所选课程, because lazy= ' dynamic ' is specified, there is no direct return to the list, but it needs to be used. All ()
Student1.classes.all ()

Six. Paging Navigation

The pagination object of Flask-sqlalchemy can be conveniently paged,

Call the Pagenate (page, per_page=20, error_out=true) function on a query object to get the pagination object, the first parameter represents the current page, the second parameter represents the number displayed per page, error_out= True if the specified page does not have content that will appear with a 404 error, otherwise an empty list is returned

#从get方法中取得页码page = request.args.get (' page ', 1, type = int) #获取pagination对象 pagination = Post.query.order_by (Post.timesta Mp.desc ()). Paginate (page, per_page=10, error_out = False) #pagination对象的items方法返回当前页的内容列表 posts = Pagination.items

common methods for pagination objects:

Has_next: Do you have the next page?

Has_prev: Do you have a previous page?

Items: Return all contents of the current page

Next (Error_out=false): Returns the Pagination object on the next page

Prev (Error_out=false): Returns the Pagination object on the previous page

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

Pages: Total pages

Per_page: Number of displays per page

Prev_num: Previous Page page number

Next_num: Next Page page number

Query: Returns the queried object that created this pagination object

Total: Number of records returned by the query

Iter_pages (left_edge=2, left_current=2, right_current=5, right_edge=2)

using in Templates

Method One:

{% macro render_pagination (pagination, endpoint)  %}   <div class=pagination>  {%- for page in pagination.iter_ Pages ()  %}    {% if page %}      {%  if page != pagination.page %}        <a  href= "{{ url_for (endpoint, page=page)  }}" >{{ page }}</a>       {% else %}        <strong>{{  page }}</strong>      {% endif %}     {% else %}      <span class=ellipsis>...</span>     {% endif %}  {%- endfor %}  </div>{%  endmacro %} 

Method Two: jinjia2 render +bootstrap Template

<!--  Create page--><ul class= "pagination" >{#上一页 #}    {%  if  Pagination.has_prev  %}        <li><a href= "{{  url_for (' Useradmin ', page=pagination.prev_num)  }} ">&laquo;</a></li>     {% endif %}    {#页码 #}    {% set page_ now = pagination.page  %}    {% set page_count =  pagination.pages %}    {% if pagination.pages <= 5 %}         {% for p in pagination.iter_pages ()  % }                {% if  p == pagination.page %}               &nbSp;   <li ><a style= "Background-color: darkgray;opacity: 0.7;color :  black " href=" {{ url_for (' useradmin ', page=p)  }} ">{{ p }}</a></li >                {%  else %}                     <li ><a href= "{{ url_for (' useradmin ', page=p)  }}" >{{  p }}</a></li>                 {% endif %}        {% endfor  %}        {% else %}             {%  if page_now-2 >0 %}        &nbsP;        <li><a href= "{{ url_for (' UserAdmin ', page= page_now-2)  }} ">{{ page_now-2 }}</a></li>             {% endif %}             {% if  page_now-1 >0  %}                 <li><a href= "{{ url _for (' Useradmin ', page=page_now-1)  }} ">{{ page_now-1 }}</a></li>             {% endif %}                 <li ><a style= " Background-color: darkgray;opacity: 0.7;color: black " href=" {{ url_for (' UserAdmin '), Page=page_now)  }} ">{{ page_now }}</a></li>            {%  if  (Page_count-page_now)  >1  %}                 <li><a href= "{{ url_for (' UserAdmin ', page= page_now+1)  }} ">{{ page_now+1 }}</a></li>             {% endif %}             {% if  (Page_count - page_now)  >2 %}                 <li><a href= "{{  url_for (' Useradmin ', page=page_now+1)  }} ">{{ page_now+2 }}</a></li>             {% endif %}     {% endif %}{#下一页 #}    {%  if pagination.has_next  %}         <li><a href= "{{ url_for (' useradmin ', page=pagination.next_num)  }}" >&raquo;</a></li>    {% endif %}    < li><span style= "Color: black" > pages   ( {{ page_now }}/{{ page_count  }} ) </span></li></ul>

Effect Show:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/89/47/wKiom1gOOurSf9olAAAK6xH4iUE659.png "title=" Paging style 1.png "alt=" Wkiom1gooursf9olaaak6xh4iue659.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/89/47/wKiom1gOOs-z9QPuAAAK6xH4iUE840.png "style=" float: none; "title=" page style 1.png "alt=" Wkiom1goos-z9qpuaaak6xh4iue840.png "/>650" this.width=650; "src="/HTTP/ S5.51cto.com/wyfs02/m01/89/45/wkiol1goos-twauwaaah7jdesnq274.png "title=" page style 2.png "style=" Float:none; "alt=" Wkiol1goos-twauwaaah7jdesnq274.png "/>

This article from "Sour Milk" blog, declined reprint!

Those years we learn flask-sqlalchemy, to achieve database operations, paging and other functions