本文整理汇总了Python中sqlalchemy.sql.select函数的典型用法代码示例。如果您正苦于以下问题:Python select函数的具体用法?Python select怎么用?Python select使用的例子?那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了select函数的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: extractData
def extractData(self):
self.logger.info('Connecting to database')
engine = create_engine(self.config['database'])
meta = MetaData()
meta.bind = engine
self.job = Table('job', meta, autoload=True)
self.job_status = Table('job_status', meta, autoload=True)
self.job_status_type_description = Table('job_status_type_description', meta, autoload=True)
job=self.job
job_status=self.job_status
s=self.job_status_type_description.select()
self.type_db_values = s.execute().fetchall()
self.queue_db_values = self.getJobSummary(job.c.queue)
self.user_db_values = self.getJobSummary(job.c.userId, job.c.localUser)
self.node_db_values = self.getJobSummary(job.c.workerNode)
self.logger.info('Generating job list')
node_job_status = self.config['node_job_status'].split(',')
maximum = func.max(self.job_status.c.id).label('m')
s1 = select([maximum]).group_by(job_status.c.jobId).alias('a')
s2 = select([job.c.id, job.c.lrmsAbsLayerJobId, job.c.workerNode, job_status.c.type,
job_status.c.time_stamp]).select_from(job.join(job_status).join(s1,job_status.c.id==text('m'))). \
where(and_(job_status.c.type.in_(node_job_status)))
self.job_db_values = s2.execute().fetchall()
return {}
示例2: test_sql_create_extra_fields
def test_sql_create_extra_fields(self):
"""
Test if `SqlStore` creates extra columns.
"""
# extend the db
self._make_store(self.db_url,
extra_fields={sqlalchemy.Column('extra', sqlalchemy.VARCHAR(length=128)): (lambda arg: arg.foo.value)})
# if this query does not error out, the column is defined
q = sql.select([sqlfunc.count(self.store.t_store.c.extra)]).distinct()
results = self.conn.execute(q)
# self.c.execute("select distinct count(extra) from %s" % self.store.table_name)
rows = results.fetchall()
assert_equal(len(rows), 1)
# create and save an object
obj = SimplePersistableObject('an object')
obj.foo = SimplePersistableObject('an attribute')
id_ = db.save(obj)
# check that the value has been saved
q = sql.select([self.store.t_store.c.extra]).where(self.store.t_store.c.id == id_)
# self.c.execute("select extra from %s where id=%d"
# % (self.store.table_name, id_))
results = self.conn.execute(q)
rows = results.fetchall()
assert_equal(len(rows), 1)
assert_equal(rows[0][0], obj.foo.value)
示例3: test_all_aliases
def test_all_aliases(self):
orders = table('order', column('order'))
s = select([orders.c.order]).cte("regional_sales")
r1 = s.alias()
r2 = s.alias()
s2 = select([r1, r2]).where(r1.c.order > r2.c.order)
self.assert_compile(
s2,
'WITH regional_sales AS (SELECT "order"."order" '
'AS "order" FROM "order") '
'SELECT anon_1."order", anon_2."order" '
'FROM regional_sales AS anon_1, '
'regional_sales AS anon_2 WHERE anon_1."order" > anon_2."order"'
)
s3 = select([orders]).select_from(orders.join(r1, r1.c.order == orders.c.order))
self.assert_compile(
s3,
'WITH regional_sales AS '
'(SELECT "order"."order" AS "order" '
'FROM "order")'
' SELECT "order"."order" '
'FROM "order" JOIN regional_sales AS anon_1 ON anon_1."order" = "order"."order"'
)
示例4: create_type
def create_type(sess, fqname):
u'''
クラスなり型なりを追加
'''
t = tables.Type.__table__
query = sql.select([t.c.id], t.c.fqname == fqname, t)
result = sess.execute(query).fetchall()
if result:
return result[0][0]
name = fqname.split('.')[-1]
typ = tables.Type(name=name, fqname=fqname)
with sess.begin():
sess.add(typ)
query = sql.select([t.c.id], t.c.fqname == fqname, t)
result = sess.execute(query).fetchone()[0]
return result
示例5: create_bought_product
def create_bought_product(self, qty, user_uuid, product_uuid):
"""
Create new bought item given product_uuid and user_uuid
of product or user with given uuid doesnt exists returns None
Returns primary_key that has been inserted
Keyword Arguments:
qty -- amount of items bought (int),
user_uuid -- unique user uuid (str),
product_uuid -- unique product uuid (str)
"""
try:
user_id = self.conn.execute(select([users.c.user_id])\
.where(users.c.user_uuid == user_uuid)).scalar()
product_id = self.conn.execute(select([products.c.product_id])\
.where(products.c.product_uuid == product_uuid)).scalar()
except:
raise
if product_id and user_id:
ins = bought_products.insert()\
.values(quantity = qty, user_id = user_id, product_id = product_id)
trans = self.conn.begin()
try:
res = self.conn.execute(ins)
trans.commit()
return res.inserted_primary_key[0]
except Exception as e:
trans.rollback()
raise
logging.error(sys.exc_info[0])
else:
return
示例6: grant_permission_for_group
def grant_permission_for_group(permission_name, group_name):
execute(
groups_permissions.insert().values(
group_id=select([groups.c.group_id]).where(groups.c.group_name == group_name),
permission_id=select([permissions.c.permission_id]).where(permissions.c.permission_name == permission_name)
)
)
示例7: _get_trace_id
def _get_trace_id(self, trace):
# Get the hash.
trace_hash = self.get_hash(trace)
# Check the database.
stmt = select([traces_table.c.id]).where(
traces_table.c.trace_hash == trace_hash
)
row = self._execute(stmt).first()
if row:
return row.id
# Attempt to insert a new trace.
compressed = zlib.compress(str(trace))
stmt = traces_table.insert().from_select([
traces_table.c.trace_hash,
traces_table.c.data,
], select([
literal(trace_hash),
literal(compressed, type_=Binary),
]).where(
~exists([traces_table.c.id]).where(
traces_table.c.trace_hash == trace_hash
)
))
self._execute(stmt)
return self._get_trace_id(trace)
示例8: tags
def tags(metadata):
conn = metadata.bind.connect()
session = DBSession()
taggings = metadata.tables["taggings"]
tags = metadata.tables["tags"]
tag_result = conn.execute(select([tags]))
for row in tag_result:
tag = Tag(id=row["id"], name=row["name"])
session.add(tag)
session.flush()
result = conn.execute(select([taggings]))
for row in result:
# get type
tag_type = row["taggable_type"]
if tag_type == "Group":
Model = Group
elif tag_type == "Person":
Model = Profile
elif tag_type == "Company":
Model = Company
# get tag id
tag = Tag.query.get(row["tag_id"])
obj = Model.query.get(row["taggable_id"])
if obj:
obj.tags.append(tag)
# get taggable id
session.flush()
示例9: get_tx_addresses
def get_tx_addresses(tx=None):
in_addresses = []
out_addresses = []
if tx['removed']==True:
in_addresses = ALL_VOUT.query.with_entities(ALL_VOUT.address, ALL_VOUT.value, ALL_VOUT.txin_tx_id, ALL_VOUT.txout_tx_hash).filter(ALL_VOUT.txin_tx_id==int(tx['id'])).order_by(ALL_VOUT.in_idx).all()
out_addresses = ALL_VOUT.query.with_entities(ALL_VOUT.address, ALL_VOUT.value, ALL_VOUT.txin_tx_id, ALL_VOUT.txin_tx_hash).filter(ALL_VOUT.txout_tx_id==int(tx['id'])).order_by(ALL_VOUT.out_idx).all()
return in_addresses , out_addresses
s1 = select([STXO.address, STXO.value, STXO.txin_tx_id, STXO.txout_tx_hash, STXO.in_idx]).where(STXO.txin_tx_id == int(tx['id']))
s2 = select([VTXO.address, VTXO.value, VTXO.txin_tx_id, VTXO.txout_tx_hash, VTXO.in_idx]).where(VTXO.txin_tx_id == int(tx['id']))
q = s1.union(s2).alias('in_addresses')
in_addresses=db_session.query(q).order_by('in_idx').all()
s1 = select([STXO.address, STXO.value, STXO.txin_tx_id, STXO.txout_tx_hash, STXO.out_idx]).where(STXO.txout_tx_id == tx['id'])
s2 = select([VTXO.address, VTXO.value, VTXO.txin_tx_id, VTXO.txout_tx_hash, VTXO.out_idx]).where(VTXO.txout_tx_id == tx['id'])
q = s1.union(s2).alias('out_addresses')
out_addresses=db_session.query(q).order_by('out_idx').all()
return in_addresses , out_addresses
示例10: _get_memory_id
def _get_memory_id(self, mem):
# Check the local cache.
mem_hash = self.get_hash(mem)
if mem_hash in self.memories:
return self.memories[mem_hash]
# Attempt to insert a new memory.
# This is the expected case.
stmt = memories_table.insert().from_select([
memories_table.c.name_hash,
memories_table.c.name,
], select([
literal(mem_hash),
literal(mem),
]).where(
~exists([memories_table.c.id]).where(
memories_table.c.name_hash == mem_hash
)
)
)
self._execute(stmt)
# Check the database.
stmt = select([memories_table.c.id]).where(
memories_table.c.name_hash == mem_hash
)
row = self._execute(stmt).first()
ident = row.id
self.memories[mem_hash] = ident
return ident
示例11: create_conversion_batch
async def create_conversion_batch(entity_name, entity_id, format, user_id):
entity_name = entity_name.upper()
if entity_name == 'AUTHOR':
author = model.Author.__table__
q = select([case([(author.c.first_name == None, author.c.last_name)],
else_ = author.c.first_name + ' ' + author.c.last_name)])\
.where(author.c.id == entity_id)
elif entity_name == 'SERIES':
series = model.Series.__table__
q = select([series.c.title]).where(series.c.id == entity_id)
elif entity_name == 'BOOKSHELF':
shelf = model.Bookshelf.__table__
q = select([shelf.c.name]).where(shelf.c.id == entity_id)
else:
raise ValueError('Invalid entity name')
format_id = await get_format_id(format)
async with engine.acquire() as conn:
batch = model.ConversionBatch.__table__
res = await conn.execute(q)
name = await res.scalar()
name = "Books for %s %s" % (entity_name.lower(), name)
res = await conn.execute(batch.insert()\
.values(name=name, for_entity=entity_name,
entity_id=entity_id, format_id=format_id,
created_by_id = user_id,
modified_by_id = user_id, version_id =1 )\
.returning(batch.c.id))
return await res.scalar()
示例12: start
def start(conf):
# connect to db
db.engine = engine = engine_from_config(dict(conf.items('sqlalchemy')), prefix='')
db.metadata.bind = engine
conn = engine.connect()
Session = sessionmaker(bind=engine)
session = Session()
profiles = []
topics = []
for user in session.query(User):
for profile in user.profiles:
if profile.origin == 5:
profiles.append(profile.profile_id)
for topic in user.topics:
if topic.profile_id in profiles:
topics.append(topic.topic_id)
for topic_id in topics:
print "checking", topic_id
s = select([func.count(db.t_message.c.message_id)], and_(db.t_message.c.origin == 5, db.t_message.c.topic_id == topic_id))
(count,) = conn.execute(s).fetchone()
if count > 1000:
(m_id,) = conn.execute(select([db.t_message.c.message_id],
db.t_message.c.topic_id == topic_id).order_by(
db.t_message.c.message_id.desc()).offset(1000).limit(1)).fetchone()
print "purging", topic_id, count, m_id
conn.execute(db.t_message.delete().where(and_(db.t_message.c.message_id < m_id, db.t_message.c.topic_id == topic_id)))
示例13: create_mapper
def create_mapper(tag_tbl, tag_domain_tbl, tag_predicate_tbl, tag_value_tbl,
tagging_tbl):
"Mapper factory."
m = mapper(Tag,
tag_tbl,
id_attribute='tag_id',
slug_expression=lambda cls: as_slug_expression(
func.concatenate(cls.domain, ':',
cls.predicate, '=',
cls.value)),
extension=TagMapperExtension(tag_domain_tbl,
tag_predicate_tbl, tag_value_tbl),
properties=
dict(tagged=relationship(Tagged,
secondary=tagging_tbl,
back_populates='tags'),
domain=column_property(
select([tag_domain_tbl.c.domain]) \
.where(tag_tbl.c.tag_domain_id ==
tag_domain_tbl.c.tag_domain_id)
),
predicate=column_property(
select([tag_predicate_tbl.c.predicate]) \
.where(tag_tbl.c.tag_predicate_id ==
tag_predicate_tbl.c.tag_predicate_id)
),
value=column_property(
select([tag_value_tbl.c.value]) \
.where(tag_tbl.c.tag_value_id ==
tag_value_tbl.c.tag_value_id)
),
)
)
return m
示例14: testMetricDataTimeStampQueryParams
def testMetricDataTimeStampQueryParams(uid):
'''
This test makes MetricDataHandler GET calls with from and to params :
_models/<uid>/data?from=<>&to=<>
'''
with repository.engineFactory().connect() as conn:
firstMetricData = conn.execute(
sql.select([schema.metric_data])
.where(schema.metric_data.c.uid == uid)
.order_by(sql.expression.asc(schema.metric_data.c.timestamp))
.limit(1)).fetchall()
lastMetricData = conn.execute(
sql.select([schema.metric_data])
.where(schema.metric_data.c.uid == uid)
.order_by(sql.expression.desc(schema.metric_data.c.timestamp))
.limit(1)).fetchall()
firstTimeStamp = firstMetricData[0].timestamp
lastTimeStamp = lastMetricData[0].timestamp
response = self.app.get("/%s/data?from=%s&to=%s"
% (uid, firstTimeStamp, lastTimeStamp), headers=self.headers)
assertions.assertSuccess(self, response)
getAllModelsResult = utils.jsonDecode(response.body)
for metricData in getAllModelsResult['data']:
self.assertGreaterEqual(datetime.strptime(metricData[0],
'%Y-%m-%d %H:%M:%S'), firstTimeStamp)
self.assertLessEqual(datetime.strptime(metricData[0],
'%Y-%m-%d %H:%M:%S'), lastTimeStamp)
示例15: get_school_info
def get_school_info(connection,school_id=None):
if school_id is None:
stmt = select([school])
return connection.execute(stmt)
else:
stmt = select([school]).where(school.c.id == school_id)
return connection.execute(stmt)