MySql使用存储过程开发

原创已于 2022-07-10 10:52:24 修改 · 1.7k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #python

于 2022-07-09 19:41:19 首次发布

PYTHON 同时被 2 个专栏收录

19 篇文章

订阅专栏

mysql

3 篇文章

订阅专栏

本文详细介绍了如何使用Navicat for MySQL连接和操作数据库，创建存储过程解决查询需求，以及在Django项目中应用存储过程实现数据展示。从基础连接到高级应用，适合初学者快速上手。

废话

过去初入职场时，使用Microsoft SqlServer进行开发工作，经常使用存储过程解决数据处理问题，很多变化频繁的业务逻辑，如果通过存储过程实现，在变更时，就会显得特别简单和方便。只要通过发布脚本就可实现。不得不说，当年，通过Microsoft SqlServer开发数据库及报表应用，是真的好用。

多年后的今天，发现如今的数据库应用是MySql的天下了，免费开源是真香，使用第三方工具进行MySql的开发还是不错的，不过个人感觉，开发过程不如Microsoft Sql Server官方开发环境使用方便，逻辑性更强，但是谁叫他收费呢，而且还不是一般的贵。

所谓废话少说，接下来直入主题吧。

一、Navicat for MySQL

当然，使用命令行进行开发，也是完全可以的，不过那简直是闲的蛋疼才那样干。学生时候，看到别人在“黑窗口”里敲代码，认为牛的不得了。现在再看到这样做，第一感觉是这人脑子有问题，放着简洁，美观，方便的工具不用，还用最原始的命令方式操作，您咋不用二进制写呢？所以我们选择Navicat for MySQL做为开发工具。

数据库安装过程就不说了，又不用天天安装，某度一下就解决的事。
Navicat for MySQL有绿色版本，不用安装。序列号，自己解决，下点工夫还是能找到的，某宝也就几块钱的事，我觉得与其搜索好几个小时，还不如花几块钱搞定，难道你的几个小时连几块钱都不值吗？

在安装目录下找到美丽的navicat.exe图标，双击之打开开发工具。
在这里插入图片描述

1.1 连接数据库

一眼就看到工具栏上有连接图标，这太醒目了，都无法形容了。
在这里插入图片描述
打开这个窗口时，发现其实很多内容都已经填好了。如果是安装在本机上的话，只需要随便填个名字（这个时候咱叫他“小甜甜"），密码（这个不能随便输）只有你自己知道。填好之后，“连接测试”一下。他会告诉你是否连接成功。
在这里插入图片描述
看到这个提示时，整个人都轻松了很多。

1.2 查询数据库

1.2.1 打开数据库连接

此时我们看到，”小甜甜“已经安静的在房间时等着咱了，还不快点过陪陪他，难道要等到她变成”牛夫人“？
在这里插入图片描述
双击”小甜甜“也行，右击”小甜甜“也行。她都会立马开心（打开连接）的。

1.2.2 最简单的查询数据

选择一个数据库，然后【查询->新建查询】就可以打开一个查询窗口。
在这里插入图片描述
在窗口里输入最简单的查询语言

select * from country;

”运行“，数据展现在了"结果1”中。
在这里插入图片描述
到现在mysql数据库，已经完美的安装，并可正常使用了。开心不！

二、创建存储过程

MySql这个“函数”让我困扰了很久，一度以为MySql不支持存储过程，而我好几年面对的都没有一个对MySql溜的知道这个问题，或都说他们故意不告诉我，因为在Microsoft SqlServer中，函数和存储过程是分开两个节点的，所以创建函数就是创建函数，创建存储过程就是存储过程。而MySql把函数和存储过程都归类在函数的子项。

在这里插入图片描述

2.1 新建存储过程

右键【函数->新建函数】，选择【过程】，【下一步】
配置参数，注意，这里的类型，选择varchar时，后面需要自己填上长度。否则在使用存储过程时会出错。当然，编写存储过程体时也可以再修改变量。
【完成】打开编辑窗口。
存储过程体就写在”BEGIN"和“END"内。在参数区可以对参数进行修改。

2.2 编写一个存储过程

2.2.1 需求

就在上文的样例数据库world中的country表中查询某洲，超过某个数据人口的国家。

2.2.2 编写存储过程体

常用的查询脚本
通常使用的查询脚本，例如： 查询亚洲，超过10亿人的国家

# 查询亚洲，超过10亿人的国家
SELECT *
FROM country
WHERE Continent = 'Asia' And Population > 1000000000;

存储过程
这时需要把洲（Continent）人口数（Population）作为参数传给存储过程。于是先修改参数为：

这里要注意，参数名称不可与表字段名称相同。

IN `vContinent` varchar(30),IN `lPopulation` long

编写脚本：

BEGIN
	#Routine body goes here...
	SELECT *
	FROM country
  WHERE `Continent` = vContinent And `Population` > lPopulation
;
END

保存存储过程（点【保存】或者Ctrl+s）：
填写一个实用的名字就可以了。
在这里插入图片描述
执行存储过程（点【运行】）
先输入参数，再【确定】

此时在【结果1】中可以看到查询到的结果。

三、应用储存过程

使用Python环境，Django框架查询并显示数据。

3.1 创建Django工程

3.1.1 创建Django工程，命名为【country】

这里创建过程忽略

3.1.2 创建子应用【world】

创建过程忽略

3.2 配置文件【settings.py】

打开配置文件【country->country->settings.py】
在这里插入图片描述

3.2.1 安装MySql驱动MySqlClient

在命令行直接执行：

pip install mysqlclient

