mysql与django交互_练习：Django与MySQL交互

需求：备份开发人员提供过来的单条 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 页面

获取备份SQL语句

请输入 update SQL

---

备份语句

{{ result }}

使用说明:

本程序只适用于钉钉提交过来的修改生产数据库，单条 update 语句。

修改 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='root@123')

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

总结：

功能基本实现，不足的地方：数据库类型不太准确，以后再修改。