A simple tutorial on using the sqlalchemy library in the Python Flask framework

This article mainly introduces a simple tutorial on using the sqlalchemy database in the Python Flask framework, which is used to connect to and operate databases in a concise manner, for more information, see sqlalchemy in flask, which is more thorough than sqlalchemy, and simpler in some methods.

First import the class library:

View the CODE piece derived from my CODE piece on CODE

  from flask import Flask   from flask.ext.sqlalchemy import SQLAlchemy

Then, you need to load the database path

View the CODE piece derived from my CODE piece on CODE

  mysqlname='mysql://user:passwd@127.0.0.1/student?charset=utf8' 

View the CODE piece derived from my CODE piece on CODE

  app = Flask(__name__)   app.config['SQLALCHEMY_DATABASE_URI'] = mysqlname   db = SQLAlchemy(app) 

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

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;

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

      p = User(........)       db.session.add(p)       db.session.commit() 

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)
 

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()[
 
  , 
  
   ]
  
 

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

  insert_table.db.engine.execute(' ..... ') 

4. delete

View the CODE piece derived from my CODE piece on CODE

  me = User(........) 

View the CODE piece derived from my CODE piece on CODE

  db.session.delete(me)   db.session.commit() 

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 ()