sqlite3-to-mysql.py

Peter sørensen, 2012-09-28 15:11

Download (3.38 KB)

 
1
#! /usr/bin/env python
2
import re, fileinput, tempfile
3
from optparse import OptionParser
4

    
5

    
6
IGNOREDPREFIXES = [
7
    'PRAGMA',
8
    'BEGIN TRANSACTION;',
9
    'COMMIT;',
10
    'DELETE FROM sqlite_sequence;',
11
    'INSERT INTO "sqlite_sequence"',
12
]
13

    
14
def _replace(line):
15
    if any(line.startswith(prefix) for prefix in IGNOREDPREFIXES):
16
        return
17
    line = line.replace("INTEGER PRIMARY KEY", "INTEGER AUTO_INCREMENT PRIMARY KEY")
18
    line = line.replace("AUTOINCREMENT", "AUTO_INCREMENT")
19
    line = line.replace("DEFAULT 't'", "DEFAULT '1'")
20
    line = line.replace("DEFAULT 'f'", "DEFAULT '0'")
21
    line = line.replace(",'t'", ",'1'")
22
    line = line.replace(",'f'", ",'0'")
23
    return line
24

    
25

    
26
def _backticks(line, in_string):
27
    """Replace double quotes by backticks outside (multiline) strings
28

29
    >>> _backticks('''INSERT INTO "table" VALUES ('"string"');''', False)
30
    ('INSERT INTO `table` VALUES (\\'"string"\\');', False)
31

32
    >>> _backticks('''INSERT INTO "table" VALUES ('"Heading''', False)
33
    ('INSERT INTO `table` VALUES (\\'"Heading', True)
34

35
    >>> _backticks('''* "text":http://link.com''', True)
36
    ('* "text":http://link.com', True)
37

38
    >>> _backticks(" ');", True)
39
    (" ');", False)
40

41
    """
42
    new = ''
43
    for c in line:
44
        if not in_string:
45
            if c == "'":
46
                in_string = True
47
            elif c == '"':
48
                new = new + '`'
49
                continue
50
        elif c == "'":
51
            in_string = False
52
        new = new + c
53
    return new, in_string
54

    
55
def _process(opts, lines):
56
    if opts.database:
57
        yield '''\
58
drop database {d};
59
create database {d} character set utf8;
60
grant all on {d}.* to {u}@'%' identified by '{p}';
61
use {d};\n'''.format(d=opts.database, u=opts.username, p=opts.password)
62
    yield "SET sql_mode='NO_BACKSLASH_ESCAPES';\n"
63

    
64
    in_string = False
65
    for line in lines:
66
        if not in_string:
67
            line = _replace(line)
68
            if line is None:
69
                continue
70
        line, in_string = _backticks(line, in_string)
71
        yield line
72

    
73
def _removeNewline(line, in_string):
74
    new = ''
75
    for c in line:
76
        if not in_string:
77
            if c == "'":
78
                in_string = True
79
        elif c == "'":
80
            in_string = False
81
        elif in_string:
82
            if c == "\n":
83
                 new = new + 'Newline333'
84
                 continue
85
            if c == "\r":
86
                 new = new + 'carriagereturn333'
87
                 continue
88
        new = new + c
89
    return new, in_string
90
        
91
def _replaceNewline(lines):
92
    for line in lines:
93
           line = line.replace("Newline333", "\n")
94
           line = line.replace("carriagereturn333", "\r")
95
           yield line
96

    
97
def _Newline(lines):
98
    in_string = False
99
    for line in lines:
100
        if line is None:
101
           continue
102
        line, in_string = _removeNewline(line, in_string)
103
        yield line
104
        
105
def main():
106
    op = OptionParser()
107
    op.add_option('-d', '--database')
108
    op.add_option('-u', '--username')
109
    op.add_option('-p', '--password')
110
    opts, args = op.parse_args()
111
    lines = (l for l in fileinput.input(args))
112
    lines = (l for l in _Newline(lines))
113
    f = tempfile.TemporaryFile()
114
    for line in lines:
115
        f.write(line)
116
    f.seek(0)
117
    lines = (l for l in f.readlines())
118
    f.close()
119
    lines = (l for l in _process(opts, lines))
120
    for line in _replaceNewline(lines):
121
       print line,
122

    
123
if __name__ == "__main__":
124
    main()