Using Flask-sqlalchemy to manipulate MySQL database

1 , Introduction

Flask-sqlalchemy is a Flask extension that simplifies the operation of using SQLAlchemy in Flask programs . SQLAlchemy is a powerful relational database framework that supports a variety of database backgrounds. SQLAlchemy provides a high-level ORM, as well as lower functionality that uses database native SQL .

2 , installation

$ pip Install Flask-sqlalchemy

3 , configuring the MySQL Connection

$ vim models.py

From Flask Importflask

From Flask_script import Manager

From Flask_sqlalchemy import SQLAlchemy

App =flask (__name__)

app.config[' sqlalchemy_database_uri ' = ' mysql://kevin:[email Protected]/kevin '

app.config[' sqlalchemy_commit_on_teardown ') = True

Manager = Manager (APP)

db = SQLAlchemy (APP)

if __name__ = = ' __main__ ':

Manager.run ()

4 , defining models

$ vim models.py(only modified parts listed)

db = SQLAlchemy (APP)

Class Role (db. Model):

__tablename__ = ' roles '

ID = db. Column (db. Integer,primary_key=true)

Name = db. Column (db. String (255), unique=true)

Users = db.relationship (' User ', backref= ' role ')

def __repr__ (self):

Return ' <role%r> '%self.name

Class User (db. Model):

__tablename__ = ' users '

ID = db. Column (db. Integer,primary_key=true)

Username = db. Column (db. String (255), unique=true,index=true)

Password = db. Column (db. String (255))

role_id = db. Column (db. Integer,db. ForeignKey (' roles.id '))

def __repr__ (self):

Return ' <user%r> '%self.username

The class variable __tablename__ defines the name of the table used in the database. If __tablename__ is not defined , flask-sqlalchemy will use the template name's small writing as the table name.

You do not need to define __init__ or __repr__, and SQLAlchemy automatically creates __init__ methodsIf they are not defined . If defined, the defined field name becomes the key parameter name that is received by this method.

5 , Database operations

5.1 , creating Tables

Create a database by using the Db.create_all () function. If a database table already exists in the database, db.create_all () does not recreate or update the table.

View Database

Mysql> use Kevin

Database changed

Mysql> Show tables;

Empty Set (0.00SEC)

Create

$ python models.py Shell

>>> from models Import db

>>>db.create_all ()

View

Mysql> Show tables;

+-----------------+

| Tables_in_kevin |

+-----------------+

| Roles |

| Users |

+-----------------+

2 rows in Set (0.00SEC)

5.2 , inserting data

>>> from models import Role,user

>>>admin_role = Role (name= ' admin ')

>>>mod_role = Role (name= ' moderator ')

>>>user_role = role (name= ' user ')

>>>user_kevin = User (username= ' Kevin ', Role=admin_role)

>>>user_susan = User (username= ' Susan ', Role=user_role)

>>>user_david = User (Username= ' David ', Role=user_role)

now these objects exist only in in Python, the database has not yet been written. Therefore , the ID has not been assigned a value.

>>>print (admin_role.id)

None

>>>print (mod_role.id)

None

>>>print (user_role.id)

None

changes made to the database through database session management are in Flask-sqlalchemy, the session is represented by Db.session . Before you are ready to write the object to the database, add it to the session first.

>>>db.session.add (Admin_role)

>>>db.session.add (Mod_role)

>>>db.session.add (User_role)

>>>db.session.add (User_kevin)

>>>db.session.add (User_susan)

>>>db.session.add (User_david)

can be abbreviated as:

>>>db.session.add_all ([Admin_role,mod_role,user_role,user_kevin,user_susan,user_david])

To write the object to the database, the commit () method is called to commit the session.

>>>db.session.commit ()

look at the ID properties again, they are already assigned, and the table has data.

>>>print (admin_role.id)

1

>>>print (mod_role.id)

2

>>>print (user_role.id)

3

Mysql> select * from roles;

+----+-----------+

| ID | name |

+----+-----------+

| 1 | Admin |

| 2 | Moderator |

| 3 | User |

+----+-----------+

3 Rows in Set (0.00SEC)

Mysql> select * from users;

+----+----------+----------+---------+

| ID | Username |password | role_id |

+----+----------+----------+---------+

| 1 | Kevin |       NULL | 1 |

| 2 | Susan |       NULL | 3 |

| 3 | David |       NULL | 3 |

+----+----------+----------+---------+

3 Rows in Set (0.00SEC)

5.3 , modify data

Calling the Add () method on a database session can also update the model. Rename the "Admin" role to "Administrator" below.

>>>admin_role.name = ' Adminitrator '

>>>db.session.add (Admin_role)

>>>db.session.commit ()

>>>print (Admin_role.name)

Adminitrator

5.4 , delete data

The database session also has a delete () method. The following is the removal of the "moderator" role from the database.

>>>db.session.delete (Mod_role)

>>>db.session.commit ()

Mysql> select name from roles where name= ' moderator ';

Empty Set (0.00SEC)

5.5 , query data

Flask-sqlalchemy provides a query object for each model class . The most basic model query is to retrieve all the records in the corresponding table.

>>>role.query.all ()

[<roleu ' Adminitrator ';, <role u ' User ';]

>>>user.query.all ()

[<useru ' Kevin ';, <user u ' Susan ';, <user U ' David;]

use filters to configure query objects for more accurate database queries. The following example finds all users with the role "user"

>>>user.query.filter_by (Role=user_role). All ()

[<useru ' Susan ';, <user U ' David;]

A filter such as filter_by () is called on the query object to return a more accurate query object. Multiple filters can be called together until the desired result is obtained. See the SQLAlchemy documentation for a complete list .

As a result of the limitations of personal technology, you are also requested to point out. The author can be found through the following two groups.

Beijing Linux operation and maintenance recruitment group: 153677549

Linux OPS Development Group: 298324302

This article is from the "Chang Jie listen to the Wind People" blog, please make sure to keep this source http://kevinhao.blog.51cto.com/5204735/1883759

Using Flask-sqlalchemy to manipulate MySQL database