Converting a SQLite3 database to MySQL

I have need to migrate some sqlite3 databases to mysql for a couple django-cms projects at work. Typically in the past
I’ve used fixtures in Django to get this done, but I usually have to align the planets and concoct the elixir of
life to get the fixtures to migrate properly. It usually has to do with foreign key errors something. This is something
that should just be easy, but in my experience with Django, it never is.

Theres’s a couple posts on stackoverflow with various scripts to convert content from sqlite to mysql, but none of
them lined up the planets just right.

Then I happened on this page where there’s a python script by this guy that is not based on the others. And it just worked.
The script looks like:

convert.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#! /usr/bin/env python

import sys

def main():
print "SET sql_mode='NO_BACKSLASH_ESCAPES';"
for line in sys.stdin:
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()

Usage

1
$ sqlite3 mydb.sqlite .dump | python convert.py > out.sql

Thank you Behrang Noroozinia from internet land, you solved a long-standing problem of mine.