def test_annotate_unique_traversal(self): """test that items are copied only once during annotate, deannotate traversal #2453 """ table1 = table('table1', column('x')) table2 = table('table2', column('y')) a1 = table1.alias() s = select([a1.c.x]).select_from( a1.join(table2, a1.c.x==table2.c.y) ) for sel in ( sql_util._deep_deannotate(s), sql_util._deep_annotate(s, {'foo':'bar'}), visitors.cloned_traverse(s, {}, {}), visitors.replacement_traverse(s, {}, lambda x:None) ): # the columns clause isn't changed at all assert sel._raw_columns[0].table is a1 # the from objects are internally consistent, # i.e. the Alias at position 0 is the same # Alias in the Join object in position 1 assert sel._froms[0] is sel._froms[1].left eq_(str(s), str(sel))
def _insert_operation_form_translation(): tb = table( 'operation_form_translation', column('id', Integer), column('locale', String), column('name', String) ) columns = [c.name for c in tb.columns] data = [ (102, 'en', 'Execution'), (102, 'pt', 'Execução'), (103, 'en', 'Execution'), (103, 'pt', 'Execução'), (104, 'en', 'Execution'), (104, 'pt', 'Execução'), (105, 'en', 'Execution'), (105, 'pt', 'Execução'), (106, 'en', 'Execution'), (106, 'pt', 'Execução'), (107, 'en', 'Execution'), (107, 'pt', 'Execução'), ] rows = [dict(zip(columns, row)) for row in data] op.bulk_insert(tb, rows)
def _insert_operation_form_field_translation(): tb = table( 'operation_form_field_translation', column('id', Integer), column('locale', String), column('label', String), column('help', String), ) columns = ('id', 'locale', 'label', 'help') data = [ #normalize (3078, 'en', 'Attributes', 'Attributes'), (3078, 'pt', 'Attributos', 'Colunas para serem consideradas'), (3079, 'en', 'Alias', 'Name of the new column'), (3079, 'pt', 'Alias', 'Nome para a nova coluna criada'), (3080, 'en', 'Normalization Type', 'Type of Normalization to perform.'), (3080, 'pt', 'Tipo de Normalização', 'Tipo de Normalização para ser feita.'), #feature-indexer' (3027,'en','Attributes', 'Attributes (features) to be indexed'), (3027,'pt','Atributos', 'Atributos (features) a ser indexados'), (3028,'en','Indexer type', 'Indexer type'), (3028,'pt','Tipo de indexador', 'Tipo de indexador'), (3029,'en','Name for new indexed attribute(s)', 'Name for new indexed attribute(s)'), (3029,'pt','Nome para novo(s) atributo(s) indexado(s)', 'Nome para novo(s) atributo(s) indexado(s)'), ] rows = [dict(zip(columns, row)) for row in data] op.bulk_insert(tb, rows)
def test_annotate_fromlist_preservation(self): """test the FROM list in select still works even when multiple annotate runs have created copies of the same selectable #2453, continued """ table1 = table('table1', column('x')) table2 = table('table2', column('y')) a1 = table1.alias() s = select([a1.c.x]).select_from( a1.join(table2, a1.c.x==table2.c.y) ) assert_s = select([select([s])]) for fn in ( sql_util._deep_deannotate, lambda s: sql_util._deep_annotate(s, {'foo':'bar'}), lambda s:visitors.cloned_traverse(s, {}, {}), lambda s:visitors.replacement_traverse(s, {}, lambda x:None) ): sel = fn(select([fn(select([fn(s)]))])) eq_(str(assert_s), str(sel))
def _insert_operation_operation_form(): tb = table( 'operation_operation_form', column('operation_id', Integer), column('operation_form_id', Integer)) columns = [c.name for c in tb.columns] data = [ [REGRESSION_MODEL, 102], [ISOTONIC_REGRESSION, 103], [AFT_SURVIVAL_REGRESSION, 104], [GBT_REGRESSOR, 105], [RANDOM_FOREST_REGRESSOR, 106], [GENERALIZED_LINEAR_REGRESSOR, 107], [REGRESSION_MODEL, 41], [ISOTONIC_REGRESSION, 41], [AFT_SURVIVAL_REGRESSION, 41], [GBT_REGRESSOR, 41], [RANDOM_FOREST_REGRESSOR, 41], [GENERALIZED_LINEAR_REGRESSOR, 41], ] rows = [dict(zip(columns, row)) for row in data] op.bulk_insert(tb, rows)
def upgrade(): # Insert G-Cloud 9 lot records lot_table = table( 'lots', column('name', sa.String), column('slug', sa.String), column('one_service_limit', sa.Boolean), column('data', sa.JSON) ) op.bulk_insert(lot_table, [ { 'name': 'Cloud hosting', 'slug': 'cloud-hosting', 'one_service_limit': False, 'data': {"unitSingular": "service", "unitPlural": "services"} }, { 'name': 'Cloud software', 'slug': 'cloud-software', 'one_service_limit': False, 'data': {"unitSingular": "service", "unitPlural": "services"} }, { 'name': 'Cloud support', 'slug': 'cloud-support', 'one_service_limit': False, 'data': {"unitSingular": "service", "unitPlural": "services"} }, ])
def _insert_operation_operation_form(): tb = table( 'operation_operation_form', column('operation_id', Integer), column('operation_form_id', Integer)) columns = ('operation_id', 'operation_form_id') data = [ (3022, 39),#normalize (3022, 40), (3022, 41), (3022, 43), (3022, 110), (3022, 3022), (3012, 39),#feature-indexer' (3012, 40), (3012, 41), (3012, 43), (3012, 110), (3012,3012), ] rows = [dict(zip(columns, row)) for row in data] op.bulk_insert(tb, rows)
def test_update_returning(self): table1 = table( 'mytable', column('myid', Integer), column('name', String(128)), column('description', String(128))) u = update( table1, values=dict(name='foo')).returning(table1.c.myid, table1.c.name) self.assert_compile(u, 'UPDATE mytable SET name=:name OUTPUT ' 'inserted.myid, inserted.name') u = update(table1, values=dict(name='foo')).returning(table1) self.assert_compile(u, 'UPDATE mytable SET name=:name OUTPUT ' 'inserted.myid, inserted.name, ' 'inserted.description') u = update( table1, values=dict( name='foo')).returning(table1).where(table1.c.name == 'bar') self.assert_compile(u, 'UPDATE mytable SET name=:name OUTPUT ' 'inserted.myid, inserted.name, ' 'inserted.description WHERE mytable.name = ' ':name_1') u = update(table1, values=dict(name='foo' )).returning(func.length(table1.c.name)) self.assert_compile(u, 'UPDATE mytable SET name=:name OUTPUT ' 'LEN(inserted.name) AS length_1')
def test_limit_offset_with_correlated_order_by(self): t1 = table('t1', column('x', Integer), column('y', Integer)) t2 = table('t2', column('x', Integer), column('y', Integer)) order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).as_scalar() s = select([t1]).where(t1.c.x == 5).order_by(order_by) \ .limit(10).offset(20) self.assert_compile( s, "SELECT anon_1.x, anon_1.y " "FROM (SELECT t1.x AS x, t1.y AS y, " "ROW_NUMBER() OVER (ORDER BY " "(SELECT t2.y FROM t2 WHERE t1.x = t2.x)" ") AS mssql_rn " "FROM t1 " "WHERE t1.x = :x_1) AS anon_1 " "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1", checkparams={'param_1': 20, 'param_2': 10, 'x_1': 5} ) c = s.compile(dialect=mssql.MSDialect()) eq_(len(c._result_columns), 2) assert t1.c.x in set(c._create_result_map()['x'][1]) assert t1.c.y in set(c._create_result_map()['y'][1])
def test_aliases_schemas(self): metadata = MetaData() table1 = table('mytable', column('myid', Integer), column('name', String), column('description', String), ) table4 = Table( 'remotetable', metadata, Column('rem_id', Integer, primary_key=True), Column('datatype_id', Integer), Column('value', String(20)), schema = 'remote_owner' ) s = table4.select() c = s.compile(dialect=self.__dialect__) assert table4.c.rem_id in set(c.result_map['rem_id'][1]) s = table4.select(use_labels=True) c = s.compile(dialect=self.__dialect__) print c.result_map assert table4.c.rem_id in set(c.result_map['remote_owner_remotetable_rem_id'][1]) self.assert_compile(table4.select(), "SELECT remotetable_1.rem_id, remotetable_1.datatype_id, remotetable_1.value FROM remote_owner.remotetable AS remotetable_1") self.assert_compile(table4.select(use_labels=True), "SELECT remotetable_1.rem_id AS remote_owner_remotetable_rem_id, remotetable_1.datatype_id AS remote_owner_remotetable_datatype_id, remotetable_1.value AS remote_owner_remotetable_value FROM remote_owner.remotetable AS remotetable_1") self.assert_compile(table1.join(table4, table1.c.myid==table4.c.rem_id).select(), "SELECT mytable.myid, mytable.name, mytable.description, remotetable_1.rem_id, remotetable_1.datatype_id, remotetable_1.value FROM mytable JOIN remote_owner.remotetable AS remotetable_1 ON remotetable_1.rem_id = mytable.myid")
def _insert_operation_form_translation(): tb = table( 'operation_form_translation', column('id', Integer), column('locale', String), column('name', String)) columns = ('id', 'locale', 'name') data = [ (3024, 'en', 'Execution'), (3024, 'pt', 'Execução'), (3025, 'en', 'Execution'), (3025, 'pt', 'Execução'), (3026, 'en', 'Execution'), (3026, 'pt', 'Execução'), (3027, 'en', 'Execution'), (3027, 'pt', 'Execução'), (3028, 'en', 'Execution'), (3028, 'pt', 'Execução'), (3029, 'en', 'Execution'), (3029, 'pt', 'Execução'), (3030, 'en', 'Execution'), (3030, 'pt', 'Execução'), (3031, 'en', 'Execution'), (3031, 'pt', 'Execução'), ] rows = [dict(zip(columns, row)) for row in data] op.bulk_insert(tb, rows)
def test_recursive(self): parts = table('parts', column('part'), column('sub_part'), column('quantity'), ) included_parts = select([ parts.c.sub_part, parts.c.part, parts.c.quantity]).\ where(parts.c.part=='our part').\ cte(recursive=True) incl_alias = included_parts.alias() parts_alias = parts.alias() included_parts = included_parts.union( select([ parts_alias.c.part, parts_alias.c.sub_part, parts_alias.c.quantity]).\ where(parts_alias.c.part==incl_alias.c.sub_part) ) s = select([ included_parts.c.sub_part, func.sum(included_parts.c.quantity).label('total_quantity')]).\ select_from(included_parts.join( parts,included_parts.c.part==parts.c.part)).\ group_by(included_parts.c.sub_part) self.assert_compile(s, "WITH RECURSIVE anon_1(sub_part, part, quantity) " "AS (SELECT parts.sub_part AS sub_part, parts.part " "AS part, parts.quantity AS quantity FROM parts " "WHERE parts.part = :part_1 UNION SELECT parts_1.part " "AS part, parts_1.sub_part AS sub_part, parts_1.quantity " "AS quantity FROM parts AS parts_1, anon_1 AS anon_2 " "WHERE parts_1.part = anon_2.sub_part) " "SELECT anon_1.sub_part, " "sum(anon_1.quantity) AS total_quantity FROM anon_1 " "JOIN parts ON anon_1.part = parts.part " "GROUP BY anon_1.sub_part" ) # quick check that the "WITH RECURSIVE" varies per # dialect self.assert_compile(s, "WITH anon_1(sub_part, part, quantity) " "AS (SELECT parts.sub_part AS sub_part, parts.part " "AS part, parts.quantity AS quantity FROM parts " "WHERE parts.part = :part_1 UNION SELECT parts_1.part " "AS part, parts_1.sub_part AS sub_part, parts_1.quantity " "AS quantity FROM parts AS parts_1, anon_1 AS anon_2 " "WHERE parts_1.part = anon_2.sub_part) " "SELECT anon_1.sub_part, " "sum(anon_1.quantity) AS total_quantity FROM anon_1 " "JOIN parts ON anon_1.part = parts.part " "GROUP BY anon_1.sub_part", dialect=mssql.dialect() )
def test_cube_operators(self): t = table('t', column('value'), column('x'), column('y'), column('z'), column('q')) stmt = select([func.sum(t.c.value)]) self.assert_compile( stmt.group_by(func.cube(t.c.x, t.c.y)), "SELECT sum(t.value) AS sum_1 FROM t GROUP BY CUBE(t.x, t.y)" ) self.assert_compile( stmt.group_by(func.rollup(t.c.x, t.c.y)), "SELECT sum(t.value) AS sum_1 FROM t GROUP BY ROLLUP(t.x, t.y)" ) self.assert_compile( stmt.group_by( func.grouping_sets(t.c.x, t.c.y) ), "SELECT sum(t.value) AS sum_1 FROM t " "GROUP BY GROUPING SETS(t.x, t.y)" ) self.assert_compile( stmt.group_by( func.grouping_sets( sql.tuple_(t.c.x, t.c.y), sql.tuple_(t.c.z, t.c.q), ) ), "SELECT sum(t.value) AS sum_1 FROM t GROUP BY " "GROUPING SETS((t.x, t.y), (t.z, t.q))" )
def get(self, database, query): bs = block_size.c.block_size stmt = powa_getstatdata_detailed_db() stmt = stmt.where( (column("datname") == bindparam("database")) & (column("queryid") == bindparam("query"))) stmt = stmt.alias() from_clause = outerjoin(powa_statements, stmt, and_(powa_statements.c.queryid == stmt.c.queryid, powa_statements.c.dbid == stmt.c.dbid)) c = stmt.c rblk = mulblock(sum(c.shared_blks_read).label("shared_blks_read")) wblk = mulblock(sum(c.shared_blks_hit).label("shared_blks_hit")) stmt = (select([ column("query"), sum(c.calls).label("calls"), sum(c.runtime).label("runtime"), rblk, wblk, (rblk + wblk).label("total_blks")]) .select_from(from_clause) .where(powa_statements.c.queryid == bindparam("query")) .group_by(column("query"), bs)) value = self.execute(stmt, params={ "query": query, "database": database, "from": self.get_argument("from"), "to": self.get_argument("to") }) if value.rowcount < 1: self.render("xhr.html", content="No data") return self.render("database/query/detail.html", stats=value.first())
def test_annotations(self): """test that annotated clause constructs use the decorated class' compiler. """ t1 = table('t1', column('c1'), column('c2')) dispatch = Select._compiler_dispatch try: @compiles(Select) def compile(element, compiler, **kw): return "OVERRIDE" s1 = select([t1]) self.assert_compile( s1, "OVERRIDE" ) self.assert_compile( s1._annotate({}), "OVERRIDE" ) finally: Select._compiler_dispatch = dispatch if hasattr(Select, '_compiler_dispatcher'): del Select._compiler_dispatcher
def by_stream(self, query, operand, maybe_negate): # type: (Query, str, ConditionTransform) -> Query stream = get_stream(operand, self.user_profile.realm) if stream is None: raise BadNarrowOperator('unknown stream ' + operand) if self.user_profile.realm.is_zephyr_mirror_realm: # MIT users expect narrowing to "social" to also show messages to /^(un)*social(.d)*$/ # (unsocial, ununsocial, social.d, etc) m = re.search(r'^(?:un)*(.+?)(?:\.d)*$', stream.name, re.IGNORECASE) # Since the regex has a `.+` in it and "" is invalid as a # stream name, this will always match assert(m is not None) base_stream_name = m.group(1) matching_streams = get_active_streams(self.user_profile.realm).filter( name__iregex=r'^(un)*%s(\.d)*$' % (self._pg_re_escape(base_stream_name),)) matching_stream_ids = [matching_stream.id for matching_stream in matching_streams] recipients_map = bulk_get_recipients(Recipient.STREAM, matching_stream_ids) cond = column("recipient_id").in_([recipient.id for recipient in recipients_map.values()]) return query.where(maybe_negate(cond)) recipient = get_recipient(Recipient.STREAM, type_id=stream.id) cond = column("recipient_id") == recipient.id return query.where(maybe_negate(cond))
def _by_search_tsearch(self, query, operand, maybe_negate): # type: (Query, str, ConditionTransform) -> Query tsquery = func.plainto_tsquery(literal("zulip.english_us_search"), literal(operand)) ts_locs_array = func.ts_match_locs_array query = query.column(ts_locs_array(literal("zulip.english_us_search"), column("rendered_content"), tsquery).label("content_matches")) # We HTML-escape the subject in Postgres to avoid doing a server round-trip query = query.column(ts_locs_array(literal("zulip.english_us_search"), func.escape_html(column("subject")), tsquery).label("subject_matches")) # Do quoted string matching. We really want phrase # search here so we can ignore punctuation and do # stemming, but there isn't a standard phrase search # mechanism in Postgres for term in re.findall('"[^"]+"|\S+', operand): if term[0] == '"' and term[-1] == '"': term = term[1:-1] term = '%' + connection.ops.prep_for_like_query(term) + '%' cond = or_(column("content").ilike(term), column("subject").ilike(term)) query = query.where(maybe_negate(cond)) cond = column("search_tsvector").op("@@")(tsquery) return query.where(maybe_negate(cond))
def test_insert_returning(self): dialect = postgresql.dialect() table1 = table('mytable', column('myid', Integer), column('name', String(128)), column('description', String(128)), ) i = insert( table1, values=dict( name='foo')).returning( table1.c.myid, table1.c.name) self.assert_compile(i, 'INSERT INTO mytable (name) VALUES ' '(%(name)s) RETURNING mytable.myid, ' 'mytable.name', dialect=dialect) i = insert(table1, values=dict(name='foo')).returning(table1) self.assert_compile(i, 'INSERT INTO mytable (name) VALUES ' '(%(name)s) RETURNING mytable.myid, ' 'mytable.name, mytable.description', dialect=dialect) i = insert(table1, values=dict(name='foo' )).returning(func.length(table1.c.name)) self.assert_compile(i, 'INSERT INTO mytable (name) VALUES ' '(%(name)s) RETURNING length(mytable.name) ' 'AS length_1', dialect=dialect)
def upgrade(): op.add_column('task_groups', sa.Column('sort_index', sa.String(length=250), nullable=False)) op.add_column('cycle_task_groups', sa.Column('sort_index', sa.String(length=250), nullable=False)) workflows_table = table( 'workflows', column('id', sa.Integer) ) task_groups_table = table( 'task_groups', column('id', sa.Integer), column('sort_index', sa.String), column('workflow_id', sa.Integer), ) cycles_table = table( 'cycles', column('id', sa.Integer), column('sort_index', sa.String), column('workflow_id', sa.Integer), ) cycle_task_groups_table = table( 'cycle_task_groups', column('id', sa.Integer), column('sort_index', sa.String), column('cycle_id', sa.Integer), ) _set_sort_index(workflows_table, 'workflow_id', task_groups_table) _set_sort_index(cycles_table, 'cycle_id', cycle_task_groups_table)
def upgrade(): op.execute('SET search_path TO mineturer') conn = op.get_bind() res = conn.execute('select userid, password from users') results = res.fetchall() users = table( 'users', column('bcrypt_pwd', sa.String), column('userid', sa.Integer) ) for result in results: userid = result[0] pwd = result[1] bcrypt_pwd = bcrypt.hashpw( pwd.encode('utf-8'), bcrypt.gensalt() ) op.execute( users.update().where( users.c.userid == op.inline_literal(userid) ).values({ 'bcrypt_pwd': op.inline_literal(bcrypt_pwd)} ) ) op.execute('SET search_path TO public')
def test_update_returning(self): dialect = postgresql.dialect() table1 = table( 'mytable', column( 'myid', Integer), column( 'name', String(128)), column( 'description', String(128))) u = update( table1, values=dict( name='foo')).returning( table1.c.myid, table1.c.name) self.assert_compile(u, 'UPDATE mytable SET name=%(name)s ' 'RETURNING mytable.myid, mytable.name', dialect=dialect) u = update(table1, values=dict(name='foo')).returning(table1) self.assert_compile(u, 'UPDATE mytable SET name=%(name)s ' 'RETURNING mytable.myid, mytable.name, ' 'mytable.description', dialect=dialect) u = update(table1, values=dict(name='foo' )).returning(func.length(table1.c.name)) self.assert_compile( u, 'UPDATE mytable SET name=%(name)s ' 'RETURNING length(mytable.name) AS length_1', dialect=dialect)
def upgrade(): userexternalid = table('userexternalid', column('updated_at', sa.DateTime), column('last_used_at', sa.DateTime)) op.add_column('userexternalid', sa.Column('last_used_at', sa.DateTime(), nullable=True)) op.execute(userexternalid.update().values(last_used_at=userexternalid.c.updated_at)) op.alter_column('userexternalid', 'last_used_at', nullable=False)
def test_noorderby_parameters_insubquery(self): """test that the ms-sql dialect does not include ORDER BY positional parameters in subqueries""" table1 = table( "mytable", column("myid", Integer), column("name", String), column("description", String), ) q = select( [table1.c.myid, sql.literal('bar').label('c1')], order_by=[table1.c.name + '-'] ).alias("foo") crit = q.c.myid == table1.c.myid dialect = mssql.dialect() dialect.paramstyle = "qmark" dialect.positional = True self.assert_compile( select(["*"], crit), "SELECT * FROM (SELECT mytable.myid AS " "myid, ? AS c1 FROM mytable) AS foo, mytable WHERE " "foo.myid = mytable.myid", dialect=dialect, checkparams={'param_1': 'bar'}, # if name_1 is included, too many parameters are passed to dbapi checkpositional=('bar', ) )
def upgrade(): ### commands auto generated by Alembic ### op.create_table('employees_attendance', sa.Column('id', sa.Integer(), nullable=False), sa.Column('attendanceDate', sa.Date(), nullable=False), sa.Column('arriveTime', sa.Time(), nullable=False), sa.Column('leaveTime', sa.Time(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('createdBy_id', sa.Integer(), nullable=True), sa.Column('issueDateTime', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['createdBy_id'], ['users.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['employee_id'], ['users.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id') ) abilities_table = table('abilities', column('id', Integer), column('name', String), ) op.bulk_insert(abilities_table, [ {'name': "employeeAttendances.list"}, {'name': "employeeAttendances.show"}, {'name': "employeeAttendances.delete"}, {'name': "employeeAttendances.update"}, {'name': "employeeAttendances.create"}, {'name': "feedbacks.list"}, {'name': "feedbacks.show"} ] )
def test_delete_extra_froms(self): t1 = table("t1", column("c1")) t2 = table("t2", column("c1")) q = sql.delete(t1).where(t1.c.c1 == t2.c.c1) self.assert_compile( q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1" )
def genericize_imapaccount(): class ImapAccount_(Base): __table__ = Base.metadata.tables['imapaccount'] # Get data from columns-to-be-dropped with session_scope() as db_session: results = db_session.query(ImapAccount_.id, ImapAccount_.imap_host).all() to_insert = [dict(id=r[0], imap_host=r[1]) for r in results] # Rename table, add new columns. op.rename_table('imapaccount', 'account') op.add_column('account', sa.Column('type', sa.String(16))) # Create new table, insert data # The table op.create_table('imapaccount', sa.Column('imap_host', sa.String(512)), sa.Column('id', sa.Integer()), sa.ForeignKeyConstraint(['id'], ['account.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id')) # The ad-hoc table for insert table_ = table('imapaccount', column('imap_host', sa.String()), column('id', sa.Integer)) if to_insert: op.bulk_insert(table_, to_insert) # Drop columns now op.drop_column('account', 'imap_host')
def genericize_thread(): class Thread_(Base): __table__ = Base.metadata.tables['thread'] # Get data from columns-to-be-dropped with session_scope() as db_session: results = db_session.query(Thread_.id, Thread_.g_thrid).all() to_insert = [dict(id=r[0], g_thrid=r[1]) for r in results] # Add new columns op.add_column('thread', sa.Column('type', sa.String(16))) # Create new table, insert data # The table op.create_table('imapthread', sa.Column('g_thrid', sa.BigInteger(), nullable=True, index=True), sa.Column('id', sa.Integer()), sa.ForeignKeyConstraint(['id'], ['thread.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id')) # The ad-hoc table for insert table_ = table('imapthread', column('g_thrid', sa.BigInteger), column('id', sa.Integer)) if to_insert: op.bulk_insert(table_, to_insert) # Drop columns now op.drop_column('thread', 'g_thrid')
def test_insert_returning(self): table1 = table( "mytable", column("myid", Integer), column("name", String(128)), column("description", String(128)), ) i = insert(table1, values=dict(name="foo")).returning( table1.c.myid, table1.c.name ) self.assert_compile( i, "INSERT INTO mytable (name) OUTPUT " "inserted.myid, inserted.name VALUES " "(:name)", ) i = insert(table1, values=dict(name="foo")).returning(table1) self.assert_compile( i, "INSERT INTO mytable (name) OUTPUT " "inserted.myid, inserted.name, " "inserted.description VALUES (:name)", ) i = insert(table1, values=dict(name="foo")).returning( func.length(table1.c.name) ) self.assert_compile( i, "INSERT INTO mytable (name) OUTPUT " "LEN(inserted.name) AS length_1 VALUES " "(:name)", )
def downgrade_imapthread(): class ImapThread_(Base): __table__ = Base.metadata.tables['imapthread'] # Get data from table-to-be-dropped with session_scope() as db_session: results = db_session.query(ImapThread_.id, ImapThread_.g_thrid).all() to_insert = [dict(id=r[0], g_thrid=r[1]) for r in results] # Drop columns, add new columns + insert data op.drop_column('thread', 'type') op.add_column('thread', sa.Column('g_thrid', sa.BigInteger(), nullable=True, index=True)) table_ = table('thread', column('g_thrid', sa.BigInteger), column('id', sa.Integer)) for r in to_insert: op.execute( table_.update(). where(table_.c.id == r['id']). values({'g_thrid': r['g_thrid']}) ) # Drop table op.drop_table('imapthread')
def test_delete_extra_froms(self): t1 = table('t1', column('c1')) t2 = table('t2', column('c1')) q = sql.delete(t1).where(t1.c.c1 == t2.c.c1) self.assert_compile( q, "DELETE FROM t1 USING t1, t2 WHERE t1.c1 = t2.c1" )
import sqlalchemy as sa import lifeloopweb.db.models import lifeloopweb.db.utils from sqlalchemy.dialects import mysql from sqlalchemy.sql import table, column from sqlalchemy import String, Binary, Integer # revision identifiers, used by Alembic. revision = '314e77a901e4' down_revision = '8caf035d7fea' branch_labels = None depends_on = None age_ranges = table('age_ranges', column('id', Binary), column('priority', Integer), column('description', String)) def upgrade(): # ### commands auto generated by Alembic - please adjust! ### conn = op.get_bind() conn.execute(age_ranges.update().where( age_ranges.c.description == 'Any Age: 13+').values( description='Any Age')) op.bulk_insert(age_ranges, [{ 'id': lifeloopweb.db.utils.generate_guid().bytes, 'priority': 150, 'description': 'Teens' }, { 'id': lifeloopweb.db.utils.generate_guid().bytes,
def _insert_operation_form_field(): tb = table( 'operation_form_field', column('id', Integer), column('name', String), column('type', String), column('required', Integer), column('order', Integer), column('default', Text), column('suggested_widget', String), column('values_url', String), column('values', String), column('scope', String), column('form_id', Integer), ) columns = ('id', 'name', 'type', 'required', 'order', 'default', 'suggested_widget', 'values_url', 'values', 'scope', 'form_id') data = [ #Dataset (5171, 'dataset', 'TEXT', 1, 1, None, 'text', None, None, 'EXECUTION', 5171), #Train/Validation/test split (5172, 'train_validation_test_split', 'TEXT', 0, 2, '60%-20%-20%', 'text', None, None, 'EXECUTION', 5172), #K-fold cross validation (5173, 'use_k_fold_cross_validation', 'INTEGER', 0, 3, None, 'checkbox', None, None, 'EXECUTION', 5173), #Train/Validation/test split (5174, 'percent_of_train_data', 'INTEGER', 0, 4, None, 'integer', None, None, 'EXECUTION', 5174), #Shuffle data (5175, 'shuffle_data', 'INTEGER', 0, 5, None, 'checkbox', None, None, 'EXECUTION', 5175), (5176, 'load_dataset_in_memory', 'TEXT', 0, 6, 'one batch at a time', 'dropdown', None, json.dumps([ { "key": "one batch at a time", "value": "one batch at a time" }, { "key": "full dataset", "value": "full dataset" }, ]), 'EXECUTION', 5176), (5177, 'seed', 'INTEGER', 0, 7, 17, 'integer', None, None, 'EXECUTION', 5177), ] rows = [dict(list(zip(columns, row))) for row in data] op.bulk_insert(tb, rows)
def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.alter_column('course', 'isStandard', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('event', 'isOnCalendar', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('service', 'isCalendarBlocked', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('sponsor', 'isLogoReceived', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('sponsor', 'isSponsorThankedFB', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('sponsor', 'isWebsiteUpdated', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.add_column('sponsorlevel', sa.Column('treatment', sa.String(length=32), nullable=True)) op.alter_column('sponsorlevel', 'display', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('sponsorrace', 'display', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('sponsorrace', 'isRDCertified', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('sponsortag', 'isBuiltIn', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('tag', 'isBuiltIn', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) op.alter_column('user', 'active', existing_type=mysql.TINYINT(display_width=1), type_=sa.Boolean(), existing_nullable=True) # ### end Alembic commands ### sponsorlevel = table( 'sponsorlevel', column('treatment', sa.String()), ) op.execute( sponsorlevel.update().\ values({'treatment':op.inline_literal('summarize')}) )
def get_sqla_query( # sqla self, groupby, metrics, granularity, from_dttm, to_dttm, filter=None, # noqa is_timeseries=True, timeseries_limit=15, timeseries_limit_metric=None, row_limit=None, inner_from_dttm=None, inner_to_dttm=None, orderby=None, extras=None, columns=None, form_data=None, order_desc=True, prequeries=None, is_prequery=False, ): """Querying any sqla table from this common interface""" template_kwargs = { 'from_dttm': from_dttm, 'groupby': groupby, 'metrics': metrics, 'row_limit': row_limit, 'to_dttm': to_dttm, 'form_data': form_data, } template_processor = self.get_template_processor(**template_kwargs) db_engine_spec = self.database.db_engine_spec orderby = orderby or [] # For backward compatibility if granularity not in self.dttm_cols: granularity = self.main_dttm_col # Database spec supports join-free timeslot grouping time_groupby_inline = db_engine_spec.time_groupby_inline cols = {col.column_name: col for col in self.columns} metrics_dict = {m.metric_name: m for m in self.metrics} if not granularity and is_timeseries: raise Exception( _('Datetime column not provided as part table configuration ' 'and is required by this type of chart')) if not groupby and not metrics and not columns: raise Exception(_('Empty query?')) for m in metrics: if m not in metrics_dict: raise Exception(_("Metric '{}' is not valid".format(m))) metrics_exprs = [metrics_dict.get(m).sqla_col for m in metrics] if metrics_exprs: main_metric_expr = metrics_exprs[0] else: main_metric_expr = literal_column('COUNT(*)').label('ccount') select_exprs = [] groupby_exprs = [] if groupby: select_exprs = [] inner_select_exprs = [] inner_groupby_exprs = [] for s in groupby: col = cols[s] outer = col.sqla_col inner = col.sqla_col.label(col.column_name + '__') groupby_exprs.append(outer) select_exprs.append(outer) inner_groupby_exprs.append(inner) inner_select_exprs.append(inner) elif columns: for s in columns: select_exprs.append(cols[s].sqla_col) metrics_exprs = [] if granularity: dttm_col = cols[granularity] time_grain = extras.get('time_grain_sqla') time_filters = [] if is_timeseries: timestamp = dttm_col.get_timestamp_expression(time_grain) select_exprs += [timestamp] groupby_exprs += [timestamp] # Use main dttm column to support index with secondary dttm columns if db_engine_spec.time_secondary_columns and \ self.main_dttm_col in self.dttm_cols and \ self.main_dttm_col != dttm_col.column_name: time_filters.append(cols[self.main_dttm_col].get_time_filter( from_dttm, to_dttm)) time_filters.append(dttm_col.get_time_filter(from_dttm, to_dttm)) select_exprs += metrics_exprs qry = sa.select(select_exprs) tbl = self.get_from_clause(template_processor, db_engine_spec) if not columns: qry = qry.group_by(*groupby_exprs) where_clause_and = [] having_clause_and = [] for flt in filter: if not all([flt.get(s) for s in ['col', 'op', 'val']]): continue col = flt['col'] op = flt['op'] eq = flt['val'] col_obj = cols.get(col) if col_obj: if op in ('in', 'not in'): values = [] for v in eq: # For backwards compatibility and edge cases # where a column data type might have changed if isinstance(v, basestring): v = v.strip("'").strip('"') if col_obj.is_num: v = utils.string_to_num(v) # Removing empty strings and non numeric values # targeting numeric columns if v is not None: values.append(v) cond = col_obj.sqla_col.in_(values) if op == 'not in': cond = ~cond where_clause_and.append(cond) else: if col_obj.is_num: eq = utils.string_to_num(flt['val']) if op == '==': where_clause_and.append(col_obj.sqla_col == eq) elif op == '!=': where_clause_and.append(col_obj.sqla_col != eq) elif op == '>': where_clause_and.append(col_obj.sqla_col > eq) elif op == '<': where_clause_and.append(col_obj.sqla_col < eq) elif op == '>=': where_clause_and.append(col_obj.sqla_col >= eq) elif op == '<=': where_clause_and.append(col_obj.sqla_col <= eq) elif op == 'LIKE': where_clause_and.append(col_obj.sqla_col.like(eq)) if extras: where = extras.get('where') if where: where = template_processor.process_template(where) where_clause_and += [sa.text('({})'.format(where))] having = extras.get('having') if having: having = template_processor.process_template(having) having_clause_and += [sa.text('({})'.format(having))] if granularity: qry = qry.where(and_(*(time_filters + where_clause_and))) else: qry = qry.where(and_(*where_clause_and)) qry = qry.having(and_(*having_clause_and)) if not orderby and not columns: orderby = [(main_metric_expr, not order_desc)] for col, ascending in orderby: direction = asc if ascending else desc qry = qry.order_by(direction(col)) if row_limit: qry = qry.limit(row_limit) if is_timeseries and \ timeseries_limit and groupby and not time_groupby_inline: if self.database.db_engine_spec.inner_joins: # some sql dialects require for order by expressions # to also be in the select clause -- others, e.g. vertica, # require a unique inner alias inner_main_metric_expr = main_metric_expr.label('mme_inner__') inner_select_exprs += [inner_main_metric_expr] subq = select(inner_select_exprs) subq = subq.select_from(tbl) inner_time_filter = dttm_col.get_time_filter( inner_from_dttm or from_dttm, inner_to_dttm or to_dttm, ) subq = subq.where( and_(*(where_clause_and + [inner_time_filter]))) subq = subq.group_by(*inner_groupby_exprs) ob = inner_main_metric_expr if timeseries_limit_metric: timeseries_limit_metric = metrics_dict.get( timeseries_limit_metric) ob = timeseries_limit_metric.sqla_col direction = desc if order_desc else asc subq = subq.order_by(direction(ob)) subq = subq.limit(timeseries_limit) on_clause = [] for i, gb in enumerate(groupby): on_clause.append(groupby_exprs[i] == column(gb + '__')) tbl = tbl.join(subq.alias(), and_(*on_clause)) else: # run subquery to get top groups subquery_obj = { 'prequeries': prequeries, 'is_prequery': True, 'is_timeseries': False, 'row_limit': timeseries_limit, 'groupby': groupby, 'metrics': metrics, 'granularity': granularity, 'from_dttm': inner_from_dttm or from_dttm, 'to_dttm': inner_to_dttm or to_dttm, 'filter': filter, 'orderby': orderby, 'extras': extras, 'columns': columns, 'form_data': form_data, 'order_desc': True, } result = self.query(subquery_obj) dimensions = [c for c in result.df.columns if c not in metrics] top_groups = self._get_top_groups(result.df, dimensions) qry = qry.where(top_groups) return qry.select_from(tbl)
def upgrade(): # Create an ad-hoc table to use for the insert statement. item_table = table('item', column('model', String), column('brand_id', Integer), column('gender_id', Integer), column('small_image_url', String), column('medium_image_url', String), column('type', String) ) # 5|scarpa|EUR op.bulk_insert( item_table, [ {'model': 'quantic', 'brand_id': '5', 'gender_id': '1', 'type': 'rock'}, {'model': 'quantic', 'brand_id': '5', 'gender_id': '2', 'type': 'rock'}, {'model': 'rapid gtx', 'brand_id': '5', 'gender_id': '1', 'type': 'approach'}, {'model': 'rapid gtx', 'brand_id': '5', 'gender_id': '2', 'type': 'approach'}, {'model': 'rapid', 'brand_id': '5', 'gender_id': '1', 'type': 'approach'}, {'model': 'rapid', 'brand_id': '5', 'gender_id': '2', 'type': 'approach'}, {'model': 'kalipe gtx', 'brand_id': '5', 'gender_id': '1', 'type': 'approach'}, {'model': 'kalipe gtx', 'brand_id': '5', 'gender_id': '2', 'type': 'approach'}, {'model': 'kalipe', 'brand_id': '5', 'gender_id': '1', 'type': 'approach'}, {'model': 'kalipe', 'brand_id': '5', 'gender_id': '2', 'type': 'approach'}, {'model': 'crux air', 'brand_id': '5', 'gender_id': '1', 'type': 'approach'}, {'model': 'crux air', 'brand_id': '5', 'gender_id': '2', 'type': 'approach'}, ]) # 3|la sportiva|EUR op.bulk_insert( item_table, [ {'model': 'kubo', 'brand_id': '3', 'gender_id': '1', 'type': 'rock'}, {'model': 'kubo', 'brand_id': '3', 'gender_id': '2', 'type': 'rock'}, ]) # 12|butora|US op.bulk_insert( item_table, [ {'model': 'wing', 'brand_id': '12', 'gender_id': '3', 'type': 'approach'}, {'model': 'icarus', 'brand_id': '12', 'gender_id': '3', 'type': 'approach'}, ]) # 2|five ten|US op.bulk_insert( item_table, [ {'model': 'crawe', 'brand_id': '2', 'gender_id': '1', 'type': 'rock'}, {'model': 'crawe', 'brand_id': '2', 'gender_id': '2', 'type': 'rock'}, {'model': 'NIAD Lace', 'brand_id': '2', 'gender_id': '1', 'type': 'rock'}, {'model': 'NIAD Lace', 'brand_id': '2', 'gender_id': '2', 'type': 'rock'}, {'model': 'NIAD VCS', 'brand_id': '2', 'gender_id': '1', 'type': 'rock'}, {'model': 'NIAD VCS', 'brand_id': '2', 'gender_id': '2', 'type': 'rock'}, ]) # 27|black diamond|US op.bulk_insert( item_table, [ {'model': 'fuel', 'brand_id': '27', 'gender_id': '1', 'type': 'approach'}, {'model': 'fuel', 'brand_id': '27', 'gender_id': '2', 'type': 'approach'}, {'model': 'prime', 'brand_id': '27', 'gender_id': '1', 'type': 'approach'}, {'model': 'prime', 'brand_id': '27', 'gender_id': '2', 'type': 'approach'}, {'model': 'session', 'brand_id': '27', 'gender_id': '1', 'type': 'approach'}, {'model': 'session', 'brand_id': '27', 'gender_id': '2', 'type': 'approach'}, {'model': 'session suede', 'brand_id': '27', 'gender_id': '1', 'type': 'approach'}, {'model': 'session suede', 'brand_id': '27', 'gender_id': '2', 'type': 'approach'}, {'model': 'mission lt', 'brand_id': '27', 'gender_id': '1', 'type': 'approach'}, {'model': 'mission lt', 'brand_id': '27', 'gender_id': '2', 'type': 'approach'}, {'model': 'circuit', 'brand_id': '27', 'gender_id': '1', 'type': 'approach'}, {'model': 'circuit', 'brand_id': '27', 'gender_id': '2', 'type': 'approach'}, {'model': 'technician', 'brand_id': '27', 'gender_id': '1', 'type': 'approach'}, {'model': 'technician', 'brand_id': '27', 'gender_id': '2', 'type': 'approach'}, ])
def test_quoting_initial_chars(self): self.assert_compile(column("_somecol"), '"_somecol"') self.assert_compile(column("$somecol"), '"$somecol"')
def get_sqla_query( # sqla self, metrics, granularity, from_dttm, to_dttm, columns=None, groupby=None, filter=None, is_timeseries=True, timeseries_limit=15, timeseries_limit_metric=None, row_limit=None, inner_from_dttm=None, inner_to_dttm=None, orderby=None, extras=None, order_desc=True, ) -> SqlaQuery: """Querying any sqla table from this common interface""" template_kwargs = { "from_dttm": from_dttm, "groupby": groupby, "metrics": metrics, "row_limit": row_limit, "to_dttm": to_dttm, "filter": filter, "columns": {col.column_name: col for col in self.columns}, } is_sip_38 = is_feature_enabled("SIP_38_VIZ_REARCHITECTURE") template_kwargs.update(self.template_params_dict) extra_cache_keys: List[Any] = [] template_kwargs["extra_cache_keys"] = extra_cache_keys template_processor = self.get_template_processor(**template_kwargs) db_engine_spec = self.database.db_engine_spec prequeries: List[str] = [] orderby = orderby or [] # For backward compatibility if granularity not in self.dttm_cols: granularity = self.main_dttm_col # Database spec supports join-free timeslot grouping time_groupby_inline = db_engine_spec.time_groupby_inline cols: Dict[str, Column] = {col.column_name: col for col in self.columns} metrics_dict: Dict[str, SqlMetric] = { m.metric_name: m for m in self.metrics } if not granularity and is_timeseries: raise Exception( _("Datetime column not provided as part table configuration " "and is required by this type of chart")) if (not metrics and not columns and (is_sip_38 or (not is_sip_38 and not groupby))): raise Exception(_("Empty query?")) metrics_exprs: List[ColumnElement] = [] for m in metrics: if utils.is_adhoc_metric(m): metrics_exprs.append(self.adhoc_metric_to_sqla(m, cols)) elif m in metrics_dict: metrics_exprs.append(metrics_dict[m].get_sqla_col()) else: raise Exception( _("Metric '%(metric)s' does not exist", metric=m)) if metrics_exprs: main_metric_expr = metrics_exprs[0] else: main_metric_expr, label = literal_column("COUNT(*)"), "ccount" main_metric_expr = self.make_sqla_column_compatible( main_metric_expr, label) select_exprs: List[Column] = [] groupby_exprs_sans_timestamp: OrderedDict = OrderedDict() if (is_sip_38 and metrics and columns) or (not is_sip_38 and groupby): # dedup columns while preserving order groupby = list(dict.fromkeys(columns if is_sip_38 else groupby)) select_exprs = [] for s in groupby: if s in cols: outer = cols[s].get_sqla_col() else: outer = literal_column(f"({s})") outer = self.make_sqla_column_compatible(outer, s) groupby_exprs_sans_timestamp[outer.name] = outer select_exprs.append(outer) elif columns: for s in columns: select_exprs.append( cols[s].get_sqla_col() if s in cols else self. make_sqla_column_compatible(literal_column(s))) metrics_exprs = [] time_range_endpoints = extras.get("time_range_endpoints") groupby_exprs_with_timestamp = OrderedDict( groupby_exprs_sans_timestamp.items()) if granularity: dttm_col = cols[granularity] time_grain = extras.get("time_grain_sqla") time_filters = [] if is_timeseries: timestamp = dttm_col.get_timestamp_expression(time_grain) select_exprs += [timestamp] groupby_exprs_with_timestamp[timestamp.name] = timestamp # Use main dttm column to support index with secondary dttm columns. if (db_engine_spec.time_secondary_columns and self.main_dttm_col in self.dttm_cols and self.main_dttm_col != dttm_col.column_name): time_filters.append(cols[self.main_dttm_col].get_time_filter( from_dttm, to_dttm, time_range_endpoints)) time_filters.append( dttm_col.get_time_filter(from_dttm, to_dttm, time_range_endpoints)) select_exprs += metrics_exprs labels_expected = [c._df_label_expected for c in select_exprs] select_exprs = db_engine_spec.make_select_compatible( groupby_exprs_with_timestamp.values(), select_exprs) qry = sa.select(select_exprs) tbl = self.get_from_clause(template_processor) if (is_sip_38 and metrics) or (not is_sip_38 and not columns): qry = qry.group_by(*groupby_exprs_with_timestamp.values()) where_clause_and = [] having_clause_and: List = [] for flt in filter: if not all([flt.get(s) for s in ["col", "op"]]): continue col = flt["col"] op = flt["op"].upper() col_obj = cols.get(col) if col_obj: is_list_target = op in ( utils.FilterOperator.IN.value, utils.FilterOperator.NOT_IN.value, ) eq = self.filter_values_handler( values=flt.get("val"), target_column_is_numeric=col_obj.is_numeric, is_list_target=is_list_target, ) if op in ( utils.FilterOperator.IN.value, utils.FilterOperator.NOT_IN.value, ): cond = col_obj.get_sqla_col().in_(eq) if isinstance(eq, str) and NULL_STRING in eq: cond = or_(cond, col_obj.get_sqla_col() is None) if op == utils.FilterOperator.NOT_IN.value: cond = ~cond where_clause_and.append(cond) else: if col_obj.is_numeric: eq = utils.cast_to_num(flt["val"]) if op == utils.FilterOperator.EQUALS.value: where_clause_and.append(col_obj.get_sqla_col() == eq) elif op == utils.FilterOperator.NOT_EQUALS.value: where_clause_and.append(col_obj.get_sqla_col() != eq) elif op == utils.FilterOperator.GREATER_THAN.value: where_clause_and.append(col_obj.get_sqla_col() > eq) elif op == utils.FilterOperator.LESS_THAN.value: where_clause_and.append(col_obj.get_sqla_col() < eq) elif op == utils.FilterOperator.GREATER_THAN_OR_EQUALS.value: where_clause_and.append(col_obj.get_sqla_col() >= eq) elif op == utils.FilterOperator.LESS_THAN_OR_EQUALS.value: where_clause_and.append(col_obj.get_sqla_col() <= eq) elif op == utils.FilterOperator.LIKE.value: where_clause_and.append( col_obj.get_sqla_col().like(eq)) elif op == utils.FilterOperator.IS_NULL.value: where_clause_and.append(col_obj.get_sqla_col() == None) elif op == utils.FilterOperator.IS_NOT_NULL.value: where_clause_and.append(col_obj.get_sqla_col() != None) else: raise Exception( _("Invalid filter operation type: %(op)s", op=op)) if config["ENABLE_ROW_LEVEL_SECURITY"]: where_clause_and += self._get_sqla_row_level_filters( template_processor) if extras: where = extras.get("where") if where: where = template_processor.process_template(where) where_clause_and += [sa.text("({})".format(where))] having = extras.get("having") if having: having = template_processor.process_template(having) having_clause_and += [sa.text("({})".format(having))] if granularity: qry = qry.where(and_(*(time_filters + where_clause_and))) else: qry = qry.where(and_(*where_clause_and)) qry = qry.having(and_(*having_clause_and)) if not orderby and ((is_sip_38 and metrics) or (not is_sip_38 and not columns)): orderby = [(main_metric_expr, not order_desc)] # To ensure correct handling of the ORDER BY labeling we need to reference the # metric instance if defined in the SELECT clause. metrics_exprs_by_label = {m._label: m for m in metrics_exprs} for col, ascending in orderby: direction = asc if ascending else desc if utils.is_adhoc_metric(col): col = self.adhoc_metric_to_sqla(col, cols) elif col in cols: col = cols[col].get_sqla_col() if isinstance(col, Label) and col._label in metrics_exprs_by_label: col = metrics_exprs_by_label[col._label] qry = qry.order_by(direction(col)) if row_limit: qry = qry.limit(row_limit) if (is_timeseries and timeseries_limit and not time_groupby_inline and ((is_sip_38 and columns) or (not is_sip_38 and groupby))): if self.database.db_engine_spec.allows_joins: # some sql dialects require for order by expressions # to also be in the select clause -- others, e.g. vertica, # require a unique inner alias inner_main_metric_expr = self.make_sqla_column_compatible( main_metric_expr, "mme_inner__") inner_groupby_exprs = [] inner_select_exprs = [] for gby_name, gby_obj in groupby_exprs_sans_timestamp.items(): inner = self.make_sqla_column_compatible( gby_obj, gby_name + "__") inner_groupby_exprs.append(inner) inner_select_exprs.append(inner) inner_select_exprs += [inner_main_metric_expr] subq = select(inner_select_exprs).select_from(tbl) inner_time_filter = dttm_col.get_time_filter( inner_from_dttm or from_dttm, inner_to_dttm or to_dttm, time_range_endpoints, ) subq = subq.where( and_(*(where_clause_and + [inner_time_filter]))) subq = subq.group_by(*inner_groupby_exprs) ob = inner_main_metric_expr if timeseries_limit_metric: ob = self._get_timeseries_orderby(timeseries_limit_metric, metrics_dict, cols) direction = desc if order_desc else asc subq = subq.order_by(direction(ob)) subq = subq.limit(timeseries_limit) on_clause = [] for gby_name, gby_obj in groupby_exprs_sans_timestamp.items(): # in this case the column name, not the alias, needs to be # conditionally mutated, as it refers to the column alias in # the inner query col_name = db_engine_spec.make_label_compatible(gby_name + "__") on_clause.append(gby_obj == column(col_name)) tbl = tbl.join(subq.alias(), and_(*on_clause)) else: if timeseries_limit_metric: orderby = [( self._get_timeseries_orderby(timeseries_limit_metric, metrics_dict, cols), False, )] # run prequery to get top groups prequery_obj = { "is_timeseries": False, "row_limit": timeseries_limit, "metrics": metrics, "granularity": granularity, "from_dttm": inner_from_dttm or from_dttm, "to_dttm": inner_to_dttm or to_dttm, "filter": filter, "orderby": orderby, "extras": extras, "columns": columns, "order_desc": True, } if not is_sip_38: prequery_obj["groupby"] = groupby result = self.query(prequery_obj) prequeries.append(result.query) dimensions = [ c for c in result.df.columns if c not in metrics and c in groupby_exprs_sans_timestamp ] top_groups = self._get_top_groups( result.df, dimensions, groupby_exprs_sans_timestamp) qry = qry.where(top_groups) return SqlaQuery( extra_cache_keys=extra_cache_keys, labels_expected=labels_expected, sqla_query=qry.select_from(tbl), prequeries=prequeries, )
def load_world_bank_health_n_pop(only_metadata=False, force=False): """Loads the world bank health dataset, slices and a dashboard""" tbl_name = "wb_health_population" database = utils.get_example_database() table_exists = database.has_table_by_name(tbl_name) if not only_metadata and (not table_exists or force): data = get_example_data("countries.json.gz") pdf = pd.read_json(data) pdf.columns = [col.replace(".", "_") for col in pdf.columns] pdf.year = pd.to_datetime(pdf.year) pdf.to_sql( tbl_name, database.get_sqla_engine(), if_exists="replace", chunksize=50, dtype={ "year": DateTime(), "country_code": String(3), "country_name": String(255), "region": String(255), }, index=False, ) print("Creating table [wb_health_population] reference") tbl = db.session.query(TBL).filter_by(table_name=tbl_name).first() if not tbl: tbl = TBL(table_name=tbl_name) tbl.description = utils.readfile( os.path.join(EXAMPLES_FOLDER, "countries.md")) tbl.main_dttm_col = "year" tbl.database = database tbl.filter_select_enabled = True metrics = [ "sum__SP_POP_TOTL", "sum__SH_DYN_AIDS", "sum__SH_DYN_AIDS", "sum__SP_RUR_TOTL_ZS", "sum__SP_DYN_LE00_IN", "sum__SP_RUR_TOTL", ] for m in metrics: if not any(col.metric_name == m for col in tbl.metrics): aggr_func = m[:3] col = str(column(m[5:]).compile(db.engine)) tbl.metrics.append( SqlMetric(metric_name=m, expression=f"{aggr_func}({col})")) db.session.merge(tbl) db.session.commit() tbl.fetch_metadata() defaults = { "compare_lag": "10", "compare_suffix": "o10Y", "limit": "25", "granularity_sqla": "year", "groupby": [], "metric": "sum__SP_POP_TOTL", "metrics": ["sum__SP_POP_TOTL"], "row_limit": config["ROW_LIMIT"], "since": "2014-01-01", "until": "2014-01-02", "time_range": "2014-01-01 : 2014-01-02", "where": "", "markup_type": "markdown", "country_fieldtype": "cca3", "secondary_metric": { "aggregate": "SUM", "column": { "column_name": "SP_RUR_TOTL", "optionName": "_col_SP_RUR_TOTL", "type": "DOUBLE", }, "expressionType": "SIMPLE", "hasCustomLabel": True, "label": "Rural Population", }, "entity": "country_code", "show_bubbles": True, } print("Creating slices") slices = [ Slice( slice_name="Region Filter", viz_type="filter_box", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, viz_type="filter_box", date_filter=False, filter_configs=[ { "asc": False, "clearable": True, "column": "region", "key": "2s98dfu", "metric": "sum__SP_POP_TOTL", "multiple": True, }, { "asc": False, "clearable": True, "key": "li3j2lk", "column": "country_name", "metric": "sum__SP_POP_TOTL", "multiple": True, }, ], ), ), Slice( slice_name="World's Population", viz_type="big_number", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, since="2000", viz_type="big_number", compare_lag="10", metric="sum__SP_POP_TOTL", compare_suffix="over 10Y", ), ), Slice( slice_name="Most Populated Countries", viz_type="table", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, viz_type="table", metrics=["sum__SP_POP_TOTL"], groupby=["country_name"], ), ), Slice( slice_name="Growth Rate", viz_type="line", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, viz_type="line", since="1960-01-01", metrics=["sum__SP_POP_TOTL"], num_period_compare="10", groupby=["country_name"], ), ), Slice( slice_name="% Rural", viz_type="world_map", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, viz_type="world_map", metric="sum__SP_RUR_TOTL_ZS", num_period_compare="10", ), ), Slice( slice_name="Life Expectancy VS Rural %", viz_type="bubble", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, viz_type="bubble", since="2011-01-01", until="2011-01-02", series="region", limit=0, entity="country_name", x="sum__SP_RUR_TOTL_ZS", y="sum__SP_DYN_LE00_IN", size="sum__SP_POP_TOTL", max_bubble_size="50", filters=[{ "col": "country_code", "val": [ "TCA", "MNP", "DMA", "MHL", "MCO", "SXM", "CYM", "TUV", "IMY", "KNA", "ASM", "ADO", "AMA", "PLW", ], "op": "not in", }], ), ), Slice( slice_name="Rural Breakdown", viz_type="sunburst", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, viz_type="sunburst", groupby=["region", "country_name"], since="2011-01-01", until="2011-01-01", ), ), Slice( slice_name="World's Pop Growth", viz_type="area", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, since="1960-01-01", until="now", viz_type="area", groupby=["region"], ), ), Slice( slice_name="Box plot", viz_type="box_plot", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, since="1960-01-01", until="now", whisker_options="Min/max (no outliers)", x_ticks_layout="staggered", viz_type="box_plot", groupby=["region"], ), ), Slice( slice_name="Treemap", viz_type="treemap", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, since="1960-01-01", until="now", viz_type="treemap", metrics=["sum__SP_POP_TOTL"], groupby=["region", "country_code"], ), ), Slice( slice_name="Parallel Coordinates", viz_type="para", datasource_type="table", datasource_id=tbl.id, params=get_slice_json( defaults, since="2011-01-01", until="2011-01-01", viz_type="para", limit=100, metrics=[ "sum__SP_POP_TOTL", "sum__SP_RUR_TOTL_ZS", "sum__SH_DYN_AIDS" ], secondary_metric="sum__SP_POP_TOTL", series="country_name", ), ), ] misc_dash_slices.add(slices[-1].slice_name) for slc in slices: merge_slice(slc) print("Creating a World's Health Bank dashboard") dash_name = "World Bank's Data" slug = "world_health" dash = db.session.query(Dash).filter_by(slug=slug).first() if not dash: dash = Dash() dash.published = True js = textwrap.dedent("""\ { "CHART-36bfc934": { "children": [], "id": "CHART-36bfc934", "meta": { "chartId": 40, "height": 25, "sliceName": "Region Filter", "width": 2 }, "type": "CHART" }, "CHART-37982887": { "children": [], "id": "CHART-37982887", "meta": { "chartId": 41, "height": 25, "sliceName": "World's Population", "width": 2 }, "type": "CHART" }, "CHART-17e0f8d8": { "children": [], "id": "CHART-17e0f8d8", "meta": { "chartId": 42, "height": 92, "sliceName": "Most Populated Countries", "width": 3 }, "type": "CHART" }, "CHART-2ee52f30": { "children": [], "id": "CHART-2ee52f30", "meta": { "chartId": 43, "height": 38, "sliceName": "Growth Rate", "width": 6 }, "type": "CHART" }, "CHART-2d5b6871": { "children": [], "id": "CHART-2d5b6871", "meta": { "chartId": 44, "height": 52, "sliceName": "% Rural", "width": 7 }, "type": "CHART" }, "CHART-0fd0d252": { "children": [], "id": "CHART-0fd0d252", "meta": { "chartId": 45, "height": 50, "sliceName": "Life Expectancy VS Rural %", "width": 8 }, "type": "CHART" }, "CHART-97f4cb48": { "children": [], "id": "CHART-97f4cb48", "meta": { "chartId": 46, "height": 38, "sliceName": "Rural Breakdown", "width": 3 }, "type": "CHART" }, "CHART-b5e05d6f": { "children": [], "id": "CHART-b5e05d6f", "meta": { "chartId": 47, "height": 50, "sliceName": "World's Pop Growth", "width": 4 }, "type": "CHART" }, "CHART-e76e9f5f": { "children": [], "id": "CHART-e76e9f5f", "meta": { "chartId": 48, "height": 50, "sliceName": "Box plot", "width": 4 }, "type": "CHART" }, "CHART-a4808bba": { "children": [], "id": "CHART-a4808bba", "meta": { "chartId": 49, "height": 50, "sliceName": "Treemap", "width": 8 }, "type": "CHART" }, "COLUMN-071bbbad": { "children": [ "ROW-1e064e3c", "ROW-afdefba9" ], "id": "COLUMN-071bbbad", "meta": { "background": "BACKGROUND_TRANSPARENT", "width": 9 }, "type": "COLUMN" }, "COLUMN-fe3914b8": { "children": [ "CHART-36bfc934", "CHART-37982887" ], "id": "COLUMN-fe3914b8", "meta": { "background": "BACKGROUND_TRANSPARENT", "width": 2 }, "type": "COLUMN" }, "GRID_ID": { "children": [ "ROW-46632bc2", "ROW-3fa26c5d", "ROW-812b3f13" ], "id": "GRID_ID", "type": "GRID" }, "HEADER_ID": { "id": "HEADER_ID", "meta": { "text": "World's Bank Data" }, "type": "HEADER" }, "ROOT_ID": { "children": [ "GRID_ID" ], "id": "ROOT_ID", "type": "ROOT" }, "ROW-1e064e3c": { "children": [ "COLUMN-fe3914b8", "CHART-2d5b6871" ], "id": "ROW-1e064e3c", "meta": { "background": "BACKGROUND_TRANSPARENT" }, "type": "ROW" }, "ROW-3fa26c5d": { "children": [ "CHART-b5e05d6f", "CHART-0fd0d252" ], "id": "ROW-3fa26c5d", "meta": { "background": "BACKGROUND_TRANSPARENT" }, "type": "ROW" }, "ROW-46632bc2": { "children": [ "COLUMN-071bbbad", "CHART-17e0f8d8" ], "id": "ROW-46632bc2", "meta": { "background": "BACKGROUND_TRANSPARENT" }, "type": "ROW" }, "ROW-812b3f13": { "children": [ "CHART-a4808bba", "CHART-e76e9f5f" ], "id": "ROW-812b3f13", "meta": { "background": "BACKGROUND_TRANSPARENT" }, "type": "ROW" }, "ROW-afdefba9": { "children": [ "CHART-2ee52f30", "CHART-97f4cb48" ], "id": "ROW-afdefba9", "meta": { "background": "BACKGROUND_TRANSPARENT" }, "type": "ROW" }, "DASHBOARD_VERSION_KEY": "v2" } """) pos = json.loads(js) update_slice_ids(pos, slices) dash.dashboard_title = dash_name dash.position_json = json.dumps(pos, indent=4) dash.slug = slug dash.slices = slices[:-1] db.session.merge(dash) db.session.commit()
def mute_cond(row: Dict[str, Any]) -> Selectable: recipient_id = row['recipient_id'] topic_name = row['topic_name'] stream_cond = column("recipient_id") == recipient_id topic_cond = topic_match_sa(topic_name) return and_(stream_cond, topic_cond)
Create Date: 2014-03-21 18:27:16.802081 """ # revision identifiers, used by Alembic. revision = '476e9e882141' down_revision = '387cf5a9a3ad' from alembic import op from datetime import datetime from sqlalchemy.sql import table, column, select, insert, and_, or_ import sqlalchemy as sa context_implications_table = table( 'context_implications', column('id', sa.Integer), column('context_id', sa.Integer), column('source_context_id', sa.Integer), column('context_scope', sa.String), column('source_context_scope', sa.String), column('updated_at', sa.DateTime), column('modified_by_id', sa.Integer), ) contexts_table = table( 'contexts', column('id', sa.Integer), column('related_object_id', sa.Integer), column('related_object_type', sa.String), )
def upgrade(): # ### commands auto generated by Alembic - please adjust! ### contacttype_table = table( 'ContactTypes', column('name', String), column('description', String), column('isactive', Boolean), ) op.execute( 'Truncate table public."ContactTypes" RESTART IDENTITY CASCADE;commit;' ) op.bulk_insert(contacttype_table, [ { 'name': 'Email', 'description': 'Email', 'isactive': True }, { 'name': 'Street Address', 'description': 'Street Address', 'isactive': True }, { 'name': 'Home Phone', 'description': 'Home Phone', 'isactive': True }, { 'name': 'Work Phone', 'description': 'Work Phone', 'isactive': True }, { 'name': 'Work Phone 2', 'description': 'second work phone 2', 'isactive': True }, { 'name': 'Mobile Phone', 'description': 'Mobile Phone', 'isactive': True }, { 'name': 'Other', 'description': 'Other Contact information, if any', 'isactive': True }, ]) op.create_table( 'FOIRequestContactInformation', sa.Column('foirequestcontactid', sa.Integer(), autoincrement=True, nullable=False), sa.Column('contactinformation', sa.String(length=500), nullable=False), sa.Column('dataformat', sa.String(length=40), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=True), sa.Column('updated_at', sa.DateTime(), nullable=True), sa.Column('createdby', sa.String(length=120), nullable=True), sa.Column('updatedby', sa.String(length=120), nullable=True), sa.Column('contacttypeid', sa.Integer(), nullable=True), sa.Column('foirequest_id', sa.Integer(), nullable=True), sa.Column('foirequestversion_id', sa.Integer(), nullable=True), sa.ForeignKeyConstraint( ['contacttypeid'], ['ContactTypes.contacttypeid'], ), sa.ForeignKeyConstraint( ['foirequest_id', 'foirequestversion_id'], ['FOIRequests.foirequestid', 'FOIRequests.version'], ), sa.PrimaryKeyConstraint('foirequestcontactid'))
def get_sqla_query( # sqla self, groupby, metrics, granularity, from_dttm, to_dttm, filter=None, # noqa is_timeseries=True, timeseries_limit=15, timeseries_limit_metric=None, row_limit=None, inner_from_dttm=None, inner_to_dttm=None, orderby=None, extras=None, columns=None, order_desc=True, ): """Querying any sqla table from this common interface""" template_kwargs = { "from_dttm": from_dttm, "groupby": groupby, "metrics": metrics, "row_limit": row_limit, "to_dttm": to_dttm, "filter": filter, "columns": {col.column_name: col for col in self.columns}, } template_kwargs.update(self.template_params_dict) extra_cache_keys: List[Any] = [] template_kwargs["extra_cache_keys"] = extra_cache_keys template_processor = self.get_template_processor(**template_kwargs) db_engine_spec = self.database.db_engine_spec prequeries: List[str] = [] orderby = orderby or [] # For backward compatibility if granularity not in self.dttm_cols: granularity = self.main_dttm_col # Database spec supports join-free timeslot grouping time_groupby_inline = db_engine_spec.time_groupby_inline cols = {col.column_name: col for col in self.columns} metrics_dict = {m.metric_name: m for m in self.metrics} if not granularity and is_timeseries: raise Exception( _("Datetime column not provided as part table configuration " "and is required by this type of chart")) if not groupby and not metrics and not columns: raise Exception(_("Empty query?")) metrics_exprs = [] for m in metrics: if utils.is_adhoc_metric(m): metrics_exprs.append(self.adhoc_metric_to_sqla(m, cols)) elif m in metrics_dict: metrics_exprs.append(metrics_dict.get(m).get_sqla_col()) else: raise Exception( _("Metric '%(metric)s' does not exist", metric=m)) if metrics_exprs: main_metric_expr = metrics_exprs[0] else: main_metric_expr, label = literal_column("COUNT(*)"), "ccount" main_metric_expr = self.make_sqla_column_compatible( main_metric_expr, label) select_exprs = [] groupby_exprs_sans_timestamp = OrderedDict() if groupby: select_exprs = [] for s in groupby: if s in cols: outer = cols[s].get_sqla_col() else: outer = literal_column(f"({s})") outer = self.make_sqla_column_compatible(outer, s) groupby_exprs_sans_timestamp[outer.name] = outer select_exprs.append(outer) elif columns: for s in columns: select_exprs.append( cols[s].get_sqla_col() if s in cols else self. make_sqla_column_compatible(literal_column(s))) metrics_exprs = [] groupby_exprs_with_timestamp = OrderedDict( groupby_exprs_sans_timestamp.items()) if granularity: dttm_col = cols[granularity] time_grain = extras.get("time_grain_sqla") time_filters = [] if is_timeseries: timestamp = dttm_col.get_timestamp_expression(time_grain) select_exprs += [timestamp] groupby_exprs_with_timestamp[timestamp.name] = timestamp # Use main dttm column to support index with secondary dttm columns if (db_engine_spec.time_secondary_columns and self.main_dttm_col in self.dttm_cols and self.main_dttm_col != dttm_col.column_name): time_filters.append(cols[self.main_dttm_col].get_time_filter( from_dttm, to_dttm)) time_filters.append(dttm_col.get_time_filter(from_dttm, to_dttm)) select_exprs += metrics_exprs labels_expected = [c._df_label_expected for c in select_exprs] select_exprs = db_engine_spec.make_select_compatible( groupby_exprs_with_timestamp.values(), select_exprs) qry = sa.select(select_exprs) tbl = self.get_from_clause(template_processor) if not columns: qry = qry.group_by(*groupby_exprs_with_timestamp.values()) where_clause_and = [] having_clause_and = [] for flt in filter: if not all([flt.get(s) for s in ["col", "op"]]): continue col = flt["col"] op = flt["op"] col_obj = cols.get(col) if col_obj: is_list_target = op in ("in", "not in") eq = self.filter_values_handler( flt.get("val"), target_column_is_numeric=col_obj.is_num, is_list_target=is_list_target, ) if op in ("in", "not in"): cond = col_obj.get_sqla_col().in_(eq) if "<NULL>" in eq: cond = or_(cond, col_obj.get_sqla_col() == None) # noqa if op == "not in": cond = ~cond where_clause_and.append(cond) else: if col_obj.is_num: eq = utils.string_to_num(flt["val"]) if op == "==": where_clause_and.append(col_obj.get_sqla_col() == eq) elif op == "!=": where_clause_and.append(col_obj.get_sqla_col() != eq) elif op == ">": where_clause_and.append(col_obj.get_sqla_col() > eq) elif op == "<": where_clause_and.append(col_obj.get_sqla_col() < eq) elif op == ">=": where_clause_and.append(col_obj.get_sqla_col() >= eq) elif op == "<=": where_clause_and.append(col_obj.get_sqla_col() <= eq) elif op == "LIKE": where_clause_and.append( col_obj.get_sqla_col().like(eq)) elif op == "IS NULL": where_clause_and.append( col_obj.get_sqla_col() == None) # noqa elif op == "IS NOT NULL": where_clause_and.append( col_obj.get_sqla_col() != None) # noqa if extras: where = extras.get("where") if where: where = template_processor.process_template(where) where_clause_and += [sa.text("({})".format(where))] having = extras.get("having") if having: having = template_processor.process_template(having) having_clause_and += [sa.text("({})".format(having))] if granularity: qry = qry.where(and_(*(time_filters + where_clause_and))) else: qry = qry.where(and_(*where_clause_and)) qry = qry.having(and_(*having_clause_and)) if not orderby and not columns: orderby = [(main_metric_expr, not order_desc)] for col, ascending in orderby: direction = asc if ascending else desc if utils.is_adhoc_metric(col): col = self.adhoc_metric_to_sqla(col, cols) elif col in cols: col = cols[col].get_sqla_col() qry = qry.order_by(direction(col)) if row_limit: qry = qry.limit(row_limit) if is_timeseries and timeseries_limit and groupby and not time_groupby_inline: if self.database.db_engine_spec.allows_joins: # some sql dialects require for order by expressions # to also be in the select clause -- others, e.g. vertica, # require a unique inner alias inner_main_metric_expr = self.make_sqla_column_compatible( main_metric_expr, "mme_inner__") inner_groupby_exprs = [] inner_select_exprs = [] for gby_name, gby_obj in groupby_exprs_sans_timestamp.items(): inner = self.make_sqla_column_compatible( gby_obj, gby_name + "__") inner_groupby_exprs.append(inner) inner_select_exprs.append(inner) inner_select_exprs += [inner_main_metric_expr] subq = select(inner_select_exprs).select_from(tbl) inner_time_filter = dttm_col.get_time_filter( inner_from_dttm or from_dttm, inner_to_dttm or to_dttm) subq = subq.where( and_(*(where_clause_and + [inner_time_filter]))) subq = subq.group_by(*inner_groupby_exprs) ob = inner_main_metric_expr if timeseries_limit_metric: ob = self._get_timeseries_orderby(timeseries_limit_metric, metrics_dict, cols) direction = desc if order_desc else asc subq = subq.order_by(direction(ob)) subq = subq.limit(timeseries_limit) on_clause = [] for gby_name, gby_obj in groupby_exprs_sans_timestamp.items(): # in this case the column name, not the alias, needs to be # conditionally mutated, as it refers to the column alias in # the inner query col_name = db_engine_spec.make_label_compatible(gby_name + "__") on_clause.append(gby_obj == column(col_name)) tbl = tbl.join(subq.alias(), and_(*on_clause)) else: if timeseries_limit_metric: orderby = [( self._get_timeseries_orderby(timeseries_limit_metric, metrics_dict, cols), False, )] # run prequery to get top groups prequery_obj = { "is_timeseries": False, "row_limit": timeseries_limit, "groupby": groupby, "metrics": metrics, "granularity": granularity, "from_dttm": inner_from_dttm or from_dttm, "to_dttm": inner_to_dttm or to_dttm, "filter": filter, "orderby": orderby, "extras": extras, "columns": columns, "order_desc": True, } result = self.query(prequery_obj) prequeries.append(result.query) dimensions = [ c for c in result.df.columns if c not in metrics and c in groupby_exprs_sans_timestamp ] top_groups = self._get_top_groups( result.df, dimensions, groupby_exprs_sans_timestamp) qry = qry.where(top_groups) return SqlaQuery( extra_cache_keys=extra_cache_keys, labels_expected=labels_expected, sqla_query=qry.select_from(tbl), prequeries=prequeries, )
def fetch_metadata(self): """Fetches the metadata for the table and merges it in""" try: table = self.get_sqla_table_object() except Exception: raise Exception( "Table doesn't seem to exist in the specified database, " "couldn't fetch column information") TC = TableColumn # noqa shortcut to class M = SqlMetric # noqa metrics = [] any_date_col = None for col in table.columns: try: datatype = "{}".format(col.type).upper() except Exception as e: datatype = "UNKNOWN" logging.error( "Unrecognized data type in {}.{}".format(table, col.name)) logging.exception(e) dbcol = ( db.session .query(TC) .filter(TC.table == self) .filter(TC.column_name == col.name) .first() ) db.session.flush() if not dbcol: dbcol = TableColumn(column_name=col.name, type=datatype) dbcol.groupby = dbcol.is_string dbcol.filterable = dbcol.is_string dbcol.sum = dbcol.is_num dbcol.avg = dbcol.is_num dbcol.is_dttm = dbcol.is_time db.session.merge(self) self.columns.append(dbcol) if not any_date_col and dbcol.is_time: any_date_col = col.name quoted = "{}".format( column(dbcol.column_name).compile(dialect=db.engine.dialect)) if dbcol.sum: metrics.append(M( metric_name='sum__' + dbcol.column_name, verbose_name='sum__' + dbcol.column_name, metric_type='sum', expression="SUM({})".format(quoted) )) if dbcol.avg: metrics.append(M( metric_name='avg__' + dbcol.column_name, verbose_name='avg__' + dbcol.column_name, metric_type='avg', expression="AVG({})".format(quoted) )) if dbcol.max: metrics.append(M( metric_name='max__' + dbcol.column_name, verbose_name='max__' + dbcol.column_name, metric_type='max', expression="MAX({})".format(quoted) )) if dbcol.min: metrics.append(M( metric_name='min__' + dbcol.column_name, verbose_name='min__' + dbcol.column_name, metric_type='min', expression="MIN({})".format(quoted) )) if dbcol.count_distinct: metrics.append(M( metric_name='count_distinct__' + dbcol.column_name, verbose_name='count_distinct__' + dbcol.column_name, metric_type='count_distinct', expression="COUNT(DISTINCT {})".format(quoted) )) dbcol.type = datatype db.session.merge(self) db.session.commit() metrics.append(M( metric_name='count', verbose_name='COUNT(*)', metric_type='count', expression="COUNT(*)" )) for metric in metrics: m = ( db.session.query(M) .filter(M.metric_name == metric.metric_name) .filter(M.table_id == self.id) .first() ) metric.table_id = self.id if not m: db.session.add(metric) db.session.commit() if not self.main_dttm_col: self.main_dttm_col = any_date_col
"""Make cti directory fields format strings Revision ID: 2bb6543883a6 Revises: 444b39e9aa32 """ # revision identifiers, used by Alembic. revision = '2bb6543883a6' down_revision = '444b39e9aa32' from alembic import op from sqlalchemy import sql, and_ fields_table = sql.table('ctidirectoryfields', sql.column('dir_id'), sql.column('fieldname'), sql.column('value')) def upgrade(): conn = op.get_bind() rows = conn.execute( sql.select([ fields_table.c.dir_id, fields_table.c.fieldname, fields_table.c.value ]).where(~fields_table.c.value.like('{%}'))) to_upgrade = {(row.dir_id, row.fieldname): row.value for row in rows} for (dir_id, fieldname), value in to_upgrade.items(): new_value = '{%s}' % '} {'.join(value.split(' ')) op.execute(fields_table.update().where( and_(fields_table.c.dir_id == dir_id, fields_table.c.fieldname == fieldname)).values(
def _table(name): return table(name, column('col1'), column('col2'), column('col3'))
def upgrade(): # We repeat the data migration from migration c92f30c03b62, so we're sure # there are no NULL values left in the source column. connection = op.get_bind() # Inline table definition we can use in this migration. references = sql.table( 'references', sql.column('id', sa.Integer()), sql.column('accession', sa.String(20)), sql.column( 'source', sa.Enum('ncbi', 'ncbi_slice', 'lrg', 'url', 'upload', name='reference_source')), sql.column('source_data', sa.String(255)), sql.column('geninfo_identifier', sa.String(13)), sql.column('slice_accession', sa.String(20)), sql.column('slice_start', sa.Integer()), sql.column('slice_stop', sa.Integer()), sql.column('slice_orientation', sa.Enum('forward', 'reverse', name='slice_orientation')), sql.column('download_url', sa.String(255))) # Get all rows. result = connection.execute(references.select().with_only_columns([ references.c.id, references.c.accession, references.c.source, references.c.source_data, references.c.geninfo_identifier, references.c.slice_accession, references.c.slice_start, references.c.slice_stop, references.c.slice_orientation, references.c.download_url ])) # Generate parameter values for the UPDATE query below. def update_params(r): data = None if r.source: source = r.source data = r.source_data if r.accession.startswith('LRG_'): source = 'lrg' elif r.slice_accession: source = 'ncbi_slice' data = '{}:{}:{}:{}'.format(r.slice_accession, r.slice_start, r.slice_stop, r.slice_orientation) elif r.download_url: source = 'url' data = r.download_url elif r.geninfo_identifier: source = 'ncbi' else: source = 'upload' return {'r_id': r.id, 'r_source': source, 'r_source_data': data} # Process a few rows at a time, since they will be read in memory. while True: chunk = result.fetchmany(1000) if not chunk: break # Populate `source` and `source_data` based on existing column values. statement = references.update().where( references.c.id == sql.bindparam('r_id')).values({ 'source': sql.bindparam('r_source'), 'source_data': sql.bindparam('r_source_data') }) # Execute UPDATE query for fetched rows. connection.execute(statement, [update_params(r) for r in chunk]) # Unfortunately, SQLite doesn't support adding the NOT NULL constraint on # an existing column. We use batch_alter_table to workaround this. with op.batch_alter_table('references') as batch_op: batch_op.alter_column('source', nullable=False, existing_type=sa.Enum('ncbi', 'ncbi_slice', 'lrg', 'url', 'upload', name='reference_source'))
# http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, WITHOUT # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the # License for the specific language governing permissions and limitations # under the License. """Set Port.pxe_enabled to True if NULL Revision ID: f6fdb920c182 Revises: 5ea1b0d310e Create Date: 2016-02-12 16:53:21.008580 """ from alembic import op from sqlalchemy import Boolean, String from sqlalchemy.sql import table, column, null # revision identifiers, used by Alembic. revision = 'f6fdb920c182' down_revision = '5ea1b0d310e' port = table('ports', column('uuid', String(36)), column('pxe_enabled', Boolean())) def upgrade(): op.execute(port.update().where(port.c.pxe_enabled == null()).values( {'pxe_enabled': True}))
def get_query_str( # sqla self, engine, qry_start_dttm, groupby, metrics, granularity, from_dttm, to_dttm, filter=None, # noqa is_timeseries=True, timeseries_limit=15, timeseries_limit_metric=None, row_limit=None, inner_from_dttm=None, inner_to_dttm=None, orderby=None, extras=None, columns=None): """Querying any sqla table from this common interface""" template_kwargs = { 'from_dttm': from_dttm, 'groupby': groupby, 'metrics': metrics, 'row_limit': row_limit, 'to_dttm': to_dttm, } template_processor = self.get_template_processor(**template_kwargs) # For backward compatibility if granularity not in self.dttm_cols: granularity = self.main_dttm_col cols = {col.column_name: col for col in self.columns} metrics_dict = {m.metric_name: m for m in self.metrics} if not granularity and is_timeseries: raise Exception(_( "Datetime column not provided as part table configuration " "and is required by this type of chart")) for m in metrics: if m not in metrics_dict: raise Exception(_("Metric '{}' is not valid".format(m))) metrics_exprs = [metrics_dict.get(m).sqla_col for m in metrics] timeseries_limit_metric = metrics_dict.get(timeseries_limit_metric) timeseries_limit_metric_expr = None if timeseries_limit_metric: timeseries_limit_metric_expr = \ timeseries_limit_metric.sqla_col if metrics: main_metric_expr = metrics_exprs[0] else: main_metric_expr = literal_column("COUNT(*)").label("ccount") select_exprs = [] groupby_exprs = [] if groupby: select_exprs = [] inner_select_exprs = [] inner_groupby_exprs = [] for s in groupby: col = cols[s] outer = col.sqla_col inner = col.sqla_col.label(col.column_name + '__') groupby_exprs.append(outer) select_exprs.append(outer) inner_groupby_exprs.append(inner) inner_select_exprs.append(inner) elif columns: for s in columns: select_exprs.append(cols[s].sqla_col) metrics_exprs = [] if granularity: @compiles(ColumnClause) def visit_column(element, compiler, **kw): """Patch for sqlalchemy bug TODO: sqlalchemy 1.2 release should be doing this on its own. Patch only if the column clause is specific for DateTime set and granularity is selected. """ text = compiler.visit_column(element, **kw) try: if ( element.is_literal and hasattr(element.type, 'python_type') and type(element.type) is DateTime ): text = text.replace('%%', '%') except NotImplementedError: # Some elements raise NotImplementedError for python_type pass return text dttm_col = cols[granularity] time_grain = extras.get('time_grain_sqla') if is_timeseries: timestamp = dttm_col.get_timestamp_expression(time_grain) select_exprs += [timestamp] groupby_exprs += [timestamp] time_filter = dttm_col.get_time_filter(from_dttm, to_dttm) select_exprs += metrics_exprs qry = sa.select(select_exprs) tbl = table(self.table_name) if self.schema: tbl.schema = self.schema # Supporting arbitrary SQL statements in place of tables if self.sql: from_sql = template_processor.process_template(self.sql) tbl = TextAsFrom(sa.text(from_sql), []).alias('expr_qry') if not columns: qry = qry.group_by(*groupby_exprs) where_clause_and = [] having_clause_and = [] for flt in filter: if not all([flt.get(s) for s in ['col', 'op', 'val']]): continue col = flt['col'] op = flt['op'] eq = flt['val'] col_obj = cols.get(col) if col_obj: if op in ('in', 'not in'): values = [types.strip("'").strip('"') for types in eq] if col_obj.is_num: values = [utils.js_string_to_num(s) for s in values] cond = col_obj.sqla_col.in_(values) if op == 'not in': cond = ~cond where_clause_and.append(cond) elif op == '==': where_clause_and.append(col_obj.sqla_col == eq) elif op == '!=': where_clause_and.append(col_obj.sqla_col != eq) elif op == '>': where_clause_and.append(col_obj.sqla_col > eq) elif op == '<': where_clause_and.append(col_obj.sqla_col < eq) elif op == '>=': where_clause_and.append(col_obj.sqla_col >= eq) elif op == '<=': where_clause_and.append(col_obj.sqla_col <= eq) elif op == 'LIKE': where_clause_and.append( col_obj.sqla_col.like(eq.replace('%', '%%'))) if extras: where = extras.get('where') if where: where_clause_and += [wrap_clause_in_parens( template_processor.process_template(where))] having = extras.get('having') if having: having_clause_and += [wrap_clause_in_parens( template_processor.process_template(having))] if granularity: qry = qry.where(and_(*([time_filter] + where_clause_and))) else: qry = qry.where(and_(*where_clause_and)) qry = qry.having(and_(*having_clause_and)) if groupby: qry = qry.order_by(desc(main_metric_expr)) elif orderby: for col, ascending in orderby: direction = asc if ascending else desc qry = qry.order_by(direction(col)) qry = qry.limit(row_limit) if is_timeseries and timeseries_limit and groupby: # some sql dialects require for order by expressions # to also be in the select clause -- others, e.g. vertica, # require a unique inner alias inner_main_metric_expr = main_metric_expr.label('mme_inner__') inner_select_exprs += [inner_main_metric_expr] subq = select(inner_select_exprs) subq = subq.select_from(tbl) inner_time_filter = dttm_col.get_time_filter( inner_from_dttm or from_dttm, inner_to_dttm or to_dttm, ) subq = subq.where(and_(*(where_clause_and + [inner_time_filter]))) subq = subq.group_by(*inner_groupby_exprs) ob = inner_main_metric_expr if timeseries_limit_metric_expr is not None: ob = timeseries_limit_metric_expr subq = subq.order_by(desc(ob)) subq = subq.limit(timeseries_limit) on_clause = [] for i, gb in enumerate(groupby): on_clause.append( groupby_exprs[i] == column(gb + '__')) tbl = tbl.join(subq.alias(), and_(*on_clause)) qry = qry.select_from(tbl) sql = "{}".format( qry.compile( engine, compile_kwargs={"literal_binds": True},), ) logging.info(sql) sql = sqlparse.format(sql, reindent=True) return sql
def mute_cond(muted): stream_cond = column("recipient_id") == recipient_map[ muted[0].lower()] topic_cond = func.upper(column("subject")) == func.upper( muted[1]) return and_(stream_cond, topic_cond)
def _add_version_column(table_name): op.add_column(table_name, sa.Column('version', sa.Integer)) table = sql.table(table_name, sql.column('version')) op.execute(table.update().values(version=1)) op.alter_column(table_name, 'version', nullable=False)
def _insert_operation_form_field(): tb = table( 'operation_form_field', column('id', Integer), column('name', String), column('type', String), column('required', Integer), column('order', Integer), column('default', Text), column('suggested_widget', String), column('values_url', String), column('values', String), column('scope', String), column('form_id', Integer), column('enable_conditions', String), ) columns = ('id', 'name', 'type', 'required', 'order', 'default', 'suggested_widget', 'values_url', 'values', 'scope', 'form_id', 'enable_conditions') enabled_condition = 'this.loss.internalValue === "huber" || this.loss.internalValue === "quantile"' enabled_condition2 = 'this.n_iter_no_change.internalValue !== "0"' data = [ #Flatten - data_format (4357, 'features', 'TEXT', 1, 1, None, 'attribute-selector', None, None, 'EXECUTION', 4006, None), (4358, 'label', 'TEXT', 1, 2, None, 'attribute-selector', None, None, 'EXECUTION', 4006, None), (4359, 'prediction', 'TEXT', 0, 3, 'prediction', 'text', None, None, 'EXECUTION', 4006, None), (4360, 'validation_fraction', 'FLOAT', 0, 6, 0.1, 'decimal', None, None, 'EXECUTION', 4006, enabled_condition2), (4361, 'max_depth', 'INTEGER', 0, 8, 3, 'integer', None, None, 'EXECUTION', 4006, None), (4362, 'n_iter_no_change', 'INTEGER', 0, 5, None, 'integer', None, None, 'EXECUTION', 4006, None), (4363, 'tol', 'DECIMAL', 0, 12, 1e-4, 'decimal', None, None, 'EXECUTION', 4006, None), (4364, 'criterion', 'TEXT', 0, 13, 'friedman_mse', 'dropdown', None, json.dumps([ { "key": "friedman_mse", "value": "Mean squared error with improvement score by Friedman" }, { "key": "mse", "value": "Mean squared error" }, { "key": "mae", "value": "Mean absolute error" }, ]), 'EXECUTION', 4006, None), (4365, 'loss', 'TEXT', 0, 13, 'ls', 'dropdown', None, json.dumps([ { "key": "ls", "value": "Least squares regression" }, { "key": "lad", "value": "Least absolute deviation" }, { "key": "huber", "value": "Combination of the two above" }, { "key": "quantile", "value": "Quantile regression" }, ]), 'EXECUTION', 4006, None), (4366, 'subsample', 'DECIMAL', 0, 14, 1.0, 'decimal', None, None, 'EXECUTION', 4006, None), (4367, 'alpha', 'DECIMAL', 0, 15, 0.9, 'decimal', None, None, 'EXECUTION', 4006, enabled_condition), (4368, 'min_weight_fraction_leaf', 'DECIMAL', 0, 16, 0, 'decimal', None, None, 'EXECUTION', 4006, None), (4369, 'max_leaf_nodes', 'INTEGER', 0, 17, None, 'integer', None, None, 'EXECUTION', 4006, None), (4370, 'min_impurity_decrease', 'DECIMAL', 0, 18, 0, 'decimal', None, None, 'EXECUTION', 4006, None), (4371, 'max_features', 'TEXT', 0, 22, None, 'dropdown', None, json.dumps([ { "key": "auto", "value": "auto" }, { "key": "sqrt", "value": "sqrt" }, { "key": "log2", "value": "log2" }, ]), 'EXECUTION', 4006, None), ] rows = [dict(list(zip(columns, row))) for row in data] op.bulk_insert(tb, rows)
def get_old_messages_backend(request, user_profile, anchor=REQ(converter=int), num_before=REQ(converter=to_non_negative_int), num_after=REQ(converter=to_non_negative_int), narrow=REQ('narrow', converter=narrow_parameter, default=None), use_first_unread_anchor=REQ( default=False, converter=ujson.loads), apply_markdown=REQ(default=True, converter=ujson.loads)): include_history = ok_to_include_history(narrow, user_profile.realm) if include_history and not use_first_unread_anchor: query = select([column("id").label("message_id")], None, "zerver_message") inner_msg_id_col = literal_column("zerver_message.id") elif narrow is None: query = select( [column("message_id"), column("flags")], column("user_profile_id") == literal(user_profile.id), "zerver_usermessage") inner_msg_id_col = column("message_id") else: # TODO: Don't do this join if we're not doing a search query = select( [column("message_id"), column("flags")], column("user_profile_id") == literal(user_profile.id), join( "zerver_usermessage", "zerver_message", literal_column("zerver_usermessage.message_id") == literal_column("zerver_message.id"))) inner_msg_id_col = column("message_id") num_extra_messages = 1 is_search = False if narrow is not None: # Add some metadata to our logging data for narrows verbose_operators = [] for term in narrow: if term['operator'] == "is": verbose_operators.append("is:" + term['operand']) else: verbose_operators.append(term['operator']) request._log_data['extra'] = "[%s]" % (",".join(verbose_operators), ) # Build the query for the narrow num_extra_messages = 0 builder = NarrowBuilder(user_profile, inner_msg_id_col) for term in narrow: if term['operator'] == 'search' and not is_search: query = query.column("subject").column("rendered_content") is_search = True query = builder.add_term(query, term) # We add 1 to the number of messages requested if no narrow was # specified to ensure that the resulting list always contains the # anchor message. If a narrow was specified, the anchor message # might not match the narrow anyway. if num_after != 0: num_after += num_extra_messages else: num_before += num_extra_messages sa_conn = get_sqlalchemy_connection() if use_first_unread_anchor: condition = column("flags").op("&")(UserMessage.flags.read.mask) == 0 # We exclude messages on muted topics when finding the first unread # message in this narrow muting_conditions = exclude_muting_conditions(user_profile, narrow) if muting_conditions: condition = and_(condition, *muting_conditions) first_unread_query = query.where(condition) first_unread_query = first_unread_query.order_by( inner_msg_id_col.asc()).limit(1) first_unread_result = list( sa_conn.execute(first_unread_query).fetchall()) if len(first_unread_result) > 0: anchor = first_unread_result[0][0] else: anchor = 10000000000000000 before_query = None after_query = None if num_before != 0: before_anchor = anchor if num_after != 0: # Don't include the anchor in both the before query and the after query before_anchor = anchor - 1 before_query = query.where(inner_msg_id_col <= before_anchor) \ .order_by(inner_msg_id_col.desc()).limit(num_before) if num_after != 0: after_query = query.where(inner_msg_id_col >= anchor) \ .order_by(inner_msg_id_col.asc()).limit(num_after) if num_before == 0 and num_after == 0: # This can happen when a narrow is specified. after_query = query.where(inner_msg_id_col == anchor) if before_query is not None: if after_query is not None: query = union_all(before_query.self_group(), after_query.self_group()) else: query = before_query else: query = after_query main_query = alias(query) query = select(main_query.c, None, main_query).order_by(column("message_id").asc()) # This is a hack to tag the query we use for testing query = query.prefix_with("/* get_old_messages */") query_result = list(sa_conn.execute(query).fetchall()) # The following is a little messy, but ensures that the code paths # are similar regardless of the value of include_history. The # 'user_messages' dictionary maps each message to the user's # UserMessage object for that message, which we will attach to the # rendered message dict before returning it. We attempt to # bulk-fetch rendered message dicts from remote cache using the # 'messages' list. search_fields = dict() # type: Dict[int, Dict[str, text_type]] message_ids = [] # type: List[int] user_message_flags = {} # type: Dict[int, List[str]] if include_history: message_ids = [row[0] for row in query_result] # TODO: This could be done with an outer join instead of two queries user_message_flags = dict( (user_message.message_id, user_message.flags_list()) for user_message in UserMessage.objects.filter( user_profile=user_profile, message__id__in=message_ids)) for row in query_result: message_id = row[0] if user_message_flags.get(message_id) is None: user_message_flags[message_id] = ["read", "historical"] if is_search: (_, subject, rendered_content, content_matches, subject_matches) = row search_fields[message_id] = get_search_fields( rendered_content, subject, content_matches, subject_matches) else: for row in query_result: message_id = row[0] flags = row[1] user_message_flags[message_id] = parse_usermessage_flags(flags) message_ids.append(message_id) if is_search: (_, _, subject, rendered_content, content_matches, subject_matches) = row search_fields[message_id] = get_search_fields( rendered_content, subject, content_matches, subject_matches) cache_transformer = lambda row: Message.build_dict_from_raw_db_row( row, apply_markdown) id_fetcher = lambda row: row['id'] message_dicts = generic_bulk_cached_fetch( lambda message_id: to_dict_cache_key_id(message_id, apply_markdown), Message.get_raw_db_rows, message_ids, id_fetcher=id_fetcher, cache_transformer=cache_transformer, extractor=extract_message_dict, setter=stringify_message_dict) message_list = [] for message_id in message_ids: msg_dict = message_dicts[message_id] msg_dict.update({"flags": user_message_flags[message_id]}) msg_dict.update(search_fields.get(message_id, {})) message_list.append(msg_dict) statsd.incr('loaded_old_messages', len(message_list)) ret = {'messages': message_list, "result": "success", "msg": ""} return json_success(ret)
def upgrade(): corp_type_table = table("corp_type", column("code", String), column("description", String), column("service_fee_code", String), column("bcol_fee_code", String), column("gl_memo", String), column("service_gl_memo", String)) filing_type_table = table("filing_type", column("code", String), column("description", String)) fee_schedule_table = table("fee_schedule", column("filing_type_code", String), column("corp_type_code", String), column("fee_code", String), column("fee_start_date", Date), column("fee_end_date", Date), column("future_effective_fee_code", String), column("priority_fee_code", String)) # Corp Type op.bulk_insert(corp_type_table, [{ "code": "NRO", "description": "Name Requests Online", "service_fee_code": "TRF01", "bcol_fee_code": None, "gl_memo": "Benefit Companies", "service_gl_memo": "SBC Modernization Service Charge" }]) # Filing Types op.bulk_insert(filing_type_table, [{ 'code': 'NM620', 'description': 'Reg. Submission Online' }, { 'code': 'NM606', 'description': 'Upgrade to Priority' }]) # Fee Schedule op.bulk_insert(fee_schedule_table, [{ "filing_type_code": "NM620", "corp_type_code": "NRO", "fee_code": "EN103", "fee_start_date": date.today(), "fee_end_date": None, "future_effective_fee_code": None, "priority_fee_code": "PRI01" }, { "filing_type_code": "NM606", "corp_type_code": "NRO", "fee_code": "EN105", "fee_start_date": date.today(), "fee_end_date": None, "future_effective_fee_code": None, "priority_fee_code": None }])
def by_has(self, query, operand, maybe_negate): if operand not in ['attachment', 'image', 'link']: raise BadNarrowOperator("unknown 'has' operand " + operand) col_name = 'has_' + operand cond = column(col_name) return query.where(maybe_negate(cond))
def long2ip(ip_int): '''Converts a saved integer/long back into an IP address''' return str(netaddr.IPAddress(ip_int)) # revision identifiers, used by Alembic. revision = '1ec4a28fe0ff' down_revision = 'c7225db614c1' branch_labels = None depends_on = None solves_table = table( 'solves', column('id', db.Integer), column('ip', db.Integer), ) tracking_table = table( 'tracking', column('id', db.Integer), column('ip', db.String(46)), column('team', db.Integer), column('date', db.DateTime), ) def upgrade(): # ### commands auto generated by Alembic - please adjust! ### connection = op.get_bind()
def _insert_operation_form_field_translation(): tb = table( 'operation_form_field_translation', column('id', Integer), column('locale', String), column('label', String), column('help', String), ) columns = ('id', 'locale', 'label', 'help') data = [ #Flatten - data_format (4357, 'en', 'Features', 'Features.'), (4357, 'pt', 'Atributo(s) previsor(es)', 'Atributo(s) previsor(es).'), (4358, 'en', 'Label attribute', 'Label attribute.'), (4358, 'pt', 'Atributo com o rótulo', 'Atributo com o rótulo.'), (4359, 'en', 'Prediction attribute (new)', 'Prediction attribute (new).'), (4359, 'pt', 'Atributo com a predição (novo)', 'Atributo usado para predição (novo).'), (4360, 'en', 'Validation fraction', 'The proportion of training data to set aside as validation set for early' ' stopping.'), (4360, 'pt', 'Fração de validação', 'A proporção de dados de treinamento a serem retirados como validação' ' definida para parada antecipada.'), (4361, 'en', 'Maximum depth', 'Maximum depth of the individual regression estimators. The maximum depth limits' ' the number of nodes in the tree. Tune this parameter for best performance; the' ' best value depends on the interaction of the input variables.'), (4361, 'pt', 'Profundidade máxima', 'Profundidade máxima na árvore.'), (4362, 'en', 'Early stopping', 'Used to decide if early stopping will be used to terminate training when' ' validation score is not improving.'), (4362, 'pt', 'Parada antecipada', 'Usada para decidir se a parada antecipada vai ser usada para terminar treino' ' quando a pontuação de validação não está melhorando.'), (4363, 'en', 'Tolerance', 'Tolerance for the early stopping.'), (4363, 'pt', 'Tolerância', 'Tolerância para a parada antecipada.'), (4364, 'en', 'Criterion', 'The function to measure the quality of a split..'), (4364, 'pt', 'Critério', 'A função para medir a qualidade de um split..'), (4365, 'en', 'Loss', 'Loss function to be optimized.'), (4365, 'pt', 'Perda', 'Função de perda a ser otimizada.'), (4366, 'en', 'Subsample', 'The fraction of samples to be used for fitting the individual base learners.' ), (4366, 'pt', 'Subamostra', 'A fração de amostras para serem usadas para fazer o fitting em learners de base' ' individual.'), (4367, 'en', 'Alpha', 'The alpha-quantile of the huber loss function and the quantiles loss function.' ), (4367, 'pt', 'Alfa', 'O alfa-quantil da função huber loss e a função de perda de quantis.' ), (4368, 'en', 'Min. weight fraction leaf', 'The minimum weighted fraction of the sum total of weights (of all' ' the input samples) required to be at a leaf node..'), (4368, 'pt', 'Fração ponderada mínima', 'A fração ponderada mínima da soma total de pesos (de todas as amostras' ' de entrada) necessária para estar em um nó folha.'), (4369, 'en', 'Max. leaf nodes', 'Grow trees with max_leaf_nodes in best-first fashion.'), (4369, 'pt', 'Max. nós folha', 'Cresça árvores com max_leaf_nodes da melhor maneira possível.'), (4370, 'en', 'Min. impurity decrease', 'A node will be split if this split induces a decrease of the impurity' ' greater than or equal to this value.'), (4370, 'pt', 'Redução mínima da impureza', 'Um nó será dividido se essa divisão induzir uma redução da impureza' ' maior ou igual a esse valor.'), (4371, 'en', 'Max. features', 'The number of features to consider when looking for the best split.' ), (4371, 'pt', 'Número máximo de atributos', 'Número de atributos a serem considerados ao procurar a melhor ' 'divisão.'), ] rows = [dict(list(zip(columns, row))) for row in data] op.bulk_insert(tb, rows)
""" from alembic import op from sqlalchemy.dialects import postgresql from sqlalchemy.sql import column, table import sqlalchemy as sa # revision identifiers, used by Alembic. revision = '41a4531be082' down_revision = 'e8665a81606d' branch_labels = None depends_on = None account_name = table( 'account_name', column('id', postgresql.UUID()), column('created_at', sa.TIMESTAMP(timezone=True)), column('updated_at', sa.TIMESTAMP(timezone=True)), column('name', sa.Unicode(63)), column('user_id', sa.Integer), column('organization_id', sa.Integer), column('reserved', sa.Boolean), ) profile = table( 'profile', column('uuid', postgresql.UUID()), column('created_at', sa.TIMESTAMP(timezone=True)), column('updated_at', sa.TIMESTAMP(timezone=True)), column('name', sa.Unicode(63)), column('user_id', sa.Integer),
import sqlalchemy as sa from progressbar import ProgressBar import progressbar.widgets from coaster.utils import buid2uuid, uuid2buid # revision identifiers, used by Alembic. revision = '87fc422c81f9' down_revision = '039d2745e628' branch_labels = None depends_on = None user_session = table( 'user_session', column('id', sa.Integer()), column('buid', sa.Unicode(22)), column('uuid', UUIDType(binary=False)), ) def get_progressbar(label, maxval): return ProgressBar( maxval=maxval, widgets=[ label, ': ', progressbar.widgets.Percentage(), ' ', progressbar.widgets.Bar(), ' ',
def upgrade(): # Create Lots table and Lot to Framework relationship op.create_table( 'lots', sa.Column('id', sa.Integer(), nullable=False), sa.Column('slug', sa.String(), nullable=False), sa.Column('name', sa.String(), nullable=False), sa.Column('one_service_limit', sa.Boolean(), nullable=False), sa.PrimaryKeyConstraint('id')) op.create_index(op.f('ix_lots_slug'), 'lots', ['slug'], unique=False) op.create_table( 'framework_lots', sa.Column('framework_id', sa.Integer(), nullable=False), sa.Column('lot_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint( ['framework_id'], ['frameworks.id'], ), sa.ForeignKeyConstraint( ['lot_id'], ['lots.id'], )) # Insert G-Cloud lot records lot_table = table('lots', column('name', sa.String), column('slug', sa.String), column('one_service_limit', sa.Boolean)) op.bulk_insert(lot_table, [ { 'name': 'Software as a Service', 'slug': 'saas', 'one_service_limit': False }, { 'name': 'Platform as a Service', 'slug': 'paas', 'one_service_limit': False }, { 'name': 'Infrastructure as a Service', 'slug': 'iaas', 'one_service_limit': False }, { 'name': 'Specialist Cloud Services', 'slug': 'scs', 'one_service_limit': False }, ]) framework_lots_table = table('framework_lots', column('framework_id', sa.Integer), column('lot_id', sa.Integer)) # Add 4 lots (ids 1-4) to all G-Cloud frameworks (ids 1-4) op.bulk_insert(framework_lots_table, [{ 'framework_id': framework_id, 'lot_id': lot_id } for framework_id, lot_id in itertools.product(range(1, 5), range(1, 5))]) op.add_column(u'archived_services', sa.Column('lot_id', sa.BigInteger(), nullable=True)) op.create_index(op.f('ix_archived_services_lot_id'), 'archived_services', ['lot_id'], unique=False) op.create_foreign_key(None, 'archived_services', 'lots', ['lot_id'], ['id']) op.add_column(u'draft_services', sa.Column('lot_id', sa.BigInteger(), nullable=True)) op.create_index(op.f('ix_draft_services_lot_id'), 'draft_services', ['lot_id'], unique=False) op.create_foreign_key(None, 'draft_services', 'lots', ['lot_id'], ['id']) op.add_column(u'services', sa.Column('lot_id', sa.BigInteger(), nullable=True)) op.create_index(op.f('ix_services_lot_id'), 'services', ['lot_id'], unique=False) op.create_foreign_key(None, 'services', 'lots', ['lot_id'], ['id'])
def query(self): query = powa_getstatdata_sample("query") query = query.where((column("datname") == bindparam("database")) & (column("queryid") == bindparam("query"))) query = query.alias() c = query.c total_blocks = ((c.shared_blks_read + c.shared_blks_hit).label("total_blocks")) def bps(col): ts = extract("epoch", greatest(c.mesure_interval, '1 second')) return (mulblock(col) / ts).label(col.name) cols = [ to_epoch(c.ts), c.rows, c.calls, case([(total_blocks == 0, 0)], else_=cast(c.shared_blks_hit, Numeric) * 100 / total_blocks).label("hit_ratio"), bps(c.shared_blks_read), bps(c.shared_blks_hit), bps(c.shared_blks_dirtied), bps(c.shared_blks_written), bps(c.local_blks_read), bps(c.local_blks_hit), bps(c.local_blks_dirtied), bps(c.local_blks_written), bps(c.temp_blks_read), bps(c.temp_blks_written), c.blk_read_time, c.blk_write_time, (c.runtime / greatest(c.calls, 1)).label("avg_runtime") ] from_clause = query if self.has_extension("pg_stat_kcache"): # Add system metrics from pg_stat_kcache, # and detailed hit ratio. kcache_query = kcache_getstatdata_sample() kc = inner_cc(kcache_query) kcache_query = (kcache_query.where( kc.queryid == bindparam("query")).alias()) kc = kcache_query.c sys_hits = (greatest(mulblock(c.shared_blks_read) - kc.reads, 0).label("kcache_hitblocks")) sys_hitratio = (cast(sys_hits, Numeric) * 100 / mulblock(total_blocks)) disk_hit_ratio = (kc.reads / mulblock(total_blocks)) total_time = greatest(c.runtime, 1) cols.extend([ kc.reads, kc.writes, ((kc.user_time * 1000 * 100) / total_time).label("user_time"), ((kc.system_time * 1000 * 100) / total_time).label("system_time"), case([(total_blocks == 0, 0)], else_=disk_hit_ratio).label("disk_hit_ratio"), case([(total_blocks == 0, 0)], else_=sys_hitratio).label("sys_hit_ratio") ]) from_clause = from_clause.join(kcache_query, kcache_query.c.ts == c.ts) else: cols.extend([ case([(total_blocks == 0, 0)], else_=cast(c.shared_blks_read, Numeric) * 100 / total_blocks).label("miss_ratio") ]) return (select(cols).select_from(from_clause).where( c.calls != None).order_by(c.ts).params(samples=100))