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 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 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 Execute(object):
def __init__(self):
self.mysql = MySQLConnector()
self.error = ErrorHandling()
filterwarnings('ignore', category=MySQL.Warning)
def execute_query(self, cursor, query):
attempts = 0
while True:
try:
cursor.execute(query)
break
except MySQL.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)
if attempts > MySQLConnector.MAX_ATTEMPTS:
sys.exit(1)
#resetwarnings()
return True
class MySQLConnector(object):
"""
This class is used to establish connection to MySQL server on local machine/AWS ec2 instance.
"""
MAX_ATTEMPTS = 5 # max number of times to try a query before exiting
MYSQL_TIMEOUT = 4 # number of seconds to wait before trying a query again in case of a failure
def __init__(self, user=None, passwd=None, host=None, port=None):
self.user = user
self.passwd = passwd
self.host = host
self.port = port
self.error = ErrorHandling()
def connect(self):
"""
Makes a connection with MySQL server. Returns tuple of (conn, db_cursor, dict_cursor)
"""
conn = None
attempts = 0
while True:
try:
conn = MySQLdb.connect(host=self.host,
port=self.port,
user=self.user,
passwd=self.passwd)
conn.autocommit(True)
break
except MySQLdb.Error, e:
attempts += 1
self.error.warn(" *MYSQL Connection ERROR:%s\n (%d attempt)" % (e, attempts))
time.sleep(MySQLConnector.MYSQL_TIMEOUT)
if attempts > MySQLConnector.MAX_ATTEMPTS:
sys.exit(1)
return conn
def __init__(self):
self.error = ErrorHandling()
self.mysql = MySQLConnector()
self.execute = Execute()
self.table = ConstructReplicationQuery()
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))
def __init__(self):
self.mysql = MySQLConnector()
self.error = ErrorHandling()
self.execute_one_col = ExecuteGetListOneColumn()
self.execute = Execute()
def __init__(self, user=None, passwd=None, host=None, port=None):
self.user = user
self.passwd = passwd
self.host = host
self.port = port
self.error = ErrorHandling()
def __init__(self):
self.mysql = MySQLConnector()
self.error = ErrorHandling()
filterwarnings('ignore', category=MySQL.Warning)
def __init__(self):
self.mysql = MySQLConnector()
self.error = ErrorHandling()
