本文整理汇总了Python中qiita_db.sql_connection.SQLConnectionHandler.execute_queue方法的典型用法代码示例。如果您正苦于以下问题:Python SQLConnectionHandler.execute_queue方法的具体用法?Python SQLConnectionHandler.execute_queue怎么用?Python SQLConnectionHandler.execute_queue使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类qiita_db.sql_connection.SQLConnectionHandler
的用法示例。
在下文中一共展示了SQLConnectionHandler.execute_queue方法的9个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: create
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [as 别名]
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
示例2: delete
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [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
"""
cls._check_subclass()
if not cls.exists(id_):
raise QiitaDBUnknownIDError(id_, cls.__name__)
raw_datas = [str(rd) for rd in Study(cls(id_).study_id).raw_data()]
if raw_datas:
raise QiitaDBError("Sample template can not be erased because "
"there are raw datas (%s) associated." %
', '.join(raw_datas))
table_name = cls._table_name(id_)
conn_handler = SQLConnectionHandler()
# Delete the sample template filepaths
queue = "delete_sample_template_%d" % id_
conn_handler.create_queue(queue)
conn_handler.add_to_queue(
queue,
"DELETE FROM qiita.sample_template_filepath WHERE study_id = %s",
(id_, ))
conn_handler.add_to_queue(
queue,
"DROP TABLE qiita.{0}".format(table_name))
conn_handler.add_to_queue(
queue,
"DELETE FROM qiita.{0} where {1} = %s".format(cls._table,
cls._id_column),
(id_,))
conn_handler.add_to_queue(
queue,
"DELETE FROM qiita.{0} where {1} = %s".format(cls._column_table,
cls._id_column),
(id_,))
conn_handler.execute_queue(queue)
示例3: __setitem__
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [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
"""
conn_handler = SQLConnectionHandler()
queue_name = "set_item_%s" % self._id
conn_handler.create_queue(queue_name)
self.add_setitem_queries(column, value, conn_handler, queue_name)
try:
conn_handler.execute_queue(queue_name)
except QiitaDBExecutionError as e:
# catching error so we can check if the error is due to different
# column type or something else
type_lookup = defaultdict(lambda: 'varchar')
type_lookup[int] = 'integer'
type_lookup[float] = 'float8'
type_lookup[str] = 'varchar'
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)"""
column_type = conn_handler.execute_fetchone(
sql, (column, self._table, self._dynamic_table))
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
示例4: extend
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [as 别名]
def extend(self, md_template):
"""Adds the given sample template to the current one
Parameters
----------
md_template : DataFrame
The metadata template file contents indexed by samples Ids
"""
conn_handler = SQLConnectionHandler()
queue_name = "EXTEND_SAMPLE_TEMPLATE_%d" % self.id
conn_handler.create_queue(queue_name)
md_template = self._clean_validate_template(md_template, self.study_id, SAMPLE_TEMPLATE_COLUMNS)
self._add_common_extend_steps_to_queue(md_template, conn_handler, queue_name)
conn_handler.execute_queue(queue_name)
self.generate_files()
示例5: USING
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [as 别名]
JOIN qiita.study_raw_data USING (raw_data_id)
LOOP
INSERT INTO qiita.study_prep_template (study_id, prep_template_id)
VALUES (vals.study_id, vals.prep_template_id);
END LOOP;
END $do$;
--- Drop the study_raw__data table as it's not longer used
DROP TABLE qiita.study_raw_data;
-- The raw_data_id column now can be nullable
ALTER TABLE qiita.prep_template
ALTER COLUMN raw_data_id DROP NOT NULL;
"""
conn_handler.add_to_queue(queue, sql)
conn_handler.execute_queue(queue)
# After the changes in the database have been performed, move the files
# to the uploads folder
errors = []
for st_id, fps in move_files:
try:
move_filepaths_to_upload_folder(st_id, fps)
except Exception, e:
# An error here is unlikely. However, it's possible and there is no
# clean way that we can unroll all the previous changes in the DB.
errors.append((st_id, fps, str(e)))
# Show the user any error that could have been generated during the files
# movement
if errors:
示例6: create
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [as 别名]
def create(cls, md_template, raw_data, study, data_type,
investigation_type=None):
r"""Creates the metadata template in the database
Parameters
----------
md_template : DataFrame
The metadata template file contents indexed by samples Ids
raw_data : RawData
The raw_data to which the prep template belongs to.
study : Study
The study to which the prep template belongs to.
data_type : str or int
The data_type of the prep template
investigation_type : str, optional
The investigation type, if relevant
Returns
-------
A new instance of `cls` to access to the PrepTemplate stored in the DB
Raises
------
QiitaDBColumnError
If the investigation_type is not valid
If a required column is missing in md_template
"""
# If the investigation_type is supplied, make sure it is one of
# the recognized investigation types
if investigation_type is not None:
cls.validate_investigation_type(investigation_type)
# Get a connection handler
conn_handler = SQLConnectionHandler()
queue_name = "CREATE_PREP_TEMPLATE_%d" % raw_data.id
conn_handler.create_queue(queue_name)
# Check if the data_type is the id or the string
if isinstance(data_type, (int, long)):
data_type_id = data_type
data_type_str = convert_from_id(data_type, "data_type",
conn_handler)
else:
data_type_id = convert_to_id(data_type, "data_type", conn_handler)
data_type_str = data_type
md_template = cls._clean_validate_template(md_template, study.id,
data_type_str, conn_handler)
# Insert the metadata template
# We need the prep_id for multiple calls below, which currently is not
# supported by the queue system. Thus, executing this outside the queue
prep_id = conn_handler.execute_fetchone(
"INSERT INTO qiita.prep_template (data_type_id, raw_data_id, "
"investigation_type) VALUES (%s, %s, %s) RETURNING "
"prep_template_id", (data_type_id, raw_data.id,
investigation_type))[0]
cls._add_common_creation_steps_to_queue(md_template, prep_id,
conn_handler, queue_name)
try:
conn_handler.execute_queue(queue_name)
except Exception:
# Clean up row from qiita.prep_template
conn_handler.execute(
"DELETE FROM qiita.prep_template where "
"{0} = %s".format(cls._id_column), (prep_id,))
# Check if sample IDs present here but not in sample template
sql = ("SELECT sample_id from qiita.required_sample_info WHERE "
"study_id = %s")
# Get list of study sample IDs, prep template study IDs,
# and their intersection
prep_samples = set(md_template.index.values)
unknown_samples = prep_samples.difference(
s[0] for s in conn_handler.execute_fetchall(sql, [study.id]))
if unknown_samples:
raise QiitaDBExecutionError(
'Samples found in prep template but not sample template: '
'%s' % ', '.join(unknown_samples))
# some other error we haven't seen before so raise it
raise
pt = cls(prep_id)
pt.generate_files()
return pt
示例7: update_category
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [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
"""
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)
conn_handler = SQLConnectionHandler()
queue_name = "update_category_%s_%s" % (self._id, category)
conn_handler.create_queue(queue_name)
for k, v in viewitems(samples_and_values):
sample = self[k]
sample.add_setitem_queries(category, v, conn_handler, queue_name)
try:
conn_handler.execute_queue(queue_name)
except QiitaDBExecutionError as e:
# catching error so we can check if the error is due to different
# column type or something else
type_lookup = defaultdict(lambda: 'varchar')
type_lookup[int] = 'integer'
type_lookup[float] = 'float8'
type_lookup[str] = 'varchar'
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)"""
column_type = conn_handler.execute_fetchone(
sql, (category, self._table, self._table_name(self._id)))
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
示例8: TestConnHandler
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [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):
#.........这里部分代码省略.........
示例9: flatten
# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import execute_queue [as 别名]
'{1}=%s and filepath_id=%s'.format(table, column), (pt.id, mf[0]))
ids = flatten(ids)
# (2) delete the entries from the prep_template_filepath table
conn_handler.add_to_queue(
q_name, "DELETE FROM qiita.{0} "
"WHERE {1}=%s and filepath_id=%s;".format(table, column),
(pt.id, mf[0]))
# (3) delete the entries from the filepath table
conn_handler.add_to_queue(
q_name,
"DELETE FROM qiita.filepath WHERE "
"filepath_id IN ({0});".format(', '.join(map(str, ids))))
try:
conn_handler.execute_queue(q_name)
except Exception as e:
raise
# create correct versions of the mapping files
for prep_template_id in all_ids:
prep_template_id = prep_template_id[0]
pt = PrepTemplate(prep_template_id)
# we can guarantee that all the filepaths will be prep templates so
# we can just generate the qiime mapping files
for _, fpt in pt.get_filepaths():
pt.create_qiime_mapping_file(fpt)