练习：Django与MySQL交互

hevenue

2020-01-19

需求：备份开发人员提供过来的单条 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//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//dist/html5shiv.min.js"></script>
      <script src="https://cdn.jsdelivr.net/npm//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//dist/jquery.min.js"></script>
     <!-- 加载 Bootstrap 的所有 JavaScript 插件。你也可以根据需要只加载单个插件。 -->
     <script src="https://cdn.jsdelivr.net/npm//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='')
        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

练习：Django与MySQL交互