def add_column_to_mysql(cf, table, columns, keys):
connection = general_storage_mysql.create_connection(cf)
##query_str ="alter table %s add column %s,add key %s" %(table,
## ", add column ".join(columns),
## ", add key ".join(["%s (%s)" %(x,x) for x in keys]))
query_str = "alter table %s add column %s" % (
table, ", add column ".join(columns))
general_storage_mysql.execute_query(connection, query_str)
def delete_mysql_item(cf,i):
## Main function to call deleteitem to mysql database
if i['object_type']=='post':
query="delete from twit_posts_%s(%s) where post_id=%s" %(cf.client_short_name,i['object_id'])
else:
query="delete from twit_comments_%s(%s) where comment_id=%s" %(cf.client_short_name,i['object_id'])
connection = general_storage_mysql.create_connection(cf)
general_storage_mysql.execute_query(connection,query)
def insert_dynamodb_item_into_mysql(cf,i):
## Main function to call normalizer to normalize object from dynamodb object to mysql object, and then insert normalized item to mysql database
if i['object_type']=='post':
nl = Normalizer_post_dynomodb_mysql()
else:
nl = Normalizer_comment_dynomodb_mysql()
nl.normalize_source_to_target(cf,i)
connection = general_storage_mysql.create_connection(cf)
attributes,values = general_storage_mysql.simple_json_to_mysql_query(nl.target)
query="insert into twit_%s_%s(%s) values(%s)" %(nl.name,cf.client_short_name,attributes,values)
print(query)
general_storage_mysql.execute_query(connection,query)
def insert_dynamodb_item_into_mysql(cf, i):
## Main function to call normalizer to normalize object from dynamodb object to mysql object, and then insert normalized item to mysql database
if i['object_type'] == 'post':
nl = Normalizer_post_dynomodb_mysql()
else:
nl = Normalizer_comment_dynomodb_mysql()
nl.normalize_source_to_target(cf, i)
table = "twit_%s_%s" % (nl.name, cf.client_short_name)
## create table if not exists
general_storage_mysql.create_table_if_non_exists(cf,
table,
like_table=None)
## add columns if not exists
general_storage_mysql.add_columns_if_non_exists(cf, table, nl.target)
attributes, values = general_storage_mysql.simple_json_to_mysql_query(
nl.target)
query = "insert into %s(%s) values(%s)" % (table, attributes, values)
logging.info(query)
connection = general_storage_mysql.create_connection(cf)
general_storage_mysql.execute_query(connection, query)
def insert_to_mysql(cf, table, item):
connection = general_storage_mysql.create_connection(cf)
attributes, values = general_storage_mysql.simple_json_to_mysql_query(item)
query_str = "insert into %s(%s) values(%s)" % (table, attributes, values)
general_storage_mysql.execute_query(connection, query_str)
def create_table_if_non_exists(cf, table):
connection = general_storage_mysql.create_connection(cf)
query_str = """CREATE TABLE IF NOT EXISTS """ + table + " ( `id` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
general_storage_mysql.execute_query(connection, query_str)
def get_all_columns(cf, table):
connection = general_storage_mysql.create_connection(cf)
return [
x['Field'] for x in general_storage_mysql.execute_query(
connection, "SHOW COLUMNS FROM %s" % (table))
]