本文整理汇总了Python中qiita_db.sql_connection.SQLConnectionHandler.execute_fetchall方法的典型用法代码示例。如果您正苦于以下问题:Python SQLConnectionHandler.execute_fetchall方法的具体用法?Python SQLConnectionHandler.execute_fetchall怎么用?Python SQLConnectionHandler.execute_fetchall使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类qiita_db.sql_connection.SQLConnectionHandler
的用法示例。
在下文中一共展示了SQLConnectionHandler.execute_fetchall方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: get_filepaths
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
def get_filepaths(self):
r"""Retrieves the list of (filepath_id, filepath)"""
# Check that this function has been called from a subclass
self._check_subclass()
# Check if the connection handler has been provided. Create a new
# one if not.
conn_handler = SQLConnectionHandler()
try:
filepath_ids = conn_handler.execute_fetchall(
"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),
(self.id, ))
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]
示例2: to_dataframe
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
def to_dataframe(self):
"""Returns the metadata template as a dataframe
Returns
-------
pandas DataFrame
The metadata in the template,indexed on sample id
"""
conn_handler = SQLConnectionHandler()
cols = get_table_cols(self._table, conn_handler)
if 'study_id' in cols:
cols.remove('study_id')
dyncols = get_table_cols(self._table_name(self._id), conn_handler)
# remove sample_id from dyncols so not repeated
dyncols.remove('sample_id')
# Get all metadata for the template
sql = """SELECT {0}, {1} FROM qiita.{2} req
INNER JOIN qiita.{3} dyn on req.sample_id = dyn.sample_id
WHERE req.{4} = %s""".format(
", ".join("req.%s" % c for c in cols),
", ".join("dyn.%s" % d for d in dyncols),
self._table, self._table_name(self._id), self._id_column)
meta = conn_handler.execute_fetchall(sql, [self._id])
cols = cols + dyncols
# 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)
# Turn id cols to value cols
for col, value in viewitems(self.str_cols_handlers):
df[col].replace(value, inplace=True)
df.rename(columns=self.translate_cols_dict, inplace=True)
return df
示例3: status
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [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'.
"""
conn_handler = SQLConnectionHandler()
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"""
pd_statuses = conn_handler.execute_fetchall(sql, (self._id,))
return infer_status(pd_statuses)
示例4: preprocessed_data
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
def preprocessed_data(self):
conn_handler = SQLConnectionHandler()
prep_datas = conn_handler.execute_fetchall(
"SELECT preprocessed_data_id FROM "
"qiita.prep_template_preprocessed_data WHERE prep_template_id=%s",
(self.id,))
return [x[0] for x in prep_datas]
示例5: __call__
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [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
"""
study_sql, sample_sql, meta_headers = \
self._parse_study_search_string(searchstr, True)
conn_handler = SQLConnectionHandler()
# get all studies containing the metadata headers requested
study_ids = {x[0] for x in conn_handler.execute_fetchall(study_sql)}
# 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:
study_res = conn_handler.execute_fetchall(sample_sql.format(sid))
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
示例6: update
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [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
"""
conn_handler = SQLConnectionHandler()
# Clean and validate the metadata template given
new_map = self._clean_validate_template(md_template, self.study_id,
self.columns_restrictions)
# Retrieving current metadata
current_map = self._transform_to_dict(conn_handler.execute_fetchall(
"SELECT * FROM qiita.{0}".format(self._table_name(self.id))))
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()
示例7: to_file
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
def to_file(self, fp, samples=None):
r"""Writes the MetadataTemplate to the file `fp` in tab-delimited
format
Parameters
----------
fp : str
Path to the output file
samples : set, optional
If supplied, only the specified samples will be written to the
file
"""
conn_handler = SQLConnectionHandler()
metadata_map = self._transform_to_dict(conn_handler.execute_fetchall(
"SELECT * FROM qiita.{0} WHERE {1}=%s".format(self._table,
self._id_column),
(self.id,)))
dyn_vals = self._transform_to_dict(conn_handler.execute_fetchall(
"SELECT * FROM qiita.{0}".format(self._table_name(self.id))))
for k in metadata_map:
for key, value in viewitems(self.translate_cols_dict):
id_ = metadata_map[k][key]
metadata_map[k][value] = self.str_cols_handlers[key][id_]
del metadata_map[k][key]
metadata_map[k].update(dyn_vals[k])
metadata_map[k].pop('study_id', None)
# Remove samples that are not in the samples list, if it was supplied
if samples is not None:
for sid, d in metadata_map.items():
if sid not in samples:
metadata_map.pop(sid)
# Write remaining samples to file
headers = sorted(list(metadata_map.values())[0].keys())
with open(fp, 'w') as f:
# First write the headers
f.write("sample_name\t%s\n" % '\t'.join(headers))
# Write the values for each sample id
for sid, d in sorted(metadata_map.items()):
values = [str(d[h]) for h in headers]
values.insert(0, sid)
f.write("%s\n" % '\t'.join(values))
示例8: metadata_headers
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
def metadata_headers():
"""Returns metadata headers available
Returns
-------
list
Alphabetical list of all metadata headers available
"""
conn_handler = SQLConnectionHandler()
return [x[0] for x in
conn_handler.execute_fetchall(
"SELECT DISTINCT column_name FROM qiita.study_sample_columns "
"ORDER BY column_name")]
示例9: metadata_headers
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
def metadata_headers():
"""Returns metadata headers available
Returns
-------
list
Alphabetical list of all metadata headers available
"""
conn_handler = SQLConnectionHandler()
return [x[0] for x in
conn_handler.execute_fetchall(
"SELECT DISTINCT column_name FROM qiita.study_sample_columns "
"UNION SELECT column_name FROM information_schema.columns "
"WHERE table_name = 'required_sample_info' "
"ORDER BY column_name")]
示例10: to_dataframe
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
def to_dataframe(self):
"""Returns the metadata template as a dataframe
Returns
-------
pandas DataFrame
The metadata in the template,indexed on sample id
"""
conn_handler = SQLConnectionHandler()
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))
meta = conn_handler.execute_fetchall(sql, (self._id,))
# 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
示例11: qiime_map_fp
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
def qiime_map_fp(self):
"""The QIIME mapping filepath attached to the prep template
Returns
-------
str
The filepath of the QIIME mapping file
"""
conn_handler = SQLConnectionHandler()
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)
fn = conn_handler.execute_fetchall(sql, (self._id,))[0][1]
base_dir = get_mountpoint('templates')[0][1]
return join(base_dir, fn)
示例12: SQLConnectionHandler
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
from qiita_db.sql_connection import SQLConnectionHandler
from qiita_db.data import RawData
from qiita_db.util import move_filepaths_to_upload_folder
conn_handler = SQLConnectionHandler()
queue = "PATCH_25"
conn_handler.create_queue(queue)
# the system may contain raw data with no prep template associated to it.
# Retrieve all those raw data ids
sql = """SELECT raw_data_id
FROM qiita.raw_data
WHERE raw_data_id NOT IN (
SELECT DISTINCT raw_data_id FROM qiita.prep_template);"""
rd_ids = [x[0] for x in conn_handler.execute_fetchall(sql)]
# We will delete those RawData. However, if they have files attached, we should
# move them to the uploads folder of the study
sql_detach = """DELETE FROM qiita.study_raw_data
WHERE raw_data_id = %s AND study_id = %s"""
sql_unlink = "DELETE FROM qiita.raw_filepath WHERE raw_data_id = %s"
sql_delete = "DELETE FROM qiita.raw_data WHERE raw_data_id = %s"
sql_studies = """SELECT study_id FROM qiita.study_raw_data
WHERE raw_data_id = %s"""
move_files = []
for rd_id in rd_ids:
rd = RawData(rd_id)
filepaths = rd.get_filepaths()
studies = [s[0] for s in conn_handler.execute_fetchall(sql_studies,
(rd_id,))]
示例13: TestConnHandler
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
class TestConnHandler(TestCase):
def test_create_queue(self):
self.conn_handler.create_queue("toy_queue")
self.assertEqual(self.conn_handler.list_queues(), ["toy_queue"])
def test_close(self):
self.assertEqual(self.conn_handler._user_conn.closed, 0)
self.conn_handler.close()
self.assertNotEqual(self.conn_handler._user_conn.closed, 0)
def test_run_queue(self):
self.conn_handler.create_queue("toy_queue")
self.conn_handler.add_to_queue(
"toy_queue", "INSERT INTO qiita.qiita_user (email, name, password,"
"phone) VALUES (%s, %s, %s, %s)",
['[email protected]', 'Toy', 'pass', '111-111-11112'])
self.conn_handler.add_to_queue(
"toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, "
"phone = '222-222-2221' WHERE email = %s",
['[email protected]'])
obs = self.conn_handler.execute_queue("toy_queue")
self.assertEqual(obs, [])
obs = self.conn_handler.execute_fetchall(
"SELECT * from qiita.qiita_user WHERE email = %s",
['[email protected]'])
exp = [['[email protected]', 1, 'pass', 'Toy', None, None, '222-222-2221',
None, None, None]]
self.assertEqual(obs, exp)
def test_run_queue_many(self):
sql = ("INSERT INTO qiita.qiita_user (email, name, password,"
"phone) VALUES (%s, %s, %s, %s)")
sql_args = [
('[email protected]', 'p1', 'pass1', '111-111'),
('[email protected]', 'p2', 'pass2', '111-222')
]
self.conn_handler.create_queue("toy_queue")
self.conn_handler.add_to_queue(
"toy_queue", sql, sql_args, many=True)
self.conn_handler.execute_queue('toy_queue')
# make sure both users added
obs = self.conn_handler.execute_fetchall(
"SELECT * from qiita.qiita_user WHERE email = %s",
['[email protected]'])
exp = [['[email protected]', 5, 'pass1', 'p1', None, None, '111-111',
None, None, None]]
self.assertEqual(obs, exp)
obs = self.conn_handler.execute_fetchall(
"SELECT * from qiita.qiita_user WHERE email = %s",
['[email protected]'])
exp = [['[email protected]', 5, 'pass2', 'p2', None, None, '111-222',
None, None, None]]
self.assertEqual(obs, exp)
def test_run_queue_last_return(self):
self.conn_handler.create_queue("toy_queue")
self.conn_handler.add_to_queue(
"toy_queue", "INSERT INTO qiita.qiita_user (email, name, password,"
"phone) VALUES (%s, %s, %s, %s)",
['[email protected]', 'Toy', 'pass', '111-111-11112'])
self.conn_handler.add_to_queue(
"toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, "
"phone = '222-222-2221' WHERE email = %s RETURNING phone",
['[email protected]'])
obs = self.conn_handler.execute_queue("toy_queue")
self.assertEqual(obs, ['222-222-2221'])
def test_run_queue_placeholders(self):
self.conn_handler.create_queue("toy_queue")
self.conn_handler.add_to_queue(
"toy_queue", "INSERT INTO qiita.qiita_user (email, name, password,"
"phone) VALUES (%s, %s, %s, %s) RETURNING email, password",
['[email protected]', 'Toy', 'pass', '111-111-11112'])
self.conn_handler.add_to_queue(
"toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, "
"phone = '222-222-2221' WHERE email = %s AND password = %s",
['{0}', '{1}'])
obs = self.conn_handler.execute_queue("toy_queue")
self.assertEqual(obs, [])
obs = self.conn_handler.execute_fetchall(
"SELECT * from qiita.qiita_user WHERE email = %s",
['[email protected]'])
exp = [['[email protected]', 1, 'pass', 'Toy', None, None, '222-222-2221',
None, None, None]]
self.assertEqual(obs, exp)
def test_queue_fail(self):
"""Fail if no results data exists for substitution"""
self.conn_handler = SQLConnectionHandler()
self.conn_handler.create_queue("toy_queue")
self.conn_handler.add_to_queue(
"toy_queue",
"INSERT INTO qiita.qiita_user (email, name, password) VALUES "
"(%s, %s, %s)", ['[email protected]', 'Toy', 'pass'])
self.conn_handler.add_to_queue(
"toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1 "
"WHERE email = %s and password = %s", [{0}, {1}])
with self.assertRaises(QiitaDBExecutionError):
#.........这里部分代码省略.........
示例14: SQLConnectionHandler
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
# Feb 11, 2015
# This changes all analysis files to be relative path instead of absolute
from os.path import basename, dirname
from qiita_db.util import get_mountpoint
from qiita_db.sql_connection import SQLConnectionHandler
conn_handler = SQLConnectionHandler()
filepaths = conn_handler.execute_fetchall(
'SELECT f.* from qiita.filepath f JOIN qiita.analysis_filepath afp ON '
'f.filepath_id = afp.filepath_id')
# retrieve relative filepaths as dictionary for matching
mountpoints = {m[1].rstrip('/\\'): m[0] for m in get_mountpoint(
'analysis', conn_handler=conn_handler, retrieve_all=True)}
for filepath in filepaths:
filename = basename(filepath['filepath'])
# find the ID of the analysis filepath used
mp_id = mountpoints[dirname(filepath['filepath']).rstrip('/\\')]
conn_handler.execute(
'UPDATE qiita.filepath SET filepath = %s, data_directory_id = %s WHERE'
' filepath_id = %s',
[filename, mp_id, filepath['filepath_id']])
示例15: SQLConnectionHandler
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_fetchall [as 别名]
# This patch recreates all the QIIME mapping files to avoid lower/upper case
# problems. See https://github.com/biocore/qiita/issues/799
#
# heavily based on 7.py
from os.path import basename
from skbio.util import flatten
from qiita_db.sql_connection import SQLConnectionHandler
from qiita_db.metadata_template import PrepTemplate
conn_handler = SQLConnectionHandler()
sql = "SELECT prep_template_id FROM qiita.prep_template"
all_ids = conn_handler.execute_fetchall(sql)
q_name = 'unlink-bad-mapping-files'
conn_handler.create_queue(q_name)
# remove all the bad mapping files
for prep_template_id in all_ids:
prep_template_id = prep_template_id[0]
pt = PrepTemplate(prep_template_id)
fps = pt.get_filepaths()
# get the QIIME mapping file, note that the way to figure out what is and
# what's not a qiime mapping file is to check for the existance of the
# word qiime in the basename of the file path, hacky but that's the way
# it is being done in qiita_pet/uimodules/raw_data_tab.py