Flask + pymysql: Mysql database instance, flaskpymysql

Flask + pymysql: Mysql database instance, flaskpymysql

Install flask-sqlalchemy and pymysql modules

pip install flask-sqlalchemy pymysql 

### Introduction to Flask-SQLAlchemy

1. ORM: Object Relationship Mapping (model Relationship ing ).

2. flask-sqlalchemy is a set of ORM frameworks.

3. Benefits of ORM: it is very convenient for us to operate databases and operate objects. Because a table is abstracted into a class, and a piece of data is abstracted into an object of the class.

4. install 'flask-sqlalchemy ': 'sudo pip install flask-sqlalchemy '.

Install Mysql database

From flask. ext. sqlalchemy import SQLAlchemy from flask import Flask ''' configure database ''' app = Flask (_ name _) app. config ['secret _ key'] = 'hard to guess '# the root user is logged in. Enter your own password. The default port of MySQL is 3306, enter the database name jianshu created before, connection method reference \ # http://docs.sqlalchemy.org/en/latest/dialects/mysql.html app. config ['sqlalchemy _ DATABASE_URI '] = 'mysql + pymysql: // jianshu: jianshu@127.0.0.1: 3306/jianshu # Set this item to automatically submit the change app in the database after each request ends. config ['sqlalchemy _ commit_on_tearlow'] = True # instantiate db = SQLALCHEMY (app)

Model Definition

'''Define the model and establish the relationship ''' class Role (db. model): # define the table name _ tablename _ = 'roles' # define the column Object id = db. column (db. integer, primary_key = True) name = db. column (db. string (64), unique = True) user = db. relationship ('user', backref = 'role') # The repr () method shows a readable string. Although not completely necessary, it is good for debugging and testing. Def _ repr _ (self): return '<Role {}> '. format (self. name) class User (db. model): _ tablename _ = 'users' id = db. column (db. integer, primary_key = True) username = db. column (db. string (64), unique = True, index = True) role_id = db. column (db. integer, db. foreignKey ('roles. id ') def _ repr _ (self): return' <User {}> '. format (self. username)

　　

Link

Relational databases establish connections in different tables by using links. The relationship diagram shows a simple relationship between a user and a user role. This role has a one-to-multiple relationship with the user, because one role can belong to multiple users, and one user can have only one role.
The following model class shows the one-to-many relationship expressed in.

class Role(db.Model):  # ...  users = db.relationship('User', backref='role')  class User(db.Model):  # ...  role_id = db.Column(db.Integer, db.ForeignKey('roles.id')) 

#!/usr/bin/env python  from exts import db from datetime import datetime   class User(db.Model):  __tablename__ = 'user' id = db.Column(db.Integer,primary_key=True,autoincrement=True)  username = db.Column(db.String(50),nullable=False)  telephone = db.Column(db.String(11),nullable=False)  password = db.Column(db.String(100), nullable=False)  class Questions(db.Model):  __tablename__ = 'questions' id = db.Column(db.Integer,primary_key=True,autoincrement=True)  title = db.Column(db.String(100),nullable=False)  content = db.Column(db.Text,nullable=False)  create_time = db.Column(db.DateTime,default=datetime.now)  author_id = db.Column(db.Integer,db.ForeignKey('user.id'))   author = db.relationship('User',backref=db.backref('questions'))  class Answer(db.Model):  __tablename__ = 'answer' id = db.Column(db.Integer,primary_key=True,autoincrement=True)  content = db.Column(db.Text,nullable=False)  question_id = db.Column(db.Integer,db.ForeignKey('questions.id'))  author_id = db.Column(db.Integer,db.ForeignKey('user.id'))   question = db.relationship('Questions',backref = db.backref('answers'))  author = db.relationship('User',backref = db.backref('answers'))

Add, delete, modify, and query Flask-SQLAlchemy data:

1. Add:

# Add:
Article1 = Article (title = 'aaa', content = 'bbb ')
Db. session. add (article1)
# Transactions
Db. session. commit ()

2. check:

# Query
# Select * from article where article. title = 'aaa ';
Article1 = Article. query. filter (Article. title = 'aaa'). first ()
Print 'title: % s' % article1.title
Print 'content: % s' % article1.content

3. Change:

# Change:
#1. Search for the data you want to change
Article1 = Article. query. filter (Article. title = 'aaa'). first ()
#2. Modify the data where you need to modify it.
Article1.title = 'new title'
#3. Commit transactions
Db. session. commit ()

4. Delete:

'''
# Delete
#1. Search for the data to be deleted
Article1 = Article. query. filter (Article. content = 'bbb '). first ()
#2. Delete this data item
Db. session. delete (article1)
#3. Commit transactions
Db. session. commit ()
'''

The above flask + pymysql instance for Mysql database operations is all the content shared by Alibaba Cloud. I hope you can give us a reference and support for the customer's house.