本文整理汇总了Python中qiita_db.sql_connection.TRN.execute_fetchlast方法的典型用法代码示例。如果您正苦于以下问题:Python TRN.execute_fetchlast方法的具体用法?Python TRN.execute_fetchlast怎么用?Python TRN.execute_fetchlast使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类qiita_db.sql_connection.TRN
的用法示例。
在下文中一共展示了TRN.execute_fetchlast方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: exists
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def exists(cls, sample_id, md_template):
r"""Checks if already exists a MetadataTemplate for the provided object
Parameters
----------
sample_id : str
The sample id
md_template : MetadataTemplate
The metadata template to which the sample belongs to
Returns
-------
bool
True if already exists. False otherwise.
"""
with TRN:
cls._check_subclass()
sql = """SELECT EXISTS(
SELECT * FROM qiita.{0}
WHERE sample_id=%s AND {1}=%s
)""".format(
cls._table, cls._id_column
)
TRN.add(sql, [sample_id, md_template.id])
return TRN.execute_fetchlast()
示例2: _check_id
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def _check_id(self, id_):
r"""Checks that the MetadataTemplate id_ exists on the database"""
with TRN:
sql = "SELECT EXISTS(SELECT * FROM qiita.{0} WHERE {1}=%s)".format(
self._table, self._id_column)
TRN.add(sql, [id_])
return TRN.execute_fetchlast()
示例3: __getitem__
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def __getitem__(self, key):
r"""Returns the value of the metadata category `key`
Parameters
----------
key : str
The metadata category
Returns
-------
obj
The value of the metadata category `key`
Raises
------
KeyError
If the metadata category `key` does not exists
See Also
--------
get
"""
with TRN:
key = key.lower()
if key not in self._get_categories():
# The key is not available for the sample, so raise a KeyError
raise KeyError(
"Metadata category %s does not exists for sample %s"
" in template %d" % (key, self._id, self._md_template.id))
sql = """SELECT {0} FROM qiita.{1}
WHERE sample_id=%s""".format(key, self._dynamic_table)
TRN.add(sql, [self._id])
return TRN.execute_fetchlast()
示例4: update_category
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [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
示例5: tester
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def tester():
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
sql = """SELECT EXISTS(
SELECT * FROM qiita.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
self.assertEqual(TRN._contexts_entered, 1)
示例6: test_execute_fetchlast
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def test_execute_fetchlast(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)
sql = """SELECT EXISTS(
SELECT * FROM qiita.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
示例7: preprocessing_status
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def preprocessing_status(self):
r"""Tells if the data has been preprocessed or not
Returns
-------
str
One of {'not_preprocessed', 'preprocessing', 'success', 'failed'}
"""
with TRN:
sql = """SELECT preprocessing_status FROM qiita.prep_template
WHERE {0}=%s""".format(self._id_column)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
示例8: study_id
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def study_id(self):
"""Gets the study id with which this prep template is associated
Returns
-------
int
The ID of the study with which this prep template is associated
"""
with TRN:
sql = """SELECT study_id FROM qiita.study_prep_template
WHERE prep_template_id=%s"""
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
示例9: transfer_file_to_artifact
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def transfer_file_to_artifact(analysis_id, a_timestamp, command_id,
data_type_id, params, artifact_type_id,
filepath_id):
"""Creates a new artifact with the given filepath id
Parameters
----------
analysis_id : int
The analysis id to attach the artifact
a_timestamp : datetime.datetime
The generated timestamp of the artifact
command_id : int
The command id of the artifact
data_type_id : int
The data type id of the artifact
params : str
The parameters of the artifact
artifact_type_id : int
The artifact type
filepath_id : int
The filepath id
Returns
-------
int
The artifact id
"""
with TRN:
# Add the row in the artifact table
# Magic number 4: Visibility -> sandbox
sql = """INSERT INTO qiita.artifact
(generated_timestamp, command_id, data_type_id,
command_parameters, visibility_id, artifact_type_id,
submitted_to_vamps)
VALUES (%s, %s, %s, %s, %s, %s, %s)
RETURNING artifact_id"""
TRN.add(sql, [a_timestamp, command_id, data_type_id, params, 4,
artifact_type_id, False])
artifact_id = TRN.execute_fetchlast()
# Link the artifact with its file
sql = """INSERT INTO qiita.artifact_filepath (artifact_id, filepath_id)
VALUES (%s, %s)"""
TRN.add(sql, [artifact_id, filepath_id])
# Link the artifact with the analysis
sql = """INSERT INTO qiita.analysis_artifact
(analysis_id, artifact_id)
VALUES (%s, %s)"""
TRN.add(sql, [analysis_id, artifact_id])
return artifact_id
示例10: delete
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [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()
示例11: raw_data
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [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()
示例12: __setitem__
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [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
示例13: create_rarefaction_job
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def create_rarefaction_job(depth, biom_artifact_id, analysis, srare_cmd_id):
"""Create a new rarefaction job
Parameters
----------
depth : int
The rarefaction depth
biom_artifact_id : int
The artifact id of the input rarefaction biom table
analysis : dict
Dictionary with the analysis information
srare_cmd_id : int
The command id of the single rarefaction command
Returns
-------
job_id : str
The job id
params : str
The job parameters
"""
# Add the row in the procesisng job table
params = ('{"depth":%d,"subsample_multinomial":false,"biom_table":%s}'
% (depth, biom_artifact_id))
with TRN:
# magic number 3: status -> success
sql = """INSERT INTO qiita.processing_job
(email, command_id, command_parameters,
processing_job_status_id)
VALUES (%s, %s, %s, %s)
RETURNING processing_job_id"""
TRN.add(sql, [analysis['email'], srare_cmd_id, params, 3])
job_id = TRN.execute_fetchlast()
# Step 1.2.b: Link the job with the input artifact
sql = """INSERT INTO qiita.artifact_processing_job
(artifact_id, processing_job_id)
VALUES (%s, %s)"""
TRN.add(sql, [biom_artifact_id, job_id])
TRN.execute()
return job_id, params
示例14: data_type
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def data_type(self, ret_id=False):
"""Returns the data_type or the data_type id
Parameters
----------
ret_id : bool, optional
If true, return the id instead of the string, default false.
Returns
-------
str or int
string value of data_type or data_type_id if ret_id is True
"""
with TRN:
ret = "_id" if ret_id else ""
sql = """SELECT d.data_type{0}
FROM qiita.data_type d
JOIN qiita.prep_template p
ON p.data_type_id = d.data_type_id
WHERE p.prep_template_id=%s""".format(ret)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
示例15: create_non_rarefied_biom_artifact
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def create_non_rarefied_biom_artifact(analysis, biom_data, rarefied_table):
"""Creates the initial non-rarefied BIOM artifact of the analysis
Parameters
----------
analysis : dict
Dictionary with the analysis information
biom_data : dict
Dictionary with the biom file information
rarefied_table : biom.Table
The rarefied BIOM table
Returns
-------
int
The id of the new artifact
"""
# The non rarefied biom artifact is the initial biom table of the analysis.
# This table does not currently exist anywhere, so we need to actually
# create the BIOM file. To create this BIOM file we need: (1) the samples
# and artifacts they come from and (2) whether the samples where
# renamed or not. (1) is on the database, but we need to inferr (2) from
# the existing rarefied BIOM table. Fun, fun...
with TRN:
# Get the samples included in the BIOM table grouped by artifact id
# Note that the analysis contains a BIOM table per data type included
# in it, and the table analysis_sample does not differentiate between
# datatypes, so we need to check the data type in the artifact table
sql = """SELECT artifact_id, array_agg(sample_id)
FROM qiita.analysis_sample
JOIN qiita.artifact USING (artifact_id)
WHERE analysis_id = %s AND data_type_id = %s
GROUP BY artifact_id"""
TRN.add(sql, [analysis['analysis_id'], biom_data['data_type_id']])
samples_by_artifact = TRN.execute_fetchindex()
# Create an empty BIOM table to be the new master table
new_table = Table([], [], [])
ids_map = {}
for a_id, samples in samples_by_artifact:
# Get the filepath of the BIOM table from the artifact
artifact = Artifact(a_id)
biom_fp = None
for _, fp, fp_type in artifact.filepaths:
if fp_type == 'biom':
biom_fp = fp
# Note that we are sure that the biom table exists for sure, so
# no need to check if biom_fp is undefined
biom_table = load_table(biom_fp)
samples = set(samples).intersection(biom_table.ids())
biom_table.filter(samples, axis='sample', inplace=True)
# we need to check if the table has samples left before merging
if biom_table.shape[0] != 0 and biom_table.shape[1] != 0:
new_table = new_table.merge(biom_table)
ids_map.update({sid: "%d.%s" % (a_id, sid)
for sid in biom_table.ids()})
# Check if we need to rename the sample ids in the biom table
new_table_ids = set(new_table.ids())
if not new_table_ids.issuperset(rarefied_table.ids()):
# We need to rename the sample ids
new_table.update_ids(ids_map, 'sample', True, True)
sql = """INSERT INTO qiita.artifact
(generated_timestamp, data_type_id, visibility_id,
artifact_type_id, submitted_to_vamps)
VALUES (%s, %s, %s, %s, %s)
RETURNING artifact_id"""
# Magic number 4 -> visibility sandbox
# Magix number 7 -> biom artifact type
TRN.add(sql, [analysis['timestamp'], biom_data['data_type_id'],
4, 7, False])
artifact_id = TRN.execute_fetchlast()
# Associate the artifact with the analysis
sql = """INSERT INTO qiita.analysis_artifact
(analysis_id, artifact_id)
VALUES (%s, %s)"""
TRN.add(sql, [analysis['analysis_id'], artifact_id])
# Link the artifact with its file
dd_id, mp = get_mountpoint('BIOM')[0]
dir_fp = join(get_db_files_base_dir(), mp, str(artifact_id))
if not exists(dir_fp):
makedirs(dir_fp)
new_table_fp = join(dir_fp, "biom_table.biom")
with biom_open(new_table_fp, 'w') as f:
new_table.to_hdf5(f, "Generated by Qiita")
sql = """INSERT INTO qiita.filepath
(filepath, filepath_type_id, checksum,
checksum_algorithm_id, data_directory_id)
VALUES (%s, %s, %s, %s, %s)
RETURNING filepath_id"""
# Magic number 7 -> filepath_type_id = 'biom'
# Magic number 1 -> the checksum algorithm id
TRN.add(sql, [basename(new_table_fp), 7,
compute_checksum(new_table_fp), 1, dd_id])
fp_id = TRN.execute_fetchlast()
sql = """INSERT INTO qiita.artifact_filepath
#.........这里部分代码省略.........