mysqldb¶
Functions to interact with MySQL.
- pdm_utils.functions.mysqldb.change_version(engine, amount=1)¶
Change the database version number.
- Parameters
engine (Engine) – SQLAlchemy Engine object able to connect to a MySQL database.
amount (int) – Amount to increment/decrement version number.
- pdm_utils.functions.mysqldb.check_schema_compatibility(engine, pipeline, code_version=None)¶
Confirm database schema is compatible with code.
If schema version is not compatible, sys.exit is called.
- Parameters
engine (Engine) – SQLAlchemy Engine object able to connect to a MySQL database.
pipeline (str) – Description of the pipeline checking compatibility.
code_version (int) – Schema version on which the pipeline operates. If no schema version is provided, the package-wide schema version value is used.
- pdm_utils.functions.mysqldb.create_delete(table, field, data)¶
Create MySQL DELETE statement.
“‘DELETE FROM <table> WHERE <field> = ‘<data>’.”
- Parameters
table (str) – The database table to insert information.
field (str) – The column upon which the statement is conditioned.
data (str) – The value of ‘field’ upon which the statement is conditioned.
- Returns
A MySQL DELETE statement.
- Return type
str
- pdm_utils.functions.mysqldb.create_gene_table_insert(cds_ftr)¶
Create a MySQL gene table INSERT statement.
- Parameters
cds_ftr (Cds) – A pdm_utils Cds object.
- Returns
A MySQL statement to INSERT a new row in the ‘gene’ table with data for several fields.
- Return type
str
- pdm_utils.functions.mysqldb.create_genome_statements(gnm, tkt_type='')¶
Create list of MySQL statements based on the ticket type.
- Parameters
gnm (Genome) – A pdm_utils Genome object.
tkt_type (str) – ‘add’ or ‘replace’.
- Returns
List of MySQL statements to INSERT all data from a genome into the database (DELETE FROM genome, INSERT INTO phage, INSERT INTO gene, …).
- Return type
list
- pdm_utils.functions.mysqldb.create_phage_table_insert(gnm)¶
Create a MySQL phage table INSERT statement.
- Parameters
gnm (Genome) – A pdm_utils Genome object.
- Returns
A MySQL statement to INSERT a new row in the ‘phage’ table with data for several fields.
- Return type
str
- pdm_utils.functions.mysqldb.create_seq_set(engine)¶
Create set of genome sequences currently in a MySQL database.
- Parameters
engine (Engine) – SQLAlchemy Engine object able to connect to a MySQL database.
- Returns
A set of unique values from phage.Sequence.
- Return type
set
- pdm_utils.functions.mysqldb.create_tmrna_table_insert(tmrna_ftr)¶
- Parameters
tmrna_ftr –
- Returns
- pdm_utils.functions.mysqldb.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
- pdm_utils.functions.mysqldb.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.
- Parameters
table (str) – The database table to insert information.
field1 (str) – The column upon which the statement is conditioned.
value1 (str) – The value of ‘field1’ upon which the statement is conditioned.
field2 (str) – The column that will be updated.
value2 (str) – The value that will be inserted into ‘field2’.
- Returns
A MySQL UPDATE statement.
- Return type
set
- pdm_utils.functions.mysqldb.execute_transaction(engine, statement_list=[])¶
Execute list of MySQL statements within a single defined transaction.
- Parameters
engine (Engine) – SQLAlchemy Engine object able to connect to a MySQL databas.
statement_list – a list of any number of MySQL statements with no expectation that anything will return
- Returns
tuple (result, message) WHERE result (int) is 0 or 1 status code. 0 means no problems, 1 means problems message(str) is a description of the result.
- Return type
tuple
- pdm_utils.functions.mysqldb.get_schema_version(engine)¶
Identify the schema version of the database_versions_list.
Schema version data has not been persisted in every schema version, so if schema version data is not found, it is deduced from other parts of the schema.
- Parameters
engine (Engine) – SQLAlchemy Engine object able to connect to a MySQL database.
- Returns
The version of the pdm_utils database schema.
- Return type
int
- pdm_utils.functions.mysqldb.parse_feature_data(engine, ftr_type, column=None, phage_id_list=None, query=None)¶
Returns Cds objects containing data parsed from a MySQL database.
- Parameters
engine (Engine) – This parameter is passed directly to the ‘retrieve_data’ function.
query (str) – This parameter is passed directly to the ‘retrieve_data’ function.
ftr_type (str) – Indicates the type of features retrieved.
column (str) – This parameter is passed directly to the ‘retrieve_data’ function.
phage_id_list (list) – This parameter is passed directly to the ‘retrieve_data’ function.
- Returns
A list of pdm_utils Cds objects.
- Return type
list
- pdm_utils.functions.mysqldb.parse_gene_table_data(data_dict, trans_table=11)¶
Parse a MySQL database dictionary to create a Cds object.
- Parameters
data_dict (dict) – Dictionary of data retrieved from the gene table.
trans_table (int) – The translation table that can be used to translate CDS features.
- Returns
A pdm_utils Cds object.
- Return type
- pdm_utils.functions.mysqldb.parse_genome_data(engine, phage_id_list=None, phage_query=None, gene_query=None, trna_query=None, tmrna_query=None, gnm_type='')¶
Returns a list of Genome objects containing data parsed from a MySQL database.
- Parameters
engine (Engine) – This parameter is passed directly to the ‘retrieve_data’ function.
phage_query (str) – This parameter is passed directly to the ‘retrieve_data’ function to retrieve data from the phage table.
gene_query (str) – This parameter is passed directly to the ‘parse_feature_data’ function to retrieve data from the gene table. If not None, pdm_utils Cds objects for all of the phage’s CDS features in the gene table will be constructed and added to the Genome object.
trna_query (str) – This parameter is passed directly to the ‘parse_feature_data’ function to retrieve data from the trna table. If not None, pdm_utils Trna objects for all of the phage’s tRNA features in the trna table will be constructed and added to the Genome object.
tmrna_query (str) – This parameter is passed directly to the ‘parse_feature_data’ function to retrieve data from the tmrna table. If not None, pdm_utils Tmrna objects for all of the phage’s tmRNA features in the tmrna table will be constructed and added to the Genome object.
phage_id_list (list) – This parameter is passed directly to the ‘retrieve_data’ function. If there is at at least one valid PhageID, a pdm_utils genome object will be constructed only for that phage. If None, or an empty list, genome objects for all phages in the database will be constructed.
gnm_type (str) – Identifier for the type of genome.
- Returns
A list of pdm_utils Genome objects.
- Return type
list
- pdm_utils.functions.mysqldb.parse_phage_table_data(data_dict, trans_table=11, gnm_type='')¶
Parse a MySQL database dictionary to create a Genome object.
- Parameters
data_dict (dict) – Dictionary of data retrieved from the phage table.
trans_table (int) – The translation table that can be used to translate CDS features.
gnm_type (str) – Identifier for the type of genome.
- Returns
A pdm_utils genome object.
- Return type
genome
- pdm_utils.functions.mysqldb.parse_tmrna_table_data(data_dict)¶
Parse a MySQL database dictionary to create a Tmrna object.
- Parameters
data_dict (dict) – Dictionary of data retrieved from the gene table.
- Returns
A pdm_utils Tmrna object.
- Return type