本文整理汇总了Python中sqlalchemy.sql.select方法的典型用法代码示例。如果您正苦于以下问题:Python sql.select方法的具体用法?Python sql.select怎么用?Python sql.select使用的例子?那么, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类sqlalchemy.sql
的用法示例。
在下文中一共展示了sql.select方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: get_tasks
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def get_tasks(self):
"""Get all tasks in the database."""
try:
with self.db_engine.connect() as conn:
query = sql.select([self.tasks_tbl])
rs = conn.execute(query)
task_list = [objects.Task.from_db(dict(r)) for r in rs]
self._assemble_tasks(task_list=task_list)
# add reference to this state manager to each task
for t in task_list:
t.statemgr = self
return task_list
except Exception as ex:
self.logger.error("Error querying task list: %s" % str(ex))
return []
示例2: _assemble_tasks
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def _assemble_tasks(self, task_list=None):
"""Attach all the appropriate result messages to the tasks in the list.
:param task_list: a list of objects.Task instances to attach result messages to
"""
if task_list is None:
return None
with self.db_engine.connect() as conn:
query = sql.select([
self.result_message_tbl
]).where(self.result_message_tbl.c.task_id == sql.bindparam(
'task_id')).order_by(self.result_message_tbl.c.sequence.asc())
query.compile(self.db_engine)
for t in task_list:
rs = conn.execute(query, task_id=t.task_id.bytes)
error_count = 0
for r in rs:
msg = objects.TaskStatusMessage.from_db(dict(r))
if msg.error:
error_count = error_count + 1
t.result.message_list.append(msg)
t.result.error_count = error_count
示例3: get_boot_action
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def get_boot_action(self, action_id):
"""Query for a single boot action by ID.
:param action_id: string ULID bootaction id
"""
try:
with self.db_engine.connect() as conn:
query = self.ba_status_tbl.select().where(
self.ba_status_tbl.c.action_id == ulid2.decode_ulid_base32(
action_id))
rs = conn.execute(query)
r = rs.fetchone()
if r is not None:
ba_dict = dict(r)
ba_dict['action_id'] = bytes(ba_dict['action_id'])
ba_dict['identity_key'] = bytes(ba_dict['identity_key'])
ba_dict['task_id'] = uuid.UUID(bytes=ba_dict['task_id'])
return ba_dict
else:
return None
except Exception as ex:
self.logger.error(
"Error querying boot action %s" % action_id, exc_info=ex)
示例4: for_update_clause
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def for_update_clause(self, select, **kw):
if self.is_subquery():
return ""
tmp = ' FOR UPDATE'
if select._for_update_arg.of:
tmp += ' OF ' + ', '.join(
self.process(elem, **kw) for elem in
select._for_update_arg.of
)
if select._for_update_arg.nowait:
tmp += " NOWAIT"
return tmp
示例5: create_uuids
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def create_uuids(migrate_engine, primary_table_name, revision_table_name):
# have changed type of cols so recreate metadata
metadata = MetaData(migrate_engine)
# 4 create uuids for primary entities and in related tables
primary_table = Table(primary_table_name, metadata, autoload=True)
if revision_table_name:
revision_table = Table(revision_table_name, metadata, autoload=True)
# fetchall wouldn't be optimal with really large sets of data but here <20k
ids = [ res[0] for res in
migrate_engine.execute(select([primary_table.c.id])).fetchall() ]
for count,id in enumerate(ids):
# if count % 100 == 0: print(count, id)
myuuid = make_uuid()
update = primary_table.update().where(primary_table.c.id==id).values(id=myuuid)
migrate_engine.execute(update)
if revision_table_name:
# ensure each id in revision table match its continuity id.
q = revision_table.update().values(id=revision_table.c.continuity_id)
migrate_engine.execute(q)
示例6: get_profit_for_pair
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def get_profit_for_pair(exchange, pair):
"""Iterates through all trades for given exchange pair over the course of trading. Starts by subtracting the long positions (the buys) and adding the short positions (the sells) to arrive at the difference (profit"""
"""The buys are always the even rows and the sells are the odd rows (buy always before sell starting from zero)"""
profit = 0
counter = 0
s = select([database.TradingPositions]).where(and_(database.TradingPositions.c.Exchange == exchange, database.TradingPositions.c.Pair == pair))
result = conn.execute(s)
for row in result:
if counter % 2 == 0:
profit = profit - row[5]
counter += 1
else:
profit = profit + row[5]
counter += 1
return profit
示例7: test_no_net_change_timestamp
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def test_no_net_change_timestamp(self):
t = self._timestamp_fixture()
import datetime
self.conn.execute(
t.insert(), {"x": datetime.datetime(2012, 5, 18, 15, 32, 5)}
)
with self.op.batch_alter_table("hasts") as batch_op:
batch_op.alter_column("x", type_=DateTime())
eq_(
self.conn.execute(select([t.c.x])).fetchall(),
[(datetime.datetime(2012, 5, 18, 15, 32, 5),)],
)
示例8: intersect
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def intersect(self,qs):
#here the .self_group() is necessary to ensure the correct grouping within the INTERSECT...
my_s = self.get_bare_select(columns = [self.table.c.pk.label('pk')]).alias()
qs_s = qs.get_bare_select(columns = [qs.table.c.pk.label('pk')]).alias()
my_pk_s = select([column('pk')]).select_from(my_s)
qs_pk_s = select([column('pk')]).select_from(qs_s)
condition = self.table.c.pk.in_(expression.intersect(my_pk_s,qs_pk_s))
new_qs = QuerySet(self.backend,
self.table,
self.cls,
condition = condition,
order_bys = self.order_bys,
raw = self.raw,
include = self.include,
only = self.only)
return new_qs
示例9: append
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def append(self,obj):
with self.obj.backend.transaction(implicit = True):
#if the object is not yet in a DB, we save it first.
if obj.pk is None:
self.obj.backend.save(obj)
relationship_table = self.params['relationship_table']
condition = and_(relationship_table.c[self.params['related_pk_field_name']] == obj.pk,
relationship_table.c[self.params['pk_field_name']] == self.obj.pk)
s = select([func.count(text('*'))]).where(condition)
result = self.obj.backend.connection.execute(s)
cnt = result.first()[0]
if cnt:
return #the object is already inside
values = {
self.params['pk_field_name'] : self.obj.pk,
self.params['related_pk_field_name'] : obj.pk
}
insert = relationship_table.insert().values(**values)
self.obj.backend.connection.execute(insert)
self._queryset = None
示例10: upgrade
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def upgrade():
relative_modifier = table('relative_modifier',
column('id', sa.Integer),
column('value', sa.Float),
column('numeric_value', sa.Numeric(precision=8, scale=5)))
op.add_column('relative_modifier',
sa.Column('numeric_value', sa.Numeric(precision=8, scale=5)))
conn = op.get_bind()
sel = select([relative_modifier.c.id, relative_modifier.c.value])
results = conn.execute(sel)
q = Decimal(10) ** -5
for id_, float_value in results:
decimal_value = Decimal(float_value).quantize(q)
up = update(relative_modifier).where(relative_modifier.c.id == id_)\
.values({'numeric_value': decimal_value})
conn.execute(up)
op.drop_column('relative_modifier', 'value')
op.alter_column('relative_modifier', 'numeric_value', nullable=True,
new_column_name='value', existing_type=sa.Numeric(precision=8,
scale=5))
示例11: downgrade
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def downgrade():
relative_modifier = table('relative_modifier',
column('id', sa.Integer),
column('value', sa.Numeric(precision=8, scale=5)),
column('float_value', sa.Float))
op.add_column('relative_modifier', sa.Column('float_value', sa.Float))
conn = op.get_bind()
sel = select([relative_modifier.c.id, relative_modifier.c.value])
results = conn.execute(sel)
for id_, decimal_value in results:
float_value = float(decimal_value)
up = update(relative_modifier).where(relative_modifier.c.id == id_)\
.values({'float_value': float_value})
conn.execute(up)
op.drop_column('relative_modifier', 'value')
op.alter_column('relative_modifier', 'float_value', nullable=True,
new_column_name='value', existing_type=sa.Float)
示例12: add_note
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def add_note(db, server, user, text):
id_query = select([sqlalchemy.sql.expression.func.max(table.c.note_id).label("maxid")]) \
.where(table.c.user == user.lower())
max_id = db.execute(id_query).scalar()
if max_id is None:
note_id = 1
else:
note_id = max_id + 1
query = table.insert().values(
note_id=note_id,
connection=server,
user=user.lower(),
text=text,
deleted=False,
added=datetime.today()
)
db.execute(query)
db.commit()
示例13: test_limit_offset
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def test_limit_offset(self):
t1 = table('t1', column('c1'), column('c2'), column('c3'))
s = select([t1]).limit(3).offset(5)
#assert s ==
s = select([t1]).limit(3)
#assert s ==
s = select([t1]).limit(3).distinct()
#assert s ==
s = select([t1]).order_by(t1.c.c2).limit(3).offset(5).distinct()
#assert s ==
s = select([t1]).order_by(t1.c.c2).limit(3).offset(5)
#assert s ==
s = select([t1]).order_by(t1.c.c2).offset(5)
#assert s ==
s = select([t1]).order_by(t1.c.c2).limit(3)
#assert s ==
stmt = s.compile(self.engine)
示例14: setup
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def setup(self):
self.conn = testing.db.connect()
self.engine = self.conn.engine
self.dialect = self.conn.dialect
self.metadata = MetaData()
self.user_name = self.engine.execute('sel user').scalar()
self.db_schema = self.engine.execute('sel database').scalar()
self.tbl_name = self.user_name + '_test'
self.view_name = self.user_name + '_test_view'
# Setup test table (user should have necessary rights to create table)
self.test_table = Table(self.tbl_name, self.metadata,
Column('id', Integer, primary_key=True),
PrimaryKeyConstraint('id', name='my_pk'))
# Setup a test view
#self.test_view = CreateView(self.view_name, select([self.test_table.c.id.label('view_id')]))
# Create tables
self.metadata.create_all(self.engine)
#Create views
#self.conn.execute(self.test_view)
示例15: get_next_queued_task
# 需要导入模块: from sqlalchemy import sql [as 别名]
# 或者: from sqlalchemy.sql import select [as 别名]
def get_next_queued_task(self, allowed_actions=None):
"""Query the database for the next (by creation timestamp) queued task.
If specified, only select tasks for one of the actions in the allowed_actions
list.
:param allowed_actions: list of string action names
"""
try:
with self.db_engine.connect() as conn:
if allowed_actions is None:
query = self.tasks_tbl.select().where(
self.tasks_tbl.c.status == hd_fields.TaskStatus.
Queued).order_by(self.tasks_tbl.c.created.asc())
rs = conn.execute(query)
else:
query = sql.text("SELECT * FROM tasks WHERE "
"status = :queued_status AND "
"action = ANY(:actions) "
"ORDER BY created ASC")
rs = conn.execute(
query,
queued_status=hd_fields.TaskStatus.Queued,
actions=allowed_actions)
r = rs.first()
if r is not None:
task = objects.Task.from_db(dict(r))
self._assemble_tasks(task_list=[task])
task.statemgr = self
return task
else:
return None
except Exception as ex:
self.logger.error(
"Error querying for next queued task: %s" % str(ex),
exc_info=True)
return None