sqlite3-to-mysql.py

Daniel GraƱa, 2012-04-17 19:55

Download (2.24 KB)

 
1
#! /usr/bin/env python
2
import re, fileinput
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("AUTOINCREMENT", "AUTO_INCREMENT")
18
    line = line.replace("DEFAULT 't'", "DEFAULT '1'")
19
    line = line.replace("DEFAULT 'f'", "DEFAULT '0'")
20
    line = line.replace(",'t'", ",'1'")
21
    line = line.replace(",'f'", ",'0'")
22
    return line
23

    
24

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

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

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

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

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

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

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

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

    
72
def main():
73
    op = OptionParser()
74
    op.add_option('-d', '--database')
75
    op.add_option('-u', '--username')
76
    op.add_option('-p', '--password')
77
    opts, args = op.parse_args()
78

    
79
    lines = (l for l in fileinput.input(args))
80
    for line in _process(opts, lines):
81
        print line,
82

    
83

    
84
if __name__ == "__main__":
85
    main()