django model创建存储过程并执行

1、创建存储过程
创建一个空的migration，在里面写SQL。

python manage.py makemigrations [app name] --empty
python manage.py makemigrations store --empty

在migrations文件中operations列表中：有一堆sql语句不用管直接注释掉自己重写

from django.db import migrations
import pymysql  #导入pymysql包 也可以只用django默认的
from django.conf import settings


class Migration(migrations.Migration):

    dependencies = [
        ('admin', '000*****'),  #此处为自动创建不用理会
    ]

    drop_proc_sql = """
        drop procedure if exists insert_f;  ## 判定是否已经存在存储过程，存在则删除
    """
    create_proc_sql = """
        ## 创建存储过程，传入JSON参数，根据前4个值查询是否已存在，不存在则以6个参数值向表新增数据
        create procedure insert_f(params LONGTEXT CHARACTER SET utf8)
        ## 存储过程的开始
        begin
   			#根据实际情况编写存储过程
        end;
    """
    db_conn = pymysql.connect(
        host=settings.DATABASES['default']['HOST'],
        port=int(settings.DATABASES['default']['PORT']),
        user=settings.DATABASES['default']['USER'],
        password=settings.DATABASES['default']['PASSWORD'],
        database=settings.DATABASES['default']['NAME'],
        charset='utf8'
    )
    db_conn.autocommit(1)
    cursor = db_conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute(drop_proc_sql)
    cursor.execute(create_proc_sql)
    cursor.close()
    # 关闭数据库连接
    db_conn.close()

    # operations = [
    #     migrations.RunSQL(create_proc_sql)
    # ]

2、调用存储过程

import pymysql
from django.conf import settings
db = pymysql.connect(
        host=settings.DATABASES['default']['HOST'],
        port=int(settings.DATABASES['default']['PORT']),
        user=settings.DATABASES['default']['USER'],
        password=settings.DATABASES['default']['PASSWORD'],
        database=settings.DATABASES['default']['NAME'],
        charset='utf8'
    )
cursor.callproc("insert_f", "param_0","param_1","param_2") # 参数为存储过程名称和存储过程接收的参数
db.commit()
# 获取数据
data = cursor.fetchall()
cursor.execute("select @_proc_name_0,@_proc_name_1,@_proc_name_2")# 查询调用存储过程后返回的参数
param_value = cursor.fetchone()
# 关闭数据库连接
db.close()