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 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 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(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 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 _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 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 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 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