Of Stack

A simple tutorial using the sqlalchemy library in Python's Flask framework

  • 2020-05-07 19:58:49
  • OfStack

sqlalchemy in flask is more thorough and simpler in some ways than sqlalchemy

First of all, import class library:

View the code slice on CODE that is derived to my code slice 


  <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 slice on CODE that is derived to my code slice 


  <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 slice on CODE that is derived to my code slice 


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


Through the previous two steps, we have already connected flask to the database

Next, we will link flask to the specific table in 1.

Thus, an model model is built

View the code slice on CODE to derive my code slice 


  <span style="font-size:18px;">class User(db.Model): 
   
    """ storage   Number of each alarm type   .   In order to   minutes   Is a unit for statistics  
    :param source: string , The police source  
    :param network_logic_area: string , The logical network area to which the alarm belongs  
    :param start_time: datetime ,  Alarm 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 code above links falsk to the specific hello table

In this class, we first specify the table, then list all the columns in the table, and finally define an initialization function to be used by the insert data


Now start the specific database operation:

1, insert

View the code slice on CODE that is derived to my code slice 


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

A piece of data is constructed through class User

2, find

Get data with primary key:
Code example: 


User.query.get(1)

<User
 u'admin'>

The reverse check is carried out with 1 precise parameter:
Code example: 


peter
=

User.query.filter_by(username='peter').first() 
# Note: query functions precisely query.filter_by() , is to query by passing parameters; Other enhanced query functions are query.filter() , by passing an expression. 

print(peter.id) 
# Returns if the data does not exist None

Fuzzy query:
Code example:
  


User.query.filter(User.email.endswith('@example.com')).all()

[<User
 u'admin'>,
 <User u'guest'>]

Logic non-1:
Code example:
  


peter
=

User.query.filter(User.username
 !=

'peter').first()

print(peter.id)

Logic non-2:
Code example:
  


from

sqlalchemy import

not_

peter
=

User.query.filter(not_(User.username=='peter')).first()

print(peter.id)

Logic and:
Code example: 


  <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>
0

Logic or:
Code example: 


  <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>
1

filter_by: this can only put specific conditions, not a complex calculation,

filter: you can put some complicated calculations in this one

.first: take the first data

.all: fetch all data

There is one other way to do things like sort and count

3. Use sql statement

The native statements of sql can be used directly from the db constructed earlier

View the code slice on CODE that is derived to my code slice 


  <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>
2


4, delete

View the code slice on CODE derived to my code slice 


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

View the code slice on CODE that is derived to my code slice 


  <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>
4

5. Update data 


  <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>
5

Related articles: