[Fixed]-Running a .sql file after migrations in django

migrations.RunSQL()

Does not accept a file as input. Only raw SQL. To solve this you need to use either:

migrations.RunSQL(
    """
    INSERT INTO 'mileages_mileages' ('id', 'miles', 'start_location', 'end_location') 
    VALUES
       (NULL,3, 'Location 1', 'Location 2');
    """)

or

def load_data_from_sql(apps, schema_editor):
   file_path = os.path.join(os.path.dirname(__file__), 'file_name.sql')
   sql_statement = open(file_path).read()
   with connection.cursor() as c:
       c.execute(sql_statement)

class Migration(migrations.Migration):
    dependencies = [
        ('..', '...'),
    ]

    operations = [
        migrations.RunPython(load_data_from_sql),
    ]

Here is the full script(not perfect, but as is) for converting MySQL statements with DELIMITER to SQL execution.

Information about multiply delimiters in SQL statement https://stackoverflow.com/a/52292690/9521312

Add script execution in migration file

There are both examples of using script: run sql file or run raw MySQL statement

from anywhere import migrate_run_sql

operations = [
              migrations.RunPython(migrate_run_sql.run_sql_file('contract_triggers.sql')),
              migrations.RunPython(migrate_run_sql.run_sql(
                                                           """
                                                           DELIMITER $$
                                                           CREATE TRIGGER trigger_name BEFORE INSERT ON table
                                                           FOR EACH ROW
                                                           BEGIN
                                                             IF NEW.number <> 'anynumber' AND NEW.number <> 'anynumber'
                                                               THEN
                                                                 SET NEW.number = 'anynumber';
                                                             END IF;
                                                           END$$
                                                           """
                                                           ))
             ]

Script file

# -*- coding: utf-8 -*-
from django.db import connection
import re
from StringIO import StringIO
from django.conf import settings
import os

# this function get raw MySQL statement
def run_sql(sql): 
    def load_data_from_sql(app, schema_editor):
        f = StringIO(sql)
        return _runsql(f)

    return load_data_from_sql

# this function get sql file
def run_sql_file(filename):
    def load_data_from_sql(app, schema_editor):
        filepath = os.path.join(settings.PROJECT_PATH, '../deploy/mysql/', filename)
        with open(filepath, 'rb') as f:
            return _runsql(f)

    return load_data_from_sql

# in this function content splits and checks line by line
def _runsql(f):
    with connection.cursor() as c:
        file_data = f.readlines()
        statement = ''
        delimiter = ';\n'
        for line in file_data:
            if re.findall('DELIMITER', line): # found delimiter
                if re.findall('^\s*DELIMITER\s+(\S+)\s*$', line):
                    delimiter = re.findall('^\s*DELIMITER\s+(\S+)\s*$', line)[0] + '\n'
                    continue
                else:
                    raise SyntaxError('Your usage of DELIMITER is not correct, go and fix it!')
            statement += line # add lines while not met lines with current delimiter
            if line.endswith(delimiter):
                if delimiter != ';\n':
                    statement = statement.replace(';', '; --').replace(delimiter, ';') # found delimiter, add dash symbols (or any symbols you want) for converting MySQL statements with multiply delimiters in SQL statement
                c.execute(statement) # execute current statement
                statement = '' # begin collect next statement

Hope it will help!

When using migrations.RunSQL, if you get the following error:

sqlparse is required if you don’t split your SQL

You just need to install sqlparse to solve the problem:

pip install sqlparse

You can execute a raw SQL

operations = [
    migrations.RunSQL('sql statement here')
]

Inspiration from paveldroo’s solution by creating a wrapper around migrations.RunSQL to load the sql file:

# core/migrations.py
from pathlib import Path
from typing import AnyStr

from django.db import migrations


def _read_sql_file(path: Path) -> AnyStr:
    with open(path, "r") as sql_file:
        return sql_file.read()


class RunSQLFile(migrations.RunSQL):
    def __init__(
        self,
        sql_file_path: Path,
        reverse_sql=None,
        state_operations=None,
        hints=None,
        elidable=False,
    ):
        sql = _read_sql_file(sql_file_path)
        super().__init__(
            sql=sql,
            reverse_sql=reverse_sql,
            state_operations=state_operations,
            hints=hints,
            elidable=elidable,
        )

# app/models.py
class MyModelView(models.Model):
    class Meta:
        managed = False
        db_table = "my_model_view"

    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=1024)
    updated = models.DateTimeField()

-- app/sql/my_model_view.sql
DROP VIEW IF EXISTS my_model_view;

CREATE VIEW my_model_view AS (
  -- SELECT HERE
)

# app/migrations/0002_create_my_model_view.py
# Generated by Django 3.2 on 2023-03-06 22:11
from pathlib import Path

from django.db import migrations, models

from core.migrations import RunSQLFile


class Migration(migrations.Migration):

    dependencies = [
        ("0001_initial"),
    ]

    operations = [
        migrations.CreateModel(
            name="MyModelView",
            fields=[
                ("id", models.IntegerField(primary_key=True, serialize=False)),
                ("name", models.CharField(max_length=1024)),
                ("updated", models.DateTimeField()),
            ],
            options={
                "db_table": "my_model_view",
                "managed": False,
            },
        ),
        RunSQLFile(
            sql_file_path=Path(
                "./app/sql/my_model_view.sql"
            ),
            reverse_sql="DROP VIEW IF EXISTS my_model_view",
        ),
    ]