def getGene_all():
"""Fetches gene information from the database
args:none
return:rows (tuple of dictionaries) as dictionary of Accession_No, Gene_Identifier, Chromosomal_Location, Organism, Sequence_Length, DNA_Sequence
"""
connection = dbconnection.getdbconnection()
sql = 'SELECT Accession_No, Gene_Identifier, Chromosomal_Location, Organism, Sequence_Length, DNA_Sequence FROM Gene'
cursor = connection.cursor()
cursor.execute(sql)
for row in cursor.fetchall():
yield row
connection.close()
def getProinfo_name(protein_name):
"""Fetches protein information from the database by protein name
args: protein_name (from user input)
return: row (tuple) with values of accession_No, Protein_id, Protein_name, Amino_acid_sequence
"""
connection = dbconnection.getdbconnection()
sql = 'SELECT Accession_No, Protein_id, Protein_name, Amino_acid_sequence from Protein WHERE Protein_name LIKE "%s%%" ' % protein_name
cursor = connection.cursor()
cursor.execute(sql)
for row in cursor.fetchall():
yield row
connection.close()
def getCodonUsage_chrom():
"""Fetches condon usage information of chromosome 12 from database
args: none
return: row (tuple) with values of Amacid, Codon, Number, per1000, Fraction
"""
connection = dbconnection.getdbconnection()
sql = 'SELECT Amacid, Codon, Number, per1000, Fraction from CodonUsage_per_chrom'
cursor = connection.cursor()
cursor.execute(sql)
for row in cursor.fetchall():
yield row
connection.close()
def getCodonUsage(acno):
"""Fetches condon usage per entry by accession number from the database
args: acno (accession number from user input)
return: row (tuple) with values of Amacid, Codon, Number, per1000, Fraction for the that entry
"""
connection = dbconnection.getdbconnection()
sql = 'SELECT Accession_No, Amacid, Codon, Number, per1000, Fraction From CodonUsage_per_Entry WHERE Accession_No = "%s" ' % (
acno)
cursor = connection.cursor()
cursor.execute(sql)
for row in cursor.fetchall():
yield row
connection.close()
def getGene_gi(gene_identifier):
"""
Fetches gene information from the database by gene identifier
args: gene_identifier (from user input)
return: rows (tuple of dictionaries) as dictionary of Accession_No, Gene_Identifier, Chromosomal_Location, Organism, Sequence_Length, DNA_Sequence
"""
connection = dbconnection.getdbconnection()
sql = 'SELECT Accession_No, Gene_Identifier, Chromosomal_Location, Organism, Sequence_Length, DNA_Sequence ' \
'FROM Gene WHERE Gene_Identifier = "%s" ' % gene_identifier
cursor = connection.cursor()
cursor.execute(sql)
for row in cursor.fetchall():
yield row
connection.close()
def getCDS():
"""Fetches CDS information join with protein id from the database
args: none
return: row (tuple) with values of Accession_No, Gene_Identifier,CDS_start, CDS_end, CDS_Sequence, Protein_id
"""
connection = dbconnection.getdbconnection()
sql = 'SELECT g.Accession_No, g.Gene_Identifier, g.CDS_start, g.CDS_end, g.CDS_Sequence, p.Protein_id '\
'FROM Gene g, Protein p '\
'WHERE g.Accession_No = p.Accession_No'
cursor = connection.cursor()
cursor.execute(sql)
for row in cursor.fetchall():
yield row
connection.close()
def getProtein_all():
"""Fetches protein information from Protein table and cds sequence from Gene table in the database
args: none
return: row (tuple) with values of Accession_No, Protein_id, Protein_name, Amino_acid_sequence, CDS_sequence
"""
connection = dbconnection.getdbconnection()
sql = 'SELECT p.Accession_No, p.Protein_id, p.Protein_name, p.Amino_acid_sequence, g.CDS_Sequence '\
'FROM Protein p, Gene g '\
'WHERE p.Accession_No = g.Accession_No'
cursor = connection.cursor()
cursor.execute(sql)
for row in cursor.fetchall():
yield row
connection.close()
def create_indexes(index_info):
"""this function is used by Genbank_parser.py to create indexes in the database
"""
connection = dbconnection.getdbconnection()
for table in index_info.keys():
sql = "ALTER TABLE %s ADD INDEX (%s)" % (table, ', '.join(
index_info[table][1]))
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
connection.close()
def getGene_proteinid():
"""Fetches gene information from Gene table and protein id from protein table in the database
args:none
return: rows (tuple of dictionaries) as dictionary of Accession_No, Gene_Identifier, Chromosomal_Location, Organism, Sequence_Length,
DNA_Sequence, Protein_id
"""
connection = dbconnection.getdbconnection()
sql = 'SELECT g.Accession_No, g.Gene_Identifier, g.Chromosomal_Location, g.Organism, g.Sequence_Length, g.DNA_Sequence, p.Protein_id '\
'FROM Gene g, Protein p '\
'WHERE g.Accession_No = p.Accession_No'
cursor = connection.cursor()
cursor.execute(sql)
for row in cursor.fetchall():
yield row
connection.close()
def fetch_one(table_name, select_columns, where_dict):
connection = dbconnection.getdbconnection()
sql = 'SELECT %s FROM %s WHERE ' % (", ".join(select_columns), table_name)
for key in where_dict.keys():
if type(where_dict.get(key)) == str:
sql += '%s = "%s" AND ' % (key, where_dict.get(key))
else:
sql += '%s = %s AND ' % (key, where_dict.get(key))
sql = sql[:-5]
cursor = connection.cursor()
cursor.execute(sql)
row = cursor.fetchone()
connection.close()
return row
def create_tables(table_names, columns_data_list):
"""this function is used by Genbank_parser.py to create tables in the database
"""
connection = dbconnection.getdbconnection()
for i in range(len(table_names)):
table_name = table_names[i]
columns_data = columns_data_list[i]
table_create_sql = "CREATE TABLE IF NOT EXISTS %s(" % table_name
for column_info in columns_data:
table_create_sql += " " + column_info[0] + " " + column_info[
1] + ","
table_create_sql = table_create_sql[:-1] + ")"
cursor = connection.cursor()
cursor.execute(table_create_sql)
connection.commit()
connection.close()
def insert_row(table_name, columns, values):
"""this function is used by Genbank_parser.py to insert information as row to table in the database
"""
connection = dbconnection.getdbconnection()
try:
sql = 'INSERT INTO %s (%s) VALUES (' % (table_name, ", ".join(columns))
for value in values:
if type(value) == str:
sql += '"%s"' % value + ", "
else:
sql += str(value) + ", "
sql = sql[:-2] + ")"
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
except pymysql.err.IntegrityError:
pass
connection.close()
