class ReplicateTable(object): def __init__(self): self.error = ErrorHandling() self.mysql = MySQLConnector() self.execute = Execute() self.table = ConstructReplicationQuery() def replicate_table(self, db, source_table_name, destination_table_name): """ :param db: :param source_table_name: :param destination_table_name: """ self.error.warn("making TABLE %s, an exact copy of TABLE %s..." % (destination_table_name, source_table_name)) self.error.warn("connecting to DATABASE %s..." % db) (db_conn, db_cursor, dict_cursor) = self.mysql.db_connect(db) self.error.warn("cloning structure of table...") clone_query = self.table.construct_replication_query(db_conn, source_table_name, destination_table_name) self.execute.execute_(db, db_cursor, clone_query, True) self.error.warn("populating newly created table, TABLE %s" % destination_table_name) populate_query = """INSERT INTO " + destination_table_name + " SELECT * FROM """ + source_table_name self.execute.execute_(db, db_cursor, populate_query, True) self.error.warn("finished replicating table!")
class ExecuteWrite(object): def __init__(self): self.mysql = MySQLConnector() self.error = ErrorHandling() def execute_write(self, db, db_conn, query, row, write_cursor=None, warn_query=False): """ This method executes a write query. :rtype : object :param db: :param db_conn: :param mysql: :param row: :param write_cursor: :param warn_query: """ if warn_query: self.error.warn("SQL (write many) QUERY: %s" % query) if not write_cursor: write_cursor = db_conn.cursor() attempts = 0 while True: try: write_cursor.execute(query, row) break except MySQLdb.Error, e: attempts += 1 self.error.warn(" *MYSQL Corpus DB ERROR on %s:\n%s (%d attempt)" % (query, e, attempts)) time.sleep(MySQLConnector.MYSQL_TIMEOUT) (db_conn, db_cursor, dict_cursor) = self.mysql.db_connect(db) write_cursor = db_conn.cursor() if attempts > MySQLConnector.MAX_ATTEMPTS: sys.exit(1) return write_cursor
class ExecuteGetList(object): def __init__(self): self.mysql = MySQLConnector() self.error = ErrorHandling() def execute_get_list(self, db, db_cursor, sql, warn_query=False): """ Executes a given query, returns results as a list of lists. :param db: :param db_cursor: :param sql: :param warn_query: """ if warn_query: self.error.warn("SQL QUERY: %s" % sql) data = [] attempts = 0 while True: try: db_cursor.execute(sql) data = db_cursor.fetchall() break except MySQLdb.Error, e: attempts += 1 self.error.warn(" *MYSQL Corpus DB ERROR on %s:\n%s (%d attempt)" % (sql, e, attempts)) time.sleep(MySQLConnector.MYSQL_TIMEOUT) (db_conn, db_cursor, dict_cursor) = self.mysql.db_connect(db) if attempts > MySQLConnector.MAX_ATTEMPTS: sys.exit(1) return data
class ExecuteGetDict(object): def __init__(self): self.mysql = MySQLConnector() self.error = ErrorHandling() def execute_get_dict(self, dict_cursor, query, warn_query=False): """ Executes a given query, returns results as a list of dicts. :param dict_cursor: :param query: :param warn_query: """ #if warn_query: #self.error.warn("SQL (DictCursor) QUERY: %s" % query) data = [] attempts = 0 while True: try: dict_cursor.execute(query) data = dict_cursor.fetchall() break except MySQLdb.Error, e: attempts += 1 self.error.warn(" *MYSQL Corpus DB ERROR on %s:\n%s (%d attempt)" % (query, e, attempts)) time.sleep(MySQLConnector.MYSQL_TIMEOUT) (db_conn, db_cursor, dict_cursor) = self.mysql.connect() # TODO connection parameters if attempts > MySQLConnector.MAX_ATTEMPTS: sys.exit(1) return data
class QuickExecuteWriteMany(object): def __init__(self): self.mysql = MySQLConnector() self.execute = ExecuteWriteMany() def quick_execute_write_many(self, db, sql, rows, write_cursor=None, warn_query=False): """ Executes a write query """ (db_conn, db_cursor, dict_cursor) = self.mysql.db_connect(db) return self.execute.execute_write_many(db, db_conn, sql, rows, write_cursor, warn_query)
class RandomSubset(object): def __init__(self): self.mysql = MySQLConnector() self.error = ErrorHandling() self.execute_one_col = ExecuteGetListOneColumn() self.execute = Execute() def random_subset_table(self, db, source_table_name, destination_table_name, field, percent=.10, distinct=True): """ This method selects a random sample of records from source table to destination table :param db: :param source_table_name: :param destination_table_name: :param field: :param percent: :param distinct: """ self.error.warn("making TABLE %s, a %2.2f percent random subset of TABLE %s on unique key %s..." % ( destination_table_name, percent, source_table_name, field)) self.error.warn("connecting to DATABASE %s..." % db) (db_conn, db_cursor, dict_cursor) = self.mysql.db_connect(db) self.error.warn("removing destination table if it exists...") sql = 'DROP TABLE IF EXISTS %s' % destination_table_name self.execute.execute_(db, db_cursor, sql, True) self.error.warn("cloning structure of table...") sql = 'CREATE TABLE %s LIKE %s' % (destination_table_name, source_table_name) self.execute.execute_(db, db_cursor, sql, True) is_distinct_text = ' distinct' if distinct else '' self.error.warn('grabbing a%s subset (%2.6f percent) of the keys on which to base the new table' % ( is_distinct_text, 100 * percent)) sql = 'SELECT DISTINCT(%s) FROM %s' % (field, source_table_name) if distinct else 'SELECT %s FROM %s' % ( field, source_table_name) unique_key_list = self.execute_one_col.execute_get_list_one_col(db, db_cursor, sql, True) self.error.warn(str(unique_key_list[1:5])) new_keys = sample(unique_key_list, int(floor(len(unique_key_list) * percent))) new_keys = map(str, new_keys) self.error.warn("populating newly created table, TABLE %s" % destination_table_name) populate_query = "INSERT INTO %s SELECT * FROM %s WHERE %s IN (%s)" % ( destination_table_name, source_table_name, field, ','.join(new_keys)) self.execute.execute_(db, db_cursor, populate_query, False) self.error.warn("finished making TABLE %s, a %2.2f percent random subset of TABLE %s on unique key %s!" % ( destination_table_name, percent, source_table_name, field))
class QuickExecute(object): def __init__(self): self.mysql = MySQLConnector() self.execute = Execute() def quick_execute(self, db, query, warn=False): """ This method performs the db connect and execute in the same call. """ (con, cursor_, dict_cursor) = self.mysql.db_connect(db) return self.execute.execute_(db, cursor_, query, warn)
class GetColumnNameTypes(object): def __init__(self): self.mysql = MySQLConnector() self.execute = ExecuteGetList() def get_table_column_name_types(self, db, table): """ This method returns a list of column names and types from a database table. :param db: database :param table: table """ (db_conn, db_cursor, dict_cursor) = self.mysql.db_connect(db) query = """SELECT column_name, column_type FROM information_schema.columns WHERE table_schema='{0:s}' AND table_name='{1:s}'""".format(db, table) return self.execute.execute_get_list(db, db_cursor, query)
def __init__(self): self.error = ErrorHandling() self.mysql = MySQLConnector() self.execute = Execute() self.table = ConstructReplicationQuery()
def __init__(self): self.mysql = MySQLConnector() self.error = ErrorHandling() self.execute_one_col = ExecuteGetListOneColumn() self.execute = Execute()
def __init__(self): self.mysql = MySQLConnector() self.execute = ExecuteGetList()
def __init__(self): self.mysql = MySQLConnector() self.error = ErrorHandling()
def __init__(self): self.mysql = MySQLConnector() self.execute = ExecuteWriteMany()