需求：备份开发人员提供过来的单条 update mysql 语句
使用 PyCharm 创建一个 Django 项目，添加一个 app01 Application。
修改 settings.py
# 允许所有的主机访问
ALLOWED_HOSTS = ['*']
# 注释
#'django.middleware.csrf.CsrfViewMiddleware',修改 urls.py
# 导入 views
from app01 import views
# 在 urlpatterns 下添加
urlpatterns = [
path('mysql_deal', views.mysql_deal),
]在 tmplates 目录下添加 index.html 页面
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
<title>获取备份SQL语句</title>
<!-- Bootstrap -->
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
<!-- HTML5 shim 和 Respond.js 是为了让 IE8 支持 HTML5 元素和媒体查询（media queries）功能 -->
<!-- 警告：通过 file:// 协议（就是直接将 html 页面拖拽到浏览器中）访问页面时 Respond.js 不起作用 -->
<!--[if lt IE 9]>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/html5shiv.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dest/respond.min.js"></script>
<![endif]-->
</head>
<body>
<form action="/mysql_deal" method="post">
<h2>请输入 update SQL</h2>
<textarea class="form-control" rows="3" name="sql_text"></textarea>
<input style="margin-top: 10px" class="btn btn-default" type="submit" value="提交">
</form>
<hr>
<h2>备份语句</h2>
<textarea class="form-control" rows="8">{{ result }}</textarea>
<p style="color: red; font-size: large; margin-top: 8px;">使用说明:</p>
<p>本程序只适用于钉钉提交过来的修改生产数据库，单条 update 语句。</p>
<!-- jQuery (Bootstrap 的所有 JavaScript 插件都依赖 jQuery，所以必须放在前边) -->
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.min.js"></script>
<!-- 加载 Bootstrap 的所有 JavaScript 插件。你也可以根据需要只加载单个插件。 -->
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js"></script>
</body>
</html>修改 views.py
from django.http import HttpResponse
from django.shortcuts import render
import pymysql
# Create your views here.
def mysql_deal(request):
res2 = ''
if request.method == 'POST':
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='[email protected]')
cursor = conn.cursor()
# 获取从web界面得到的 sql 语句
inp = request.POST.get("sql_text", None)
# print(inp, type(inp))
# 获取 select 语句, db, where 条件
s_sql = change_select(inp)
# print(s_sql)
# 获取sql查询的所有值
cursor.execute('%s' % s_sql[0])
r = cursor.fetchone()
val = []
for l in r:
val.append(l)
val_len = len(val)
# 获取表结构的字段
head = []
fields = cursor.description
for field in fields:
head.append(field[0])
# head.append(field)
# print(head)
# 拼接成备份的 UPDATE SQL
res = ''
for index in range(val_len):
tmp = "`" str(head[index]) "` = " "'" str(val[index]) "', "
res = tmp
res2 = "UPDATE " s_sql[1] " SET " res[:-2] " WHERE " s_sql[2]
res2 = res2.replace("'None'", 'NULL').replace("'b'\x00''", "b'0'").replace("'b'\x01''", "b'1'")
# print(res2)
cursor.close()
conn.close()
return render(request, "index.html", {"result": res2})
def change_select(sql):
sql_db = sql.split('SET')[0].replace('UPDATE', '').replace('update', '')
# print(sql_db)
sql_condition = sql.split('WHERE')[-1]
# print(sql_condition)
select_sql = "SELECT * FROM" sql_db "WHERE" sql_condition
change_sql = [select_sql, sql_db, sql_condition]
return change_sql创建 docker 镜像
# 在 /iba/mysql_deal 下存放项目相关的文件
vi iba_mysql.df
FROM python:3.5
MAINTAINER from klvchen
RUN pip install django && pip install PyMySQL && apt-get clean
# 创建镜像
docker build -f iba_mysql.df -t python_django:20.01.01 .启动项目
# 把项目上传到 /iba/mysql_deal 目录下，名字为 iba_mysql
# 创建 docker-compose.yml 文件
vi docker-compose.yml
version: '3.4'
services:
klvchen:
image: python_django:20.01.01
ports:
- 8002:8002
command:
- /bin/bash
- -c
- |
cd /iba_mysql
python manage.py runserver 0.0.0.0:8002
volumes:
- /iba/mysql_deal/iba_mysql:/iba_mysql
# 启动
docker-compose up -d访问 ip:8002/mysql_deal
总结：
功能基本实现，不足的地方：数据库类型不太准确，以后再修改。