def select_join(self, table1, table2, cols, table1_col, table2_col=None, join_type=None): """ Left join all rows and columns from two tables where a common value is shared. :param table1: Name of table #1 :param table2: Name of table #2 :param cols: List of columns or column tuples String or flat list: Assumes column(s) are from table #1 if not specified List of tuples: Each tuple in list of columns represents (table_name, column_name) :param table1_col: Column from table #1 to use as key :param table2_col: Column from table #2 to use as key :param join_type: Type of join query :return: Queried rows """ # Check if cols is a list of tuples if isinstance(cols[0], tuple): cols = join_cols(['{0}.{1}'.format(tbl, col) for tbl, col in cols]) else: cols = join_cols(['{0}.{1}'.format(table1, col) for col in cols]) # Validate join_type and table2_col join_type = join_type.lower().split(' ', 1)[0].upper() + ' JOIN' if join_type else 'LEFT JOIN' assert join_type in JOIN_QUERY_TYPES table2_col = table2_col if table2_col else table1_col # Concatenate and return statement statement = ''' SELECT {columns} FROM {table1} {join_type} {table2} ON {table1}.{table1_col} = {table2}.{table2_col} '''.format(table1=wrap(table1), table2=wrap(table2), columns=cols, table1_col=table1_col, table2_col=table2_col, join_type=join_type) return self.fetch(statement)
def modify_column(self, table, name, new_name=None, data_type=None, null=None, default=None): """Modify an existing column.""" existing_def = self.get_schema_dict(table)[name] # Set column name new_name = new_name if new_name is not None else name # Set data type if not data_type: data_type = existing_def['Type'] # Set NULL if null is None: null_ = 'NULL' if existing_def['Null'].lower( ) == 'yes' else 'NOT NULL' else: null_ = 'NULL' if null else 'NOT NULL' default = 'DEFAULT {0}'.format(default if default else null_) query = 'ALTER TABLE {0} CHANGE {1} {2} {3} {4} {5}'.format( wrap(table), wrap(name), wrap(new_name), data_type, null_, default) self.execute(query) self._printer('\tModified column {0}'.format(name))
def delete(self, table, where=None): """Delete existing rows from a table.""" if where: where_key, where_val = where query = "DELETE FROM {0} WHERE {1}='{2}'".format( wrap(table), where_key, where_val) else: query = 'DELETE FROM {0}'.format(wrap(table)) self.execute(query) return True
def copy_database(self, source, destination): """ Copy a database's content and structure. SMALL Database speed improvements (DB size < 5mb) Using optimized is about 178% faster Using one_query is about 200% faster LARGE Database speed improvements (DB size > 5mb) Using optimized is about 900% faster Using one_query is about 2600% faster :param source: Source database :param destination: Destination database """ print( '\tCopying database {0} structure and data to database {1}'.format( source, destination)) with Timer('\nSuccess! Copied database {0} to {1} in '.format( source, destination)): # Create destination database if it does not exist if destination in self.databases: self.truncate_database(destination) # Truncate database if it does exist else: self.create_database(destination) # Copy database structure and data self.change_db(source) tables = self.tables # Change database to destination self.change_db(destination) print('\n') _enable_printing = self.enable_printing self.enable_printing = False # Copy tables structure for table in tqdm( tables, total=len(tables), desc='Copying {0} table structures'.format(source)): self.execute('CREATE TABLE {0}.{1} LIKE {2}.{1}'.format( destination, wrap(table), source)) # Copy tables data for table in tqdm(tables, total=len(tables), desc='Copying {0} table data'.format(source)): self.execute( 'INSERT INTO {0}.{1} SELECT * FROM {2}.{1}'.format( destination, wrap(table), source)) self.enable_printing = _enable_printing
def select_where(self, table, cols, where, return_type=list): """ Query certain rows from a table where a particular value is found. cols parameter can be passed as a iterable (list, set, tuple) or a string if only querying a single column. where parameter can be passed as a two or three part tuple. If only two parts are passed the assumed operator is equals(=). :param table: Name of table :param cols: List, tuple or set of columns or string with single column name :param where: WHERE clause, accepts either a two or three part tuple two-part: (where_column, where_value) three-part: (where_column, comparison_operator, where_value) :param return_type: Type, type to return values in :return: Queried rows """ # Unpack WHERE clause dictionary into tuple if isinstance(where, (list, set)): # Multiple WHERE clause's (separate with AND) clauses = [self._where_clause(clause) for clause in where] where_statement = ' AND '.join(clauses) else: where_statement = self._where_clause(where) # Concatenate full statement and execute statement = "SELECT {0} FROM {1} WHERE {2}".format(join_cols(cols), wrap(table), where_statement) values = self.fetch(statement) return self._return_rows(table, cols, values, return_type)
def _copy_database_data_serverside(self, source, destination, tables): """Select rows from a source database and insert them into a destination db in one query""" for table in tqdm(tables, total=len(tables), desc='Copying table data (optimized)'): self.execute('INSERT INTO {0}.{1} SELECT * FROM {2}.{1}'.format( destination, wrap(table), source))
def copy_table_structure(self, source, destination, table): """ Copy a table from one database to another. :param source: Source database :param destination: Destination database :param table: Table name """ self.execute('CREATE TABLE {0}.{1} LIKE {2}.{1}'.format( destination, wrap(table), source))
def drop_column(self, table, name): """Remove a column to an existing table.""" try: self.execute('ALTER TABLE {0} DROP COLUMN {1}'.format( wrap(table), name)) self._printer('\tDropped column {0} from {1}'.format(name, table)) except ProgrammingError: self._printer( "\tCan't DROP '{0}'; check that column/key exists in '{1}'". format(name, table)) return name
def get_schema(self, table, with_headers=False): """Retrieve the database schema for a particular table.""" f = self.fetch('desc ' + wrap(table)) if not isinstance(f[0], list): f = [f] # Replace None with '' schema = [['' if col is None else col for col in row] for row in f] # If with_headers is True, insert headers to first row before returning if with_headers: schema.insert( 0, ['Column', 'Type', 'Null', 'Key', 'Default', 'Extra']) return schema
def select(self, table, cols, execute=True, select_type='SELECT', return_type=list): """Query every row and only certain columns from a table.""" # Validate query type select_type = select_type.upper() assert select_type in SELECT_QUERY_TYPES # Concatenate statement statement = '{0} {1} FROM {2}'.format(select_type, join_cols(cols), wrap(table)) if not execute: # Return command return statement # Retrieve values values = self.fetch(statement) return self._return_rows(table, cols, values, return_type)
def insert(self, table, columns, values, execute=True): """Insert a single row into a table.""" # TODO: Cant accept lists? # Concatenate statement cols, vals = get_col_val_str(columns) statement = "INSERT INTO {0} ({1}) VALUES ({2})".format( wrap(table), cols, vals) # Execute statement if execute: self._cursor.execute(statement, values) self._commit() self._printer( '\tMySQL row successfully inserted into {0}'.format(table)) # Only return statement else: return statement
def insert_many(self, table, columns, values, limit=MAX_ROWS_PER_QUERY, execute=True): """ Insert multiple rows into a table. If only one row is found, self.insert method will be used. """ # Make values a list of lists if it is a flat list if not isinstance(values[0], (list, set, tuple)): values = [] for v in values: if v is not None and len(v) > 0: values.append([v]) else: values.append([None]) # Concatenate statement cols, vals = get_col_val_str(columns) statement = 'INSERT INTO {0} ({1}) VALUES ({2})'.format( wrap(table), cols, vals) if execute and len(values) > limit: while len(values) > 0: vals = [ values.pop(0) for i in range(0, min(limit, len(values))) ] self._cursor.executemany(statement, vals) self._commit() elif execute: # Execute statement self._cursor.executemany(statement, values) self._commit() self._printer('\tMySQL rows (' + str(len(values)) + ') successfully INSERTED') # Only return statement else: return statement
def add_column(self, table, name='ID', data_type='int(11)', after_col=None, null=False, primary_key=False): """Add a column to an existing table.""" location = 'AFTER {0}'.format(after_col) if after_col else 'FIRST' null_ = 'NULL' if null else 'NOT NULL' comment = "COMMENT 'Column auto created by mysql-toolkit'" pk = 'AUTO_INCREMENT PRIMARY KEY {0}'.format( comment) if primary_key else '' query = 'ALTER TABLE {0} ADD COLUMN {1} {2} {3} {4} {5}'.format( wrap(table), name, data_type, null_, pk, location) self.execute(query) self._printer("\tAdded column '{0}' to '{1}' {2}".format( name, table, '(Primary Key)' if primary_key else '')) return name
def rename(self, old_table, new_table): """ Rename a table. You must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table. """ try: command = 'RENAME TABLE {0} TO {1}'.format(wrap(old_table), wrap(new_table)) except: command = 'ALTER TABLE {0} RENAME {1}'.format( wrap(old_table), wrap(new_table)) self.execute(command) self._printer('Renamed {0} to {1}'.format(wrap(old_table), wrap(new_table))) return old_table, new_table
def update(self, table, columns, values, where): """ Update the values of a particular row where a value is met. :param table: table name :param columns: column(s) to update :param values: updated values :param where: tuple, (where_column, where_value) """ # Unpack WHERE clause dictionary into tuple where_col, where_val = where # Create column string from list of values cols = get_col_val_str(columns, query_type='update') # Concatenate statement statement = "UPDATE {0} SET {1} WHERE {2}='{3}'".format( wrap(table), cols, where_col, where_val) # Execute statement self._cursor.execute(statement, values) self._printer('\tMySQL cols (' + str(len(values)) + ') successfully UPDATED')
def set_primary_key(self, table, column): """Create a Primary Key constraint on a specific column when the table is already created.""" self.execute('ALTER TABLE {0} ADD PRIMARY KEY ({1})'.format( wrap(table), column)) self._printer('\tAdded primary key to {0} on column {1}'.format( wrap(table), column))
def create_database(self, name): """Create a new database.""" statement = "CREATE DATABASE {0} DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci".format( wrap(name)) return self.execute(statement)
def get_duplicate_vals(self, table, column): """Retrieve duplicate values in a column of a table.""" query = 'SELECT {0} FROM {1} GROUP BY {0} HAVING COUNT(*) > 1'.format( join_cols(column), wrap(table)) return self.fetch(query)
def select_where_between(self, table, cols, where_col, between): """ Query rows from a table where a columns value is found between two values. :param table: Name of the table :param cols: List, tuple or set of columns or string with single column name :param where_col: Column to check values against :param between: Tuple with min and max values for comparison :return: Queried rows """ # Unpack WHERE clause dictionary into tuple min_val, max_val = between # Concatenate full statement and execute statement = "SELECT {0} FROM {1} WHERE {2} BETWEEN {3} AND {4}".format(join_cols(cols), wrap(table), where_col, min_val, max_val) return self.fetch(statement)
def drop_index(self, table, column): """Drop an index from a table.""" self.execute('ALTER TABLE {0} DROP INDEX {1}'.format( wrap(table), column)) self._printer('\tDropped index from column {0}'.format(column))
def select_where_like(self, table, cols, where_col, start=None, end=None, anywhere=None, index=(None, None), length=None): """ Query rows from a table where a specific pattern is found in a column. MySQL syntax assumptions: (%) The percent sign represents zero, one, or multiple characters. (_) The underscore represents a single character. :param table: Name of the table :param cols: List, tuple or set of columns or string with single column name :param where_col: Column to check pattern against :param start: Value to be found at the start :param end: Value to be found at the end :param anywhere: Value to be found anywhere :param index: Value to be found at a certain index :param length: Minimum character length :return: Queried rows """ # Retrieve search pattern pattern = self._like_pattern(start, end, anywhere, index, length) # Concatenate full statement and execute statement = "SELECT {0} FROM {1} WHERE {2} LIKE '{3}'".format(join_cols(cols), wrap(table), where_col, pattern) return self.fetch(statement)
def _select_limit_statement(table, cols='*', offset=0, limit=MAX_ROWS_PER_QUERY): """Concatenate a select with offset and limit statement.""" return 'SELECT {0} FROM {1} LIMIT {2}, {3}'.format(join_cols(cols), wrap(table), offset, limit)
def count_rows_distinct(self, table, cols='*'): """Get the number distinct of rows in a particular table.""" return self.fetch('SELECT COUNT(DISTINCT {0}) FROM {1}'.format( join_cols(cols), wrap(table)))
def drop_primary_key(self, table): """Drop a Primary Key constraint for a specific table.""" if self.get_primary_key(table): self.execute('ALTER TABLE {0} DROP PRIMARY KEY'.format( wrap(table)))
def count_rows(self, table, cols='*'): """Get the number of rows in a particular table.""" query = 'SELECT COUNT({0}) FROM {1}'.format(join_cols(cols), wrap(table)) result = self.fetch(query) return result if result is not None else 0