def add_field_to_our_tables(table_name, source_file):
loader = OurTableLoader()
result = loader.loadUnprocessed(source_file)
db = MySQLdb.connect(host="localhost",
user="******",
db="article_refactor_24_3_2016")
cur = db.cursor(MySQLdb.cursors.DictCursor)
cur.execute("select * from %s" % table_name)
for entry in cur.fetchallDict():
print "*" * 30
print entry["rna1_name"], entry["rna2_name"]
i = 0
for row in result:
if row["start_1"] == entry["start_1"] and row["start_2"] == entry["start_2"] and \
row["end_1"] == entry["end_1"] and row["end_2"] == entry["end_2"]:
i += 1
cur.execute(
"""UPDATE %(table_name)s
SET rna1_poly_u=%(rna1_poly_u)s, rna2_poly_u=%(rna2_poly_u)s
WHERE
start_1=%(start_1)s and start_2=%(start_2)s and
end_1=%(end_1)s and end_2=%(end_2)s""" % {
"table_name": table_name,
"rna1_poly_u": db.literal(row["poly-u of rna1"]),
"rna2_poly_u": db.literal(row["poly-u of rna2"]),
"start_1": row["start_1"],
"start_2": row["start_2"],
"end_1": row["end_1"],
"end_2": row["end_2"]
})
matches = cur.fetchallDict()
if i != 1:
print "--> error!"
print i
db.commit()
def add_field_to_our_tables(table_name, source_file):
loader = OurTableLoader()
result = loader.loadUnprocessed(source_file)
db = MySQLdb.connect(host="localhost",user="******",db="article_refactor_24_3_2016")
cur = db.cursor(MySQLdb.cursors.DictCursor)
cur.execute("select * from %s" % table_name)
for entry in cur.fetchallDict():
print "*" * 30
print entry["rna1_name"], entry["rna2_name"]
i = 0
for row in result:
if row["start_1"] == entry["start_1"] and row["start_2"] == entry["start_2"] and \
row["end_1"] == entry["end_1"] and row["end_2"] == entry["end_2"]:
i += 1
cur.execute("""UPDATE %(table_name)s
SET rna1_poly_u=%(rna1_poly_u)s, rna2_poly_u=%(rna2_poly_u)s
WHERE
start_1=%(start_1)s and start_2=%(start_2)s and
end_1=%(end_1)s and end_2=%(end_2)s""" % {"table_name": table_name,
"rna1_poly_u": db.literal(row["poly-u of rna1"]),
"rna2_poly_u": db.literal(row["poly-u of rna2"]),
"start_1": row["start_1"],
"start_2": row["start_2"],
"end_1": row["end_1"],
"end_2": row["end_2"]})
matches = cur.fetchallDict()
if i != 1:
print "--> error!"
print i
db.commit()
def __init__(self, our_tables_list, article_tables_list):
loader = OurTableLoader()
self._our_tables_list = \
[loader.createTable(name, loader.loadUnprocessed(path)) for name, path in our_tables_list]
loader = TableLoader()
self._article_tables_list = [loader.createTable(name, loader.load(path)) for name, path in article_tables_list]
def generate_table_old(table_path, name, is_our_table=False):
if not is_our_table:
loader = TableLoader()
table = loader.createTable(name, loader.load(table_path))
else:
loader = OurTableLoader()
table = loader.createTable(name, loader.loadUnprocessed(table_path))
db = MySQLdb.connect(host="localhost",
user="******",
db="article_refactor_24_3_2016")
cur = db.cursor(MySQLdb.cursors.DictCursor)
# Generate the keys for the table
id_keys = [
TableGlobals.FIRST_START_BASE_KEY, TableGlobals.FIRST_END_BASE_KEY,
TableGlobals.FIRST_STRAND_KEY, TableGlobals.SECOND_START_BASE_KEY,
TableGlobals.SECOND_END_BASE_KEY, TableGlobals.SECOND_STRAND_KEY
]
for key in table._dctData.values()[0].keys():
if key not in id_keys:
id_keys.append(key)
fields = ", ".join("%s VARCHAR(200)" % key.replace(" ", "_").replace(
"-", "_").replace("'", "").replace("/", "") for key in id_keys
if key != "")
print fields
cur.execute("CREATE TABLE %s (%s)" % (table.get_name(), fields))
table_as_list = []
# Generate dictionary for each row according to the keys
for key, value in table:
id_values = key.split(Table.ID_DELIMITER)
for id_key, id_val in zip(id_keys, id_values):
value[id_key] = str(id_val)
table_as_list.append(value)
# print "-->", value
# Go over the rows and add them to the db
for row in table_as_list:
values = ",".join("%s" % db.literal(str(row[key])) for key in id_keys
if key != "")
# print "INSERT INTO %s VALUES (%s)" % (table.get_name(), values)
cur.execute("INSERT INTO %s VALUES (%s)" % (table.get_name(), values))
db.commit()
