Coverage for fundamentals/mysql/sqlite2mysql.py : 0%
Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1#!/usr/local/bin/python
2# encoding: utf-8
3"""
4Take a sqlite database file and copy the tables within it to a MySQL database
6Usage:
7 sqlite2mysql -s <pathToSettingsFile> <pathToSqliteDB> [<tablePrefix>]
9Options:
11 pathToSqliteDB path to the sqlite database file
12 tablePrefix a string to prefix the table names when copying to mysql database
13 pathToSettingsFile path to a settings file with logging and database information (yaml file)
15 -h, --help show this help message
16 -v, --version show version
17 -s, --settings the settings file
18"""
19from builtins import object
20import sys
21import os
22import sqlite3 as lite
23os.environ['TERM'] = 'vt100'
24from fundamentals import tools
25from fundamentals.mysql import writequery
27from datetime import datetime, date, time
29def main(arguments=None):
30 """
31 The main function used when ``yaml_to_database.py`` when installed as a cl tool
32 """
34 # setup the command-line util settings
35 su = tools(
36 arguments=arguments,
37 docString=__doc__,
38 logLevel="WARNING",
39 options_first=False,
40 projectName=False
41 )
42 arguments, settings, log, dbConn = su.setup()
44 # unpack remaining cl arguments using `exec` to setup the variable names
45 # automatically
46 for arg, val in list(arguments.items()):
47 if arg[0] == "-":
48 varname = arg.replace("-", "") + "Flag"
49 else:
50 varname = arg.replace("<", "").replace(">", "")
51 if isinstance(val, str):
52 exec(varname + " = '%s'" % (val,))
53 else:
54 exec(varname + " = %s" % (val,))
55 if arg == "--dbConn":
56 dbConn = val
57 log.debug('%s = %s' % (varname, val,))
59 from fundamentals.mysql import sqlite2mysql
60 converter = sqlite2mysql(
61 log=log,
62 settings=settings,
63 pathToSqlite=pathToSqliteDB,
64 tablePrefix=tablePrefix,
65 dbConn=dbConn
66 )
67 converter.convert_sqlite_to_mysql()
69 return
71class sqlite2mysql(object):
72 """
73 *Take a sqlite database file and copy the tables within it to a MySQL database*
75 **Key Arguments**
77 - ``log`` -- logger
78 - ``settings`` -- the settings dictionary
79 - ``pathToSqlite`` -- path to the sqlite database to transfer into the MySQL database
80 - ``tablePrefix`` -- a prefix to add to all the tablename when converting to mysql. Default *""*
81 - ``dbConn`` -- mysql database connection
84 **Usage**
86 To setup your logger, settings and database connections, please use the ``fundamentals`` package (`see tutorial here <http://fundamentals.readthedocs.io/en/latest/#tutorial>`_).
88 To convert and import the content of a sqlite database into MySQL run the following:
90 .. todo::
92 - add a tutorial about ``sqlite2mysql`` to documentation
94 ```python
95 from fundamentals.mysql import sqlite2mysql
96 converter = sqlite2mysql(
97 log=log,
98 settings=settings,
99 pathToSqlite="/path/to/sqlite.db",
100 tablePrefix="external"
101 )
102 converter.convert_sqlite_to_mysql()
103 ```
105 """
106 # Initialisation
108 def __init__(
109 self,
110 log,
111 pathToSqlite,
112 tablePrefix="",
113 settings=False,
114 dbConn=False
115 ):
116 self.log = log
117 log.debug("instansiating a new 'sqlite2mysql' object")
118 self.settings = settings
119 self.pathToSqlite = pathToSqlite
120 self.tablePrefix = tablePrefix
121 self.dbConn = dbConn
123 if not self.tablePrefix:
124 self.tablePrefix = ""
126 if len(self.tablePrefix):
127 self.tablePrefix = self.tablePrefix + "_"
128 # xt-self-arg-tmpx
130 return None
132 def convert_sqlite_to_mysql(
133 self):
134 """*copy the contents of the sqlite database into the mysql database*
136 See class docstring for usage
137 """
138 from fundamentals.renderer import list_of_dictionaries
139 from fundamentals.mysql import directory_script_runner
140 self.log.debug('starting the ``convert_sqlite_to_mysql`` method')
142 con = lite.connect(self.pathToSqlite)
143 con.row_factory = lite.Row
144 cur = con.cursor()
146 # GET ALL TABLE NAMES
147 cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
148 tables = cur.fetchall()
150 createStatements = []
151 inserts = []
152 for table in tables:
153 table = table['name']
154 if table == "sqlite_sequence":
155 continue
157 # CREATE TABLE collection_books (folder_id, fingerprint, primary key(folder_id, fingerprint));
158 # GENEREATE THE MYSQL CREATE STATEMENTS FOR EACH TABLE
159 cur.execute(
160 "SELECT sql FROM sqlite_master WHERE name = '%(table)s';" % locals())
161 createStatement = cur.fetchone()
162 createStatement = createStatement[0].replace('"', '`') + ";"
163 if "DEFAULT" not in createStatement:
164 if "primary key(" in createStatement:
165 tmp = createStatement.split("primary key(")
166 tmp[0] = tmp[0].replace(
167 ",", " varchar(150) DEFAULT NULL,")
168 createStatement = ("primary key(").join(tmp)
169 if "primary key," in createStatement:
170 tmp = createStatement.split("primary key,")
171 tmp[1] = tmp[1].replace(
172 ",", " varchar(150) DEFAULT NULL,")
173 tmp[1] = tmp[1].replace(
174 ");", " varchar(150) DEFAULT NULL);")
175 createStatement = ("primary key,").join(tmp)
176 createStatement = createStatement.replace(
177 "INTEGER PRIMARY KEY", "INTEGER AUTO_INCREMENT PRIMARY KEY")
178 createStatement = createStatement.replace(
179 "AUTOINCREMENT", "AUTO_INCREMENT")
180 createStatement = createStatement.replace(
181 "DEFAULT 't'", "DEFAULT '1'")
182 createStatement = createStatement.replace(
183 "DEFAULT 'f'", "DEFAULT '0'")
184 createStatement = createStatement.replace(",'t'", ",'1'")
185 createStatement = createStatement.replace(",'f'", ",'0'")
186 if "CREATE TABLE `" in createStatement:
187 createStatement = createStatement.replace(
188 "CREATE TABLE `", "CREATE TABLE IF NOT EXISTS `" + self.tablePrefix)
189 else:
190 createStatement = createStatement.replace(
191 "CREATE TABLE ", "CREATE TABLE IF NOT EXISTS " + self.tablePrefix)
192 if ", primary key(" in createStatement:
193 createStatement = createStatement.replace(", primary key(", """,
194`dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,
195`dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP,
196`updated` tinyint(4) DEFAULT '0',
197primary key(""")
198 else:
199 createStatement = createStatement.replace(");", """,
200 `dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,
201 `dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP,
202 `updated` tinyint(4) DEFAULT '0');
203 """)
204 createStatement = createStatement.replace(
205 " text primary key", " varchar(100) primary key")
206 createStatement = createStatement.replace(
207 "`EntryText` TEXT NOT NULL,", "`EntryText` TEXT,")
208 createStatement = createStatement.replace(
209 "`SelectionText` TEXT NOT NULL", "`SelectionText` TEXT")
210 createStatement = createStatement.replace(
211 "`Filename` INTEGER NOT NULL,", "`Filename` TEXT NOT NULL,")
212 createStatement = createStatement.replace(
213 "`SessionPartUUID` TEXT NOT NULL UNIQUE,", "`SessionPartUUID` VARCHAR(100) NOT NULL UNIQUE,")
214 createStatement = createStatement.replace(
215 "`Name` TEXT PRIMARY KEY NOT NULL", "`Name` VARCHAR(100) PRIMARY KEY NOT NULL")
216 createStatement = createStatement.replace(
217 " VARCHAR ", " VARCHAR(100) ")
218 createStatement = createStatement.replace(
219 " VARCHAR,", " VARCHAR(100),")
221 if len(createStatement.lower().split("datecreated")) > 2:
222 createStatement = createStatement.replace(
223 "`dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,\n", "")
225 # GRAB THE DATA TO ADD TO THE MYSQL DATABASE TABLES
226 cur.execute(
227 "SELECT * from '%(table)s';" % locals())
228 rows = cur.fetchall()
230 allRows = []
231 dateCreated = True
232 for row in rows:
233 thisDict = dict(row)
234 # CHECK FOR DATE-CREATED COLUMN - DON'T WANT TO DUPLICATE
235 if dateCreated and "datecreated" in str(thisDict.keys()).lower():
236 dateCreated = False
237 allRows.append(thisDict)
239 # RECURSIVELY CREATE MISSING DIRECTORIES
240 if not os.path.exists("/tmp/headjack/"):
241 os.makedirs("/tmp/headjack/")
243 writequery(
244 log=self.log,
245 sqlQuery=createStatement,
246 dbConn=self.dbConn,
247 )
249 from fundamentals.mysql import insert_list_of_dictionaries_into_database_tables
250 # USE dbSettings TO ACTIVATE MULTIPROCESSING
251 insert_list_of_dictionaries_into_database_tables(
252 dbConn=self.dbConn,
253 log=self.log,
254 dictList=allRows,
255 dbTableName=self.tablePrefix + table,
256 uniqueKeyList=[],
257 dateModified=True,
258 dateCreated=dateCreated,
259 batchSize=10000,
260 replace=True,
261 dbSettings=self.settings["database settings"]
262 )
264 # # DUMP THE DATA INTO A MYSQL DATABASE
265 # dataSet = list_of_dictionaries(
266 # log=self.log,
267 # listOfDictionaries=allRows
268 # )
269 # originalList = dataSet.list
270 # now = datetime.now()
271 # now = now.strftime("%Y%m%dt%H%M%S%f.sql")
272 # mysqlData = dataSet.mysql(
273 # tableName=self.tablePrefix + table, filepath="/tmp/headjack/" +
274 # now, createStatement=createStatement)
276 # directory_script_runner(
277 # log=self.log,
278 # pathToScriptDirectory="/tmp/headjack/",
279 # databaseName=self.settings["database settings"]["db"],
280 # loginPath=self.settings["database settings"]["loginPath"],
281 # successRule="delete",
282 # failureRule="failed"
283 # )
285 con.close()
287 self.log.debug('completed the ``convert_sqlite_to_mysql`` method')
288 return None
290 # use the tab-trigger below for new method
291 # xt-class-method
293if __name__ == '__main__':
294 main()