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

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?

Recommended Posts

Dump SQLite3 data and migrate to MySQL
Compress python data and write to sqlite
[AWS] Migrate data from DynamoDB to Aurora MySQL
Python logging and dump to json
Migrate your own CMS data to WordPress
Scraping using lxml and saving to MySQL
[Kaggle] From data reading to preprocessing and encoding
Migrate Qiita stock and LGTM to "Hatena Bookmark"
[Python] How to read data from CIFAR-10 and CIFAR-100
Data retrieval from MacNote3 and migration to Write
Connect to mysql
Write a script to convert a MySQL dump to TSV
Data cleaning How to handle missing and outliers
Try to divide twitter data into SPAM and HAM
MySQL installation on Aws Linux 2 and test data preparation
Output a binary dump in binary and revert to a binary file
Easy to use SQLite3
Connect python to mysql
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
[Introduction to Data Scientists] Descriptive Statistics and Simple Regression Analysis ♬
How to add new data (lines and plots) using matplotlib
Build a Python environment and transfer data to the server
[Introduction to Data Scientists] Basics of Python ♬ Functions and classes
[Introduction to Python] Combine Nikkei 225 and NY Dow csv data
Get additional data to LDAP with python (Writer and Reader)