Flask + pymysql for Mysql database operations, flaskpymysql

Flask + pymysql for Mysql database operations, 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 SQLAlchemyfrom 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. For the connection method, see \ # login = 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 pythonfrom exts import dbfrom datetime import datetimeclass 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 ()
'''