本文整理汇总了Python中qiita_db.sql_connection.TRN.execute方法的典型用法代码示例。如果您正苦于以下问题:Python TRN.execute方法的具体用法?Python TRN.execute怎么用?Python TRN.execute使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类qiita_db.sql_connection.TRN
的用法示例。
在下文中一共展示了TRN.execute方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: test_delete_study
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def test_delete_study(self):
# as samples have been submitted to EBI, this will fail
job = self._create_job('delete_study', {'study': 1})
private_task(job.id)
self.assertEqual(job.status, 'error')
self.assertIn("Cannot delete artifact 2: it has been "
"submitted to EBI", job.log.msg)
# delete everything from the EBI submissions and the processing job so
# we can try again: test success (with tags)
with TRN:
sql = """DELETE FROM qiita.ebi_run_accession"""
TRN.add(sql)
sql = """DELETE FROM qiita.artifact_processing_job"""
TRN.add(sql)
TRN.execute()
# adding tags
Study(1).update_tags(self.user, ['my new tag!'])
job = self._create_job('delete_study', {'study': 1})
private_task(job.id)
self.assertEqual(job.status, 'success')
with self.assertRaises(QiitaDBUnknownIDError):
Study(1)
示例2: _common_creation_steps
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def _common_creation_steps(cls, md_template, obj_id):
r"""Executes the common creation steps
Parameters
----------
md_template : DataFrame
The metadata template file contents indexed by sample ids
obj_id : int
The id of the object being created
"""
with TRN:
cls._check_subclass()
# Get some useful information from the metadata template
sample_ids = md_template.index.tolist()
headers = sorted(md_template.keys().tolist())
# Insert values on template_sample table
values = [[obj_id, s_id] for s_id in sample_ids]
sql = """INSERT INTO qiita.{0} ({1}, sample_id)
VALUES (%s, %s)""".format(cls._table, cls._id_column)
TRN.add(sql, values, many=True)
# Insert rows on *_columns table
datatypes = get_datatypes(md_template.ix[:, headers])
# psycopg2 requires a list of tuples, in which each tuple is a set
# of values to use in the string formatting of the query. We have
# all the values in different lists (but in the same order) so use
# zip to create the list of tuples that psycopg2 requires.
values = [[obj_id, h, d] for h, d in zip(headers, datatypes)]
sql = """INSERT INTO qiita.{0} ({1}, column_name, column_type)
VALUES (%s, %s, %s)""".format(cls._column_table,
cls._id_column)
TRN.add(sql, values, many=True)
# Create table with custom columns
table_name = cls._table_name(obj_id)
column_datatype = ["%s %s" % (col, dtype)
for col, dtype in zip(headers, datatypes)]
sql = """CREATE TABLE qiita.{0} (
sample_id varchar NOT NULL, {1},
CONSTRAINT fk_{0} FOREIGN KEY (sample_id)
REFERENCES qiita.study_sample (sample_id)
ON UPDATE CASCADE
)""".format(table_name, ', '.join(column_datatype))
TRN.add(sql)
# Insert values on custom table
values = as_python_types(md_template, headers)
values.insert(0, sample_ids)
values = [list(v) for v in zip(*values)]
sql = """INSERT INTO qiita.{0} (sample_id, {1})
VALUES (%s, {2})""".format(
table_name, ", ".join(headers),
', '.join(["%s"] * len(headers)))
TRN.add(sql, values, many=True)
# Execute all the steps
TRN.execute()
示例3: update_category
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def update_category(self, category, samples_and_values):
"""Update an existing column
Parameters
----------
category : str
The category to update
samples_and_values : dict
A mapping of {sample_id: value}
Raises
------
QiitaDBUnknownIDError
If a sample_id is included in values that is not in the template
QiitaDBColumnError
If the column does not exist in the table. This is implicit, and
can be thrown by the contained Samples.
ValueError
If one of the new values cannot be inserted in the DB due to
different types
"""
with TRN:
if not set(self.keys()).issuperset(samples_and_values):
missing = set(self.keys()) - set(samples_and_values)
table_name = self._table_name(self._id)
raise QiitaDBUnknownIDError(missing, table_name)
for k, v in viewitems(samples_and_values):
sample = self[k]
sample.setitem(category, v)
try:
TRN.execute()
except ValueError as e:
# catching error so we can check if the error is due to
# different column type or something else
value_types = set(type_lookup(type(value)) for value in viewvalues(samples_and_values))
sql = """SELECT udt_name
FROM information_schema.columns
WHERE column_name = %s
AND table_schema = 'qiita'
AND (table_name = %s OR table_name = %s)"""
TRN.add(sql, [category, self._table, self._table_name(self._id)])
column_type = TRN.execute_fetchlast()
if any([column_type != vt for vt in value_types]):
value_str = ", ".join([str(value) for value in viewvalues(samples_and_values)])
value_types_str = ", ".join(value_types)
raise ValueError(
'The new values being added to column: "%s" are "%s" '
'(types: "%s"). However, this column in the DB is of '
'type "%s". Please change the values in your updated '
"template or reprocess your template." % (category, value_str, value_types_str, column_type)
)
raise e
示例4: _update_accession_numbers
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def _update_accession_numbers(self, column, values):
"""Update accession numbers stored in `column` with the ones in `values`
Parameters
----------
column : str
The column name where the accession number are stored
values : dict of {str: str}
The accession numbers keyed by sample id
Raises
------
QiitaDBError
If a sample in `values` already has an accession number
QiitaDBWarning
If `values` is not updating any accesion number
"""
with TRN:
sql = """SELECT sample_id, {0}
FROM qiita.{1}
WHERE {2}=%s
AND {0} IS NOT NULL""".format(column, self._table,
self._id_column)
TRN.add(sql, [self.id])
db_vals = {sample_id: accession
for sample_id, accession in TRN.execute_fetchindex()}
common_samples = set(db_vals) & set(values)
diff = [sample for sample in common_samples
if db_vals[sample] != values[sample]]
if diff:
raise QiitaDBError(
"The following samples already have an accession number: "
"%s" % ', '.join(diff))
# Remove the common samples form the values dictionary
values = deepcopy(values)
for sample in common_samples:
del values[sample]
if values:
sql_vals = ', '.join(["(%s, %s)"] * len(values))
sql = """UPDATE qiita.{0} AS t
SET {1}=c.{1}
FROM (VALUES {2}) AS c(sample_id, {1})
WHERE c.sample_id = t.sample_id
AND t.{3} = %s
""".format(self._table, column, sql_vals,
self._id_column)
sql_vals = list(chain.from_iterable(values.items()))
sql_vals.append(self.id)
TRN.add(sql, sql_vals)
TRN.execute()
else:
warnings.warn("No new accession numbers to update",
QiitaDBWarning)
示例5: test_context_manager_no_commit
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def test_context_manager_no_commit(self):
with TRN:
sql = """INSERT INTO qiita.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [["insert1", 1], ["insert2", 2], ["insert3", 3]]
TRN.add(sql, args, many=True)
TRN.execute()
self._assert_sql_equal([])
self._assert_sql_equal([("insert1", True, 1), ("insert2", True, 2), ("insert3", True, 3)])
self.assertEqual(TRN._connection.get_transaction_status(), TRANSACTION_STATUS_IDLE)
示例6: test_context_manager_rollback
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def test_context_manager_rollback(self):
try:
with TRN:
sql = """INSERT INTO qiita.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [["insert1", 1], ["insert2", 2], ["insert3", 3]]
TRN.add(sql, args, many=True)
TRN.execute()
raise ValueError("Force exiting the context manager")
except ValueError:
pass
self._assert_sql_equal([])
self.assertEqual(TRN._connection.get_transaction_status(), TRANSACTION_STATUS_IDLE)
示例7: delete
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def delete(cls, id_):
r"""Deletes the table from the database
Parameters
----------
id_ : integer
The object identifier
Raises
------
QiitaDBUnknownIDError
If no sample template with id id_ exists
QiitaDBError
If the study that owns this sample template has raw datas
"""
with TRN:
cls._check_subclass()
if not cls.exists(id_):
raise QiitaDBUnknownIDError(id_, cls.__name__)
# Check if there is any PrepTemplate
sql = """SELECT EXISTS(SELECT * FROM qiita.study_prep_template
WHERE study_id=%s)"""
TRN.add(sql, [id_])
has_prep_templates = TRN.execute_fetchlast()
if has_prep_templates:
raise QiitaDBError("Sample template can not be erased because "
"there are prep templates associated.")
table_name = cls._table_name(id_)
# Delete the sample template filepaths
sql = """DELETE FROM qiita.sample_template_filepath
WHERE study_id = %s"""
args = [id_]
TRN.add(sql, args)
TRN.add("DROP TABLE qiita.{0}".format(table_name))
sql = "DELETE FROM qiita.{0} WHERE {1} = %s".format(
cls._table, cls._id_column)
TRN.add(sql, args)
sql = "DELETE FROM qiita.{0} WHERE {1} = %s".format(
cls._column_table, cls._id_column)
TRN.add(sql, args)
TRN.execute()
示例8: add_filepath
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def add_filepath(self, filepath, fp_id=None):
r"""Populates the DB tables for storing the filepath and connects the
`self` objects with this filepath"""
with TRN:
fp_id = self._fp_id if fp_id is None else fp_id
try:
fpp_id = insert_filepaths([(filepath, fp_id)], None, "templates", "filepath", move_files=False)[0]
sql = """INSERT INTO qiita.{0} ({1}, filepath_id)
VALUES (%s, %s)""".format(
self._filepath_table, self._id_column
)
TRN.add(sql, [self._id, fpp_id])
TRN.execute()
except Exception as e:
LogEntry.create("Runtime", str(e), info={self.__class__.__name__: self.id})
raise e
示例9: raw_data
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def raw_data(self, raw_data):
with TRN:
sql = """SELECT (
SELECT raw_data_id
FROM qiita.prep_template
WHERE prep_template_id=%s)
IS NOT NULL"""
TRN.add(sql, [self.id])
exists = TRN.execute_fetchlast()
if exists:
raise QiitaDBError(
"Prep template %d already has a raw data associated"
% self.id)
sql = """UPDATE qiita.prep_template
SET raw_data_id = %s
WHERE prep_template_id = %s"""
TRN.add(sql, [raw_data.id, self.id])
TRN.execute()
示例10: test_context_manager_checker
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def test_context_manager_checker(self):
with self.assertRaises(RuntimeError):
TRN.add("SELECT 42")
with self.assertRaises(RuntimeError):
TRN.execute()
with self.assertRaises(RuntimeError):
TRN.commit()
with self.assertRaises(RuntimeError):
TRN.rollback()
with TRN:
TRN.add("SELECT 42")
with self.assertRaises(RuntimeError):
TRN.execute()
示例11: test_index
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def test_index(self):
with TRN:
self.assertEqual(TRN.index, 0)
TRN.add("SELECT 42")
self.assertEqual(TRN.index, 1)
sql = "INSERT INTO qiita.test_table (int_column) VALUES (%s)"
args = [[1], [2], [3]]
TRN.add(sql, args, many=True)
self.assertEqual(TRN.index, 4)
TRN.execute()
self.assertEqual(TRN.index, 4)
TRN.add(sql, args, many=True)
self.assertEqual(TRN.index, 7)
self.assertEqual(TRN.index, 0)
示例12: test_execute_return
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def test_execute_return(self):
with TRN:
sql = """INSERT INTO qiita.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
TRN.add(sql, ["test_insert", 2])
sql = """UPDATE qiita.test_table SET bool_column = %s
WHERE str_column = %s RETURNING int_column"""
TRN.add(sql, [False, "test_insert"])
obs = TRN.execute()
self.assertEqual(obs, [[["test_insert", 2]], [[2]]])
示例13: __setitem__
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def __setitem__(self, column, value):
r"""Sets the metadata value for the category `column`
Parameters
----------
column : str
The column to update
value : str
The value to set. This is expected to be a str on the assumption
that psycopg2 will cast as necessary when updating.
Raises
------
ValueError
If the value type does not match the one in the DB
"""
with TRN:
self.setitem(column, value)
try:
TRN.execute()
except ValueError as e:
# catching error so we can check if the error is due to
# different column type or something else
value_type = type_lookup(type(value))
sql = """SELECT udt_name
FROM information_schema.columns
WHERE column_name = %s
AND table_schema = 'qiita'
AND (table_name = %s OR table_name = %s)"""
TRN.add(sql, [column, self._table, self._dynamic_table])
column_type = TRN.execute_fetchlast()
if column_type != value_type:
raise ValueError(
'The new value being added to column: "{0}" is "{1}" '
'(type: "{2}"). However, this column in the DB is of '
'type "{3}". Please change the value in your updated '
'template or reprocess your template.'.format(
column, value, value_type, column_type))
raise e
示例14: preprocessing_status
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def preprocessing_status(self, state):
r"""Update the preprocessing status
Parameters
----------
state : str, {'not_preprocessed', 'preprocessing', 'success', 'failed'}
The current status of preprocessing
Raises
------
ValueError
If the state is not known.
"""
if (state not in ('not_preprocessed', 'preprocessing', 'success') and
not state.startswith('failed:')):
raise ValueError('Unknown state: %s' % state)
with TRN:
sql = """UPDATE qiita.prep_template SET preprocessing_status = %s
WHERE {0} = %s""".format(self._id_column)
TRN.add(sql, [state, self.id])
TRN.execute()
示例15: investigation_type
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
def investigation_type(self, investigation_type):
r"""Update the investigation type
Parameters
----------
investigation_type : str
The investigation type to set, should be part of the ENA ontology
Raises
------
QiitaDBColumnError
If the investigation type is not a valid ENA ontology
"""
with TRN:
if investigation_type is not None:
self.validate_investigation_type(investigation_type)
sql = """UPDATE qiita.prep_template SET investigation_type = %s
WHERE {0} = %s""".format(self._id_column)
TRN.add(sql, [investigation_type, self.id])
TRN.execute()