Skip to content

Instantly share code, notes, and snippets.

@rednebmas
Created May 20, 2018 00:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save rednebmas/df2535924bbbf7f32f3dd6040e370d15 to your computer and use it in GitHub Desktop.
Save rednebmas/df2535924bbbf7f32f3dd6040e370d15 to your computer and use it in GitHub Desktop.
Convert sqlite3 dump to mysql importable statements
import re, fileinput
print 'SET FOREIGN_KEY_CHECKS=0;'
def main():
for line in fileinput.input():
process = False
for nope in ('BEGIN TRANSACTION','COMMIT',
'sqlite_sequence','CREATE UNIQUE INDEX'):
if nope in line: break
else:
process = True
if not process: continue
m = re.search('CREATE TABLE "([a-z_]*)"(.*)', line)
if m:
name, sub = m.groups()
sub = sub.replace('"','`')
line = '''DROP TABLE IF EXISTS %(name)s;
CREATE TABLE IF NOT EXISTS %(name)s%(sub)s
'''
line = line % dict(name=name, sub=sub)
else:
m = re.search('INSERT INTO "([a-z_]*)"(.*)', line)
if m:
line = 'INSERT INTO %s%s\n' % m.groups()
line = line.replace('"', r'\"')
line = line.replace('"', "'")
line = line.strip()
line = line.replace(' +00:00', "")
line = line[0:len(line)-1].replace(';', '\;') + line[-1]
line = re.sub(r'INSERT INTO "([^"]+)"', r"INSERT INTO \1", line)
line = re.sub(r"([^'])'t'(.)", "\\1THIS_IS_TRUE\\2", line)
line = line.replace('THIS_IS_TRUE', '1')
line = re.sub(r"([^'])'f'(.)", "\\1THIS_IS_FALSE\\2", line)
line = line.replace('THIS_IS_FALSE', '0')
line = line.replace('AUTOINCREMENT', 'AUTO_INCREMENT')
if re.search('^CREATE INDEX', line):
line = line.replace('"','`')
print line,
print ""
main()
print 'SET FOREIGN_KEY_CHECKS=1;'
@xy2z
Copy link

xy2z commented Mar 14, 2019

For python3 just add parentheses after print()

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment