本文整理汇总了Python中qiita_db.sql_connection.TRN.execute_fetchindex方法的典型用法代码示例。如果您正苦于以下问题:Python TRN.execute_fetchindex方法的具体用法?Python TRN.execute_fetchindex怎么用?Python TRN.execute_fetchindex使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类qiita_db.sql_connection.TRN
的用法示例。
在下文中一共展示了TRN.execute_fetchindex方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: test_execute_fetchindex
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def test_execute_fetchindex(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)
self.assertEqual(TRN.execute_fetchindex(), [["insert3", 3]])
sql = """INSERT INTO qiita.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [["insert4", 4], ["insert5", 5], ["insert6", 6]]
TRN.add(sql, args, many=True)
self.assertEqual(TRN.execute_fetchindex(3), [["insert4", 4]])
示例2: status
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def status(self):
"""The status of the prep template
Returns
-------
str
The status of the prep template
Notes
-----
The status of a prep template is inferred by the status of the
processed data generated from this prep template. If no processed
data has been generated with this prep template; then the status
is 'sandbox'.
"""
with TRN:
sql = """SELECT processed_data_status
FROM qiita.processed_data_status pds
JOIN qiita.processed_data pd
USING (processed_data_status_id)
JOIN qiita.preprocessed_processed_data ppd_pd
USING (processed_data_id)
JOIN qiita.prep_template_preprocessed_data pt_ppd
USING (preprocessed_data_id)
WHERE pt_ppd.prep_template_id=%s"""
TRN.add(sql, [self._id])
return infer_status(TRN.execute_fetchindex())
示例3: update
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def update(self, md_template):
r"""Update values in the template
Parameters
----------
md_template : DataFrame
The metadata template file contents indexed by samples Ids
Raises
------
QiitaDBError
If md_template and db do not have the same sample ids
If md_template and db do not have the same column headers
If self.can_be_updated is not True
"""
with TRN:
# Clean and validate the metadata template given
new_map = self._clean_validate_template(md_template, self.study_id, self.columns_restrictions)
# Retrieving current metadata
sql = "SELECT * FROM qiita.{0}".format(self._table_name(self.id))
TRN.add(sql)
current_map = self._transform_to_dict(TRN.execute_fetchindex())
current_map = pd.DataFrame.from_dict(current_map, orient="index")
# simple validations of sample ids and column names
samples_diff = set(new_map.index).difference(current_map.index)
if samples_diff:
raise QiitaDBError(
"The new template differs from what is stored "
"in database by these samples names: %s" % ", ".join(samples_diff)
)
columns_diff = set(new_map.columns).difference(current_map.columns)
if columns_diff:
raise QiitaDBError(
"The new template differs from what is stored "
"in database by these columns names: %s" % ", ".join(columns_diff)
)
# here we are comparing two dataframes following:
# http://stackoverflow.com/a/17095620/4228285
current_map.sort(axis=0, inplace=True)
current_map.sort(axis=1, inplace=True)
new_map.sort(axis=0, inplace=True)
new_map.sort(axis=1, inplace=True)
map_diff = (current_map != new_map).stack()
map_diff = map_diff[map_diff]
map_diff.index.names = ["id", "column"]
changed_cols = map_diff.index.get_level_values("column").unique()
if not self.can_be_updated(columns=set(changed_cols)):
raise QiitaDBError(
"The new template is modifying fields that cannot be "
"modified. Try removing the target gene fields or "
"deleting the processed data. You are trying to modify: %s" % ", ".join(changed_cols)
)
for col in changed_cols:
self.update_category(col, new_map[col].to_dict())
self.generate_files()
示例4: _update_accession_numbers
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [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: raw_data
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def raw_data(self):
with TRN:
sql = """SELECT raw_data_id FROM qiita.prep_template
WHERE prep_template_id=%s"""
TRN.add(sql, [self.id])
result = TRN.execute_fetchindex()
if result:
# If there is any result, it will be in the first row
# and in the first element of that row, thus [0][0]
return result[0][0]
return None
示例6: __call__
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def __call__(self, searchstr, user):
"""Runs a Study query and returns matching studies and samples
Parameters
----------
searchstr : str
Search string to use
user : User object
User making the search. Needed for permissions checks.
Returns
-------
dict
Found samples in format
{study_id: [[samp_id1, meta1, meta2, ...],
[samp_id2, meta1, meta2, ...], ...}
list
metadata column names searched for
Notes
-----
Metadata information for each sample is in the same order as the
metadata columns list returned
Metadata column names and string searches are case-sensitive
"""
with TRN:
study_sql, sample_sql, meta_headers = \
self._parse_study_search_string(searchstr, True)
# get all studies containing the metadata headers requested
TRN.add(study_sql)
study_ids = set(TRN.execute_fetchflatten())
# strip to only studies user has access to
if user.level not in {'admin', 'dev', 'superuser'}:
study_ids = study_ids.intersection(
Study.get_by_status('public') | user.user_studies |
user.shared_studies)
results = {}
# run search on each study to get out the matching samples
for sid in study_ids:
TRN.add(sample_sql.format(sid))
study_res = TRN.execute_fetchindex()
if study_res:
# only add study to results if actually has samples
# in results
results[sid] = study_res
self.results = results
self.meta_headers = meta_headers
return results, meta_headers
示例7: _to_dict
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def _to_dict(self):
r"""Returns the categories and their values in a dictionary
Returns
-------
dict of {str: str}
A dictionary of the form {category: value}
"""
with TRN:
sql = "SELECT * FROM qiita.{0} WHERE sample_id=%s".format(self._dynamic_table)
TRN.add(sql, [self._id])
d = dict(TRN.execute_fetchindex()[0])
# Remove the sample_id, is not part of the metadata
del d["sample_id"]
return d
示例8: to_dataframe
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def to_dataframe(self):
"""Returns the metadata template as a dataframe
Returns
-------
pandas DataFrame
The metadata in the template,indexed on sample id
"""
with TRN:
cols = sorted(get_table_cols(self._table_name(self._id)))
# Get all metadata for the template
sql = "SELECT {0} FROM qiita.{1}".format(", ".join(cols), self._table_name(self.id))
TRN.add(sql, [self._id])
meta = TRN.execute_fetchindex()
# Create the dataframe and clean it up a bit
df = pd.DataFrame((list(x) for x in meta), columns=cols)
df.set_index("sample_id", inplace=True, drop=True)
return df
示例9: _get_accession_numbers
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def _get_accession_numbers(self, column):
"""Return the accession numbers stored in `column`
Parameters
----------
column : str
The column name where the accession number is stored
Returns
-------
dict of {str: str}
The accession numbers keyed by sample id
"""
with TRN:
sql = """SELECT sample_id, {0}
FROM qiita.{1}
WHERE {2}=%s""".format(column, self._table,
self._id_column)
TRN.add(sql, [self.id])
result = {sample_id: accession
for sample_id, accession in TRN.execute_fetchindex()}
return result
示例10: qiime_map_fp
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def qiime_map_fp(self):
"""The QIIME mapping filepath attached to the prep template
Returns
-------
str
The filepath of the QIIME mapping file
"""
with TRN:
sql = """SELECT filepath_id, filepath
FROM qiita.filepath
JOIN qiita.{0} USING (filepath_id)
JOIN qiita.filepath_type USING (filepath_type_id)
WHERE {1} = %s AND filepath_type = 'qiime_map'
ORDER BY filepath_id DESC""".format(self._filepath_table,
self._id_column)
TRN.add(sql, [self._id])
# We know that the good filepath is the one in the first row
# because we sorted them in the SQL query
fn = TRN.execute_fetchindex()[0][1]
base_dir = get_mountpoint('templates')[0][1]
return join(base_dir, fn)
示例11: get_filepaths
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def get_filepaths(self):
r"""Retrieves the list of (filepath_id, filepath)"""
with TRN:
try:
sql = """SELECT filepath_id, filepath
FROM qiita.filepath
WHERE filepath_id IN (
SELECT filepath_id FROM qiita.{0}
WHERE {1}=%s)
ORDER BY filepath_id DESC""".format(
self._filepath_table, self._id_column)
TRN.add(sql, [self.id])
filepath_ids = TRN.execute_fetchindex()
except Exception as e:
LogEntry.create('Runtime', str(e),
info={self.__class__.__name__: self.id})
raise e
_, fb = get_mountpoint('templates')[0]
base_fp = partial(join, fb)
return [(fpid, base_fp(fp)) for fpid, fp in filepath_ids]
示例12: array_agg
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
if cols_sample:
with TRN:
# a few notes: just getting the preps with duplicated values; ignoring
# column 'sample_id' and tables 'study_sample', 'prep_template',
# 'prep_template_sample'
sql = """SELECT table_name, array_agg(column_name::text)
FROM information_schema.columns
WHERE column_name IN %s
AND table_name LIKE 'sample_%%'
AND table_name NOT IN (
'prep_template', 'prep_template_sample')
GROUP BY table_name"""
# note that we are looking for those columns with duplicated names in
# the headers
TRN.add(sql, [tuple(set(cols_sample))])
for table, columns in viewitems(dict(TRN.execute_fetchindex())):
# [1] the format is table_# so taking the #
st = SampleTemplate(int(table.split('_')[1]))
# getting just the columns of interest
st_df = st.to_dataframe()[columns]
# converting to datetime
for col in columns:
st_df[col] = st_df[col].apply(transform_date)
st.update(st_df)
if cols_prep:
with TRN:
# a few notes: just getting the preps with duplicated values; ignoring
# column 'sample_id' and tables 'study_sample', 'prep_template',
# 'prep_template_sample'
sql = """SELECT table_name, array_agg(column_name::text)
示例13: zip
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
pc_update_sql = """UPDATE qiita.prep_columns
SET column_type = 'bool'
WHERE prep_template_id = %s AND column_name = %s"""
for table in tables:
table_id = table.split("_")[1]
# Change NaN values to NULL in database
TRN.add(cols_sql, [table])
cols = TRN.execute_fetchflatten()
for col in cols:
TRN.add(null_sql.format(table, col), [nans])
TRN.execute()
# Update now boolean columns to bool in database
TRN.add("SELECT {0} FROM qiita.{1}".format(",".join(cols), table))
col_vals = zip(*TRN.execute_fetchindex())
for col, vals in zip(cols, col_vals):
if set(vals) == {None}:
# Ignore columns that are all NULL
continue
if all([v in bool_vals for v in vals]):
# Every value in the column should be bool, so do it
TRN.add(alter_sql.format(table, col), [false_vals, true_vals])
if "sample" in table:
st_update.add(table_id)
TRN.add(ssc_update_sql, [table_id, col])
else:
pr_update.add(table_id)
TRN.add(pc_update_sql, [table_id, col])
TRN.execute()
示例14: VALUES
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
TRN.add(sql, [client_id, client_secret])
sql = """INSERT INTO qiita.oauth_software (software_id, client_id)
VALUES (%s, %s)"""
TRN.add(sql, [i, client_id])
TRN.execute()
#
# Generating compressed files for picking failures -- artifact_type = BIOM
#
sql = """SELECT artifact_id FROM qiita.artifact
JOIN qiita.artifact_type USING (artifact_type_id)
WHERE artifact_type = 'BIOM'"""
TRN.add(sql)
for r in TRN.execute_fetchindex():
to_tgz = None
a = Artifact(r[0])
for _, fp, fp_type in a.filepaths:
if fp_type == 'directory':
# removing / from the path if it exists
to_tgz = fp[:-1] if fp[-1] == '/' else fp
break
if to_tgz is None:
continue
tgz = to_tgz + '.tgz'
if not exists(tgz):
with taropen(tgz, "w:gz") as tar:
tar.add(to_tgz, arcname=basename(to_tgz))
示例15: create_qiime_mapping_file
# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
def create_qiime_mapping_file(self):
"""This creates the QIIME mapping file and links it in the db.
Returns
-------
filepath : str
The filepath of the created QIIME mapping file
Raises
------
ValueError
If the prep template is not a subset of the sample template
QiitaDBWarning
If the QIIME-required columns are not present in the template
Notes
-----
We cannot ensure that the QIIME-required columns are present in the
metadata map. However, we have to generate a QIIME-compliant mapping
file. Since the user may need a QIIME mapping file, but not these
QIIME-required columns, we are going to create them and
populate them with the value XXQIITAXX.
"""
with TRN:
rename_cols = {
'barcode': 'BarcodeSequence',
'primer': 'LinkerPrimerSequence',
'description': 'Description',
}
if 'reverselinkerprimer' in self.categories():
rename_cols['reverselinkerprimer'] = 'ReverseLinkerPrimer'
new_cols = ['BarcodeSequence', 'LinkerPrimerSequence',
'ReverseLinkerPrimer']
else:
new_cols = ['BarcodeSequence', 'LinkerPrimerSequence']
# getting the latest sample template
sql = """SELECT filepath_id, filepath
FROM qiita.filepath
JOIN qiita.sample_template_filepath
USING (filepath_id)
WHERE study_id=%s
ORDER BY filepath_id DESC"""
TRN.add(sql, [self.study_id])
# We know that the good filepath is the one in the first row
# because we sorted them in the SQL query
sample_template_fname = TRN.execute_fetchindex()[0][1]
_, fp = get_mountpoint('templates')[0]
sample_template_fp = join(fp, sample_template_fname)
# reading files via pandas
st = load_template_to_dataframe(sample_template_fp)
pt = self.to_dataframe()
st_sample_names = set(st.index)
pt_sample_names = set(pt.index)
if not pt_sample_names.issubset(st_sample_names):
raise ValueError(
"Prep template is not a sub set of the sample template, "
"file: %s - samples: %s"
% (sample_template_fp,
', '.join(pt_sample_names-st_sample_names)))
mapping = pt.join(st, lsuffix="_prep")
mapping.rename(columns=rename_cols, inplace=True)
# Pre-populate the QIIME-required columns with the value XXQIITAXX
index = mapping.index
placeholder = ['XXQIITAXX'] * len(index)
missing = []
for val in viewvalues(rename_cols):
if val not in mapping:
missing.append(val)
mapping[val] = pd.Series(placeholder, index=index)
if missing:
warnings.warn(
"Some columns required to generate a QIIME-compliant "
"mapping file are not present in the template. A "
"placeholder value (XXQIITAXX) has been used to populate "
"these columns. Missing columns: %s" % ', '.join(missing),
QiitaDBWarning)
# Gets the orginal mapping columns and readjust the order to comply
# with QIIME requirements
cols = mapping.columns.values.tolist()
cols.remove('BarcodeSequence')
cols.remove('LinkerPrimerSequence')
cols.remove('Description')
new_cols.extend(cols)
new_cols.append('Description')
mapping = mapping[new_cols]
# figuring out the filepath for the QIIME map file
_id, fp = get_mountpoint('templates')[0]
filepath = join(fp, '%d_prep_%d_qiime_%s.txt' % (self.study_id,
self.id, strftime("%Y%m%d-%H%M%S")))
#.........这里部分代码省略.........