|
1
|
|
|
""" |
|
2
|
|
|
ETLT-MySQL |
|
3
|
|
|
|
|
4
|
|
|
Copyright 2016 Set Based IT Consultancy |
|
5
|
|
|
|
|
6
|
|
|
Licence MIT |
|
7
|
|
|
""" |
|
8
|
1 |
|
import datetime |
|
|
|
|
|
|
9
|
1 |
|
import os |
|
10
|
|
|
|
|
11
|
1 |
|
import pytz |
|
|
|
|
|
|
12
|
1 |
|
from etlt.writer.SqlLoaderWriter import SqlLoaderWriter |
|
|
|
|
|
|
13
|
|
|
|
|
14
|
|
|
|
|
15
|
1 |
|
class MySqlLoaderWriter(SqlLoaderWriter): |
|
|
|
|
|
|
16
|
|
|
""" |
|
17
|
|
|
Writer for storing rows in CSV format optimized for MariaDB and MySQL instances and 'load data local infile' |
|
|
|
|
|
|
18
|
|
|
statement. |
|
19
|
|
|
""" |
|
20
|
|
|
|
|
21
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
|
|
|
|
|
|
22
|
1 |
|
@staticmethod |
|
23
|
|
|
def write_date(value, file): |
|
24
|
|
|
""" |
|
25
|
|
|
Writes a date as a field to a CSV file. |
|
26
|
|
|
|
|
27
|
|
|
:param datetime.date value: The date. |
|
28
|
|
|
:param T file: The file. |
|
29
|
|
|
""" |
|
30
|
1 |
|
file.write(value.isoformat()) |
|
31
|
|
|
|
|
32
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
|
|
|
|
|
|
33
|
1 |
|
@staticmethod |
|
34
|
|
|
def write_datetime(value, file): |
|
35
|
|
|
""" |
|
36
|
|
|
Writes a datetime as a field to a CSV file. |
|
37
|
|
|
|
|
38
|
|
|
:param datetime.datetime value: The date. |
|
39
|
|
|
:param T file: The file. |
|
40
|
|
|
""" |
|
41
|
1 |
|
zone = value.tzinfo |
|
42
|
1 |
|
if zone and zone != pytz.utc: |
|
43
|
|
|
raise ValueError('Only native and UTC timezone supported') |
|
44
|
1 |
|
file.write(value.isoformat()) |
|
45
|
|
|
|
|
46
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
|
|
|
|
|
|
47
|
1 |
|
@staticmethod |
|
48
|
|
|
def write_int(value, file): |
|
49
|
|
|
""" |
|
50
|
|
|
Writes an integer as a field to a CSV file. |
|
51
|
|
|
|
|
52
|
|
|
:param int value: The integer. |
|
53
|
|
|
:param T file: The file. |
|
54
|
|
|
""" |
|
55
|
1 |
|
file.write(str(value)) |
|
56
|
|
|
|
|
57
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
|
|
|
|
|
|
58
|
1 |
|
@staticmethod |
|
59
|
|
|
def write_none(_, file): |
|
60
|
|
|
""" |
|
61
|
|
|
Writes None as a field to a CSV file. |
|
62
|
|
|
|
|
63
|
|
|
:param None _: The None object. |
|
64
|
|
|
:param T file: The file. |
|
65
|
|
|
""" |
|
66
|
1 |
|
file.write('\\N') |
|
67
|
|
|
|
|
68
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
|
|
|
|
|
|
69
|
1 |
|
@staticmethod |
|
70
|
|
|
def write_string(value, file): |
|
71
|
|
|
""" |
|
72
|
|
|
Writes a string as a field to a CSV file. |
|
73
|
|
|
|
|
74
|
|
|
:param str value: The string. |
|
75
|
|
|
:param T file: The file. |
|
76
|
|
|
""" |
|
77
|
1 |
|
if value == '': |
|
78
|
1 |
|
file.write('\\N') |
|
79
|
|
|
else: |
|
80
|
1 |
|
file.write("'") |
|
81
|
1 |
|
file.write(value.replace('\\', '\\\\').replace("'", "\\'")) |
|
82
|
1 |
|
file.write("'") |
|
83
|
|
|
|
|
84
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
|
|
|
|
|
|
85
|
1 |
|
def writerow(self, row): |
|
86
|
|
|
""" |
|
87
|
|
|
Writes a row to the destination file. |
|
88
|
|
|
|
|
89
|
|
|
:param dict[str,str|None] row: The row. |
|
90
|
|
|
""" |
|
91
|
1 |
|
for field in self._fields: |
|
|
|
|
|
|
92
|
1 |
|
self._write_field(row[field]) |
|
|
|
|
|
|
93
|
1 |
|
self._file.write(',') |
|
|
|
|
|
|
94
|
|
|
|
|
95
|
1 |
|
self._file.write(os.linesep) |
|
|
|
|
|
|
96
|
|
|
|
|
97
|
|
|
# ------------------------------------------------------------------------------------------------------------------ |
|
|
|
|
|
|
98
|
1 |
|
def get_bulk_load_sql(self, table_name): |
|
99
|
|
|
""" |
|
100
|
|
|
Returns a SQL statement for bulk loading the data into a table. |
|
101
|
|
|
|
|
102
|
|
|
:param str table_name: The name of the table. |
|
103
|
|
|
|
|
104
|
|
|
:rtype: str |
|
105
|
|
|
""" |
|
106
|
|
|
sql = "load data local infile '{FILENAME}'" |
|
107
|
|
|
sql += ' into table `{TABLE_NAME}`' |
|
108
|
|
|
sql += ' character set {ENCODING}' |
|
109
|
|
|
sql += " fields terminated by ','" |
|
110
|
|
|
sql += " optionally enclosed by '\\\''" |
|
111
|
|
|
sql += " escaped by '\\\\'" |
|
112
|
|
|
sql += " lines terminated by '\\n'" |
|
113
|
|
|
|
|
114
|
|
|
return sql.format(FILENAME=self._filename, # @todo Quoting of filename |
|
|
|
|
|
|
115
|
|
|
ENCODING=self._encoding, |
|
|
|
|
|
|
116
|
|
|
TABLE_NAME=table_name) |
|
117
|
|
|
|
|
118
|
|
|
|
|
119
|
|
|
# ---------------------------------------------------------------------------------------------------------------------- |
|
|
|
|
|
|
120
|
1 |
|
MySqlLoaderWriter.register_handler("<class 'int'>", MySqlLoaderWriter.write_int) |
|
|
|
|
|
|
121
|
1 |
|
MySqlLoaderWriter.register_handler("<class 'str'>", MySqlLoaderWriter.write_string) |
|
|
|
|
|
|
122
|
1 |
|
MySqlLoaderWriter.register_handler("<class 'NoneType'>", MySqlLoaderWriter.write_none) |
|
|
|
|
|
|
123
|
1 |
|
MySqlLoaderWriter.register_handler("<class 'datetime.date'>", MySqlLoaderWriter.write_date) |
|
|
|
|
|
|
124
|
|
|
MySqlLoaderWriter.register_handler("<class 'datetime.datetime'>", MySqlLoaderWriter.write_datetime) |
|
|
|
|
|
|
125
|
|
|
|
PyETLT /
etlt-mysql
Loading history...