Flask Basics of the Python Framework (ii)--------database operations

1.flask Four steps to connect to the database:

1. Pour into third-party database expansion pack: from Flask_sqlalchemy import SQLAlchemy
2. Configure the Config property to connect to the database:

   app.config["Sqlalchemy_database_uri"] = "Mysql://root:[email protected]/first_flask"
   app.config["sqlalchemy_track_modifications"] = False

3. Create a database First_flask
4. Create an Operational database object: DB = SQLAlchemy (APP)

The following code explains directly:

#-*-coding:utf-8-*- fromFlask Import Flask fromflask_sqlalchemy Import Sqlalchemyapp=The format of the Flask (__name__) # URL is: Database protocol://Username: Password @ip address: Port number (default can not write)/database nameapp.config["Sqlalchemy_database_uri"] ="Mysql://root:[email Protected]/first_flask"# Dynamic tracking of database modifications. Performance is not good. and will be removed in a future release. App.config[is only written to solve console prompts ."sqlalchemy_track_modifications"] =false# Creating a database operand db=SQLAlchemy (APP)classRole (db. Model): __tablename__="Roles"ID= db. Column (db. integer,primary_key=True) name= db. Column (db. String ( -), unique=True) # Creates a uses property for the role class that associates the users table. # Backref is the inverse of creating a role property for the user class, associating the roles table.    This is a special attribute of flask. Users= Db.relationship ('User', backref="role") # is equivalent to the __str__ method. def __repr__ (self):return "Role:%s%s"%(Self.id,self.name)classUser (db.    Model): # Redefine a name for the table, the default name is the lowercase of the class name, such as the default table name of the class is user. __tablename__="Users"ID= db. Column (db. integer,primary_key=True) name= db. Column (db. String ( -), unique=True) Email= db. Column (db. String ( +), unique=True) Password= db. Column (db. String ( -) # Create a foreign key, not the same as Django. Flask you need to specify a specific field to create a foreign key, you cannot create a foreign key based on the class name role_id= db. Column (db. Integer,db. ForeignKey ("roles.id") def __repr__ (self):return "User: %s%s%s%s"%(self.id,self.name,self.password,self.role_id) @app. Route ('/') def hello_world ():return 'Hello world!'if__name__ = ='__main__': # Delete all Tables Db.drop_all () # CREATE TABLE Db.create_all () Ro1= Role (name ="Admin"# The Ro1 object is added to the session first, and can be rolled back. Db.session.add (RO1) Ro2=Role () ro2.name='User'Db.session.add (RO2) # Last Insert data must be submitted Db.session.commit () US1= User (name='Wang', email='[email protected]', password='123456', role_id=ro1.id) US2= User (name='Zhang', email='[email protected]', password='201512', role_id=ro2.id) US3= User (name='Chen', email='[email protected]', password='987654', role_id=ro2.id) US4= User (name='Zhou', email='[email protected]', password='456789', role_id=ro1.id) US5= User (name='Tang', email='[email protected]', password='158104', role_id=ro2.id) US6= User (name='Wu', email='[email protected]', password='5623514', role_id=ro2.id) US7= User (name='Qian', email='[email protected]', password='1543567', role_id=ro1.id) US8= User (name='Liu', email='[email protected]', password='867322', role_id=ro1.id) US9= User (name='Li', email='[email protected]', password='4526342', role_id=ro2.id) US10= User (name='Sun', email='[email protected]', password='235523', role_id=ro2.id) Db.session.add_all ([US1, US2, US3, US4, US5, US6, US7, US8, US9, US10]) Db.session.commit () App.run ( Debug=true)

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------

Here's a bug:

When the table is created, note these two words:

# Delete all tables    db.drop_all ()    # CREATE TABLE    Db.create_all ()

Then you insert the data into the table, and this error occurs:

Sqlalchemy.exc.IntegrityError: (_mysql_exceptions. Integrityerror) (1062, "Duplicate entry ' admin ' for key ' name '") [Sql:u ' INSERT into roles (name) VALUES (%s) '] [parameter S: (' admin ',)]

Check the online a lot of information that the constraints of the field unique=true remove the good, but the root cause is not here.