这个大概率会报错。于是我们可以通过把对应文件下载到本地。下载地址就是pip源地址。在源目录里依据机器python版本来选择具体要下载的文件。
清华源的mysqlclient下载连接
下载到本地之后，在命令行导航到下载目录，然后执行pip命令。
下图是下载的mysqlclient 2.0.4版本，实用于python3.6版本的windows64位版本。
在这里插入图片描述
执行后，显示mysqlclient成功安装完成。
通过pip list命令列出所有安装的包，可以找到安装好的mysqlclient.

3.2.2 数据库连接信息

配置充许访问服务器的范围为任何主机

ALLOWED_HOSTS = ["*"]

添加app到配置文件列表

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'world.apps.WorldConfig',  # 添加创建的应用，也可以写成'world',
]

修改【DATABASE】默认数据库连接信息为：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'world',
        'HOST': 'localhost',
        'PORT': 3306,
        'USER': 'root',
        'PASSWORD': 'Aa@123456',
        'CHARSET': 'utf8'
    }
}

3.3 路由地址【urls.py】

打开主路由文件【country->country->urls.py】
在这里插入图片描述
添加如下代码，让使可能通过主路由路由到应用中。

path('world/', include("world.urls")),

添加后文件内容如下样式:

from django.contrib import admin
from django.urls import path, include

urlpatterns = [
    path('admin/', admin.site.urls),
    path('world/', include("world.urls")),
]

3.2.1 创建应用路由

在【country->world】目录下创建urls.py文件
可完成后面查询数据代码后再添加这里内容
引用了views.py文件中定义的视图类World。

from django.urls import path
from world.views import World

app_name = 'world'
urlpatterns = [
    path('', World.as_view(), name="world"),
    ]

3.3.2 添加应用路由到主路由

from django.contrib import admin
from django.urls import path, include

urlpatterns = [
    path('admin/', admin.site.urls),
    path('world/', include("world.urls")),
]

3.4 创建页面

在目录【country->template】下，创建html文件【index.html】
在这里插入图片描述

3.4.1 编写页面代码

这里需要在后端返回名为data的数据集（此代码在【country->world->views.py】文件中实现。

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>各大洲的国家</title>
    <style type="text/css">
        table{
            background-color: darkgray;
        }
        table thead{
            background-color: deepskyblue;
        }
        table tbody{
            background-color: skyblue;
        }
    </style>
</head>
<body>
<form action="" method="post">
{% csrf_token %}
<label for="continentInput">洲：</label><input name="continentInput" id="continentInput">
<label for="populationInput">人口：</label><input name="populationInput" id="populationInput">
<input type="submit" title="查询" value="查询">
{% if data == None %}
    <h3>{{ strtip }}</h3>
{% else %}
    <table>
        <thead>
        <th>名称</th>
        <th>洲</th>
        <th>地区</th>
        <th>国土面积</th>
        <th>人口</th>
        </thead>
        <tbody>
        {% for item in data %}
        <tr>
            <td>{{ item.1 }}</td>
            <td>{{ item.2 }}</td>
            <td>{{ item.3 }}</td>
            <td>{{ item.4 }}</td>
            <td>{{ item.6 }}</td>
        </tr>
        {% endfor %}
        </tbody>
    </table>
{% endif %}
</form>
</body>
</html>

3.4.2 查询数据库python实现

打开【country->world->views.py】文件，编辑如下代码：

from django.shortcuts import render

# Create your views here.
from django.views import View
from django.db import connection


class World(View):

    def get(self, request):

        strtip = '请通过洲名和人口数查询'
        data = None
        return render(request, template_name="world/index.html", context=locals())

    def post(self, request):
        continent_name = request.POST.get("continentInput", '')
        population = request.POST.get("populationInput", 0)
        with connection.cursor() as cursor:
            sql = "call sp_get_countrys(%(continent)s,%(population)s)"
            params = {
                'continent': continent_name,
                'population': population,
            }

            # callproc 无法返回数据表
            # cursor.callproc(sql, params=params)
            
            cursor.execute(sql, params)
            data = cursor.fetchall()
            print(data)
        return render(request, template_name="world/index.html", context=locals())

四、查看结果

4.1 启动程序

确认命令行当前位于根目录，即manage.py文件所在的目录。
输入启动命令

python manage.py runserver 0.0.0.0:8000

如下样式，表示工程成功启动，就可以通过浏览器打开页面了。


(country) D:\PyCharm 4.5.3\MyProject\01.查询MySql数据\country>python manage.py runserver 0.0.0.0:8000
Watching for file changes with StatReloader
Performing system checks...

System check identified no issues (0 silenced).
July 09, 2022 - 19:13:56
Django version 3.2.14, using settings 'country.settings'
Starting development server at http://0.0.0.0:8000/
Quit the server with CTRL-BREAK.

4.2 通过浏览器验证结果

在浏览器地址栏输入url：

http://127.0.0.1:8000/world/
或者
http://localhost:8000/world/

浏览器加载出这样的内容，这是从后台get到的内容。
输入参数（Asia, 100000000)点击【查询】：

在亚洲，人口数量大于100000000的国家列表就显示出来了。

五、总结

内容较多，包括了前端后端，数据库等，并非难点知识。但要学会这些，还是有不少内容的。比如sql语言，Html，python，还要熟悉django框架。

尽量详细的描述，希望能给已经做好心理准备想做码农的有所帮助。

一向的目的就是能让一点都不懂的读者，在不用消耗太多脑细胞的情况下，能够完整的理解知识。

附件

本文源码下载