本文整理汇总了Python中qiita_db.sql_connection.SQLConnectionHandler类的典型用法代码示例。如果您正苦于以下问题:Python SQLConnectionHandler类的具体用法?Python SQLConnectionHandler怎么用?Python SQLConnectionHandler使用的例子?那么恭喜您, 这里精选的类代码示例或许可以为您提供帮助。
在下文中一共展示了SQLConnectionHandler类的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: get_filepaths
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: class_modifier
def class_modifier(cls):
# First, we check that we are not in a production environment
conn_handler = SQLConnectionHandler()
# It is possible that we are connecting to a production database
test_db = conn_handler.execute_fetchone("SELECT test FROM settings")[0]
# Or the loaded configuration file belongs to a production environment
# or the test database is not qiita_test
if not qiita_config.test_environment or not test_db \
or qiita_config.database != 'qiita_test':
raise RuntimeError("Working in a production environment. Not "
"executing the tests to keep the production "
"database safe.")
# Now, we decorate the setup and teardown functions
class DecoratedClass(cls):
@build_test_database
def setUp(self):
super(DecoratedClass, self).setUp()
self.conn_handler = SQLConnectionHandler()
@drop_test_database
def tearDown(self):
super(DecoratedClass, self).tearDown()
del self.conn_handler
return DecoratedClass
示例3: status
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: test_get_preprocess_fasta_cmd_sff_run_prefix
def test_get_preprocess_fasta_cmd_sff_run_prefix(self):
# Need to alter the run_prefix of one sample so we can test the
# multiple values
conn_handler = SQLConnectionHandler()
sql = ("UPDATE qiita.prep_1 SET run_prefix='test1' WHERE "
"sample_id = '1.SKM9.640192'")
conn_handler.execute(sql)
raw_data = RawData(3)
params = Preprocessed454Params(1)
prep_template = PrepTemplate(1)
obs_cmd, obs_output_dir = _get_preprocess_fasta_cmd(
raw_data, prep_template, params)
obs_cmds = obs_cmd.split('; ')
# assumming that test_get_preprocess_fasta_cmd_sff_no_run_prefix is
# working we only need to test for the commands being ran and
# that n is valid
self.assertEqual(len(obs_cmds), 8)
self.assertTrue(obs_cmds[0].startswith('process_sff.py'))
self.assertTrue(obs_cmds[1].startswith('process_sff.py'))
self.assertTrue(obs_cmds[2].startswith('split_libraries.py'))
self.assertIn('-n 1', obs_cmds[2])
self.assertTrue(obs_cmds[3].startswith('split_libraries.py'))
self.assertIn('-n 800000', obs_cmds[3])
self.assertTrue(obs_cmds[4].startswith('cat'))
self.assertIn('split_library_log.txt', obs_cmds[4])
self.assertTrue(obs_cmds[5].startswith('cat'))
self.assertTrue('seqs.fna', obs_cmds[5])
self.assertTrue(obs_cmds[6].startswith('cat'))
self.assertIn('seqs_filtered.qual', obs_cmds[6])
示例5: test_get_preprocess_fasta_cmd_sff_run_prefix_match_error_1
def test_get_preprocess_fasta_cmd_sff_run_prefix_match_error_1(self):
# Test that the run prefixes in the prep_template and the file names
# actually match and raise an error if not
conn_handler = SQLConnectionHandler()
sql = ("""
INSERT INTO qiita.filepath (filepath_id, filepath,
filepath_type_id, checksum, checksum_algorithm_id,
data_directory_id) VALUES (19, '1_new.sff', 17, 852952723, 1,
5);
INSERT INTO qiita.raw_filepath (raw_data_id , filepath_id) VALUES
(3, 19);
INSERT INTO qiita.filepath (filepath_id, filepath,
filepath_type_id, checksum, checksum_algorithm_id,
data_directory_id) VALUES (20, '1_error.sff', 17, 852952723,
1, 5);
INSERT INTO qiita.raw_filepath (raw_data_id , filepath_id) VALUES
(3, 20);
UPDATE qiita.prep_1 SET run_prefix='preprocess_test';
UPDATE qiita.prep_1 SET run_prefix='new' WHERE
sample_id = '1.SKB8.640193';
""")
conn_handler.execute(sql)
raw_data = RawData(3)
params = Preprocessed454Params(1)
prep_template = PrepTemplate(1)
with self.assertRaises(ValueError):
_get_preprocess_fasta_cmd(raw_data, prep_template, params)
示例6: __getitem__
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
"""
conn_handler = SQLConnectionHandler()
key = key.lower()
if key not in self._get_categories(conn_handler):
# 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)
return conn_handler.execute_fetchone(sql, (self._id, ))[0]
示例7: create
def create(cls, md_template, study):
r"""Creates the sample template in the database
Parameters
----------
md_template : DataFrame
The metadata template file contents indexed by samples Ids
study : Study
The study to which the sample template belongs to.
"""
cls._check_subclass()
# Check that we don't have a MetadataTemplate for study
if cls.exists(study.id):
raise QiitaDBDuplicateError(cls.__name__, 'id: %d' % study.id)
conn_handler = SQLConnectionHandler()
queue_name = "CREATE_SAMPLE_TEMPLATE_%d" % study.id
conn_handler.create_queue(queue_name)
# Clean and validate the metadata template given
md_template = cls._clean_validate_template(md_template, study.id,
study.id, conn_handler)
cls._add_common_creation_steps_to_queue(md_template, study.id,
conn_handler, queue_name)
conn_handler.execute_queue(queue_name)
st = cls(study.id)
st.generate_files()
return st
示例8: preprocessed_data
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]
示例9: _to_dict
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}
"""
conn_handler = SQLConnectionHandler()
d = dict(conn_handler.execute_fetchone(
"SELECT * FROM qiita.{0} WHERE {1}=%s AND "
"sample_id=%s".format(self._table, self._id_column),
(self._md_template.id, self._id)))
dynamic_d = dict(conn_handler.execute_fetchone(
"SELECT * from qiita.{0} WHERE "
"sample_id=%s".format(self._dynamic_table),
(self._id, )))
d.update(dynamic_d)
del d['sample_id']
del d[self._id_column]
d.pop('study_id', None)
# Modify all the *_id columns to include the string instead of the id
for k, v in viewitems(self._md_template.translate_cols_dict):
d[v] = self._md_template.str_cols_handlers[k][d[k]]
del d[k]
return d
示例10: to_dataframe
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
示例11: test_get_qiime_minimal_mapping_single_reverse_primer
def test_get_qiime_minimal_mapping_single_reverse_primer(self):
conn_handler = SQLConnectionHandler()
conn_handler
sql = """INSERT INTO qiita.prep_columns
(prep_template_id, column_name, column_type)
VALUES (1, 'reverselinkerprimer', 'varchar');
ALTER TABLE qiita.prep_1
ADD COLUMN reverselinkerprimer varchar;
DELETE FROM qiita.prep_columns
WHERE prep_template_id = 1 AND column_name = 'run_prefix';
ALTER TABLE qiita.prep_1 DROP COLUMN run_prefix;
UPDATE qiita.prep_1 SET reverselinkerprimer = %s
"""
conn_handler.execute(sql, ('GTGCCAGCM',))
prep_template = PrepTemplate(1)
prep_template.generate_files()
out_dir = mkdtemp()
obs_fps = _get_qiime_minimal_mapping(prep_template, out_dir)
exp_fps = [join(out_dir, 'prep_1_MMF.txt')]
# Check that the returned list is as expected
self.assertEqual(obs_fps, exp_fps)
# Check that the file exists
self.assertTrue(exists(exp_fps[0]))
# Check the contents of the file
with open(exp_fps[0], "U") as f:
self.assertEqual(f.read(), EXP_PREP_RLP)
示例12: update
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()
示例13: raw_data
def raw_data(self):
conn_handler = SQLConnectionHandler()
result = conn_handler.execute_fetchone(
"SELECT raw_data_id FROM qiita.prep_template "
"WHERE prep_template_id=%s", (self.id,))
if result:
return result[0]
return None
示例14: __getitem__
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
"""
conn_handler = SQLConnectionHandler()
key = key.lower()
if key in self._get_categories(conn_handler):
# It's possible that the key is asking for one of the *_id columns
# that we have to do the translation
def handler(x):
return x
# prevent flake8 from complaining about the function not being
# used and a redefinition happening in the next few lines
handler(None)
if key in self._md_template.translate_cols_dict.values():
handler = (
lambda x: self._md_template.str_cols_handlers[key][x])
key = "%s_id" % key
# Check if we have either to query the table with required columns
# or the dynamic table
if key in get_table_cols(self._table, conn_handler):
result = conn_handler.execute_fetchone(
"SELECT {0} FROM qiita.{1} WHERE {2}=%s AND "
"sample_id=%s".format(key, self._table, self._id_column),
(self._md_template.id, self._id))[0]
return handler(result)
else:
return conn_handler.execute_fetchone(
"SELECT {0} FROM qiita.{1} WHERE "
"sample_id=%s".format(key, self._dynamic_table),
(self._id, ))[0]
else:
# 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))
示例15: _check_id
def _check_id(self, id_):
r"""Checks that the MetadataTemplate id_ exists on the database"""
self._check_subclass()
conn_handler = SQLConnectionHandler()
return conn_handler.execute_fetchone(
"SELECT EXISTS(SELECT * FROM qiita.{0} WHERE "
"{1}=%s)".format(self._table, self._id_column),
(id_, ))[0]