The reason is because App.run (debug=true). After you turn on debug mode, when we modify the code, such as deleting the table and creating the table, the two sentences comment, and then open the comment that inserts the data. This process debug mode has already run the program again by default. Now that the database has data, when we manually execute again, and then insert a data into the database, this time will be an error. Because field constraints are unique to uniqueness, there are two ways to resolve them:

The first: Do not delete the table and create the table of the two sentences, each execution should carry these two words. Whether the debug mode is executed automatically or if we execute the program manually, we will delete the table and then create the table, so how many times do not be afraid.

The second type: Turn off debug mode. That's it. App.run ()

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------

2. Database additions and deletions:

1. The following method is to return a new query that needs to be used with the executor.

Filter (): Filtering, the function is more powerful.
Filter_by (): filter, used in some of the more simple filtering scenarios.

order_by (): Sort. The default is ascending, descending requires a guide package:from sqlalchemy import *. The desc method is then introduced. such as order_by (DESC ("email")). Sort by the mailbox letter in descending order.

Group_by (): Grouping.

2. The following are some common actuators: use with the above filter.

Get (): Gets the function with the ID equal to several. For example: Query the object of id=1. Get (1). Remember: There is no "id=" in parentheses, the value of the direct pass-through ID is OK. Because the function is the function of querying the primary key equals several objects.

All (): Queries all data.

First (): Queries the data.

COUNT (): Returns the number of query results.

Paginate (): Paged query, returns a paging object. Paginate (parameter 1, parameter 2, parameter 3)

Parameter 1: Current page, Parameter 2: Displays several records per page, parameter 3: whether to return an error.

The paged object returned has three properties: items: Get the results of the query, pages: Get a total of how many pages, page: Get the current page.

3. Common Logic characters:

Need to pour into the package to be used: from SQLAlchemy import *

Not_ And_ Or_ also has the sort desc above said.

The common built-in ones are: In_ indicates the scope of a field.

4. Some database queries for other relationships:

EndsWith (): End With what.

StartsWith (): Start with what.

Contains (): Contains

5. Let's take a look at the above usage:

1. Querying all user Data User.query.all ()2. Query how many users User.query.count ()3. Query 1th User User.query.first ()4. Query the user with ID 4 [3 ways]user.query.get (4) User.query.filter_by (ID=4). First () User.query.filter (User.ID==4). First ()filter: (Class name. property name = =) Filter_by: (Property name = filter_by: For querying simple column names, the comparison operator filter is more powerful than filter_by, supports comparison operators, supports syntax such as OR_, In_, and so on. 5. Query all data with the end-of-name character G [Start/include] User.query.filter (User.name.endswith ('g') . All () User.query.filter (User.name.contains (' G ')). All ()6the query name does not equal all of Wang's data [2 ways] fromSQLAlchemyImportNot_
Note: Logical query format : logical Character _ ( class attribute other judgment )User.query.filter (not_ (user.name = = ' Wang ')  . All () User.query.filter (User.Name!='Wang'). All ()7. Query all data with the first name and mailbox starting with Li [2 ways] fromSQLAlchemyImportAnd_User.query.filter (And_ (User.name.startswith ('Li'), User.email.startswith ('Li')) . All () User.query.filter (User.name.startswith ('Li'), User.email.startswith ('Li') . All ()8. Query password is ' 123456' or ' email ' all data ending with ' itheima.com ' fromSQLAlchemyImportOr_User.query.filter (or_ (User.password=='123456', User.email.endswith ('itheima.com')) . All ()9. The query ID is [1, 3, 5, 7, 9] User list User.query.filter (User.id.in_ ([1, 3, 5, 7, 9]). All ()10. Query the role data relationship reference for name Liu User.query.filter_by (name='Liu'). First (). Role.name11. Query all user data and sort by mailbox User.query.order_by ('Email'). All () The default ascending User.query.order_by (desc(' email ')). All () descending12. Query the data on page 2nd, show only 3 data per page Help (User.query.paginate) three parameters:1. The number of pages currently being queried is 2. Number of pages per page 3. Do you want to return the error pages= User.query.paginate (2, 3, False) Pages.items#get the results of a queryPages.pages#Total PagesPages.page#Current Page

Flask Basics of the Python Framework (ii)--------database operations