本篇将采用 mvc 的形式来使用数据库
安装 pymysql 就不再陈述，架构红图也不在陈述
在 app 文件夹下面新建一个 config 文件夹里面起名一个文件 setting.py
这里千万记住后边不加,号 加了就变成元组
DATABASE_HOST = '127.0.0.1'
DATABASE_PORT = 3306
DATABASE_USER = 'root'
DATABASE_PASSWORD = '123456'
DATABASE_NAME= 'pycharm'
DATABASE_TABLE_STUDENT = 'student'
DATABASE_TABLE_TEACHER = 'teacher'
(模型层)在 app 文件夹下面新建一个 model 文件夹里面新建一个 mysqltools.py
这里面主要就是模型类,它实现了创建数据库,表,还有增删改查
- 关闭数据库必不可少
# coding utf-8#
#-----------------------------------------------
# Name: mysqltools
# Description
# Author: 雾烟云
# Date: 2020/8/14 0014
import pymysql
from flask import current_app
print(current_app.config)
class MysqlTools():
def __init__(self):
self.conn = pymysql.connect(
host = current_app.config['DATABASE_HOST'],
port = current_app.config['DATABASE_PORT'],
user = current_app.config['DATABASE_USER'],
passwd = current_app.config['DATABASE_PASSWORD'],
)
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
def create_database(self):
self.cursor.execute('show databases')
database_all = self.cursor.fetchall()
print(database_all)
num = 0
for content in database_all:
if content['Database'] == current_app.config['DATABASE_NAME']:
num=num+1
if num==0:
try:
create_database_sql = f'CREATE DATABASE IF NOT EXISTS {current_app.config["DATABASE_NAME"]} DEFAULT CHARSET utf8 COLLATE utf8_general_ci;'
print(create_database_sql)
self.cursor.execute(create_database_sql)
except pymysql.Error as e:
print('pymysql.Error: ', e.args[0], e.args[1])
self.create_table()
else:
print("数据库已经存在")
self.closemysql()
def create_table(self):
self.cursor.execute(f'use {current_app.config["DATABASE_NAME"]} ;')
self.cursor.execute('Show tables;')
table_list_info = self.cursor.fetchall()
num = 0
for content in table_list_info:
if content == current_app.config["DATABASE_TABLE_STUDENT"]:
num = num + 1
if num == 0:
try:
create_table_sql = f'CREATE TABLE IF NOT EXISTS `{current_app.config["DATABASE_TABLE_STUDENT"]}`(`id` INT UNSIGNED AUTO_INCREMENT,`title` VARCHAR(100) NOT NULL,`author` VARCHAR(40) NOT NULL,`date` DATE,PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;'
print(create_table_sql)
self.cursor.execute(create_table_sql)
create_table_sql2 = f'CREATE TABLE IF NOT EXISTS `{current_app.config["DATABASE_TABLE_TEACHER"]}`(`id` INT UNSIGNED AUTO_INCREMENT,`title` VARCHAR(100) NOT NULL,`author` VARCHAR(40) NOT NULL,`date` DATE,PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;'
print(create_table_sql2)
self.cursor.execute(create_table_sql2)
except pymysql.Error as e:
print('pymysql.Error: ', e.args[0], e.args[1])
else:
print("表已经存在")
print(table_list_info)
# 增加,删除,改数据
def control_data(self,sql):
self.conn.ping(reconnect=True)
self.cursor.execute(f'use {current_app.config["DATABASE_NAME"]} ;')
try:
result = self.cursor.execute(sql)
print(result)
except pymysql.Error as e:
print('pymysql.Error: ', e.args[0], e.args[1])
self.closemysql()
return result
# 查询数据库
def select_data(self,sql):
self.conn.ping(reconnect=True)
self.cursor.execute(f'use {current_app.config["DATABASE_NAME"]} ;')
try:
self.cursor.execute(sql)
data = self.cursor.fetchall()
print(data)
except pymysql.Error as e:
print('pymysql.Error: ', e.args[0], e.args[1])
self.closemysql()
return data
# 操作关闭数据库
def closemysql(self):
self.conn.commit()
self.cursor.close()
self.conn.close()
# 查询的时候关闭数据库
app.py 在使用的时候 要补充下
- 必须使用 with 上下文链接
# 两步 创建app 加载蓝图
from flask import Flask
def create_app():
app = Flask(__name__)
app.config.from_object("app.config.setting")
app.config.from_object("app.config.special")
# 加载蓝图
add_blueprint(app)
# 加载数据库
with app.app_context():
create_database()
return app
def add_blueprint(app):
from app.api.v1 import create_blueprint
app.register_blueprint(create_blueprint(),url_prefix="/v1")
# 创建数据库
def create_database():
from app.model.mysqltools import MysqlTools
tools = MysqlTools()
tools.create_database()
(控制层)control 新建一个 control 文件夹,里面在新建一个 student 文件夹和 teacher 文件夹
f 配合{}里面可以放变量
在 student 文件夹下面新建 control_student.py 文件
在 teacher 文件夹下面新建 control_teacher.py 文件
control_student.py 文件里面写
from app.model.mysqltools import MysqlTools
from flask import jsonify
class control_student():
def __init__(self):
self.control = MysqlTools()
# 查询数据
def selectuser(self):
sql = 'select * from student;'
result = self.control.select_data(sql)
return jsonify(result)
# 插入数据
def Insertuser(self,value):
sql = f'Insert into student(title,author,date) values ("{value["title"]}","{value["author"]}","{value["date"]}");'
result = self.control.control_data(sql)
if result==1:
message = {
'code':200,
'success':True
}
return jsonify(message)
- control_teacher.py 文件里面写
from app.model.mysqltools import MysqlTools
from flask import jsonify
class control_teacher():
def __init__(self):
self.control = MysqlTools()
# 查询数据
def selectteacher(self):
sql = 'select * from teacher;'
result = self.control.select_data(sql)
return jsonify(result)
# 插入数据
def Insertteacher(self,value):
sql = f'Insert into teacher (title,author,date) values ("{value["title"]}","{value["author"]}","{value["date"]}");'
print(sql)
result = self.control.control_data(sql)
if result==1:
message = {
'code':200,
'success':True
}
return jsonify(message)
(视图层) 最后路由里面写也就是视图层 V
- teacher.py
from app.libs.Redprint import RedPrint
api = RedPrint("teacher")
@api.route("/insertteacher",methods=["POST","GET"])
def InsertTeacher():
from app.control.teacher.control_teacher import control_teacher
mysqltools = control_teacher()
values = {
'title':'测试老师数据222',
'author':'老师测试222',
'date':'2020-07-31'
}
mysqlresult = mysqltools.Insertteacher(values)
return mysqlresult
@api.route("/selectteacher",methods=["POST","GET"])
def SelectTeacher():
from app.control.teacher.control_teacher import control_teacher
mysqltools = control_teacher()
mysqlresult = mysqltools.selectteacher()
return mysqlresult
- user.py
from app.libs.Redprint import RedPrint
api = RedPrint("user")
@api.route("/insertuser",methods=["POST","GET"])
def Insertuser():
from app.control.student.control_student import control_student
mysqltools = control_student()
values = {
'title':'测试数据',
'author':'张三测试',
'date':'2020-07-31'
}
mysqlresult = mysqltools.Insertuser(values)
return mysqlresult
@api.route("/selectuser",methods=["POST","GET"])
def Selectuser():
from app.control.student.control_student import control_student
mysqltools = control_student()
mysqlresult = mysqltools.selectuser()
return mysqlresult
