I have setup my migrations in django (very small project); but I have a .sql file that has a bunch of data I need in the database.
What is the best way (or is it even possible) to have this .sql file executed after/during running migrations?
The sql file just contains an insert of the data like so:
INSERT INTO `mileages_mileages` (`id`, `miles`, `start_location`, `end_location`) VALUES
(NULL,3, 'Location 1', 'Location 2'),
I just need to execute that .sql file after running the initial migrations of my models.
Is that possible?
To use the database connection, call connection. cursor() to get a cursor object. Then, call cursor. execute(sql, [params]) to execute the SQL and cursor.
Run the makemigrations command. This should generate a migration with an AddField operation. Generate two empty migration files for the same app by running makemigrations myapp --empty twice. We've renamed the migration files to give them meaningful names in the examples below.
migrate : It creates table according to the schema defined in the migration file. sqlmigrate : It is used to show a raw SQL query of the applied migration. showmigrations : It lists out all the migrations and their status.
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
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$$
"""
))
]
# -*- 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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With