def runQuery(self, sql, options={}): ws = datagen.Warning_Singleton() if self.db is None: self.open_db() if self.db is None: raise MySQLdb._exceptions.DatabaseError('Error accessing database.') return None db = self.db cursor = db.cursor() try: result = cursor.execute(sql) records = True if (re.match('SELECT |SHOW |DESCRIBE |DESC ', sql, flags=re.IGNORECASE)): if (options.get('fetchone')): records = cursor.fetchone() else: records = cursor.fetchall() return records except (MySQLdb.OperationalError) as e: ws.sh_exc(sys.exc_info(), e) ws.wprint('Rolling back...', 3) db.rollback() except BaseException as e: ws.sh_exc(sys.exc_info(), e) return None
def list(self, options): """List tables.""" try: ws = datagen.Warning_Singleton() ws.wprint('Listing tables...', 3) records = self.get_tables() if not records: ws.wprint('No tables available', 1) return matrix = [] if options['header'] and options['format'] == 'table': matrix = [['Table', 'Columns']] for table in records: sql = f'DESCRIBE {table};' items = self.runQuery(sql) if not items: items = (('N/A',),) matrix.append([table] + [item[0] for item in items]) if options['format'] == 'table': self.list_table(matrix, options) elif options['format'] == 'json': self.list_json(matrix, options) except (MySQLdb.OperationalError) as e: ws.sh_exc(sys.exc_info(), e) ws.wprint('Rolling back...', 3) db.rollback() except BaseException as e: ws.sh_exc(sys.exc_info(), e) else: self.close_db() finally: ws.wprint('Listing finalized', 3)
def list_printer(self, data, options = {}): ws = datagen.Warning_Singleton() if options.get('output') == None: ws.wprint(data, 1) else: with open(options.get('output'), 'w') as f: f.write(data + '\n')
def list_json(self, matrix, options = {}): ws = datagen.Warning_Singleton() jsonMatrix = [] for row in matrix: jsonMatrix.append({'table': row[0], 'columns': row[1:]}) data = json.dumps(jsonMatrix) self.list_printer(data, options)
def get_tables(self): ws = datagen.Warning_Singleton() sql = 'SHOW TABLES;' records = self.runQuery(sql) if not records: return None records = [item[0] for item in records] return records
def __init__(self, **kwargs): """Initializes class.""" ws = datagen.Warning_Singleton() try: self.host = kwargs.get('host') self.username = kwargs.get('username') self.password = kwargs.get('password') self.database = kwargs.get('database') except BaseException as e: ws.sh_exc(sys.exc_info(), e)
def close_db(self): """Close database.""" ws = datagen.Warning_Singleton() ws.wprint('Closing database...', 3) try: self.db.close() return True except BaseException as e: ws.sh_exc(sys.exc_info(), e) return None
def open_db(self): """Open database.""" ws = datagen.Warning_Singleton() ws.wprint('Opening database...', 3) try: self.db = MySQLdb.connect(host=self.host, user=self.username, passwd=self.password, db=self.database) ws.wprint('Database opened', 3) return self.db except BaseException as e: ws.sh_exc(sys.exc_info(), e) return None
def list_table(self, matrix, options = {}): ws = datagen.Warning_Singleton() max_row = 0 for row in matrix: max_row = len(row) if len(row) > max_row else max_row sizes = [] for i in range(0, max_row): sizes.append(0) for row in matrix: for i, item in enumerate(row): if len(item) > sizes[i]: sizes[i] = len(item) # print columns data = '' for row in matrix: fmt_string = '' for i, item in enumerate(row): fmt_string += '{' + str(i) + ':<' + str(sizes[i]) + '} ' data += fmt_string.format(*row) + '\n' data = data[0:-1] self.list_printer(data, options)
def save_data(self, data, fkeys=None, **options): """Save data into database.""" try: ws = datagen.Warning_Singleton() ws.wprint('Starting database part...', 3) table_name = options['table'] hdb = handle_db(**options['database']) db = hdb.open_db() if db is None: raise MySQLdb._exceptions.DatabaseError( 'Error accessing database.') cursor = db.cursor() if options['drop'] is True: # Deletes table if it already exists ws.wprint('Dropping ' + table_name + '...', 2) sql = 'DROP TABLE IF EXISTS ' + table_name cursor.execute(sql) # Creates a new table ws.wprint('Creating ' + table_name + '...', 2) sql = 'CREATE TABLE IF NOT EXISTS ' + table_name + '(' if options['id_increment'] is True: sql += '`id` mediumint(8) unsigned NOT NULL auto_increment, ' if fkeys is not None: # from list like ['custormers__id', 'products__id', 'sales_dept__id'], # save str before __ in table and after in key tables_keys = '' table_key = '' table_tuple = [(re.search('.*(?=__)', fkeys).group(0), re.search('(?<=__).*$', fkeys).group(0)) for fkeys in fkeys] # noqa:E501 fkeys_types = {} for table, key in table_tuple: sql2 = f'describe {table}' cursor.execute(sql2) result = cursor.fetchall() if len([item for item in result if item[0] == key]) == 0: raise AttributeError( f"Error with '{table}__{key}' in {table_name}") found_key = [item for item in result if item[0] == key][0] fkeys_types[f'{table}__{key}'] = found_key[1] table_key = table + '__' + found_key[0] tables_keys += table_key + ', ' sql += f'`{table_key}` {found_key[1]} NOT NULL, ' for col in self._args_details: column_name = list(col.keys())[0] datatype = col[column_name].datatype size = col[column_name].size type_options = ' '.join(col[column_name].type_options) null = col[column_name].null sql += '`' + column_name + '` ' sql += datatype + '' if size is not None: sql += '(' + str(size) + ') ' else: sql += ' ' sql += type_options + ' ' sql += null + ', ' if options['id_increment'] is True: sql += 'PRIMARY KEY (`id`)' else: sql = sql[:-2] sql += ')' cursor.execute(sql) # Inserts data into the newly created table # sql = 'INSERT INTO ' + options['table'] + '(' + ', '.join(data['content']['cols']) + ') ' ws.wprint('Inserting data into ' + table_name + '...', 2) sql = 'INSERT INTO ' + table_name + '(' if fkeys is not None: sql += tables_keys sql += ', '.join( [list(item.keys())[0] for item in self._args_details]) + ') ' # noqa:E501 sql += 'VALUES' for row in data: strrow = list( map( lambda r: ("'" + r + "'") if isinstance(r, str) else str(r), row)) # noqa:E501 sql += '(' if fkeys is not None: for table, key in table_tuple: sql2 = f'SELECT {key} FROM {table} ORDER BY RAND() LIMIT 1' cursor.execute(sql2) rand_value = cursor.fetchone()[0] quote = "'" if fkeys_types[ f'{table}__{key}'][:7] == 'varchar' else '' sql += quote + str(rand_value) + quote + ', ' sql += ', '.join(strrow) + '),' sql = sql[:-1] cursor.execute(sql) db.commit() ws.wprint('Insert completed successfully', 3) except (MySQLdb.OperationalError) as e: ws.sh_exc(sys.exc_info(), e) ws.wprint('Rolling back...', 3) db.rollback() except BaseException as e: ws.sh_exc(sys.exc_info(), e) else: hdb.close_db() finally: ws.wprint('Database part finalized', 3)
def drop(self, options = {}): """List tables.""" try: ws = datagen.Warning_Singleton() ws.wprint('Dropping tables...', 3) records = self.get_tables() records = [] if not records else records tables_to_drop = [] if options['table'] == None: tables_to_drop = records else: tables_to_drop = options['table'] if not options['respectForeignKeys']: sql = 'SET FOREIGN_KEY_CHECKS=0'; self.runQuery(sql) for table in tables_to_drop: ws.wprint(f'Dropping table "{table}"...', 2) if table not in records: ws.eprint(f'TABLE "{table}" not found') continue yes = options['yesAll'] result = None if not yes: result = input(f'Do you confirm the dropping of TABLE "{table}" (y/N)? ') result = True if result.upper() == 'Y' or result.upper() == 'YES' else False else: result = True if result: sql = 'DROP TABLE `' + table + '`;' result = self.runQuery(sql) if result: ws.wprint(f'TABLE {table} successfuly dropped', 2) else: sql = f"SELECT table_type FROM information_schema.tables WHERE TABLE_SCHEMA = '{self.database}' AND TABLE_NAME = '{table}';" result = self.runQuery(sql, {'fetchone': True}) if result: result = result[0] if result != 'VIEW': ws.eprint(f'Failed to drop TABLE {table}') continue else: result = None if not yes: result = input(f'"{table}" is a \'VIEW\'. Delete the \'VIEW\' anyway (y/N)? ') result = True if result.upper() == 'Y' or result.upper() == 'YES' else False else: result = True if result: sql = f'DROP VIEW `{table}`;' result = self.runQuery(sql) if result: ws.wprint(f'VIEW {table} successfuly dropped', 2) else: ws.eprint(f'Failed to drop VIEW {table}') # except BaseException as e: # ws.sh_exc(sys.exc_info(), e) except (MySQLdb.OperationalError) as e: ws = datagen.Warning_Singleton() ws.sh_exc(sys.exc_info(), e) ws.wprint('Rolling back...', 3) db.rollback() except BaseException as e: ws.sh_exc(sys.exc_info(), e) else: pass finally: ws.wprint('Dropping finalized', 3) if options['respectForeignKeys']: sql = 'SET FOREIGN_KEY_CHECKS=1'; self.runQuery(sql) self.close_db()