[PYTHON] Dump SQLite3 data and migrate to MySQL

Introduction

I was dealing with Django as a personal product, and I was working with SQLite3 with the default, but as usual, I thought that I should move to MySQL, and when I operated using the following command, an error often occurred. I couldn't deal with it without knowing it, so it's like a memo

$ python manage.py dumpdata --indent 4 > dump.json
#Change the direction of Database
$ python manage.py loaddata dump.json

environment

• Django 1.10.5
• mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64)
• Python 3.6.x

Dump data from SQLite3

$ sqlite3 db.sqlite3 .dump > dump.sql

Or

$ sqlite3 db.sqlite3
sqlite> .output ./dump.sql
sqlite> .dump

You should now have a file like dump.sql in the specified directory.

Convert SQL data for SQLite3 so that it can be used with MySQL

As it is, it cannot be used in MySQL, so it needs to be converted.

http://www.redmine.org/attachments/download/6239/sqlite3-to-mysql.py

Redmine prepared a nice one! Thanks! !! I thought, but the script itself is only compatible with Python2.

However, it seems that print does not support Python3, so I rewrote it a little.

sqlite3-to-mysql.py

#! /usr/bin/env python

import sys

def main():
    print("SET sql_mode='NO_BACKSLASH_ESCAPES';")
    lines = sys.stdin.read().splitlines()
    for line in lines:
        processLine(line)

def processLine(line):
    if (
        line.startswith("PRAGMA") or 
        line.startswith("BEGIN TRANSACTION;") or
        line.startswith("COMMIT;") or
        line.startswith("DELETE FROM sqlite_sequence;") or
        line.startswith("INSERT INTO \"sqlite_sequence\"")
       ):
        return
    line = line.replace("AUTOINCREMENT", "AUTO_INCREMENT")
    line = line.replace("DEFAULT 't'", "DEFAULT '1'")
    line = line.replace("DEFAULT 'f'", "DEFAULT '0'")
    line = line.replace(",'t'", ",'1'")
    line = line.replace(",'f'", ",'0'")
    in_string = False
    newLine = ''
    for c in line:
        if not in_string:
            if c == "'":
                in_string = True
            elif c == '"':
                newLine = newLine + '`'
                continue
        elif c == "'":
            in_string = False
        newLine = newLine + c
    print(newLine)

if __name__ == "__main__":
    main()

Only the grammar of print was changed. If you know the difference between Python2 and 3, there is no problem

Convert

$ cat dump.sql | python sqlite3-to-mysql.py > mysql.sql

If you make it look like this, you should have a guy called mysql.sql.

Put in MySQL

$ mysql -u USERNAME -p
mysql> CREATE DATABASE hoge CHARACTER SET utf8mb4;
mysql> USE hoge;
mysql> source /path/to/mysql.sql;

Finally

As a caveat, special characters (emoji, etc.) do not seem to work well after conversion, so if you don't have to deal with such things, why don't you try it?