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
