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'")