Simple tutorial on using the sqlalchemy library in the Python Flask framework, flasksqlalchemy

Simple tutorial on using the sqlalchemy library in the Python Flask framework, flasksqlalchemy

Compared with sqlalchemy, sqlalchemy in flask is more thorough in encapsulation and simpler in some methods.

First import the Class Library:

View the CODE piece derived from my CODE piece on CODE

  <span style="font-size:18px;">from flask import Flask   from flask.ext.sqlalchemy import SQLAlchemy</span>

 

Then, you need to load the database path

View the CODE piece derived from my CODE piece on CODE

  <span style="font-size:18px;">mysqlname='<span style="color: rgb(230, 219, 116); font-family: 'Source Code Pro'; font-size: 13pt; background-color: rgb(39, 40, 34);">mysql://user:passwd@127.0.0.1/student?charset=utf8</span>'</span> 

View the CODE piece derived from my CODE piece on CODE

  <span style="font-size:18px;">app = Flask(__name__)   app.config['SQLALCHEMY_DATABASE_URI'] = mysqlname   db = SQLAlchemy(app)</span> 

In the previous two steps, we have linked flask and database.

Next we will associate flask with a specific table,

This creates a model.

View the CODE piece derived from my CODE piece on CODE

<Span style = "font-size: 18px;"> class User (db. model): "stores the quantity of each alarm type, measured in minutes: param source: string, alarm source: param network_logic_area: string, logical Network region to which the alarm belongs: param start_time: datetime, alarm occurrence time "" _ tablename _ = 'hello' id = db. column (db. integer, primary_key = True) source = db. column (db. string (255) network_logic_area = db. column (db. string (255) start_time = db. column (db. dateTime) count = db. column (db. integer) def _ init _ (self, source, network_logic_area, start_time, count): self. source = source self. network_logic_area = network_logic_area self. start_time = start_time self. count = count def alter (self): self. count + = 1; </span>

The above code associates falsk with the specific table hello.

In this class, we first need to specify the table, then list all the columns in the table, and finally define an initialization function for the data to be inserted later.

Start specific database operations now:

1. insert

View the CODE piece derived from my CODE piece on CODE

  <span style="font-size:18px;">    p = User(........)       db.session.add(p)       db.session.commit()</span> 

A data record is constructed by using the class User.

2. find

Use the primary key to obtain data:
Code example:

User.query.get(1)<User u'admin'>

Perform a lookup using an exact parameter:
Code example:

Peter = User. query. filter_by (username = 'Peter '). first () # Note: The exact query function. filter_by () is a query by passing parameters; other enhanced query functions are query. filter (), which is queried by passing an expression. Print (peter. id) # If the data does not exist, None is returned.

Fuzzy query:
Code example:
 

User.query.filter(User.email.endswith('@example.com')).all()[<User u'admin'>, <User u'guest'>]

Logical non-1:
Code example:
 

peter=User.query.filter(User.username !='peter').first()print(peter.id)

Logical non-2:
Code example:
 

fromsqlalchemy importnot_peter=User.query.filter(not_(User.username=='peter')).first()print(peter.id)

Logic and:
Code example:

fromsqlalchemy importand_peter=User.query.filter(and_(User.username=='peter', User.email.endswith('@example.com'))).first()print(peter.id)

Logic or:
Code example:

fromsqlalchemy importor_peter=User.query.filter(or_(User.username !='peter', User.email.endswith('@example.com'))).first()print(peter.id)

Filter_by: This file can only contain specific conditions, but cannot contain a complicated calculation,

Filter: some complex calculations can be put in it.

. First: Get the first data

. All: retrieve all data

There is another method for sorting, counting, and other operations.

3. Use SQL statements

You can directly use the native SQL statement through the db constructed above.

View the CODE piece derived from my CODE piece on CODE

  <span style="font-size:18px;">insert_table.db.engine.execute(' ..... ')</span> 

4. delete

View the CODE piece derived from my CODE piece on CODE

  <span style="font-size:18px;">me = User(........)</span> 

View the CODE piece derived from my CODE piece on CODE

  <span style="font-size:18px;">db.session.delete(me)   db.session.commit()</span> 

5. Update Data

Code example: u = User. query. first () u. username = 'guest '# It is easy to update data and assign values to variables, but it must be the objects returned by the query. Db. session. commit ()