flask 框架操作MySQL数据库简单示例
作者:Dawn__Z 时间:2024-01-16 12:48:10
本文实例讲述了flask 框架操作MySQL数据库。分享给大家供大家参考,具体如下:
一、创建数据库表格
"""
Created on 19-10-8
@requirement:Anaconda 4.3.0 (64-bit) Python3.6
@description:创建表格
"""
import pymysql
server = '127.0.0.1'
user = 'root'
password = 'password'
# 连接数据库
conn = pymysql.connect(server, user, password, database='information_collection') # 获取连接
cursor = conn.cursor() # 获取游标
# "**ENGINE=InnoDB DEFAULT CHARSET=utf8**"-创建表的过程中增加这条,中文就不是乱码
# 创建表
cursor.execute("""
CREATE TABLE if not exists user(
user_id INT NOT NULL auto_increment primary key,
user_name VARCHAR(100),
user_password VARCHAR(100),
user_nickname VARCHAR(100),
user_email VARCHAR(100)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
""")
# 查询数据库表user内容
cursor.execute('SELECT * FROM user')
# 查看一行 多行:cursor.fetchall()
row = cursor.fetchone()
print(row)
# if row[0] is None:
# row0 = list(row)
# row0[0] = 0
# row = tuple(row0)
# # 插入数据,注:与sqlserver有些区别
cursor.execute("INSERT INTO user VALUES('%s','%s','%s','%s')" % ('xiaoming','qwe','ming','@163.com'))
# 提交数据,才会写入表格
conn.commit()
# 关闭游标关闭数据库
cursor.close()
conn.close()
二、flask操作mysql
"""
Created on 19-10-8
@requirement:Anaconda 4.3.0 (64-bit) Python3.6
@description:
"""
from flask_sqlalchemy import SQLAlchemy
from flask import Flask, jsonify, request
import configparser
import os
app = Flask(__name__)
# 使用ConfigParser 首选需要初始化实例,并读取配置文件:
my_config = configparser.ConfigParser()
my_config.read('db.conf')
# 连接数据库information_collection
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('DEV_DATABASE_URL') or \
"mysql+pymysql://root:password@127.0.0.1:3306/information_collection"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
mydb = SQLAlchemy()
mydb.init_app(app)
# 用户模型
class User(mydb.Model):
user_id = mydb.Column(mydb.Integer, primary_key=True)
user_name = mydb.Column(mydb.String(60), nullable=False)
user_password = mydb.Column(mydb.String(30), nullable=False)
user_nickname = mydb.Column(mydb.String(50))
user_email = mydb.Column(mydb.String(30), nullable=False)
def __repr__(self):
return '<User %r>' % self.user_name
# 获取用户列表,所有数据
@app.route('/users', methods=['GET'])
def getUsers():
data = User.query.all()
datas = []
for user in data:
datas.append({'user_id': user.user_id, 'user_name': user.user_name, 'user_nickname': user.user_nickname, 'user_email': user.user_email})
return jsonify(data=datas)
# 添加用户数据,一条一条添加
@app.route('/user', methods=['POST'])
def addUser():
user_name = request.form.get('user_name')
user_password = request.form.get('user_password')
user_nickname = request.form.get('user_nickname')
user_email = request.form.get('user_email')
user = User(user_name=user_name, user_password=user_password, user_nickname=user_nickname, user_email=user_email)
try:
mydb.session.add(user)
mydb.session.commit()
except:
mydb.session.rollback()
mydb.session.flush()
userId = user.user_id
if (user.user_id is None):
result = {'msg': '添加失败'}
return jsonify(data=result)
data = User.query.filter_by(user_id=userId).first()
result = {'user_id': user.user_id, 'user_name': user.user_name, 'user_nickname': user.user_nickname, 'user_email': user.user_email}
return jsonify(data=result)
# 获取单条数据
@app.route('/user/<int:userId>', methods=['GET'])
def getUser(userId):
user = User.query.filter_by(user_id=userId).first()
if (user is None):
result = {'msg': '找不到数据'}
else:
result = {'user_id': user.user_id, 'user_name': user.user_name, 'user_nickname': user.user_nickname, 'user_email': user.user_email}
return jsonify(data=result)
# 修改用户数据
@app.route('/user/<int:userId>', methods=['PATCH'])
def updateUser(userId):
user_name = request.form.get('user_name')
user_password = request.form.get('user_password')
user_nickname = request.form.get('user_nickname')
user_email = request.form.get('user_email')
try:
user = User.query.filter_by(user_id=userId).first()
if (user is None):
result = {'msg': '找不到要修改的记录'}
return jsonify(data=result)
else:
user.user_name = user_name
user.user_password = user_password
user.user_nickname = user_nickname
user.user_email = user_email
mydb.session.commit()
except:
mydb.session.rollback() # 回滚
mydb.session.flush() # 重置
userId = user.user_id
data = User.query.filter_by(user_id=userId).first()
result = {'user_id': user.user_id, 'user_name': user.user_name, 'user_password': user.user_password, 'user_nickname': user.user_nickname, 'user_email': user.user_email}
return jsonify(data=result)
# 删除用户数据
@app.route('/user/<int:userId>', methods=['DELETE'])
def deleteUser(userId):
User.query.filter_by(user_id=userId).delete()
mydb.session.commit()
return getUsers()
if __name__ == '__main__':
app.run()
三、返回数据的样式
{
"data": {
"user_email": "@126.com",
"user_id": 6,
"user_name": "xiaoli",
"user_nickname": "lili"
}
}
希望本文所述对大家基于flask框架的Python程序设计有所帮助。
来源:https://blog.csdn.net/Dawn__Z/article/details/102408902
标签:flask,操作MySQL数据库
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
怎样从旧版本SQL Server中重新存储数据
2009-01-15 13:23:00
Python自动采集微信联系人的实现示例
2023-09-08 07:28:03
![](https://img.aspxhome.com/file/2023/2/117442_0s.jpg)
如何用Python获取计算机名,ip地址,mac地址
2021-12-31 05:41:04
![](https://img.aspxhome.com/file/2023/1/120871_0s.png)
flask框架自定义过滤器示例【markdown文件读取和展示功能】
2023-03-07 19:22:27
sqlserver 动态创建临时表的语句分享
2024-01-17 23:57:45
javascript中直接写php代码的方法
2024-05-02 16:27:46
Python实现的栈(Stack)
2022-12-18 20:35:51
如何防止未经注册的用户绕过注册界面直接进入应用系统?
2009-11-22 19:22:00
GO语言利用K近邻算法实现小说鉴黄
2023-06-21 17:15:25
Go JSON编码与解码的实现
2024-02-18 23:53:56
关于python简单的爬虫操作(requests和etree)
2022-01-08 02:17:27
![](https://img.aspxhome.com/file/2023/8/69568_0s.jpg)
Python中模拟enum枚举类型的5种方法分享
2022-05-04 01:00:10
go语言K8S 的 informer机制浅析
2024-05-13 10:44:51
![](https://img.aspxhome.com/file/2023/1/125081_0s.jpg)
where条件顺序不同、性能不同示例探讨
2024-01-16 19:59:38
GO接收GET/POST参数及发送GET/POST请求的实例详解
2024-02-08 10:27:49
![](https://img.aspxhome.com/file/2023/1/114841_0s.png)
python迭代器实例简析
2023-07-26 07:40:29
如何编写CSS代码才能更有效率
2007-11-07 18:51:00
浅谈Python 参数与变量
2023-05-19 12:27:25
mysql中的Load data的使用方法
2024-01-13 22:37:56
![](https://img.aspxhome.com/file/2023/6/83376_0s.jpg)
Django读取Mysql数据并显示在前端的实例
2023-11-09 17:36:49
![](https://img.aspxhome.com/file/2023/8/64248_0s.jpg)