def create_phage_table_insert(gnm):
"""Create a MySQL phage table INSERT statement.
:param gnm: A pdm_utils Genome object.
:type gnm: Genome
:returns:
A MySQL statement to INSERT a new row in the 'phage' table
with data for several fields.
:rtype: str
"""
cluster = mysqldb_basic.convert_for_sql(gnm.cluster,
check_set={"Singleton"},
single=True)
subcluster = mysqldb_basic.convert_for_sql(gnm.subcluster,
check_set={"none"},
single=True)
# gnm.seq is a BioPython Seq object.
# It is coerced to string by default.
statement = ("""INSERT INTO phage """
"""(PhageID, Accession, Name, HostGenus, Sequence, """
"""Length, GC, Status, DateLastModified, RetrieveRecord, """
"""AnnotationAuthor, Cluster, Subcluster) """
"""VALUES """
"""('{}', '{}', '{}', '{}', '{}', {}, {}, '{}', """
"""'{}', {}, {}, {}, {});""")
statement = statement.format(
gnm.id, gnm.accession, gnm.name, gnm.host_genus, gnm.seq,
gnm.length, gnm.gc, gnm.annotation_status, gnm.date,
gnm.retrieve_record, gnm.annotation_author, cluster,
subcluster)
return statement
def create_tmrna_table_insert(tmrna_ftr):
"""
:param tmrna_ftr:
:return:
"""
geneid, phageid = tmrna_ftr.id, tmrna_ftr.genome_id
name = tmrna_ftr.name
locus_tag = mysqldb_basic.convert_for_sql(
tmrna_ftr.locus_tag, check_set={""}, single=False)
start, stop, length = tmrna_ftr.start, tmrna_ftr.stop, tmrna_ftr.length
orientation = tmrna_ftr.orientation
note = mysqldb_basic.convert_for_sql(
tmrna_ftr.note, check_set={""}, single=False)
peptide_tag = mysqldb_basic.convert_for_sql(
tmrna_ftr.peptide_tag, check_set={""}, single=False)
statement = ("""INSERT INTO tmrna """
"""(GeneID, PhageID, Start, Stop, Length, """
"""Name, Orientation, Note, LocusTag, PeptideTag) VALUES """
"""("{}", "{}", {}, {}, {}, "{}", "{}", {}, {}, {});""")
statement = statement.format(geneid, phageid, start, stop, length,
name, orientation, note, locus_tag,
peptide_tag)
return statement
def create_trna_table_insert(trna_ftr):
"""
Create a MySQL trna table INSERT statement.
:param trna_ftr: a pdm_utils Trna object
:type trna_ftr: Trna
:returns: a MySQL statement to INSERT a new row in the 'trna' table
with all of trna_ftr's relevant data
:rtype: str
"""
# Any values that could be none should be run through mysqldb_basic's
# convert_for_sql method so that the statement is does not cause errors
# when evaluated
geneid, phageid = trna_ftr.id, trna_ftr.genome_id
name = trna_ftr.name
locus_tag = mysqldb_basic.convert_for_sql(
trna_ftr.locus_tag, check_set={""}, single=False)
start, stop, length = trna_ftr.start, trna_ftr.stop, trna_ftr.length
orientation = trna_ftr.orientation
note = mysqldb_basic.convert_for_sql(
trna_ftr.note, check_set={""}, single=False)
amino_acid, anticodon = trna_ftr.amino_acid, trna_ftr.anticodon
structure = mysqldb_basic.convert_for_sql(
trna_ftr.structure, check_set={""}, single=False)
source = mysqldb_basic.convert_for_sql(
trna_ftr.use, check_set={None}, single=False)
statement = ("""INSERT INTO trna """
"""(GeneID, PhageID, Start, Stop, Length, """
"""Name, Orientation, Note, LocusTag, AminoAcid, Anticodon, """
"""Structure, Source) VALUES ("{}", "{}", {}, {}, {}, """
""""{}", "{}", {}, {}, "{}", "{}", {}, {});""")
statement = statement.format(geneid, phageid, start, stop, length,
name, orientation, note, locus_tag,
amino_acid, anticodon, structure, source)
return statement
def create_gene_table_insert(cds_ftr):
"""Create a MySQL gene table INSERT statement.
:param cds_ftr: A pdm_utils Cds object.
:type cds_ftr: Cds
:returns:
A MySQL statement to INSERT a new row in the 'gene' table
with data for several fields.
:rtype: str
"""
locus_tag = mysqldb_basic.convert_for_sql(cds_ftr.locus_tag,
check_set={""}, single=False)
# cds_ftr.translation is a BioPython Seq object.
# It is coerced to string by default.
# Statement should be triple quoted.
# Descriptions may contain a "'", so at least description attribute needs
# to be encapsulated with double quotes.
# locus_tag can be NULL or can be a string, so it should not be
# encapsulated with '"'.
statement = ("""INSERT INTO gene """
"""(GeneID, PhageID, Start, Stop, Length, Name, """
"""Translation, Orientation, Notes, LocusTag, Parts) """
"""VALUES """
"""("{}", "{}", {}, {}, {}, "{}", "{}", "{}", "{}", {}, {});""")
statement = statement.format(cds_ftr.id, cds_ftr.genome_id,
cds_ftr.start, cds_ftr.stop,
cds_ftr.length,
cds_ftr.name, cds_ftr.translation,
cds_ftr.orientation, cds_ftr.description,
locus_tag, cds_ftr.parts)
return statement
def create_update(table, field2, value2, field1, value1):
"""Create MySQL UPDATE statement.
"'UPDATE <table> SET <field2> = '<value2' WHERE <field1> = '<data1>'."
When the new value to be added is 'singleton' (e.g. for Cluster
fields), or an empty value (e.g. None, "none", etc.),
the new value is set to NULL.
:param table: The database table to insert information.
:type table: str
:param field1: The column upon which the statement is conditioned.
:type field1: str
:param value1:
The value of 'field1' upon which the statement is conditioned.
:type value1: str
:param field2: The column that will be updated.
:type field2: str
:param value2:
The value that will be inserted into 'field2'.
:type value2: str
:returns: A MySQL UPDATE statement.
:rtype: set
"""
check_set = constants.EMPTY_SET | {"Singleton"}
part1 = f"UPDATE {table} SET {field2} = "
part3 = f" WHERE {field1} = '{value1}';"
part2 = mysqldb_basic.convert_for_sql(value2, check_set=check_set, single=True)
statement = part1 + part2 + part3
return statement
def test_convert_for_sql_4(self):
"""Verify 'Singleton' value returned is NULL."""
value = mysqldb_basic.convert_for_sql(
"Singleton", check_set={"Singleton"}, single=True)
self.assertEqual(value, "NULL")
def test_convert_for_sql_3(self):
"""Verify empty value returned is NULL."""
value = mysqldb_basic.convert_for_sql("", check_set={""}, single=True)
self.assertEqual(value, "NULL")
def test_convert_for_sql_2(self):
"""Verify non-empty value returned is encapsulated with '"'."""
value = mysqldb_basic.convert_for_sql(
"A", check_set={"Singleton"}, single=False)
self.assertEqual(value, '"A"')
def test_convert_for_sql_1(self):
"""Verify non-empty value returned is encapsulated with "'"."""
value = mysqldb_basic.convert_for_sql(
"A", check_set={"Singleton"}, single=True)
self.assertEqual(value, "'A'")