def relatValorDia(self): try: # Abrindo Sessao conecta = Conexao() sessao = conecta.Session() # Query row = (sessao.query( func.COALESCE(func.SUM(Venda.valor_recebido), 0).label('vendido'), func.COUNT(distinct( Venda.id_cliente)).label('cliente')).filter( Venda.data_emissao.between(self.dataEmissao, self.dataFim))) row.all() # salvando resultado for query in row: self.valorRecebido = str(query.vendido).replace('.', ',') self.idCliente = query.cliente # Fechando a COnexao sessao.close() except IntegrityError as err: print(err)
def get_archive_month(cls, session): """获取所有有文章发布的月份和这个月份发布的文章数量,用于显示归档(archive)信息 :return: 返回二维元组(month, article_count)的列表 """ final_result = [] # 获取所有发布过文章的月份 month_baked_sql = sql_bakery(lambda session: session.query( func.DATE_FORMAT(Post.publish_time, "%Y%m").distinct().label( "publish_month"))) month_baked_sql += lambda q: q.filter(Post.publish_time < bindparam( 'now')).order_by(text("publish_month desc")) # 获取特定月份发布的文章数量 article_count_query = sql_bakery( lambda session: session.query(func.COUNT(Post.id))) article_count_query += lambda q: q.filter( func.DATE_FORMAT(Post.publish_time, "%Y%m") == bindparam('month')) month_results = month_baked_sql(session).\ params(now=datetime.now()).all() for result in month_results: article_count = article_count_query(session).\ params(month=result.publish_month).scalar() final_result.append((datetime.strptime(result.publish_month, "%Y%m"), article_count)) return final_result
def d_status(): record = donation.query.add_columns( donation.status, func.COUNT(donation.id).label('count')).group_by( donation.status).all() return record
def top_contributors(self): return self.session.query( Signature.signer_key.label('key'), Signature.signer_name.label('name'), Signature.signer_email.label('email'), func.COUNT(Signature.signer_key).label('num_sigs') ).filter(Signature.signer_key != self.ca_key).\ group_by(Signature.signer_key).order_by(desc('num_sigs')).limit(10).all()
def thrusts(): record = event_information.query.add_columns( event_information.thrust, func.COUNT(event_information.thrust).label('count')).group_by( event_information.thrust).all() return record
def member_count(): record = community_member.query.add_columns( community_member.community_id, func.COUNT(community_member.member_id).label('count')).group_by( community_member.community_id).all() return record
def status(): record = event_information.query.add_columns( event_information.status, func.COUNT(event_information.id).label('count')).group_by( event_information.status).all() return record
def ratings(value): record = event_participation.query.add_columns( event_participation.rating, func.COUNT(event_participation.rating).label('count')).group_by( event_participation.rating).filter( event_participation.event_id == value).all() return record
def thrusts(): record = user_information.query.add_columns( user_information.thrust, func.COUNT(user_information.thrust).label('count')).group_by( user_information.thrust).filter( user_information.thrust != 0).all() return record
def user_type(): record = user_account.query.add_columns( user_account.type, func.COUNT(user_account.id).label('count')).group_by( user_account.type).filter( and_(user_account.type != 1, user_account.type != 5)).all() return record
def report_open_close_passbook_month(passbook_type, from_month, to_month): from_month = from_month + ' 00:00:00' to_month = to_month + ' 00:00:00' result = db.session.query( TransactionSlip.transaction_date.cast(Date).label('transaction_date'), func.COUNT(func.IF(TransactionSlip.transaction_type == TransactionType.OPEN_PASSBOOK, 1, None)) .label('passbook_open'), func.COUNT(func.IF(and_(TransactionSlip.transaction_type == TransactionType.WITHDRAW, Passbook.balance_amount == 0), 1, None)) .label('passbook_close'), ) \ .join(Passbook, Passbook.id == TransactionSlip.passbook_id) \ .join(PassbookTypes, PassbookTypes.id == Passbook.passbook_type_id) \ .filter(PassbookTypes.id == passbook_type) \ .filter(TransactionSlip.transaction_date.between(from_month, to_month)) \ .group_by(TransactionSlip.transaction_date.cast(Date)) \ .all() return result
def get_specific_month_hold_count(hold_date): """ 获取某天有持仓用户 :return: list """ # db = database.connection('portfolio_sta') # metadata = MetaData(bind=db) t = Table('ds_share', metadata, autoload=True) # Session = sessionmaker(bind=db) # session = Session() rst = session.query(func.COUNT(func.DISTINCT(t.c.ds_uid))).filter( \ t.c.ds_date == hold_date, t.c.ds_amount > 0) return rst.all()
def get_specific_month_num_naive(s_date, e_date, t_type): """ 获取某个时间段内ds_trade_type=t_type的且uid在uids内的用户数 :param s_date: string, 开始日期 :param e_date: string, 结束日期 :param t_type: array, 交易类型 :return: int """ # db = database.connection('portfolio_sta') # metadata = MetaData(bind=db) t = Table('ds_order_pdate', metadata, autoload=True) # Session = sessionmaker(bind=db) # session = Session() rst = session.query(func.COUNT(func.DISTINCT(t.c.ds_uid))).filter( \ t.c.ds_placed_date >= s_date, \ t.c.ds_placed_date <= e_date, \ t.c.ds_trade_type.in_(t_type)) return rst.all()
def top_contributors_by_month(self): grouped_sigs = self.session.query( Signature.signer_key.label('key'), Signature.signer_name.label('name'), Signature.signer_email.label('email'), func.strftime('%Y-%m', Signature.sign_date).label('sign_month'), func.COUNT(Signature.id).label('num_sigs')).join(Key).filter( Signature.signer_key != self.ca_key).group_by( 'key', 'sign_month').subquery() return self.session.query( grouped_sigs.c.key, grouped_sigs.c.name, grouped_sigs.c.email, grouped_sigs.c.sign_month, grouped_sigs.c.num_sigs ).group_by(grouped_sigs.c.sign_month).having(grouped_sigs.c.num_sigs==func.MAX(grouped_sigs.c.num_sigs)).\ order_by(desc(grouped_sigs.c.sign_month)).limit(24).all()
def signs_per_month_plot(self): signs_per_month_data = self.domain_query( func.strftime('%Y-%m', Signature.sign_date).label('sign_month'), func.COUNT(Signature.id).label('num_sigs')).join(Key).filter( Signature.sign_date > date.today() - relativedelta(years=2)).group_by('sign_month').order_by( asc('sign_month')).all() data = dict(signs=[sig.num_sigs for sig in signs_per_month_data], months=[sig.sign_month for sig in signs_per_month_data]) b = Bar(data, values='signs', label='months', plot_width=800, legend=False, color='#0275d8') return components(b)
def queries(table): """Given dictionaries of tables and view-queries, return a dictionary of all the rest of the queries I need. """ def update_where(updcols, wherecols): """Return an ``UPDATE`` statement that updates the columns ``updcols`` when the ``wherecols`` match. Every column has a bound parameter of the same name. updcols are strings, wherecols are column objects """ vmap = OrderedDict() for col in updcols: vmap[col] = bindparam(col) wheres = [c == bindparam(c.name) for c in wherecols] tab = wherecols[0].table return tab.update().values(**vmap).where(and_(*wheres)) r = LiSE.allegedb.alchemy.queries_for_table_dict(table) rulebooks = table['rulebooks'] r['rulebooks_update'] = update_where(['rules'], [ rulebooks.c.rulebook, rulebooks.c.branch, rulebooks.c.turn, rulebooks.c.tick ]) for t in table.values(): key = list(t.primary_key) if 'branch' in t.columns and 'turn' in t.columns and 'tick' in t.columns: branch = t.columns['branch'] turn = t.columns['turn'] tick = t.columns['tick'] if branch in key and turn in key and tick in key: key = [branch, turn, tick] r[t.name + '_dump'] = select(list(t.c.values())).order_by(*key) r[t.name + '_insert'] = t.insert().values( tuple(bindparam(cname) for cname in t.c.keys())) r[t.name + '_count'] = select([func.COUNT('*')]).select_from(t) r['del_char_things'] = table['things'].delete().where( table['things'].c.character == bindparam('character')) r['del_char_avatars'] = table['avatars'].delete().where( table['avatars'].c.character_graph == bindparam('character')) things = table['things'] r['del_things_after'] = things.delete().where( and_( things.c.character == bindparam('character'), things.c.thing == bindparam('thing'), things.c.branch == bindparam('branch'), or_( things.c.turn > bindparam('turn'), and_(things.c.turn == bindparam('turn'), things.c.tick >= bindparam('tick'))))) avatars = table['avatars'] r['del_avatars_after'] = avatars.delete().where( and_( avatars.c.character_graph == bindparam('character'), avatars.c.avatar_graph == bindparam('graph'), avatars.c.avatar_node == bindparam('avatar'), avatars.c.branch == bindparam('branch'), or_( avatars.c.turn > bindparam('turn'), and_(avatars.c.turn == bindparam('turn'), avatars.c.tick >= bindparam('tick'))))) for handledtab in ('character_rules_handled', 'avatar_rules_handled', 'character_thing_rules_handled', 'character_place_rules_handled', 'character_portal_rules_handled', 'node_rules_handled', 'portal_rules_handled'): ht = table[handledtab] r['del_{}_turn'.format(handledtab)] = ht.delete().where( and_(ht.c.branch == bindparam('branch'), ht.c.turn == bindparam('turn'))) branches = table['branches'] r['branch_children'] = select( [branches.c.branch]).where(branches.c.parent == bindparam('branch')) tc = table['turns_completed'] r['turns_completed_update'] = update_where(['turn'], [tc.c.branch]) return r
def count_table(session, table): c = session.query(func.COUNT('*').label('rows')).select_from(table) return c.scalar()
def compute_features_from_osm(config): osm_tables = config['OSM'] bounding_box = WKTElement(config['BOUNDING_BOX'], srid=4326) grid_obj = config['GRID_OBJ'] geo_feature_obj = config['GEO_FEATURE_OBJ'] try: for feature_name, osm_table in osm_tables.items(): geo_feature_type = osm_table.wkb_geometry.type.geometry_type cropped_osm = crop_osm( osm_table, bounding_box) # crop the OSM data with a bounding box sub_query = session.query(grid_obj.gid, cropped_osm.c.fclass, func.ST_GeogFromWKB( func.ST_Intersection(grid_obj.geom, cropped_osm.c.wkb_geometry)) .label('intersection')) \ .filter(func.ST_Intersects(grid_obj.geom, cropped_osm.c.wkb_geometry)).subquery() results = [] if geo_feature_type == 'MULTIPOLYGON': results = session.query(sub_query.c.gid.label('gid'), sub_query.c.fclass.label('feature_type'), literal(feature_name).label('geo_feature'), func.SUM(func.ST_AREA(sub_query.c.intersection)).label('value'), literal('area').label('measurement')) \ .group_by(sub_query.c.gid, sub_query.c.fclass).all() elif geo_feature_type == 'MULTILINESTRING': results = session.query(sub_query.c.gid.label('gid'), sub_query.c.fclass.label('feature_type'), literal(feature_name).label('geo_feature'), func.SUM(func.ST_LENGTH(sub_query.c.intersection)).label('value'), literal('length').label('measurement')) \ .group_by(sub_query.c.gid, sub_query.c.fclass).all() elif geo_feature_type == 'POINT': results = session.query(sub_query.c.gid.label('gid'), sub_query.c.fclass.label('feature_type'), literal(feature_name).label('geo_feature'), func.COUNT(sub_query.c.intersection).label('value'), literal('count').label('measurement')) \ .group_by(sub_query.c.gid, sub_query.c.fclass).all() else: pass obj_results = [] for res in results: obj_results.append( geo_feature_obj(gid=res[0], feature_type=res[1], geo_feature=res[2], value=res[3], measurement=res[4])) # session.add_all(obj_results) # session.commit() print('{} has finished'.format(feature_name)) return except Exception as e: print(e) exit(-1)
def queries(table): """Given dictionaries of tables and view-queries, return a dictionary of all the rest of the queries I need. """ def update_where(updcols, wherecols): """Return an ``UPDATE`` statement that updates the columns ``updcols`` when the ``wherecols`` match. Every column has a bound parameter of the same name. updcols are strings, wherecols are column objects """ vmap = OrderedDict() for col in updcols: vmap[col] = bindparam(col) wheres = [c == bindparam(c.name) for c in wherecols] tab = wherecols[0].table return tab.update().values(**vmap).where(and_(*wheres)) r = allegedb.alchemy.queries_for_table_dict(table) for t in table.values(): r[t.name + '_dump'] = select(list( t.c.values())).order_by(*t.primary_key) r[t.name + '_insert'] = t.insert().values( tuple(bindparam(cname) for cname in t.c.keys())) r[t.name + '_count'] = select([func.COUNT('*')]).select_from(t) r['del_char_things'] = table['things'].delete().where( table['things'].c.character == bindparam('character')) r['del_char_avatars'] = table['avatars'].delete().where( table['avatars'].c.character_graph == bindparam('character')) things = table['things'] r['del_things_after'] = things.delete().where( and_( things.c.character == bindparam('character'), things.c.thing == bindparam('thing'), things.c.branch == bindparam('branch'), or_( things.c.turn > bindparam('turn'), and_(things.c.turn == bindparam('turn'), things.c.tick >= bindparam('tick'))))) avatars = table['avatars'] r['del_avatars_after'] = avatars.delete().where( and_( avatars.c.character_graph == bindparam('character'), avatars.c.avatar_graph == bindparam('graph'), avatars.c.avatar_node == bindparam('avatar'), avatars.c.branch == bindparam('branch'), or_( avatars.c.turn > bindparam('turn'), and_(avatars.c.turn == bindparam('turn'), avatars.c.tick >= bindparam('tick'))))) branches = table['branches'] r['branch_children'] = select( [branches.c.branch]).where(branches.c.parent == bindparam('branch')) tc = table['turns_completed'] r['turns_completed_update'] = update_where(['turn'], [tc.c.branch]) return r
def total_keys_and_sigs(self): sigs = self.domain_query( func.COUNT(Signature.id).label('num_sigs'), Signature.sign_date).join(Key).filter( Signature.sign_date > date.today() - relativedelta(years=2)).group_by(Signature.sign_date).order_by( asc(Signature.sign_date)).all() current_num_sigs = self.session.query(Signature).filter( Signature.sign_date <= date.today() - relativedelta(years=2)).count() if self.ca_key is not None: ca_sigs = self.domain_query( func.COUNT(Signature.id).label('num_sigs'), Signature.sign_date).join(Key).filter( Signature.sign_date > date.today() - relativedelta(years=2), Signature.signer_key == self.ca_key).group_by( Signature.sign_date).order_by(asc( Signature.sign_date)).all() current_num_ca_sigs = self.session.query(Signature).filter( Signature.sign_date <= date.today() - relativedelta(years=2), Signature.signer_key == self.ca_key).count() else: ca_sigs = [] current_num_ca_sigs = 0 keys = self.domain_query( func.COUNT(Key.id).label('num_keys'), Key.created).filter( Key.created > date.today() - relativedelta(years=2)).group_by( Key.created).order_by(asc(Key.created)).all() current_num_keys = self.session.query(Key).filter( Key.created <= date.today() - relativedelta(years=2)).count() sig_dates = [pandas.Timestamp(sig.sign_date) for sig in sigs] ca_sig_dates = [pandas.Timestamp(sig.sign_date) for sig in ca_sigs] key_dates = [pandas.Timestamp(key.created) for key in keys] dates = list(set(sig_dates + key_dates + ca_sig_dates)) dates.sort() date_num_sigs = [] date_num_ca_sigs = [] date_num_keys = [] for d in dates: try: i = sig_dates.index(d) current_num_sigs = current_num_sigs + sigs[i].num_sigs except: pass finally: date_num_sigs.append(current_num_sigs) try: i = ca_sig_dates.index(d) current_num_ca_sigs = current_num_ca_sigs + ca_sigs[i].num_sigs except: pass finally: date_num_ca_sigs.append(current_num_ca_sigs) try: i = key_dates.index(d) current_num_keys = current_num_keys + keys[i].num_keys except: pass finally: date_num_keys.append(current_num_keys) data = { 'Signatures': date_num_sigs, 'Keys': date_num_keys, 'Dates': pandas.Series(dates) } y_labels = ['Signatures', 'Keys'] if self.ca_key is not None: data['CA Auto Signatures'] = date_num_ca_sigs y_labels.append('CA Auto Signatures') t = TimeSeries(data, x='Dates', y=y_labels, plot_width=800) return components(t)
def queries_for_table_dict(table): r = { 'global_get': select([table['global'].c.value ]).where(table['global'].c.key == bindparam('key')), 'global_update': table['global'].update().values(value=bindparam('value')).where( table['global'].c.key == bindparam('key')), 'graph_type': select([table['graphs'].c.type ]).where(table['graphs'].c.graph == bindparam('graph')), 'del_edge_val_graph': table['edge_val'].delete().where( table['edge_val'].c.graph == bindparam('graph')), 'del_edge_val_after': table['edge_val'].delete().where( and_( table['edge_val'].c.graph == bindparam('graph'), table['edge_val'].c.orig == bindparam('orig'), table['edge_val'].c.dest == bindparam('dest'), table['edge_val'].c.idx == bindparam('idx'), table['edge_val'].c.key == bindparam('key'), table['edge_val'].c.branch == bindparam('branch'), or_( table['edge_val'].c.turn > bindparam('turn'), and_(table['edge_val'].c.turn == bindparam('turn'), table['edge_val'].c.tick >= bindparam('tick'))))), 'del_edges_graph': table['edges'].delete().where( table['edges'].c.graph == bindparam('graph')), 'del_edges_after': table['edges'].delete().where( and_( table['edges'].c.graph == bindparam('graph'), table['edges'].c.orig == bindparam('orig'), table['edges'].c.dest == bindparam('dest'), table['edges'].c.idx == bindparam('idx'), table['edges'].c.branch == bindparam('branch'), or_( table['edges'].c.turn > bindparam('turn'), and_(table['edges'].c.turn == bindparam('turn'), table['edges'].c.tick >= bindparam('tick'))))), 'del_nodes_graph': table['nodes'].delete().where( table['nodes'].c.graph == bindparam('graph')), 'del_nodes_after': table['nodes'].delete().where( and_( table['nodes'].c.graph == bindparam('graph'), table['nodes'].c.node == bindparam('node'), table['nodes'].c.branch == bindparam('branch'), or_( table['nodes'].c.turn > bindparam('turn'), and_(table['nodes'].c.turn == bindparam('turn'), table['nodes'].c.tick >= bindparam('tick'))))), 'del_node_val_graph': table['node_val'].delete().where( table['node_val'].c.graph == bindparam('graph')), 'del_node_val_after': table['node_val'].delete().where( and_( table['node_val'].c.graph == bindparam('graph'), table['node_val'].c.node == bindparam('node'), table['node_val'].c.key == bindparam('key'), table['node_val'].c.branch == bindparam('branch'), or_( table['node_val'].c.turn > bindparam('turn'), and_(table['node_val'].c.turn == bindparam('turn'), table['node_val'].c.tick >= bindparam('tick'))))), 'del_graph': table['graphs'].delete().where( table['graphs'].c.graph == bindparam('graph')), 'del_graph_val_after': table['graph_val'].delete().where( and_( table['graph_val'].c.graph == bindparam('graph'), table['graph_val'].c.key == bindparam('key'), table['graph_val'].c.branch == bindparam('branch'), or_( table['graph_val'].c.turn > bindparam('turn'), and_(table['graph_val'].c.turn == bindparam('turn'), table['graph_val'].c.tick >= bindparam('tick'))))), 'global_delete': table['global'].delete().where( table['global'].c.key == bindparam('key')), 'graphs_types': select([table['graphs'].c.graph, table['graphs'].c.type]), 'graphs_named': select([func.COUNT()]).select_from(table['graphs']).where( table['graphs'].c.graph == bindparam('graph')), 'update_branches': table['branches'].update().values( parent=bindparam('parent'), parent_turn=bindparam('parent_turn'), parent_tick=bindparam('parent_tick'), end_turn=bindparam('end_turn'), end_tick=bindparam('end_tick')).where( table['branches'].c.branch == bindparam('branch')), 'update_turns': table['turns'].update().values( end_tick=bindparam('end_tick'), plan_end_tick=bindparam('plan_end_tick')).where( and_(table['turns'].c.branch == bindparam('branch'), table['turns'].c.turn == bindparam('turn'))) } for t in table.values(): key = list(t.primary_key) if 'branch' in t.columns and 'turn' in t.columns and 'tick' in t.columns: branch = t.columns['branch'] turn = t.columns['turn'] tick = t.columns['tick'] if branch in key and turn in key and tick in key: key = [branch, turn, tick] r[t.name + '_del_time'] = t.delete().where( and_(t.c.branch == bindparam('branch'), t.c.turn == bindparam('turn'), t.c.tick == bindparam('tick'))) r[t.name + '_dump'] = select(list(t.c.values())).order_by(*key) r[t.name + '_insert'] = t.insert().values( tuple(bindparam(cname) for cname in t.c.keys())) r[t.name + '_count'] = select([func.COUNT()]).select_from(t) r[t.name + '_del'] = t.delete().where( and_(*[c == bindparam(c.name) for c in t.primary_key])) return r
def queries_for_table_dict(table): def hirev_nodes_join(wheres): hirev = select( [ table['nodes'].c.graph, table['nodes'].c.node, table['nodes'].c.branch, func.MAX(table['nodes'].c.rev).label('rev') ] ).where(and_(*wheres)).group_by( table['nodes'].c.graph, table['nodes'].c.node, table['nodes'].c.branch ).alias('hirev') return table['nodes'].join( hirev, and_( table['nodes'].c.graph == hirev.c.graph, table['nodes'].c.node == hirev.c.node, table['nodes'].c.branch == hirev.c.branch, table['nodes'].c.rev == hirev.c.rev ) ) def hirev_graph_val_join(wheres): hirev = select( [ table['graph_val'].c.graph, table['graph_val'].c.key, table['graph_val'].c.branch, func.MAX(table['graph_val'].c.rev).label('rev') ] ).where(and_(*wheres)).group_by( table['graph_val'].c.graph, table['graph_val'].c.key, table['graph_val'].c.branch ).alias('hirev') return table['graph_val'].join( hirev, and_( table['graph_val'].c.graph == hirev.c.graph, table['graph_val'].c.key == hirev.c.key, table['graph_val'].c.branch == hirev.c.branch, table['graph_val'].c.rev == hirev.c.rev ) ) def node_val_hirev_join(wheres): hirev = select( [ table['node_val'].c.graph, table['node_val'].c.node, table['node_val'].c.branch, table['node_val'].c.key, func.MAX(table['node_val'].c.rev).label('rev') ] ).where(and_(*wheres)).group_by( table['node_val'].c.graph, table['node_val'].c.node, table['node_val'].c.branch, table['node_val'].c.key ).alias('hirev') return table['node_val'].join( hirev, and_( table['node_val'].c.graph == hirev.c.graph, table['node_val'].c.node == hirev.c.node, table['node_val'].c.key == hirev.c.key, table['node_val'].c.branch == hirev.c.branch, table['node_val'].c.rev == hirev.c.rev ) ) def edges_recent_join(wheres=None): hirev = select( [ table['edges'].c.graph, table['edges'].c.nodeA, table['edges'].c.nodeB, table['edges'].c.idx, table['edges'].c.branch, func.MAX(table['edges'].c.rev).label('rev') ] ) if wheres: hirev = hirev.where(and_(*wheres)) hirev = hirev.group_by( table['edges'].c.graph, table['edges'].c.nodeA, table['edges'].c.nodeB, table['edges'].c.idx, table['edges'].c.branch ).alias('hirev') return table['edges'].join( hirev, and_( table['edges'].c.graph == hirev.c.graph, table['edges'].c.nodeA == hirev.c.nodeA, table['edges'].c.nodeB == hirev.c.nodeB, table['edges'].c.idx == hirev.c.idx, table['edges'].c.branch == hirev.c.branch, table['edges'].c.rev == hirev.c.rev ) ) def edge_val_recent_join(wheres=None): hirev = select( [ table['edge_val'].c.graph, table['edge_val'].c.nodeA, table['edge_val'].c.nodeB, table['edge_val'].c.idx, table['edge_val'].c.key, table['edge_val'].c.branch, func.MAX(table['edge_val'].c.rev).label('rev') ] ) if wheres: hirev = hirev.where( and_(*wheres) ) hirev = hirev.group_by( table['edge_val'].c.graph, table['edge_val'].c.nodeA, table['edge_val'].c.nodeB, table['edge_val'].c.idx, table['edge_val'].c.key, table['edge_val'].c.branch ).alias('hirev') return table['edge_val'].join( hirev, and_( table['edge_val'].c.graph == hirev.c.graph, table['edge_val'].c.nodeA == hirev.c.nodeA, table['edge_val'].c.nodeB == hirev.c.nodeB, table['edge_val'].c.idx == hirev.c.idx, table['edge_val'].c.branch == hirev.c.branch, table['edge_val'].c.rev == hirev.c.rev ) ) return { 'ctbranch': select( [func.COUNT(table['branches'].c.branch)] ).where( table['branches'].c.branch == bindparam('branch') ), 'ctgraph': select( [func.COUNT(table['graphs'].c.graph)] ).where( table['graphs'].c.graph == bindparam('graph') ), 'allbranch': select( [ table['branches'].c.branch, table['branches'].c.parent, table['branches'].c.parent_rev ] ).order_by(table['branches'].c.branch), 'global_get': select( [table['global'].c.value] ).where( table['global'].c.key == bindparam('key') ), 'edge_val_ins': table['edge_val'].insert().prefix_with('OR REPLACE').values( graph=bindparam('graph'), nodeA=bindparam('orig'), nodeB=bindparam('dest'), idx=bindparam('idx'), key=bindparam('key'), branch=bindparam('branch'), rev=bindparam('rev'), value=bindparam('value') ), 'edge_val_upd': table['edge_val'].update().values( value=bindparam('value') ).where( and_( table['edge_val'].c.graph == bindparam('graph'), table['edge_val'].c.nodeA == bindparam('orig'), table['edge_val'].c.nodeB == bindparam('dest'), table['edge_val'].c.idx == bindparam('idx'), table['edge_val'].c.key == bindparam('key'), table['edge_val'].c.branch == bindparam('branch'), table['edge_val'].c.rev == bindparam('rev') ) ), 'global_items': select( [ table['global'].c.key, table['global'].c.value ] ), 'ctglobal': select( [func.COUNT(table['global'].c.key)] ), 'new_graph': table['graphs'].insert().values( graph=bindparam('graph'), type=bindparam('type') ), 'graph_type': select( [table['graphs'].c.type] ).where( table['graphs'].c.graph == bindparam('graph') ), 'new_branch': table['branches'].insert().values( branch=bindparam('branch'), parent=bindparam('parent'), parent_rev=bindparam('parent_rev') ), 'del_edge_val_graph': table['edge_val'].delete().where( table['edge_val'].c.graph == bindparam('graph') ), 'del_edge_graph': table['edges'].delete().where( table['edges'].c.graph == bindparam('graph') ), 'del_node_val_graph': table['node_val'].delete().where( table['node_val'].c.graph == bindparam('graph') ), 'del_node_graph': table['nodes'].delete().where( table['nodes'].c.graph == bindparam('graph') ), 'del_graph': table['graphs'].delete().where( table['graphs'].c.graph == bindparam('graph') ), 'parrev': select( [table['branches'].c.parent_rev] ).where( table['branches'].c.branch == bindparam('branch') ), 'parparrev': select( [table['branches'].c.parent, table['branches'].c.parent_rev] ).where( table['branches'].c.branch == bindparam('branch') ), 'global_ins': table['global'].insert().values( key=bindparam('key'), value=bindparam('value') ), 'global_upd': table['global'].update().values( value=bindparam('value') ).where( table['global'].c.key == bindparam('key') ), 'global_del': table['global'].delete().where( table['global'].c.key == bindparam('key') ), 'nodes_extant': select( [table['nodes'].c.node] ).select_from( hirev_nodes_join( [ table['nodes'].c.graph == bindparam('graph'), table['nodes'].c.branch == bindparam('branch'), table['nodes'].c.rev <= bindparam('rev') ] ) ).where( table['nodes'].c.extant ), 'node_exists': select( [table['nodes'].c.extant] ).select_from( hirev_nodes_join( [ table['nodes'].c.graph == bindparam('graph'), table['nodes'].c.node == bindparam('node'), table['nodes'].c.branch == bindparam('branch'), table['nodes'].c.rev <= bindparam('rev') ] ) ), 'exist_node_ins': table['nodes'].insert().prefix_with('OR REPLACE').values( graph=bindparam('graph'), node=bindparam('node'), branch=bindparam('branch'), rev=bindparam('rev'), extant=bindparam('extant') ), 'exist_node_upd': table['nodes'].update().values( extant=bindparam('extant') ).where( and_( table['nodes'].c.graph == bindparam('graph'), table['nodes'].c.node == bindparam('node'), table['nodes'].c.branch == bindparam('branch'), table['nodes'].c.rev == bindparam('rev') ) ), 'graphs_types': select([ table['graphs'].c.graph, table['graphs'].c.type ]), 'nodes_dump': select([ table['nodes'].c.graph, table['nodes'].c.node, table['nodes'].c.branch, table['nodes'].c.rev, table['nodes'].c.extant ]).order_by( table['nodes'].c.graph, table['nodes'].c.branch, table['nodes'].c.rev, table['nodes'].c.node ), 'graph_val_items': select( [ table['graph_val'].c.key, table['graph_val'].c.value ] ).select_from( hirev_graph_val_join( [ table['graph_val'].c.graph == bindparam('graph'), table['graph_val'].c.branch == bindparam('branch'), table['graph_val'].c.rev <= bindparam('rev') ] ) ), 'graph_val_dump': select([ table['graph_val'].c.graph, table['graph_val'].c.key, table['graph_val'].c.branch, table['graph_val'].c.rev, table['graph_val'].c.value ]).order_by( table['graph_val'].c.graph, table['graph_val'].c.branch, table['graph_val'].c.rev, table['graph_val'].c.key ), 'graph_val_get': select( [ table['graph_val'].c.value ] ).select_from( hirev_graph_val_join( [ table['graph_val'].c.graph == bindparam('graph'), table['graph_val'].c.key == bindparam('key'), table['graph_val'].c.branch == bindparam('branch'), table['graph_val'].c.rev <= bindparam('rev') ] ) ), 'graph_val_ins': table['graph_val'].insert().prefix_with('OR REPLACE').values( graph=bindparam('graph'), key=bindparam('key'), branch=bindparam('branch'), rev=bindparam('rev'), value=bindparam('value') ), 'graph_val_upd': table['graph_val'].update().values( value=bindparam('value') ).where( and_( table['graph_val'].c.graph == bindparam('graph'), table['graph_val'].c.key == bindparam('key'), table['graph_val'].c.branch == bindparam('branch'), table['graph_val'].c.rev == bindparam('rev') ) ), 'node_val_items': select( [ table['node_val'].c.key, table['node_val'].c.value ] ).select_from( node_val_hirev_join( [ table['node_val'].c.graph == bindparam('graph'), table['node_val'].c.node == bindparam('node'), table['node_val'].c.branch == bindparam('branch'), table['node_val'].c.rev <= bindparam('rev') ] ) ), 'node_val_dump': select([ table['node_val'].c.graph, table['node_val'].c.node, table['node_val'].c.key, table['node_val'].c.branch, table['node_val'].c.rev, table['node_val'].c.value ]).order_by( table['node_val'].c.graph, table['node_val'].c.node, table['node_val'].c.branch, table['node_val'].c.rev, table['node_val'].c.key ), 'node_val_get': select( [ table['node_val'].c.value ] ).select_from( node_val_hirev_join( [ table['node_val'].c.graph == bindparam('graph'), table['node_val'].c.node == bindparam('node'), table['node_val'].c.key == bindparam('key'), table['node_val'].c.branch == bindparam('branch'), table['node_val'].c.rev <= bindparam('rev') ] ) ).where( table['node_val'].c.value != null() ), 'node_val_ins': table['node_val'].insert().prefix_with('OR REPLACE').values( graph=bindparam('graph'), node=bindparam('node'), key=bindparam('key'), branch=bindparam('branch'), rev=bindparam('rev'), value=bindparam('value') ), 'edge_exists': select( [table['edges'].c.extant] ).select_from( edges_recent_join( [ table['edges'].c.graph == bindparam('graph'), table['edges'].c.nodeA == bindparam('nodeA'), table['edges'].c.nodeB == bindparam('nodeB'), table['edges'].c.idx == bindparam('idx'), table['edges'].c.branch == bindparam('branch'), table['edges'].c.rev <= bindparam('rev') ] ) ), 'edges_extant': select( [ table['edges'].c.nodeA, table['edges'].c.extant ] ).select_from( edges_recent_join( [ table['edges'].c.graph == bindparam('graph'), table['edges'].c.branch == bindparam('branch'), table['edges'].c.rev <= bindparam('rev') ] ) ), 'nodeAs': select( [ table['edges'].c.nodeA, table['edges'].c.extant ] ).select_from( edges_recent_join( [ table['edges'].c.graph == bindparam('graph'), table['edges'].c.nodeB == bindparam('dest'), table['edges'].c.branch == bindparam('branch'), table['edges'].c.rev <= bindparam('rev') ] ) ), 'nodeBs': select( [ table['edges'].c.nodeB, table['edges'].c.extant ] ).select_from( edges_recent_join( [ table['edges'].c.graph == bindparam('graph'), table['edges'].c.nodeA == bindparam('orig'), table['edges'].c.branch == bindparam('branch'), table['edges'].c.rev <= bindparam('rev') ] ) ), 'multi_edges': select( [ table['edges'].c.idx, table['edges'].c.extant ] ).select_from( edges_recent_join( [ table['edges'].c.graph == bindparam('graph'), table['edges'].c.nodeA == bindparam('orig'), table['edges'].c.nodeB == bindparam('dest'), table['edges'].c.branch == bindparam('branch'), table['edges'].c.rev <= bindparam('rev') ] ) ), 'edges_dump': select([ table['edges'].c.graph, table['edges'].c.nodeA, table['edges'].c.nodeB, table['edges'].c.idx, table['edges'].c.branch, table['edges'].c.rev, table['edges'].c.extant ]).order_by( table['edges'].c.graph, table['edges'].c.branch, table['edges'].c.rev, table['edges'].c.nodeA, table['edges'].c.nodeB, table['edges'].c.idx ), 'edge_exist_ins': table['edges'].insert().prefix_with('OR REPLACE').values( graph=bindparam('graph'), nodeA=bindparam('orig'), nodeB=bindparam('dest'), idx=bindparam('idx'), branch=bindparam('branch'), rev=bindparam('rev'), extant=bindparam('extant') ), 'edge_exist_upd': table['edges'].update().values( extant=bindparam('extant') ).where( and_( table['edges'].c.graph == bindparam('graph'), table['edges'].c.nodeA == bindparam('orig'), table['edges'].c.nodeB == bindparam('dest'), table['edges'].c.idx == bindparam('idx'), table['edges'].c.branch == bindparam('branch'), table['edges'].c.rev == bindparam('rev') ) ), 'edge_val_dump': select([ table['edge_val'].c.graph, table['edge_val'].c.nodeA, table['edge_val'].c.nodeB, table['edge_val'].c.idx, table['edge_val'].c.key, table['edge_val'].c.branch, table['edge_val'].c.rev, table['edge_val'].c.value ]).order_by( table['edge_val'].c.graph, table['edge_val'].c.nodeA, table['edge_val'].c.nodeB, table['edge_val'].c.idx, table['edge_val'].c.branch, table['edge_val'].c.rev, table['edge_val'].c.key ), 'edge_val_items': select( [ table['edge_val'].c.key, table['edge_val'].c.value ] ).select_from( edge_val_recent_join( [ table['edge_val'].c.graph == bindparam('graph'), table['edge_val'].c.nodeA == bindparam('orig'), table['edge_val'].c.nodeB == bindparam('dest'), table['edge_val'].c.idx == bindparam('idx'), table['edge_val'].c.branch == bindparam('branch'), table['edge_val'].c.rev <= bindparam('rev') ] ) ), 'edge_val_get': select( [ table['edge_val'].c.value ] ).select_from( edge_val_recent_join( [ table['edge_val'].c.graph == bindparam('graph'), table['edge_val'].c.nodeA == bindparam('orig'), table['edge_val'].c.nodeB == bindparam('dest'), table['edge_val'].c.idx == bindparam('idx'), table['edge_val'].c.key == bindparam('key'), table['edge_val'].c.branch == bindparam('branch'), table['edge_val'].c.rev <= bindparam('rev') ] ) ) }