def get_maps(document, lang): """Load and return maps that intersect with the document geometry. """ if document.geometry is None: return [] document_geom = select([DocumentGeometry.geom]). \ where(DocumentGeometry.document_id == document.document_id) document_geom_detail = select([DocumentGeometry.geom_detail]). \ where(DocumentGeometry.document_id == document.document_id) topo_maps = DBSession. \ query(TopoMap). \ filter(TopoMap.redirects_to.is_(None)). \ join( DocumentGeometry, TopoMap.document_id == DocumentGeometry.document_id). \ options(load_only( TopoMap.document_id, TopoMap.editor, TopoMap.code, TopoMap.version, TopoMap.protected)). \ options(joinedload(TopoMap.locales).load_only( DocumentLocale.lang, DocumentLocale.title, DocumentLocale.version)). \ filter( or_( DocumentGeometry.geom_detail.ST_Intersects( document_geom.label('t1')), DocumentGeometry.geom_detail.ST_Intersects( document_geom_detail.label('t2')) )). \ all() if lang is not None: set_best_locale(topo_maps, lang) return topo_maps
def test_within_distance(self): """ Because SDO_WITHIN_DISTANCE requires a spatial index for the geometry used as first parameter, we have to insert out test geometries into tables, unlike to the other databases. Note that Oracle uses meter as unit for the tolerance value for geodetic coordinate systems (like 4326)! """ # test if SDO_functions._within_distance is called correctly eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(0 0)', 0)).count(), 1) eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(0 0)', 0.1)).count(), 1) eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(9 9)', 100000)).count(), 0) eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 0)).count(), 3) eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'Polygon((-10 -10, 10 -10, 10 10, -10 10, -10 -10))', 0)).count(), 4) eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'Polygon((-10 -10, 10 -10, 10 10, -10 10, -10 -10))', 200000)).count(), 5) # test if SDO_GEOM.functions._within_distance is called correctly eq_(session.scalar(select([text('1')], from_obj=['dual']).where( functions._within_distance('POINT(0 0)', 'POINT(0 0)', 0, {'tol' : 0.00000005}))), 1) eq_(session.scalar(select([text('1')], from_obj=['dual']).where( functions._within_distance('POINT(0 0)', 'POINT(0 0)', 0, {'dim1' : text(diminfo), 'dim2' : text(diminfo)}))), 1)
def constructQuery(self, context): session= Session() trusted=removeSecurityProxy(context) user_id = getattr(trusted, self.value_field, None) if user_id: query = session.query(domain.User ).filter(domain.User.user_id == user_id).order_by(domain.User.last_name, domain.User.first_name, domain.User.middle_name) return query else: sitting = trusted.__parent__ group_id = sitting.group_id group_sitting_id = sitting.group_sitting_id all_member_ids = sql.select([schema.user_group_memberships.c.user_id], sql.and_( schema.user_group_memberships.c.group_id == group_id, schema.user_group_memberships.c.active_p == True)) attended_ids = sql.select([schema.group_sitting_attendance.c.member_id], schema.group_sitting_attendance.c.group_sitting_id == group_sitting_id) query = session.query(domain.User).filter( sql.and_(domain.User.user_id.in_(all_member_ids), ~ domain.User.user_id.in_(attended_ids))).order_by( domain.User.last_name, domain.User.first_name, domain.User.middle_name) return query
def test_nested_in(self): from sqlalchemy.sql.expression import select from sqlalchemy.schema import Table, MetaData, Column from sqlalchemy.types import Integer meta = MetaData() table = Table( u'Foo', meta, Column(u'a', Integer()) ) target = self._makeOne( select( [u'a'], from_obj=table, whereclause=table.c.a.in_( self._makeOne( select( [u'a'], from_obj=table, ), comment=u'inner' ) ) ), comment=u'test' ) result = target.compile() self.assertEqual(unicode(result).replace(u'\n', ''), u'SELECT a FROM "Foo" WHERE "Foo".a IN (SELECT a FROM "Foo") /* test */')
def set_leg_waypoints(): t = time.time() dd = db.metadata.tables["device_data"] legs = db.metadata.tables["legs"] glue = db.metadata.tables["leg_waypoints"] legpoints = select( [legs.c.id, dd.c.waypoint_id, dd.c.time, dd.c.snapping_time], from_obj=dd.join(legs, and_( dd.c.device_id == legs.c.device_id, dd.c.time.between(legs.c.time_start, legs.c.time_end)))) \ .alias("legpoints") done = select([glue.c.leg], distinct=True) nounsnapped = select( [legpoints.c.id], legpoints.c.id.notin_(done), group_by=legpoints.c.id, having=func.bool_and(legpoints.c.snapping_time.isnot(None))) newitems = select( [legpoints.c.id, legpoints.c.waypoint_id, func.min(legpoints.c.time)], legpoints.c.id.in_(nounsnapped), group_by=[legpoints.c.id, legpoints.c.waypoint_id]).alias("newitems") ins = glue.insert().from_select(["leg", "waypoint", "first"], newitems) rowcount = db.engine.execute(ins).rowcount print("set_leg_waypoints on %d rows in %.2g seconds" % ( rowcount, time.time() - t))
def p_is_term(p): '''is_term : OP_IS string''' #TODO: implement starred, watched, owner, reviewer, draft username = p.parser.username if p[2] == 'reviewed': filters = [] filters.append(gertty.db.approval_table.c.change_key == gertty.db.change_table.c.key) filters.append(gertty.db.approval_table.c.value != 0) s = select([gertty.db.change_table.c.key], correlate=False).where(and_(*filters)) p[0] = gertty.db.change_table.c.key.in_(s) elif p[2] == 'open': p[0] = gertty.db.change_table.c.status.notin_(['MERGED', 'ABANDONED']) elif p[2] == 'closed': p[0] = gertty.db.change_table.c.status.in_(['MERGED', 'ABANDONED']) elif p[2] == 'submitted': p[0] = gertty.db.change_table.c.status == 'SUBMITTED' elif p[2] == 'merged': p[0] = gertty.db.change_table.c.status == 'MERGED' elif p[2] == 'abandoned': p[0] = gertty.db.change_table.c.status == 'ABANDONED' elif p[2] == 'owner': p[0] = and_(gertty.db.change_table.c.account_key == gertty.db.account_table.c.key, gertty.db.account_table.c.username == username) elif p[2] == 'reviewer': filters = [] filters.append(gertty.db.approval_table.c.change_key == gertty.db.change_table.c.key) filters.append(gertty.db.approval_table.c.account_key == gertty.db.account_table.c.key) filters.append(gertty.db.account_table.c.username == username) s = select([gertty.db.change_table.c.key], correlate=False).where(and_(*filters)) p[0] = gertty.db.change_table.c.key.in_(s) else: raise gertty.search.SearchSyntaxError('Syntax error: has:%s is not supported' % p[2])
def query(self, parent_ids=None): """ Construct a SQL query for this level of the request. """ if self.parent is None: q = select(from_obj=self.join(self.alias)) q = q.offset(self.get_child_node_value('offset', 0)) if not self.node.as_list: q = q.limit(1) else: q = q.limit(self.get_child_node_value('limit', 10)) else: q = select(from_obj=self.join(self.parent.alias)) q = self.filter(q) if parent_ids is not None: q = q.where(self.parent.alias.c.id.in_(parent_ids)) q = self.project(q) q = q.distinct() #print self, type(self) #print q ids = [] rp = db.session.execute(q) while True: row = rp.fetchone() if row is None: break row = dict(row.items()) ids.append(row.get(self.pk_id)) self.collect(row) for name, child in self.children.items(): child.query(parent_ids=ids)
def create_expression(self): if self.last_result is None: try: expr = select(self.table.columns).order_by(*[safe_collate(self.table.columns[nm], None) for nm in self.sort_key]) if self.starter is not None: expr = expr.where(and_(self.starter, self.filter)) else: expr = expr.where(self.filter) except: raise else: try: where_clause = vector_greater_than([self.table.columns[nm] for nm in self.sort_key], \ [self.last_result[n] for n in self.sort_index]) expr = (select(self.table.columns).order_by(*[safe_collate(self.table.columns[nm], None) for nm in self.sort_key]) \ .where(and_(where_clause, self.filter))) except: raise if self.limit_per is not None: expr = expr.limit(self.limit_per) if self.stream: expr = expr.execution_options(stream_results=True) return expr
def test_in_25(self): self.assert_compile( select([self.table1.c.myid.in_( select([self.table2.c.otherid]).as_scalar())]), "SELECT mytable.myid IN (SELECT myothertable.otherid " "FROM myothertable) AS anon_1 FROM mytable" )
def upgrade(migrate_engine): if migrate_engine.name == 'sqlite': return meta = MetaData(bind=migrate_engine) for table_name, ref, child in TABLES: table = Table(table_name, meta, autoload=True) column_name, ref_table_name, ref_column_name = ref column = table.c[column_name] ref_table = Table(ref_table_name, meta, autoload=True) ref_column = ref_table.c[ref_column_name] subq = select([ref_column]).where(ref_column != None) if child: # Dump and cleanup rows in child table first child_table_name, child_column_name, child_ref_column_name = child child_table = Table(child_table_name, meta, autoload=True) child_column = child_table.c[child_column_name] child_ref_column = table.c[child_ref_column_name] child_subq = select([child_ref_column]).where(~ column.in_(subq)) dump_cleanup_rows(migrate_engine, meta, child_table, child_column.in_(child_subq)) dump_cleanup_rows(migrate_engine, meta, table, ~ column.in_(subq)) params = {'columns': [column], 'refcolumns': [ref_column]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', table_name, column_name)) fkey = ForeignKeyConstraint(**params) fkey.create()
def test_migrate_batch_stureg(self): batch_guid = '2bb942b9-75cf-4055-a67a-8b9ab53a9dfc' batch = {UdlStatsConstants.REC_ID: '6', UdlStatsConstants.BATCH_GUID: batch_guid, UdlStatsConstants.TENANT: self.__tenant, UdlStatsConstants.SCHEMA_NAME: None, Constants.DEACTIVATE: False, UdlStatsConstants.LOAD_TYPE: LoadType.STUDENT_REGISTRATION, UdlStatsConstants.BATCH_OPERATION: 's', UdlStatsConstants.SNAPSHOT_CRITERIA: '{"reg_system_id": "015247bd-058c-48cd-bb4d-f6cffe5b40c1", "academic_year": 2015}'} self.insert_into_udl_stats(batch[UdlStatsConstants.REC_ID], batch_guid, self.__tenant, batch[UdlStatsConstants.LOAD_TYPE]) preprod_conn = EdMigrateSourceConnection(tenant=get_unittest_preprod_tenant_name()) count_to_source_query = select([func.count()]).select_from(preprod_conn.get_table(Constants.STUDENT_REG)) count_to_be_inserted = preprod_conn.execute(count_to_source_query).fetchall()[0][0] self.assertEqual(10, count_to_be_inserted) prod_conn = EdMigrateDestConnection(tenant=get_unittest_preprod_tenant_name()) student_reg_table = prod_conn.get_table(Constants.STUDENT_REG) count_query = select([func.count()]).select_from(student_reg_table) count_before = prod_conn.execute(count_query).fetchall()[0][0] self.assertEqual(2581, count_before) count_snapshot_query = select([func.count()], student_reg_table.c.academic_year == 2015).select_from(student_reg_table) count_to_be_deleted = prod_conn.execute(count_snapshot_query).fetchall()[0][0] self.assertEqual(1217, count_to_be_deleted) rtn = migrate_batch(batch) self.assertTrue(rtn) expected_count_after = count_before - count_to_be_deleted + count_to_be_inserted count_after = prod_conn.execute(count_query).fetchall()[0][0] self.assertEqual(expected_count_after, count_after)
def test_migrate_student_reg(self): Unittest_with_edcore_sqlite.setUpClass(EdMigrateDestConnection.get_datasource_name(TestMigrate.test_tenant), use_metadata_from_db=False) preprod_conn = EdMigrateSourceConnection(tenant=get_unittest_preprod_tenant_name()) prod_conn = EdMigrateDestConnection(tenant=get_unittest_prod_tenant_name()) batch_guid = "0aa942b9-75cf-4055-a67a-8b9ab53a9dfc" student_reg_table = preprod_conn.get_table(Constants.STUDENT_REG) get_query = select([student_reg_table.c.student_reg_rec_id]).order_by(student_reg_table.c.student_reg_rec_id) count_query = select([func.count().label('student_reg_rec_ids')], student_reg_table.c.student_reg_rec_id.in_(range(15541, 15551))) rset = preprod_conn.execute(get_query) row = rset.fetchall() self.assertEqual(10, len(row)) self.assertListEqual([(15541,), (15542,), (15543,), (15544,), (15545,), (15546,), (15547,), (15548,), (15549,), (15550,)], row) rset.close() rset = prod_conn.execute(count_query) row = rset.fetchone() self.assertEqual(0, row['student_reg_rec_ids']) rset.close() delete_count, insert_count = migrate_table(batch_guid, None, preprod_conn, prod_conn, 'student_reg', False) self.assertEqual(0, delete_count) self.assertEqual(10, insert_count) rset = prod_conn.execute(count_query) row = rset.fetchone() self.assertEqual(10, row['student_reg_rec_ids']) rset.close()
def _connect_ping_listener(connection, branch): """Ping the server at connection startup. Ping the server at transaction begin and transparently reconnect if a disconnect exception occurs. """ if branch: return # turn off "close with result". This can also be accomplished # by branching the connection, however just setting the flag is # more performant and also doesn't get involved with some # connection-invalidation awkardness that occurs (see # https://bitbucket.org/zzzeek/sqlalchemy/issue/3215/) save_should_close_with_result = connection.should_close_with_result connection.should_close_with_result = False try: # run a SELECT 1. use a core select() so that # any details like that needed by Oracle, DB2 etc. are handled. connection.scalar(select([1])) except exception.DBConnectionError: # catch DBConnectionError, which is raised by the filter # system. # disconnect detected. The connection is now # "invalid", but the pool should be ready to return # new connections assuming they are good now. # run the select again to re-validate the Connection. connection.scalar(select([1])) finally: connection.should_close_with_result = save_should_close_with_result
def get_asmt_rec_id(guid_batch, tenant_name, asmt_rec_id_info): ''' Returns asmt_rec_id from dim_asmt table Steps: 1. Get guid_asmt from integration table INT_SBAC_ASMT 2. Select asmt_rec_id from dim_asmt by the same guid_amst got from 1. It should have 1 value ''' source_table_name = asmt_rec_id_info['source_table'] guid_column_name_in_source = asmt_rec_id_info['guid_column_in_source'] target_table_name = asmt_rec_id_info['target_table'] guid_column_name_in_target = asmt_rec_id_info['guid_column_name'] rec_id_column_name = asmt_rec_id_info['rec_id'] # connect to integration table, to get the value of guid_asmt with get_udl_connection() as udl_conn: int_table = udl_conn.get_table(source_table_name) query = select([int_table.c[guid_column_name_in_source]], from_obj=int_table, limit=1) query = query.where(int_table.c['guid_batch'] == guid_batch) results = udl_conn.get_result(query) if results: guid_column_value = results[0][guid_column_name_in_source] # connect to target table, to get the value of asmt_rec_id with get_target_connection(tenant_name, guid_batch) as target_conn: dim_asmt = target_conn.get_table(target_table_name) query = select([dim_asmt.c[rec_id_column_name]], from_obj=dim_asmt, limit=1) query = query.where(dim_asmt.c[guid_column_name_in_target] == guid_column_value) query = query.where(and_(dim_asmt.c['batch_guid'] == guid_batch)) results = target_conn.get_result(query) if results: asmt_rec_id = results[0][rec_id_column_name] return asmt_rec_id
def generate_create_inheritance_view_statement(class_): viewname = cls2tbl(class_)[1:] tables = class_.__table__ cols = {} def add_cols(table): for col in table.c: if col.name not in cols: cols[col.name] = col add_cols(class_.__table__) if class_.__score_db__['inheritance'] is not None: parent = class_.__score_db__['parent'] while parent: table = parent.__table__ tables = tables.join( table, onclause=table.c.id == class_.__table__.c.id) add_cols(table) parent = parent.__score_db__['parent'] if class_.__score_db__['inheritance'] != 'single-table': viewselect = select(cols.values(), from_obj=tables) else: typecol = getattr( class_, class_.__score_db__['type_column']) typenames = [] def add_typenames(cls): typenames.append(cls.__score_db__['type_name']) for subclass in cls.__subclasses__(): add_typenames(subclass) add_typenames(class_) viewselect = select(cols.values(), from_obj=class_.__table__, whereclause=typecol.in_(typenames)) return CreateView(viewname, viewselect)
def test_union(self): from sqlalchemy.sql.expression import select from sqlalchemy.schema import Table, MetaData, Column from sqlalchemy.types import Integer meta = MetaData() table = Table( u'Foo', meta, Column(u'a', Integer()) ) target = self._makeOne( select( [u'a'], from_obj=table, whereclause=(table.c.a == 1) ), comment=u'test' ) target = target.union( select( [u'a'], from_obj=table, whereclause=(table.c.a == 2) ) ) result = target.compile() self.assertRegexpMatches(unicode(result).replace(u'\n', ''), ur'SELECT a FROM "Foo" WHERE "Foo".a = [^ ]+ UNION SELECT a FROM "Foo" WHERE "Foo".a = [^ ]+ /\* test \*/')
def test_six_pt_five(self): x = column("x") self.assert_compile(select([x]).where(or_(x == 7, true())), "SELECT x WHERE true") self.assert_compile(select([x]).where(or_(x == 7, true())), "SELECT x WHERE 1 = 1", dialect=default.DefaultDialect(supports_native_boolean=False))
def load_to_table(data_dict, guid_batch, int_table, tenant_name, udl_schema): ''' Load the table into the proper table @param data_dict: the dictionary containing the data to be loaded @param guid_batch: the id for the batch @param int_table: the name of the integration table @param tenant_name: name of the tenant @param udl_schema: udl schema name ''' # Create sqlalchemy connection and get table information from sqlalchemy ref_column_mapping_columns = {} with get_udl_connection() as conn: data_dict[mk.GUID_BATCH] = guid_batch data_dict = fix_empty_strings(data_dict) ref_table = conn.get_table('ref_column_mapping') s_int_table = conn.get_table(int_table) column_mapping_query = select([ref_table.c.target_column, ref_table.c.stored_proc_name], from_obj=ref_table).where(and_(ref_table.c.source_table == 'lz_json', ref_table.c.target_table == int_table)) results = conn.get_result(column_mapping_query) for result in results: target_column = result['target_column'] stored_proc_name = result['stored_proc_name'] value = data_dict.get(target_column) if value: if stored_proc_name: if stored_proc_name.startswith('sp_'): ref_column_mapping_columns[target_column] = stored_proc_name + '(' + QuotedString(value if type(value) is str else str(value)).getquoted().decode('utf-8') + ')' else: format_value = dict() format_value['value'] = QuotedString(value if type(value) is str else str(value)).getquoted().decode('utf-8') if s_int_table.c[target_column].type.python_type is str: format_value['length'] = s_int_table.c[target_column].type.length ref_column_mapping_columns[target_column] = stored_proc_name.format(**format_value) continue ref_column_mapping_columns[target_column] = value record_sid = 'nextval(\'{schema_name}.{tenant_sequence_name}\')'.\ format(schema_name=udl_schema, tenant_sequence_name=Constants.TENANT_SEQUENCE_NAME(tenant_name)) from_select_column_names = ['record_sid'] from_select_select_values = [record_sid] for column in s_int_table.c: value = data_dict.get(column.name) if value is not None: from_select_column_names.append(column.name) from_select_select_values.append( ref_column_mapping_columns.get(column.name, QuotedString(value if type(value) is str else str(value)).getquoted().decode('utf-8'))) insert_into_int_table = s_int_table.insert().from_select(from_select_column_names, select(from_select_select_values)) # create insert statement and execute affected_row = db_util.execute_udl_queries(conn, [insert_into_int_table], 'Exception in loading json data -- ', 'json_loader', 'load_to_table') return affected_row[0]
def get_asmt_and_outcome_result(self, conf): with get_udl_connection() as conn: asmt_table = conn.get_table(conf.get(mk.ASMT_TABLE)) asmt_outcome_table = conn.get_table(conf.get(mk.ASMT_OUTCOME_TABLE)) asmt_result = conn.get_result(select([asmt_table.c.guid_asmt]). where(asmt_table.c.guid_batch == conf.get(mk.GUID_BATCH))) asmt_outcome_result = conn.get_result(select([asmt_outcome_table.c.assessmentguid], distinct=True). where(asmt_outcome_table.c.guid_batch == conf.get(mk.GUID_BATCH))) return asmt_result, asmt_outcome_result
def get_udl_record_by_batch_guid(batch_guid, tenant): records = [] with get_prod_connection(tenant=tenant) as connection: fact_asmt_outcome = connection.get_table('fact_asmt_outcome') fact_block_asmt_outcome = connection.get_table('fact_block_asmt_outcome') select_fao = select([fact_asmt_outcome.c.student_id.label(STUDENT_ID), fact_asmt_outcome.c.asmt_guid.label(ASMT_GUID), fact_asmt_outcome.c.date_taken.label(DATE_TAKEN)]).where(and_(fact_asmt_outcome.c.batch_guid == batch_guid, fact_asmt_outcome.c.rec_status == 'C')) select_fbao = select([fact_block_asmt_outcome.c.student_id.label(STUDENT_ID), fact_block_asmt_outcome.c.asmt_guid.label(ASMT_GUID), fact_block_asmt_outcome.c.date_taken.label(DATE_TAKEN)]).where(and_(fact_block_asmt_outcome.c.batch_guid == batch_guid, fact_block_asmt_outcome.c.rec_status == 'C')) records = connection.get_result(select_fao.union(select_fbao)) return records
def verify_udl_failure(self, udl_connector, guid_batch_id): status = [('FAILURE',)] batch_table = udl_connector.get_table(Constants.UDL2_BATCH_TABLE) query = select([batch_table.c.udl_phase_step_status], and_(batch_table.c.guid_batch == guid_batch_id, batch_table.c.udl_phase == 'UDL_COMPLETE')) query2 = select([batch_table.c.udl_phase_step_status], and_(batch_table.c.guid_batch == guid_batch_id, batch_table.c.udl_phase == 'udl2.W_post_etl.task')) batch_table_data = udl_connector.execute(query).fetchall() batch_table_post_udl = udl_connector.execute(query2).fetchall() self.assertEquals(status, batch_table_data) self.assertEquals([('SUCCESS',)], batch_table_post_udl)
def test_in_26(self): self.assert_compile(self.table1.c.myid.in_( union( select([self.table1.c.myid], self.table1.c.myid == 5), select([self.table1.c.myid], self.table1.c.myid == 12), ) ), "mytable.myid IN ("\ "SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 "\ "UNION SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_2)")
def compare_results(compa, cols, vals): vals = tuple(map(none_to_minus_inf, vals)) res = set([row['id'] for row in engine.execute(select(table.columns).where(compa))]) all_ = [valmap(none_to_minus_inf, row) for row in engine.execute(select(table.columns))] cor = set() for row in all_: if tuple(row[col.name] for col in cols) > vals: cor.add(row['id']) assert_equal(res, cor)
def initialize_table_source(db, tblname, schema=None, cols=None): tbl = initialize_table(db, tblname, schema) if not cols: return select([tbl]) else: return select([tbl.c[col] for col in cols])
def rebuild_table(table, delete_missing=False): from virtuoso.alchemy import AddForeignKey, DropForeignKey print "rebuilding", table session = get_session_maker()() incoming = set(get_incoming_fks(table)) outgoing = set(table.foreign_keys) all_fkeys = incoming | outgoing self_ref = incoming & outgoing try: for fk in all_fkeys: if not delete_rows_with_missing_fkey(fk, delete_missing): print "There are missing keys, will not rebuild " + table.name return except Exception as e: traceback.print_exc() print "Could not delete missing keys" raise e # Booleans with NULL values for col in table.c: if isinstance(col.type, Boolean): session.execute(table.update().where(col == None).values(**{col.name:0})) # Drop all keys for fk in all_fkeys: try: session.execute(DropForeignKey(fk)) except Exception as e: print "Could not drop fkey %s, maybe does not exist." % (fk_as_str(fk),) print e clone = clone_table(table, table.name+"_temp", False, False) clone.create(session.bind) column_names = [c.name for c in table.columns] sel = select([getattr(table.c, cname) for cname in column_names]) with transaction.manager: session.execute(clone.insert().from_select(column_names, sel)) mark_changed(session) session.execute(DropTable(table)) # Should we create it without outgoing first? table.create(session.bind) # self ref will make the insert fail. for fk in self_ref: try: session.execute(DropForeignKey(fk)) except Exception as e: print "Could not drop fkey %s, maybe does not exist." % (fk_as_str(fk),) print e sel = select([getattr(clone.c, cname) for cname in column_names]) with transaction.manager: session.execute(table.insert().from_select(column_names, sel)) mark_changed(session) session.execute(DropTable(clone)) if delete_missing: # Delete a second time, in case. for fk in outgoing: assert delete_rows_with_missing_fkey(fk, True), "OUCH" for fk in incoming: # includes self_ref session.execute(AddForeignKey(fk))
def migrate_datastore_manager(datastores, datastore_versions): versions = select([datastore_versions]).execute() for ds_v in versions: ds = select([datastores]).\ where(datastores.c.id == ds_v.datastore_id).\ execute().fetchone() datastore_versions.update().\ where(datastore_versions.c.id == ds_v.id).\ values(manager=ds.manager).\ execute()
def gen_pagination_with_pk(chunk): max_pk_query = select([func.max(pk)]) min_pk_query = select([func.min(pk)]) max_pk = from_conn.execute(max_pk_query).scalar() or 0 min_pk = from_conn.execute(min_pk_query).scalar() or 0 min_pk = to_conn.execute(max_pk_query).scalar() or (min_pk - 1) left_seq = range(min_pk + 1, max_pk, chunk) right_seq = range(min_pk + chunk, max_pk + chunk, chunk) for min_id, max_id in zip(left_seq, right_seq): yield select_query.where(pk.between(min_id, max_id))
def _downgrade_bdm_v2(meta, bdm_table): # First delete all the image bdms # NOTE (ndipanov): This will delete all the image bdms, even the ones # that were potentially created as part of th normal # operation, not only the upgrade. We have to do it, # as we have no way of handling them in the old code. bdm_table.delete().where(bdm_table.c.source_type == 'image').execute() # NOTE (ndipanov): Set all NULL device_names (if any) to '' and let the # Nova code deal with that. This is needed so that the # return of nullable=True does not break, and should # happen only if there are instances that are just # starting up when we do the downgrade bdm_table.update().where( bdm_table.c.device_name == None ).values(device_name='').execute() instance = Table('instances', meta, autoload=True) instance_shadow = Table('shadow_instances', meta, autoload=True) instance_q = select([instance.c.uuid]) instance_shadow_q = select([instance_shadow.c.uuid]) for instance_uuid, in itertools.chain( instance_q.execute().fetchall(), instance_shadow_q.execute().fetchall()): # Get all the bdms for an instance bdm_q = select( [bdm_table.c.id, bdm_table.c.source_type, bdm_table.c.guest_format] ).where( (bdm_table.c.instance_uuid == instance_uuid) & (bdm_table.c.source_type == 'blank') ).order_by(bdm_table.c.id.asc()) blanks = [ dict(zip(('id', 'source', 'format'), row)) for row in bdm_q.execute().fetchall() ] swap = [dev for dev in blanks if dev['format'] == 'swap'] assert len(swap) < 2 ephemerals = [dev for dev in blanks if dev not in swap] for index, eph in enumerate(ephemerals): eph['virtual_name'] = 'ephemeral' + str(index) if swap: swap[0]['virtual_name'] = 'swap' for bdm in swap + ephemerals: bdm_table.update().where( bdm_table.c.id == bdm['id'] ).values(**bdm).execute()
def p_comment_term(p): '''comment_term : OP_COMMENT string''' filters = [] filters.append(gertty.db.revision_table.c.change_key == gertty.db.change_table.c.key) filters.append(gertty.db.revision_table.c.message == p[2]) revision_select = select([gertty.db.change_table.c.key], correlate=False).where(and_(*filters)) filters = [] filters.append(gertty.db.revision_table.c.change_key == gertty.db.change_table.c.key) filters.append(gertty.db.comment_table.c.revision_key == gertty.db.revision_table.c.key) filters.append(gertty.db.comment_table.c.message == p[2]) comment_select = select([gertty.db.change_table.c.key], correlate=False).where(and_(*filters)) p[0] = or_(gertty.db.change_table.c.key.in_(comment_select), gertty.db.change_table.c.key.in_(revision_select))
def _get_select_waypoints_for_routes(): waypoint_type = text('\'' + WAYPOINT_TYPE + '\'') route_type = text('\'' + ROUTE_TYPE + '\'') select_linked_waypoints = \ select([ Association.child_document_id.label('route_id'), Association.parent_document_id.label('waypoint_id') ]). \ where( and_( Association.parent_document_type == waypoint_type, Association.child_document_type == route_type)). \ cte('linked_waypoints') select_waypoint_parents = \ select([ select_linked_waypoints.c.route_id, Association.parent_document_id.label('waypoint_id') ]). \ select_from(join( select_linked_waypoints, Association, and_( Association.child_document_id == select_linked_waypoints.c.waypoint_id, Association.parent_document_type == waypoint_type ))). \ cte('waypoint_parents') select_waypoint_grandparents = \ select([ select_waypoint_parents.c.route_id, Association.parent_document_id.label('waypoint_id') ]). \ select_from(join( select_waypoint_parents, Association, and_( Association.child_document_id == select_waypoint_parents.c.waypoint_id, Association.parent_document_type == waypoint_type ))). \ cte('waypoint_grandparents') return union( select_linked_waypoints.select(), select_waypoint_parents.select(), select_waypoint_grandparents.select() ). \ cte('all_waypoints')
def _update_nodes(nodemap, instances, migrations): """For each migration and matching instance record, update the node columns if the referenced host is single-node. Skip updates for multi-node hosts. In that case, there's no way to determine which node on a host the record should be associated with. """ q = select([migrations.c.id, migrations.c.source_compute, migrations.c.dest_compute, instances.c.uuid, instances.c.host, instances.c.node], whereclause=and_(migrations.c.source_compute != None, migrations.c.dest_compute != None, instances.c.deleted == False, migrations.c.status != 'reverted', migrations.c.status != 'error'), from_obj=migrations.join(instances, migrations.c.instance_uuid == instances.c.uuid) ) result = q.execute() for migration_id, src, dest, uuid, instance_host, instance_node in result: values = {} nodes = nodemap.get(src, []) if len(nodes) == 1: # the source host is a single-node, safe to update node node = nodes[0] values['source_node'] = node if src == instance_host and node != instance_node: update(instances).where(instances.c.uuid == uuid).\ values(node=node) nodes = nodemap.get(dest, []) if len(nodes) == 1: # the dest host is a single-node, safe to update node node = nodes[0] values['dest_node'] = node if dest == instance_host and node != instance_node: update(instances).where(instances.c.uuid == uuid).\ values(node=node) if values: q = update(migrations, values=values, whereclause=migrations.c.id == migration_id) q.execute()
def test_in_27(self): # test that putting a select in an IN clause does not # blow away its ORDER BY clause self.assert_compile( select([self.table1, self.table2], self.table2.c.otherid.in_( select([self.table2.c.otherid], order_by=[self.table2.c.othername], limit=10, correlate=False) ), from_obj=[self.table1.join(self.table2, self.table1.c.myid == self.table2.c.otherid)], order_by=[self.table1.c.myid] ), "SELECT mytable.myid, " "myothertable.otherid, myothertable.othername FROM mytable "\ "JOIN myothertable ON mytable.myid = myothertable.otherid " "WHERE myothertable.otherid IN (SELECT myothertable.otherid "\ "FROM myothertable ORDER BY myothertable.othername " "LIMIT :param_1) ORDER BY mytable.myid", {'param_1': 10} )
def login(username: str, password: str): try: _user = [] for row in create_engine(db_url).connect().execute( select(user).where(user.c.username == username)): _user.append(dict(row)) if (password == _user[0]['hashpassword']): return "login success" else: return "login fail" except: return "error"
def test_apply_filter_to_query_with_iep_filters(self): with UnittestEdcoreDBConnection() as connection: fact_asmt_outcome = connection.get_table( Constants.FACT_ASMT_OUTCOME_VW) dim_student = connection.get_table(Constants.DIM_STUDENT) query = select([fact_asmt_outcome.c.school_id], from_obj=([fact_asmt_outcome])) query = apply_filter_to_query(query, fact_asmt_outcome, dim_student, {FILTERS_PROGRAM_IEP: [YES]}) self.assertIsNotNone(query._whereclause) self.assertIn("fact_asmt_outcome_vw.dmg_prg_iep", str(query._whereclause))
def test_apply_filter_to_query_with_ethnic_filters(self): with UnittestEdcoreDBConnection() as connection: fact_asmt_outcome = connection.get_table( Constants.FACT_ASMT_OUTCOME_VW) dim_student = connection.get_table(Constants.DIM_STUDENT) query = select([fact_asmt_outcome.c.school_id], from_obj=([fact_asmt_outcome])) query = apply_filter_to_query( query, fact_asmt_outcome, dim_student, {FILTERS_ETHNICITY: [FILTERS_ETHNICITY_AMERICAN]}) self.assertIsNotNone(query._whereclause) self.assertIn("fact_asmt_outcome_vw.dmg_eth_derived", str(query._whereclause))
def get_rating(id): rating = db.session.execute( select([func.sum(RatingChange.change) ]).where(RatingChange.chain_id == id)).scalar() allowed_change = 0 if current_user.is_authenticated and current_user.id: user_change = RatingChange.query.filter_by(user_id=current_user.id, chain_id=id).first() if user_change: allowed_change = -user_change.change return json.dumps({'num': rating, 'allowedChange': allowed_change})
def get(self, project_slug: str): """Get project's info""" return Project.query.filter( and_( Project.slug == project_slug, or_( Project.id.in_( select(ProjectUser.project_id).where( current_user.id == ProjectUser.user_id)), Project.is_public, ), )).first()
def get_question_by_type(self, type_id): table = self.table stmt = select([table.c.content, table.c.choices]) \ .where(table.c.type_id == type_id) \ .order_by(table.c.question_id) cursor = self.execute(stmt) row = cursor.fetchone() while row: yield { 'question_id': row.question_id, 'content': row.content, 'choices': row.choices }
def find_one(self, where_clause): """ Finds the most recent version """ stmt = select([self.get_table()]).where(where_clause).order_by( desc('version')).limit(1) # resp is a ResultProxy object of sqlalchemy which acts something like a DB Cursor resp = stmt.execute().fetchone() if resp and resp['archived']: # the row has been deleted return None return self.get_entity_from_proxy(resp)
def get_names_(objs): if not objs: return ids = set([obj.osmid for obj in objs]) tbl = PolyTable if typ == 'found' else WaysTable res = self.session.execute( select([tbl.c.osm_id, tbl.c.name]).where(tbl.c.osm_id.in_(ids))) names = res.fetchall() names = dict(names) for obj in objs: obj._name = names[obj.osmid] logging.info(names)
def _sub_query(self): select_sub = collate.make_sql_clause("" "officer_id," "count(distinct paygrade_raw) AS count_paygrade," "count(distinct police_area_id) AS count_policearea," "date_trunc('month', job_start_date) as job_start_date", ex.text) from_sub = collate.make_sql_clause('staging.officer_roles', ex.text) group_by_sub = collate.make_sql_clause("officer_id, date_trunc('month', job_start_date) ", ex.text) sub_query = ex.select(columns=[select_sub], from_obj=from_sub) \ .group_by(group_by_sub) return sub_query
def get_map(cls, session, urls): urls = tuple(urls) assert urls result_map = {} for batch in [urls[i:i + 500] for i in range(0, len(urls), 500)]: query = select([URLString])\ .where(URLString.url.in_(batch)) for row in session.execute(query): result_map[row.url] = row.id return result_map
def get_db_time(): """ Gives the utc time on the db. """ s = get_session() try: storage_date_format = None if s.bind.dialect.name == 'oracle': query = select([text("sys_extract_utc(systimestamp)")]) elif s.bind.dialect.name == 'mysql': query = select([text("utc_timestamp()")]) elif s.bind.dialect.name == 'sqlite': query = select([text("datetime('now', 'utc')")]) storage_date_format = '%Y-%m-%d %H:%M:%S' else: query = select([func.current_date()]) for now, in s.execute(query): if storage_date_format: return datetime.strptime(now, storage_date_format) return now finally: s.remove()
def prepare_pre_cache(tenant, state_code, batch_guid): ''' prepare which state and district are pre-cached :param string tenant: name of the tenant :param string state_code: stateCode representing the state :param last_pre_cached: dateTime of the last precached :rType: list :return: list of results containing district guids ''' with EdCoreDBConnection(tenant=tenant) as connector: fact_asmt_outcome_vw = connector.get_table( Constants.FACT_ASMT_OUTCOME_VW) fact_block_asmt_outcome = connector.get_table( Constants.FACT_BLOCK_ASMT_OUTCOME) query_fao = select([ distinct(fact_asmt_outcome_vw.c.district_id).label( Constants.DISTRICT_ID) ], from_obj=[fact_asmt_outcome_vw]) query_fao = query_fao.where( fact_asmt_outcome_vw.c.state_code == state_code) query_fao = query_fao.where( and_(fact_asmt_outcome_vw.c.batch_guid == batch_guid)) query_fao = query_fao.where( and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT)) query_fbao = select([ distinct(fact_block_asmt_outcome.c.district_id).label( Constants.DISTRICT_ID) ], from_obj=[fact_block_asmt_outcome]) query_fbao = query_fbao.where( fact_block_asmt_outcome.c.state_code == state_code) query_fbao = query_fbao.where( and_(fact_block_asmt_outcome.c.batch_guid == batch_guid)) query_fbao = query_fbao.where( and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT)) results = connector.get_result(query_fao.union(query_fbao)) return results
def get_rows(altn): query = select([ AlternateTranslatorNames.group, AlternateTranslatorNames.cleanname, AlternateTranslatorNames.name ], from_obj=[AlternateTranslatorNames ]).where(AlternateTranslatorNames.name == altn) results = db.session.execute(query).fetchall() ret = [] for result in results: ret.append((result[0], result[1], result[2], 0.1)) return ret
def select_output_features(self, subquery): from sqlalchemy.sql.expression import select, literal t_mtof = schema.model_trained_on_output_feature tt_mtof = t_mtof.__table__ t_f = schema.feature idtype = tt_mtof.c.idmodel.type sq = subquery.subquery('t') self.session.execute( insert_ignore(t_mtof).from_select( [t_mtof.idmodel, t_mtof.idfeature], select([literal(self.idmodel, type_=idtype), sq.c.idfeature])))
def transactions_all_json(): lower = request.args.get('after', "") upper = request.args.get('before', "") filter = request.args.get('filter', "nonuser") if filter == "nonuser": non_user_transactions = (select(Split.transaction_id).select_from( Join(Split, User, (User.account_id == Split.account_id), isouter=True)).group_by(Split.transaction_id).having( func.bool_and(User.id == None)).alias("nut")) tid = literal_column("nut.transaction_id") transactions = non_user_transactions.join(Transaction, Transaction.id == tid) else: transactions = Transaction.__table__ q = (select(Transaction.id, Transaction.valid_on, Split.account_id, Account.type, Split.amount).select_from( transactions.join( Split, Split.transaction_id == Transaction.id).join( Account, Account.id == Split.account_id))) try: datetime.strptime(lower, "%Y-%m-%d").date() except ValueError: not lower or abort(422) else: q = q.where(Transaction.valid_on >= lower) try: datetime.strptime(upper, "%Y-%m-%d").date() except ValueError: not upper or abort(422) else: q = q.where(Transaction.valid_on <= upper) res = session.execute(json_agg_core(q)).fetchone()[0] or [] return jsonify(items=res)
def p_recentlyseen_term(p): '''recentlyseen_term : OP_RECENTLYSEEN NUMBER string''' # A gertty extension now = datetime.datetime.utcnow() delta = p[2] unit = p[3] delta = age_to_delta(delta, unit) s = select([ func.datetime(func.max(gertty.db.change_table.c.last_seen), '-%s seconds' % delta) ], correlate=False) p[0] = gertty.db.change_table.c.last_seen >= s
def get_udl_record_by_batch_guid(batch_guid, tenant): records = [] with get_prod_connection(tenant=tenant) as connection: fact_asmt_outcome = connection.get_table('fact_asmt_outcome') fact_block_asmt_outcome = connection.get_table( 'fact_block_asmt_outcome') select_fao = select([ fact_asmt_outcome.c.student_id.label(STUDENT_ID), fact_asmt_outcome.c.asmt_guid.label(ASMT_GUID), fact_asmt_outcome.c.date_taken.label(DATE_TAKEN) ]).where( and_(fact_asmt_outcome.c.batch_guid == batch_guid, fact_asmt_outcome.c.rec_status == 'C')) select_fbao = select([ fact_block_asmt_outcome.c.student_id.label(STUDENT_ID), fact_block_asmt_outcome.c.asmt_guid.label(ASMT_GUID), fact_block_asmt_outcome.c.date_taken.label(DATE_TAKEN) ]).where( and_(fact_block_asmt_outcome.c.batch_guid == batch_guid, fact_block_asmt_outcome.c.rec_status == 'C')) records = connection.get_result(select_fao.union(select_fbao)) return records
def _exec_default(self, default, type_): if default.is_sequence: return self.fire_sequence(default, type_) elif default.is_callable: return default.arg(self) elif default.is_clause_element: # TODO: expensive branching here should be # pulled into _exec_scalar() conn = self.connection c = expression.select([default.arg]).compile(bind=conn) return conn._execute_compiled(c, (), {}).scalar() else: return default.arg
async def _(graphql_client=graphql_client, db=db, user_factory=user_factory): user = await user_factory( email="*****@*****.**", password="******", is_active=True, jwt_auth_id=1, ) token = user.create_reset_password_token() db_query = select(User).where(User.id == user.id) raw_query_user: User = (await db.execute(db_query)).scalar() raw_query_user.jwt_auth_id = 2 repository = UsersRepository(db) await repository.save_user(raw_query_user) await repository.commit() query = """ mutation($input: ResetPasswordInput!) { resetPassword(input: $input) { __typename ... on ResetPasswordTokenInvalid { message } } } """ response = await graphql_client.query( query, variables={"input": {"token": token, "newPassword": "******"}} ) assert not response.errors assert response.data["resetPassword"]["__typename"] == "ResetPasswordTokenInvalid" query = select(User).where(User.email == "*****@*****.**") raw_query_user: User = (await db.execute(query)).scalar() assert raw_query_user.check_password("hello")
def _get_select_users_for_routes_aggregated(): """ Returns a select which retrieves for every route the ids of associated users. """ return \ select([ DocumentTag.document_id.label('route_id'), func.array_agg( DocumentTag.user_id, type_=postgresql.ARRAY(Integer)).label('user_ids') ]). \ select_from(DocumentTag). \ group_by(DocumentTag.document_id)
def get_account_verification_token(email=None, username=None): email = email and emailer.normalize_address(email) username = username and d.get_sysname(username) logincreate = d.meta.tables['logincreate'] statement = select([logincreate.c.token]) if email: statement = statement.where(logincreate.c.email.ilike(email)) else: statement = statement.where(logincreate.c.login_name == username) return d.engine.scalar(statement)
def __cache_user_job_count_per_destination(self): # Cache the job count if necessary if self.user_job_count_per_destination is None and self.app.config.cache_user_job_count: self.user_job_count_per_destination = {} result = self.sa_session.execute(select([model.Job.table.c.user_id, model.Job.table.c.destination_id, func.count(model.Job.table.c.user_id).label('job_count')]) \ .where(and_(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING)))) \ .group_by(model.Job.table.c.user_id, model.Job.table.c.destination_id)) for row in result: if row['user_id'] not in self.user_job_count_per_destination: self.user_job_count_per_destination[row['user_id']] = {} self.user_job_count_per_destination[row['user_id']][row['destination_id']] = row['job_count'] elif self.user_job_count_per_destination is None: self.user_job_count_per_destination = {}
def find_image(service_name): image_table = Table('service_images', meta, autoload=True) image = select( columns=[text("id"), text("image_id"), text("service_name")], from_obj=image_table, whereclause=text("service_name='%s'" % service_name), limit=1).execute().fetchone() if image: return image.id return LEGACY_IMAGE_ID
def store_area_user(connection, galaxy_id, group): """ Store the areas associated with a galaxy """ LOG.info('Storing the area_users') count = connection.execute( select([func.count(AREA_USER.c.areauser_id)], from_obj=AREA_USER.join(AREA)).where( AREA.c.galaxy_id == galaxy_id)).first()[0] data = numpy.zeros(count, dtype=data_type_area_user) count = 0 for area_user in connection.execute( select([AREA_USER], from_obj=AREA_USER.join(AREA)).where( AREA.c.galaxy_id == galaxy_id).order_by( AREA_USER.c.areauser_id)): data[count] = ( area_user[AREA_USER.c.area_id], area_user[AREA_USER.c.userid], str(area_user[AREA_USER.c.create_time]), ) count += 1 group.create_dataset('area_user', data=data, compression='gzip')
def _test_storeCING2db(self): #DEFAULT disabled because it's a specific test for services not commonly used. cingDirTmpTest = os.path.join( cingDirTmp, getCallerName() ) mkdirs( cingDirTmpTest ) self.failIf(os.chdir(cingDirTmpTest), msg = "Failed to change to test directory for files: " + cingDirTmpTest) entry_code = '1brv' pdb_id = entry_code schema = NRG_DB_NAME archive_id = ARCHIVE_NRG_ID db_name = PDBJ_DB_NAME user_name = PDBJ_DB_USER_NAME nTdebug("Starting doStoreCING2db using:") nTdebug("entry_code: %s" % entry_code) # nTdebug("inputDir: %s" % inputDir) nTdebug("archive_id: %s" % archive_id) nTdebug("user_name: %s" % user_name) nTdebug("db_name: %s" % db_name) nTdebug("schema: %s" % schema) csql = CsqlAlchemy(user=user_name, db=db_name,schema=schema) self.assertFalse( csql.connect(), "Failed to connect to DB") csql.autoload() execute = csql.conn.execute centry = csql.cingentry result = execute(centry.delete().where(centry.c.pdb_id == pdb_id)) if result.rowcount: nTdebug("Removed original entries numbering: %s" % result.rowcount) if result.rowcount > 1: nTerror("Removed more than the expected ONE entry; this could be serious.") return True else: nTdebug("No original entry present yet.") # end if datetime_first = datetime.datetime(2011, 4, 7, 11, 12, 26) nTdebug("Trying datetime_first %s" % datetime_first) result = execute(centry.insert().values( pdb_id=pdb_id, name=entry_code, rev_first = 9, rev_last = 99, timestamp_first = datetime_first # datetime_last = datetime_last )) entry_id_list = execute(select([centry.c.entry_id]).where(centry.c.pdb_id==pdb_id)).fetchall() self.assertTrue( entry_id_list, "Failed to get the id of the inserted entry but got: %s" % entry_id_list) self.assertEqual( len( entry_id_list ), 1, "Failed to get ONE id of the inserted entry but got: %s" % entry_id_list)
def imdb_query(session): import time from flexget.plugins.metainfo.imdb_lookup import Movie from flexget.plugins.cli.performance import log_query_count from sqlalchemy.sql.expression import select from progressbar import ProgressBar, Percentage, Bar, ETA from sqlalchemy.orm import joinedload_all imdb_urls = [] log.info('Getting imdb_urls ...') # query so that we avoid loading whole object (maybe cached?) for id, url in session.execute(select([Movie.id, Movie.url])): imdb_urls.append(url) log.info('Got %i urls from database' % len(imdb_urls)) if not imdb_urls: log.info('so .. aborting') return # commence testing widgets = [ 'Benchmarking - ', ETA(), ' ', Percentage(), ' ', Bar(left='[', right=']') ] bar = ProgressBar(widgets=widgets, maxval=len(imdb_urls)).start() log_query_count('test') start_time = time.time() for index, url in enumerate(imdb_urls): bar.update(index) #movie = session.query(Movie).filter(Movie.url == url).first() #movie = session.query(Movie).options(subqueryload(Movie.genres)).filter(Movie.url == url).one() movie = session.query(Movie).\ options(joinedload_all(Movie.genres, Movie.languages, Movie.actors, Movie.directors)).\ filter(Movie.url == url).first() # access it's members so they're loaded var = [x.name for x in movie.genres] var = [x.name for x in movie.directors] var = [x.name for x in movie.actors] var = [x.name for x in movie.languages] log_query_count('test') took = time.time() - start_time log.debug('Took %.2f seconds to query %i movies' % (took, len(imdb_urls)))
def exists_in_table(session: Session, table_: Table, *criteria: Any) -> bool: """ Implements an efficient way of detecting if a record or records exist; should be faster than ``COUNT(*)`` in some circumstances. Args: session: SQLAlchemy :class:`Session`, :class:`Engine`, or :class:`Connection` object table_: SQLAlchemy :class:`Table` object criteria: optional SQLAlchemy "where" criteria Returns: a boolean Prototypical use: .. code-block:: python return exists_in_table(session, table, column(fieldname1) == value2, column(fieldname2) == value2) """ exists_clause = exists().select_from(table_) # ... EXISTS (SELECT * FROM tablename) for criterion in criteria: exists_clause = exists_clause.where(criterion) # ... EXISTS (SELECT * FROM tablename WHERE ...) if session.get_bind().dialect.name == SqlaDialectName.MSSQL: query = select([literal(True)]).where(exists_clause) # ... SELECT 1 WHERE EXISTS (SELECT * FROM tablename WHERE ...) else: query = select([exists_clause]) # ... SELECT EXISTS (SELECT * FROM tablename WHERE ...) result = session.execute(query).scalar() return bool(result)
def get_asmt_rec_id(guid_batch, tenant_name, asmt_rec_id_info): ''' Returns asmt_rec_id from dim_asmt table Steps: 1. Get guid_asmt from integration table INT_SBAC_ASMT 2. Select asmt_rec_id from dim_asmt by the same guid_amst got from 1. It should have 1 value ''' source_table_name = asmt_rec_id_info['source_table'] guid_column_name_in_source = asmt_rec_id_info['guid_column_in_source'] target_table_name = asmt_rec_id_info['target_table'] guid_column_name_in_target = asmt_rec_id_info['guid_column_name'] rec_id_column_name = asmt_rec_id_info['rec_id'] # connect to integration table, to get the value of guid_asmt with get_udl_connection() as udl_conn: int_table = udl_conn.get_table(source_table_name) query = select([int_table.c[guid_column_name_in_source]], from_obj=int_table, limit=1) query = query.where(int_table.c['guid_batch'] == guid_batch) results = udl_conn.get_result(query) if results: guid_column_value = results[0][guid_column_name_in_source] # connect to target table, to get the value of asmt_rec_id with get_target_connection(tenant_name, guid_batch) as target_conn: dim_asmt = target_conn.get_table(target_table_name) query = select([dim_asmt.c[rec_id_column_name]], from_obj=dim_asmt, limit=1) query = query.where( dim_asmt.c[guid_column_name_in_target] == guid_column_value) query = query.where(and_(dim_asmt.c['batch_guid'] == guid_batch)) results = target_conn.get_result(query) if results: asmt_rec_id = results[0][rec_id_column_name] return asmt_rec_id