MySQL2SQLite.py

#!/usr/bin/env python
#====================================
# MySQL2SQLite.py
#====================================
#            IMPORTS
import sys
#====================================

#====================================
#     BEGIN CLASS MySQL2SQLite
#====================================
class MySQL2SQLite:
    def __init__(self):
        self.InputFile = ""
        self.OutputFile = ""
        self.WriteFile = 0
        self.DebugMode = 0
        self.SchemaOnly = 0
        self.DirectMode = False

    def SetUp(self, In, Out = '', Debug = False, Schema = 0):
        self.InputFile = In
        if Out == '':
            self.writeFile = 0
        else:
            self.WriteFile = 1
            self.OutputFile = Out
        if Debug == True:
            self.DebugMode = 1
        if Schema == 1:
            self.SchemaOnly = 1

    def DoWork(self):
        f = open(self.InputFile)
        print "Starting Process"
        cntr = 0
        insertmode = 0
        CreateTableMode = 0
        InsertStart = "INSERT INTO "
        AI = "auto_increment"
        PK = "PRIMARY KEY "
        IPK = " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL"
        CT = "CREATE TABLE "
        # Begin
        if self.WriteFile == 1:
            OutFile = open(self.OutputFile,'w')
        while 1:
            line = f.readline()
            cntr += 1
            if not line:
                break
            # Ignore blank lines, lines that start with "--" or comments (/*!)
            if line.startswith("--"): #Comments
                pass
            elif len(line) == 1: # Blank Lines
                pass
            elif line.startswith("/*!"): # Comments
                pass
            elif line.startswith("USE"):
                #Ignore USE lines
                pass
            elif line.startswith("CREATE DATABASE "):
                pass
            elif line.startswith(CT):
                CreateTableMode = 1
                l1 = len(line)
                line = line[:l1-1]
                if self.DebugMode == 1:
                    print "Starting Create Table"
                    print line
                if self.WriteFile == 1:
                    OutFile.write(line)
            elif CreateTableMode == 1:
                # Parse the line...
                if self.DebugMode == 1:
                    print "Line to process - {0}".format(line)
                p1 = line.find(AI)
                if line.startswith(") "):
                    CreateTableMode = 0
                    if self.DebugMode == 1:
                        print "Finished Table Create"
                    newline = ");\n"
                    if self.WriteFile == 1:
                        OutFile.write(newline)
                        if self.DebugMode == 1:
                            print "Writing Line {0}".format(newline)
                elif p1 != -1:
                    # Line is primary key line
                    l = line.strip()
                    fnpos = l.find(" int(")
                    if fnpos != -1:
                        fn = l[:fnpos]
                    newline = fn + IPK #+ ",\n"
                    if self.WriteFile == 1:
                        OutFile.write(newline)
                        if self.DebugMode == 1:
                            print "Writing Line {0}".format(newline)
                elif line.strip().startswith(PK):
                    pass
                elif line.find(" unsigned ") != -1:
                    line = line.replace(" unsigned "," ")
                    line = line.strip()
                    l1 = len(line)
                    line = line[:l1-1]
                    if self.WriteFile == 1:
                        OutFile.write("," + line)
                        if self.DebugMode == 1:
                            print "Writing Line {0}".format(line)
                else:
                    l1 = len(line)
                    line = line.strip()
                    line = line[:l1-4]
                    if self.DebugMode == 1:
                        print "," + line
                    if self.WriteFile == 1:
                        OutFile.write("," + line)
            elif line.startswith(InsertStart):
                if insertmode == 0:
                    insertmode = 1
                    # Get tablename and field list here
                    istatement = line
                    # Strip CR/LF from istatement line
                    l = len(istatement)
                    istatement = istatement[:l-2]
            elif self.SchemaOnly == 0:
                if insertmode == 1:
                    posx = line.find("');")
                    pos1 = line.find("'),")
                    l1 = line[:pos1]
                    line = line.replace("\\'","''")
                    if posx != -1:
                        l1 = line[:posx+3]
                        insertmode = 0
                        if self.DebugMode == 1:
                            print istatement + l1
                            print "------------------------------"
                        if self.WriteFile == 1:
                            OutFile.write(istatement + l1+"\n")
                    elif pos1 != -1:
                        l1 = line[:pos1+2]
                        if self.DebugMode == 1:
                            print istatement + l1 + ";"
                        if self.WriteFile == 1:
                            OutFile.write(istatement + l1 + ";\n")
                    else:
                        if self.DebugMode == 1:
                            print "Testing line {0}".format(line)
                        pos1 = line.find("),")
                        posx = line.find(");")
                        if self.DebugMode == 1:
                            print "pos1 = {0}, posx = {1}".format(pos1,posx)
                        if pos1 != -1:
                            l1 = line[:pos1+1]
                            if self.DebugMode == 1:
                                print istatement + l1 + ";"
                            if self.WriteFile == 1:
                                OutFile.write(istatement + l1 + ";\n")
                        else:
                            insertmode = 0
                            l1 = line[:posx+1]
                            if self.DebugMode == 1:
                                print istatement + l1 + ";"
                            if self.WriteFile == 1:
                                OutFile.write(istatement + l1 + ";\n")
        f.close()
        if self.WriteFile == 1:
            OutFile.close()


def error(message):
    print >> sys.stderr, str(message)

def DoIt():
    #=======================================
    #            Setup Variables
    #=======================================
    SourceFile = ''
    OutputFile = ''
    Debug = False
    Help = False
    SchemaOnly = False
    #=======================================
    if len(sys.argv) == 1:
        usage()
    else:
        for a in sys.argv:
            print a
            if a.startswith("Infile="):
                pos = a.find("=")
                SourceFile = a[pos+1:]
            elif a.startswith("Outfile="):
                pos = a.find("=")
                OutputFile = a[pos+1:]
            elif a == 'Debug':
                Debug = True
            elif a == 'SchemaOnly':
                SchemaOnly = True
            elif a == '-Help' or a == '-H' or a == '-?':
                Help = True
        if Help == True:
            usage()
        r = MySQL2SQLite()
        r.SetUp(SourceFile,OutputFile,Debug,SchemaOnly)
        r.DoWork()

def usage():
    message = (
       '=======================================================================\n'
        'MySQL2SQLite - A database converter\n'
        'Author: Greg Walters\n'
        'USAGE:\n'
        'MySQL2SQLite Infile=filename [Outfile=filename] [SchemaOnly] [Debug] [-H-Help-?\n'
        '   where\n'
        '         Infile is the MySQL dump file\n'
        '         Outfile (optional) is the output filename\n'
        '            (if Outfile is omitted, assumed direct to SQLite\n'
        '         SchemaOnly (optional) Create Tables, DO NOT IMPORT DATA\n'
        '         Debug (optional) - Turn on debugging messages\n'
        '         -H or -Help or -? - Show this message\n'
        'Copyright (C) 2011 by G.D. Walters\n'
        '=======================================================================\n'
        )
    error(message)
    sys.exit(1)

if __name__ == "__main__":
    DoIt()