本文整理汇总了Python中sqlalchemy.func.coalesce函数的典型用法代码示例。如果您正苦于以下问题:Python coalesce函数的具体用法?Python coalesce怎么用?Python coalesce使用的例子?那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了coalesce函数的20个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于我们的系统推荐出更棒的Python代码示例。
示例1: results_by_district
def results_by_district(self):
""" Returns the results aggregated by the distict. """
counted = func.coalesce(func.bool_and(BallotResult.counted), False)
yeas = func.sum(BallotResult.yeas)
nays = func.sum(BallotResult.nays)
yeas_percentage = 100 * yeas / (
cast(func.coalesce(func.nullif(yeas + nays, 0), 1), Float)
)
nays_percentage = 100 - yeas_percentage
accepted = case({True: yeas > nays}, counted)
results = self.results.with_entities(
BallotResult.district.label('name'),
counted.label('counted'),
accepted.label('accepted'),
yeas.label('yeas'),
nays.label('nays'),
yeas_percentage.label('yeas_percentage'),
nays_percentage.label('nays_percentage'),
func.sum(BallotResult.empty).label('empty'),
func.sum(BallotResult.invalid).label('invalid'),
func.sum(BallotResult.eligible_voters).label('eligible_voters'),
func.array_agg(BallotResult.entity_id).label('entity_ids')
)
results = results.group_by(BallotResult.district)
results = results.order_by(None).order_by(BallotResult.district)
return results
开发者ID:OneGov,项目名称:onegov.ballot,代码行数:27,代码来源:ballot.py
示例2: packages_all_licenses_in_files
def packages_all_licenses_in_files(package_id):
pac = db.packages.alias()
pfi = db.packages_files.alias()
fil = db.files.alias()
fli = db.files_licenses.alias()
lic1 = license_approved_names().alias()
lic2 = license_approved_names().alias()
return (select([
pac.c.package_id,
pac.c.name .label('package_name'),
func.coalesce(lic1.c.short_name, lic2.c.short_name).label('license_short_name'),
func.count() .label('license_found_count')
])
.select_from(
pac
.join(pfi, pac.c.package_id == pfi.c.package_id)
.join(fil, pfi.c.file_id == fil.c.file_id)
.join(lic1, pfi.c.concluded_license_id == lic1.c.license_id, isouter=True)
.join(fli, fil.c.file_id == fli.c.file_id, isouter=True)
.join(lic2, fli.c.license_id == lic2.c.license_id)
)
.where(pac.c.package_id == package_id)
.group_by(
pac.c.package_id,
pac.c.name,
func.coalesce(lic1.c.short_name, lic2.c.short_name)
)
)
开发者ID:sschuberth,项目名称:dosocs2,代码行数:28,代码来源:queries.py
示例3: test_functional_ix_two
def test_functional_ix_two(self):
m1 = MetaData()
m2 = MetaData()
t1 = Table(
'foo', m1,
Column('id', Integer, primary_key=True),
Column('email', String(50)),
Column('name', String(50))
)
Index(
"email_idx",
func.coalesce(t1.c.email, t1.c.name).desc(), unique=True)
t2 = Table(
'foo', m2,
Column('id', Integer, primary_key=True),
Column('email', String(50)),
Column('name', String(50))
)
Index(
"email_idx",
func.coalesce(t2.c.email, t2.c.name).desc(), unique=True)
with assertions.expect_warnings(
"Skipped unsupported reflection",
"autogenerate skipping functional index"
):
diffs = self._fixture(m1, m2)
eq_(diffs, [])
开发者ID:RazerM,项目名称:alembic,代码行数:30,代码来源:test_autogen_indexes.py
示例4: _get_dep_statuses
def _get_dep_statuses(self, ti, session, dep_context):
TI = airflow.models.TaskInstance
TR = airflow.models.TriggerRule
# Checking that all upstream dependencies have succeeded
if not ti.task.upstream_list:
yield self._passing_status(
reason="The task instance did not have any upstream tasks.")
raise StopIteration
if ti.task.trigger_rule == TR.DUMMY:
yield self._passing_status(reason="The task had a dummy trigger rule set.")
raise StopIteration
# TODO(unknown): this query becomes quite expensive with dags that have many
# tasks. It should be refactored to let the task report to the dag run and get the
# aggregates from there.
qry = (
session
.query(
func.coalesce(func.sum(
case([(TI.state == State.SUCCESS, 1)], else_=0)), 0),
func.coalesce(func.sum(
case([(TI.state == State.EXCLUDED, 1)], else_=0)), 0),
func.coalesce(func.sum(
case([(TI.state == State.SKIPPED, 1)], else_=0)), 0),
func.coalesce(func.sum(
case([(TI.state == State.FAILED, 1)], else_=0)), 0),
func.coalesce(func.sum(
case([(TI.state == State.UPSTREAM_FAILED, 1)], else_=0)), 0),
func.count(TI.task_id),
)
.filter(
TI.dag_id == ti.dag_id,
TI.task_id.in_(ti.task.upstream_task_ids),
TI.execution_date == ti.execution_date,
TI.state.in_([
State.SUCCESS, State.FAILED, State.EXCLUDED,
State.UPSTREAM_FAILED, State.SKIPPED]),
)
)
successes, excluded, skipped, failed, upstream_failed, done = qry.first()
# Add excluded tasks into successful tasks as they are equivalent for
# dependency purposes. This is done in this way, not using the
# state_for_dependents function, due to the constraints of SQLAlchemy
# queries.
successes = successes + excluded
for dep_status in self._evaluate_trigger_rule(
ti=ti,
successes=successes,
skipped=skipped,
failed=failed,
upstream_failed=upstream_failed,
done=done,
flag_upstream_failed=dep_context.flag_upstream_failed,
session=session):
yield dep_status
开发者ID:owlabs,项目名称:incubator-airflow,代码行数:60,代码来源:trigger_rule_dep.py
示例5: get_info_op_bphtb
def get_info_op_bphtb(cls, p_kode, p_tahun):
pkey = FixLength(NOP)
pkey.set_raw(p_kode)
q = pbb_DBSession.query(cls.luas_bumi_sppt, cls.luas_bng_sppt,
cls.njop_bumi_sppt, cls.njop_bng_sppt, DatObjekPajak.jalan_op,
DatObjekPajak.blok_kav_no_op, DatObjekPajak.rt_op, DatObjekPajak.rw_op,
cls.nm_wp_sppt.label('nm_wp'),
func.coalesce(SpptOpBersama.luas_bumi_beban_sppt,0).label('luas_bumi_beban'),
func.coalesce(SpptOpBersama.luas_bng_beban_sppt,0).label('luas_bng_beban'),
func.coalesce(SpptOpBersama.njop_bumi_beban_sppt,0).label('njop_bumi_beban'),
func.coalesce(SpptOpBersama.njop_bng_beban_sppt,0).label('njop_bng_beban'))
q = q.filter(
cls.kd_propinsi == DatObjekPajak.kd_propinsi,
cls.kd_dati2 == DatObjekPajak.kd_dati2,
cls.kd_kecamatan == DatObjekPajak.kd_kecamatan,
cls.kd_kelurahan == DatObjekPajak.kd_kelurahan,
cls.kd_blok == DatObjekPajak.kd_blok,
cls.no_urut == DatObjekPajak.no_urut,
cls.kd_jns_op == DatObjekPajak.kd_jns_op)
q = q.outerjoin(SpptOpBersama)
return q.filter(cls.kd_propinsi == pkey['kd_propinsi'],
cls.kd_dati2 == pkey['kd_dati2'],
cls.kd_kecamatan == pkey['kd_kecamatan'],
cls.kd_kelurahan == pkey['kd_kelurahan'],
cls.kd_blok == pkey['kd_blok'],
cls.no_urut == pkey['no_urut'],
cls.kd_jns_op == pkey['kd_jns_op'],
cls.thn_pajak_sppt == p_tahun)
开发者ID:aagusti,项目名称:opensipkd-rpc,代码行数:28,代码来源:pbb.py
示例6: do_job
def do_job(self):
"""
SELECT *, coalesce(end_time,now)-start_time
FROM task t, subject s
WHERE t.subject_id=s.id AND start_time > GetDate() - @days
ORDER BY start_time
:return:
"""
session = DBSession()
now = datetime.now()
time_worked = (func.julianday(func.coalesce(Task.end_time,now)) - func.julianday(Task.start_time)) * 86400
query = session.query(Task.start_time,
func.coalesce(Task.end_time, now),
time_worked,
Subject.title,
Task.title) \
.filter(Subject.id==Task.subject_id) \
.filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \
.order_by(Task.start_time)
print '\n'
table = PrettyTable(['Start', 'End', 'Time', 'Subject', 'Title'])
table.align["Title"] = "l"
total_time = 0
day_total = 0
last_date = None
for row in query:
if last_date == None:
last_date = row[0].date()
if row[0].date() != last_date:
table.add_row([
'', '', timedelta(seconds=round(day_total)), '', ''
])
last_date = row[0].date()
day_total = 0
day_total += row[2]
total_time += row[2]
table.add_row([
row[0],
row[1],
timedelta(seconds=round(row[2])),
row[3],
row[4],
])
if day_total > 0:
table.add_row([
'', '', timedelta(seconds=round(day_total)), '', ''
])
print table
print 'Total Work time: %s' % timedelta(seconds=total_time)
print
开发者ID:sonologic,项目名称:timesheet,代码行数:60,代码来源:daily_detail.py
示例7: _total
def _total(self, balance_uid):
model = request.environ["sqlalchemy.model"]
db = request.environ["sqlalchemy.session"]
incomes = db.execute(
select(
[func.coalesce(func.sum(model.BalanceChange.c.amount), 0)],
and_(model.BalanceChange.c.balance_uid == balance_uid, model.BalanceChange.c.is_income == True),
from_obj=[model.balance_changes_table],
)
).fetchone()[0]
expenses = db.execute(
select(
[func.coalesce(func.sum(model.BalanceChange.c.amount), 0)],
and_(model.BalanceChange.c.balance_uid == balance_uid, model.BalanceChange.c.is_income == False),
from_obj=[model.balance_changes_table],
)
).fetchone()[0]
try:
return incomes - expenses
except:
log.error("", exc_info=1)
return 0
开发者ID:pawelniewie,项目名称:5groszy.pl,代码行数:25,代码来源:balances.py
示例8: _ops_for_date_range
def _ops_for_date_range(self, balance_uids, start_date, end_date, tags = [], change_categories = []):
model = self.get_sa_model()
db = self.get_sa_session()
conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])]
if start_date:
conditions.append(model.BalanceChange.occurred_on >= start_date)
if end_date:
conditions.append(model.BalanceChange.occurred_on <= end_date)
if isinstance(tags, list) and len(tags) > 0:
conditions.extend([model.BalanceChange.tags.any(tag=tag.strip().lower()) for tag in tags if tag is not None and tag.strip() != ''])
if isinstance(change_categories, list) and len(change_categories) > 0:
conditions.extend([model.BalanceChange.change_category_uid == value.strip() for value in change_categories if value is not None and value.strip() != ''])
try:
summary = db.execute(select([
func.coalesce(func.sum(
case([[model.BalanceChange.amount<0, model.BalanceChange.amount]], else_=0).label("expenses"))),
func.coalesce(func.sum(
case([[model.BalanceChange.amount>0, model.BalanceChange.amount]], else_=0).label("incomes")))
],
and_(*conditions),
from_obj=[model.balance_changes_table])).fetchone()
return {
"expenses": summary[0],
"incomes": summary[1],
}
except:
log.error(_("Can't get summary"), exc_info=1)
return 0
开发者ID:pawelniewie,项目名称:5groszy.pl,代码行数:34,代码来源:balance_changes.py
示例9: advanced_search
def advanced_search(self, **kwargs):
super(TurnoversQueryBuilder, self).advanced_search(**kwargs)
self._filter_account(kwargs.get('account_id'))
if 'date_from' in kwargs or 'date_to' in kwargs:
self._filter_date(
kwargs.get('date_from'), kwargs.get('date_to')
)
self._cashflows_from_sub = self._cashflows_from_sub.subquery()
self._cashflows_to_sub = self._cashflows_to_sub.subquery()
self.query = (
self.query
.outerjoin(
self._cashflows_from_sub,
self._cashflows_from_sub.c.account_item_id == AccountItem.id
)
.outerjoin(
self._cashflows_to_sub,
self._cashflows_to_sub.c.account_item_id == AccountItem.id
)
)
balance_condition = or_(
self._cashflows_to_sub.c.revenue != None,
self._cashflows_from_sub.c.expenses != None
)
balance_expression = (
func.coalesce(self._cashflows_to_sub.c.revenue, 0)
- func.coalesce(self._cashflows_from_sub.c.expenses, 0)
)
balance_case = (balance_condition, balance_expression)
self.update_fields({
'expenses': self._cashflows_from_sub.c.expenses,
'revenue': self._cashflows_to_sub.c.revenue,
'balance': case([balance_case,], else_=None),
})
ResourcesQueryBuilder.build_query(self)
开发者ID:alishir,项目名称:tcr,代码行数:35,代码来源:turnovers.py
示例10: _filter_payment
def _filter_payment(self, payment_from, payment_to):
if payment_from:
self.query = self.query.filter(
func.coalesce(self._sum_payments.c.payments, 0) >= payment_from
)
if payment_to:
self.query = self.query.filter(
func.coalesce(self._sum_payments.c.payments, 0) <= payment_to
)
开发者ID:alishir,项目名称:tcr,代码行数:9,代码来源:invoices.py
示例11: _filter_price
def _filter_price(self, price_from, price_to):
if price_from:
self.query = self.query.filter(
func.coalesce(LeadItem.price_from, 0) >= price_from,
)
if price_to:
self.query = self.query.filter(
func.coalesce(LeadItem.price_to, 0) <= price_to,
)
开发者ID:alishir,项目名称:tcr,代码行数:9,代码来源:leads.py
示例12: percentage_by_district
def percentage_by_district(self):
""" Returns the percentage of votes aggregated by the distict. Includes
uncounted districts and districts with no results available.
"""
results = self.election.results
results = results.join(ElectionResult.candidate_results)
results = results.filter(CandidateResult.candidate_id == self.id)
results = results.with_entities(
ElectionResult.district.label('name'),
func.array_agg(ElectionResult.entity_id).label('entities'),
func.coalesce(
func.bool_and(ElectionResult.counted), False
).label('counted'),
func.sum(ElectionResult.accounted_ballots).label('total'),
func.sum(CandidateResult.votes).label('votes'),
)
results = results.group_by(ElectionResult.district)
results = results.order_by(None)
results = results.all()
percentage = {
r.name: {
'counted': r.counted,
'entities': r.entities,
'percentage': 100 * (r.votes / r.total) if r.total else 0.0
} for r in results
}
empty = self.election.results
empty = empty.with_entities(
ElectionResult.district.label('name'),
func.array_agg(ElectionResult.entity_id).label('entities'),
func.coalesce(
func.bool_and(ElectionResult.counted), False
).label('counted')
)
empty = empty.group_by(ElectionResult.district)
empty = empty.order_by(None)
for result in empty:
update = (
result.name not in percentage
or (
set(percentage[result.name]['entities'])
!= set(result.entities)
)
)
if update:
percentage[result.name] = {
'counted': result.counted,
'entities': result.entities,
'percentage': 0.0
}
return percentage
开发者ID:OneGov,项目名称:onegov.ballot,代码行数:55,代码来源:candidate.py
示例13: index
def index(self, format='html'):
"""GET /rdfdocs: All items in the collection"""
# url('rdfdocs')
rdfdocs = Session.query(model.RDFDoc).filter(model.RDFDoc.active==True).join(model.RDFDoc.current_stats)
c.query_string = '?'
c.search = ''
if request.GET.has_key('search'):
rdfdocs = rdfdocs.filter(model.RDFDoc.name.ilike("%%%s%%" % request.GET['search']))
c.query_string += 'search=%s&' % request.GET['search']
c.search = request.GET['search']
if request.GET.has_key('errors'):
rdfdocs = rdfdocs.filter(model.StatResult.errors!=None)
c.query_string += 'errors=1&'
if request.GET.has_key('valid'):
rdfdocs = rdfdocs.filter(model.StatResult.errors==None)
c.query_string += 'valid=1&'
if request.GET.has_key('sparql'):
rdfdocs = rdfdocs.filter(model.RDFDoc.format=='sparql')
c.query_string += 'sparql=1&'
if request.GET.has_key('dumps'):
rdfdocs = rdfdocs.filter(model.RDFDoc.format!='sparql')
c.query_string += 'dumps=1&'
c.sort_order = request.GET.get('sort')
if request.GET.has_key('sort'):
if request.GET['sort'] == 'triples':
c.rdfdocs = rdfdocs.order_by(desc(func.coalesce(model.StatResult.triples, '0')))
elif request.GET['sort'] == 'warnings':
c.rdfdocs = rdfdocs.order_by(desc(func.coalesce(model.StatResult.warnings, '0')))
elif request.GET['sort'] == 'format':
c.rdfdocs = rdfdocs.order_by(func.coalesce(model.RDFDoc.format, '0'))
elif request.GET['sort'] == 'issue':
c.rdfdocs = rdfdocs.order_by(model.StatResult.errors)
elif request.GET['sort'] == 'update':
c.rdfdocs = rdfdocs.order_by(model.RDFDoc.last_updated.desc())
else:
c.rdfdocs = rdfdocs.order_by(model.RDFDoc.worked_on.desc(), model.RDFDoc.name, model.RDFDoc.last_updated.desc())
else:
c.rdfdocs = rdfdocs.order_by(model.RDFDoc.worked_on.desc(), model.RDFDoc.name, model.RDFDoc.last_updated.desc())
if request.GET.has_key('page'):
page = request.GET['page']
else:
page = 1
page_url = PageURL_WebOb(request)
c.rdfdocs_page = Page(c.rdfdocs, page=page, items_per_page=50, url=page_url)
c.rdfdoc_count = c.rdfdocs.count()
c.workers = Session.query(model.WorkerProc).order_by(model.WorkerProc.started.desc()).all()
if format=='json' or 'application/json' in request.headers.get('accept', ''):
response.content_type = 'application/json'
json_rdfdocs = []
for r in rdfdocs:
json_rdfdocs.append(r.name)
return json.dumps(json_rdfdocs)
return render('/rdfdoc/index.html')
开发者ID:AKSW,项目名称:LODStats_WWW,代码行数:53,代码来源:rdfdocs.py
示例14: get_bayar
def get_bayar(cls, p_kode):
pkey = FixLength(NOP)
pkey.set_raw(p_kode)
query = pbb_DBSession.query(
func.concat(cls.kd_propinsi, '.').concat(cls.kd_dati2).concat('-').\
concat(cls.kd_kecamatan).concat('.').concat(cls.kd_kelurahan).concat('-').\
concat(cls.kd_blok).concat('.').concat(cls.no_urut).concat('-').\
concat(cls.kd_jns_op).label('nop'), cls.thn_pajak_sppt,
cls.nm_wp_sppt, cls.jln_wp_sppt, cls.blok_kav_no_wp_sppt,
cls.rw_wp_sppt, cls.rt_wp_sppt, cls.kelurahan_wp_sppt,
cls.kota_wp_sppt, cls.kd_pos_wp_sppt, cls.npwp_sppt,
cls.kd_kls_tanah, cls.kd_kls_bng,
cls.luas_bumi_sppt, cls.luas_bng_sppt,
cls.njop_bumi_sppt, cls.njop_bng_sppt, cls.njop_sppt,
cls.njoptkp_sppt, cls.pbb_terhutang_sppt, cls.faktor_pengurang_sppt,
cls.status_pembayaran_sppt,
cls.tgl_jatuh_tempo_sppt,
cls.pbb_yg_harus_dibayar_sppt.label('pokok'),
func.max(PembayaranSppt.tgl_pembayaran_sppt).label('tgl_pembayaran_sppt'),
func.sum(func.coalesce(PembayaranSppt.jml_sppt_yg_dibayar,0)).label('bayar'),
func.sum(func.coalesce(PembayaranSppt.denda_sppt,0)).label('denda_sppt'),).\
outerjoin(PembayaranSppt,and_(
cls.kd_propinsi==PembayaranSppt.kd_propinsi,
cls.kd_dati2==PembayaranSppt.kd_dati2,
cls.kd_kecamatan==PembayaranSppt.kd_kecamatan,
cls.kd_kelurahan==PembayaranSppt.kd_kelurahan,
cls.kd_blok==PembayaranSppt.kd_blok,
cls.no_urut==PembayaranSppt.no_urut,
cls.kd_jns_op==PembayaranSppt.kd_jns_op,
cls.thn_pajak_sppt==PembayaranSppt.thn_pajak_sppt
)).\
group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan,
cls.kd_blok, cls.no_urut, cls.kd_jns_op, cls.thn_pajak_sppt,
cls.nm_wp_sppt, cls.jln_wp_sppt, cls.blok_kav_no_wp_sppt,
cls.rw_wp_sppt, cls.rt_wp_sppt, cls.kelurahan_wp_sppt,
cls.kota_wp_sppt, cls.kd_pos_wp_sppt, cls.npwp_sppt,
cls.kd_kls_tanah, cls.kd_kls_bng,
cls.luas_bumi_sppt, cls.luas_bng_sppt,
cls.njop_bumi_sppt, cls.njop_bng_sppt, cls.njop_sppt,
cls.njoptkp_sppt, cls.pbb_terhutang_sppt, cls.faktor_pengurang_sppt,
cls.status_pembayaran_sppt,
cls.tgl_jatuh_tempo_sppt,
cls.pbb_yg_harus_dibayar_sppt.label('pokok'),)
return query.filter(cls.kd_propinsi == pkey['kd_propinsi'],
cls.kd_dati2 == pkey['kd_dati2'],
cls.kd_kecamatan == pkey['kd_kecamatan'],
cls.kd_kelurahan == pkey['kd_kelurahan'],
cls.kd_blok == pkey['kd_blok'],
cls.no_urut == pkey['no_urut'],
cls.kd_jns_op == pkey['kd_jns_op'],)
开发者ID:aagusti,项目名称:opensipkd-rpc,代码行数:51,代码来源:pbb.py
示例15: get_colour_map
def get_colour_map(cls):
colour = func.coalesce(Category.colour, ParentCategory.colour)
return dict(
Category.query.outerjoin(ParentCategory, ParentCategory.id == Category.parent)
.filter(colour.isnot(None))
.values(Category.id, colour)
)
开发者ID:wonderpl,项目名称:dolly-web,代码行数:7,代码来源:models.py
示例16: _ops_for_date_range
def _ops_for_date_range(self, balance_uids, start_date, end_date, incomes = True, expenses = False):
"""
Will be removed in next release.
"""
model = request.environ["sqlalchemy.model"]
db = request.environ["sqlalchemy.session"]
conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])]
if incomes:
conditions.append(model.BalanceChange.amount >= 0)
if expenses:
conditions.append(model.BalanceChange.amount < 0)
if start_date:
conditions.append(model.BalanceChange.occurred_on >= start_date)
if end_date:
conditions.append(model.BalanceChange.occurred_on <= end_date)
try:
return db.execute(select([func.coalesce(func.sum(model.BalanceChange.amount), 0).label("summary")],
and_(*conditions),
from_obj=[model.balance_changes_table])).fetchone()[0]
except:
log.error("", exc_info=1)
return 0
开发者ID:pawelniewie,项目名称:5groszy.pl,代码行数:26,代码来源:balance_changes.py
示例17: build_query_to_populate
def build_query_to_populate(self, query, full_table, aggregate_table):
insert_columns = [aggregate_table.c.join_key]
fk = Column(self.key, Integer)
geom = Column(self.geometry_column, Geometry())
bins_table = Table(self.table, full_table.metadata, fk, geom)
if self.join_custom_data:
extra_data = Table("extra_data", full_table.metadata,
Column("verified", Boolean),
Column("timestamp", DateTime),
Column("client_ip", Integer),
Column("server_ip", Integer),
Column("location", Geometry("Point", srid=4326)),
keep_existing = True)
joining = join(full_table, extra_data,
and_(extra_data.c.client_ip == full_table.c.client_ip,
extra_data.c.server_ip == full_table.c.server_ip,
extra_data.c.timestamp == full_table.c.time),
isouter = True)
query = query.select_from(joining)
location = case([(extra_data.c.verified, func.coalesce(extra_data.c.location, full_table.c.location))], else_ = full_table.c.location)
else:
location = full_table.c.location
select_query = (query.select_from(bins_table)
.where(ST_Intersects(location, geom))
.column(fk)
.group_by(fk))
return insert_columns, select_query
开发者ID:lilbludot,项目名称:piecewise,代码行数:30,代码来源:aggregate.py
示例18: build_query_to_populate
def build_query_to_populate(self, query, full_table, aggregate_table):
insert_columns = [aggregate_table.c.join_key]
fk = Column(self.key, Integer)
geom = Column(self.geometry_column, Geometry())
bins_table = Table(self.table, full_table.metadata, fk, geom)
if self.join_custom_data:
extra_data = Table("extra_data", full_table.metadata,
Column("timestamp", DateTime),
Column("verified", Boolean),
Column("bigquery_key", String),
Column("connection_type", String),
Column("advertised_download", Integer),
Column("actual_download", Float),
Column("advertised_upload", Integer),
Column("actual_upload", Float),
Column("min_rtt", Integer),
Column("location_type", String),
Column("cost_of_service", Integer),
Column("location", Geometry("Point", srid=4326)),
keep_existing = True)
joining = join(full_table, extra_data,
and_(extra_data.c.bigquery_key == full_table.c.bigquery_key),
isouter = True)
query = query.select_from(joining)
location = case([(extra_data.c.verified, func.coalesce(extra_data.c.location, full_table.c.location))], else_ = full_table.c.location)
else:
location = full_table.c.location
select_query = (query.select_from(bins_table)
.where(ST_Intersects(location, geom))
.column(fk)
.group_by(fk))
return insert_columns, select_query
开发者ID:mchelen,项目名称:piecewise,代码行数:35,代码来源:aggregate.py
示例19: update_canonicals
def update_canonicals(canonicals):
'''
Update canonical data for android devices.
'''
global ENGINE
binding = [{"p_{}".format(k): v for k, v in canonical.items()} for canonical in canonicals]
device_table = model.metadata.tables['device']
stmt = update(device_table).\
values(device_token_new=bindparam('p_new_token')).\
where(and_(device_table.c.login_id == bindparam('p_login_id'),
func.coalesce(device_table.c.device_token_new, device_table.c.device_token) == bindparam('p_old_token')))
ENGINE.execute(stmt, binding)
with session_scope() as session:
query = text('SELECT keep_max_users_per_device( \
(:platform_id)::int2, :device_token, (:max_users_per_device)::int2)')
for canonical in canonicals:
session.execute(query,
{'platform_id': constants.PLATFORM_ANDROID,
'device_token': canonical['new_token'],
'max_users_per_device': config.max_users_per_device
})
session.execute(query,
{'platform_id': constants.PLATFORM_ANDROID_TABLET,
'device_token': canonical['new_token'],
'max_users_per_device': config.max_users_per_device
})
session.commit()
开发者ID:Nordeus,项目名称:pushkin,代码行数:28,代码来源:database.py
示例20: _set_orderby_desc
def _set_orderby_desc(self, query, model, limit,
last_id, offset, descending, orderby):
"""Return an updated query with the proper orderby and desc."""
if orderby == 'fav_user_ids':
n_favs = func.coalesce(func.array_length(model.fav_user_ids, 1), 0).label('n_favs')
query = query.add_column(n_favs)
if orderby in ['created', 'updated', 'finish_time']:
if descending:
query = query.order_by(desc(
cast(getattr(model,
orderby),
TIMESTAMP)))
else:
query = query.order_by(cast(getattr(model, orderby), TIMESTAMP))
else:
if orderby != 'fav_user_ids':
if descending:
query = query.order_by(desc(getattr(model, orderby)))
else:
query = query.order_by(getattr(model, orderby))
else:
if descending:
query = query.order_by(desc("n_favs"))
else:
query = query.order_by("n_favs")
if last_id:
query = query.limit(limit)
else:
query = query.limit(limit).offset(offset)
return query
开发者ID:influencerplus123,项目名称:tinybee.ai,代码行数:30,代码来源:__init__.py
注:本文中的sqlalchemy.func.coalesce函数示例由纯净天空整理自Github/MSDocs等源码及文档管理平台,相关代码片段筛选自各路编程大神贡献的开源项目,源码版权归原作者所有,传播和使用请参考对应项目的License;未经允许,请勿转载。 |
请发表评论