Python sqlalchemy.text() Examples

def prepare(self) -> None:
        for p in self.people:
            key = f'logsite:people:{p.id}'
            data = redis.get_container(key, ex=3600)
            if data:
                p.fav_format = data.fav_format
                p.num_matches = data.num_matches
            else:
                p.num_matches = match.get_recent_matches_by_player(p.name).count()
                stmt = text("""
                    SELECT f.name, COUNT(*) AS num_matches
                    FROM match_players AS mp
                    INNER JOIN `match` AS m ON mp.match_id = m.id
                    INNER JOIN format AS f ON m.format_id = f.id
                    WHERE mp.user_id = :pid
                    GROUP BY f.id;
                """)
                p.formats = db.DB.session.query('name', 'num_matches').from_statement(stmt).params(pid=p.id).all()
                if p.formats:
                    p.fav_format = '{0} ({1} matches)'.format(p.formats[0][0], p.formats[0][1])
                else:
                    p.fav_format = '⸺'
                redis.store(key, {'fav_format': p.fav_format, 'num_matches': p.num_matches}, ex=3600) 

def _exec(self, query, **args):
        connection = None
        try:
            connection = self.db.connect()
            q = query.format(schema=self.schema)
            # DEBUG!
            qq = q
            for k in args.keys():
                qq = qq.replace(':'+str(k), "'" + str(args[k]) + "'")
            qq = qq + ';'
            print("{0}".format(qq))
            # DEBUG! END
            return connection.execute(sqlalchemy.text(q), **args)
        finally:
            if connection is not None:
                connection.close() 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('client_user', sa.Column('default_avatar', sa.Boolean(), server_default='0', nullable=False))
    op.add_column('client_user', sa.Column('name', sa.String(length=128), server_default=sa.text('NULL'), nullable=True))
    op.add_column('gen_email', sa.Column('custom', sa.Boolean(), server_default='0', nullable=False))
    # ### end Alembic commands ### 

def list(self):
		if(hasattr(cherrypy.request, 'json')):
			qry = self.db.query(self.baseclass)
			
			if('filter' in cherrypy.request.json and cherrypy.request.json['filter']!=''):
				qry = qry.filter(text(cherrypy.request.json['filter']))
			
			if('sort' in cherrypy.request.json and cherrypy.request.json['sort']!=''):
				qry = qry.order_by(text(cherrypy.request.json['sort']))
			
			objects = qry.all()
			
		else:	
			objects = self.db.query(self.baseclass).all()
		
		return {'status': 'success', 'data': self.objectsToList(objects)} 

def member_roles_list(context, data_dict):
    '''Return the possible roles for members of groups and organizations.

    :param group_type: the group type, either ``"group"`` or ``"organization"``
        (optional, default ``"organization"``)
    :type id: string
    :returns: a list of dictionaries each with two keys: ``"text"`` (the
        display name of the role, e.g. ``"Admin"``) and ``"value"`` (the
        internal name of the role, e.g. ``"admin"``)
    :rtype: list of dictionaries

    '''
    group_type = data_dict.get('group_type', 'organization')
    roles_list = authz.roles_list()
    if group_type == 'group':
        roles_list = [role for role in roles_list
                      if role['value'] != 'editor']

    _check_access('member_roles_list', context, data_dict)
    return roles_list 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('months',
    sa.Column('id', sa.INTEGER(), server_default=sa.text("nextval('months_id_seq'::regclass)"), nullable=False),
    sa.Column('month', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('year', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('department_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['department_id'], ['departments.id'], name='months_department_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='months_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('serviceTypes',
    sa.Column('id', sa.INTEGER(), server_default=sa.text('nextval(\'"serviceTypes_id_seq"\'::regclass)'), nullable=False),
    sa.Column('month_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('service_type', sa.VARCHAR(length=36), autoincrement=False, nullable=False),
    sa.Column('count', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['month_id'], ['months.id'], name='serviceTypes_month_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='serviceTypes_pkey')
    )
    op.drop_table('use_of_force_incidents')
    ### end Alembic commands ### 

def upgrade():
    op.drop_constraint('fk_vfolder_attachment_vfolder_vfolders', 'vfolder_attachment', type_='foreignkey')
    op.drop_constraint('fk_vfolder_attachment_kernel_kernels', 'vfolder_attachment', type_='foreignkey')
    op.drop_constraint('pk_kernels', 'kernels', type_='primary')
    op.add_column('kernels',
                  sa.Column('id', GUID(),
                            server_default=sa.text('uuid_generate_v4()'),
                            nullable=False))
    op.add_column('kernels', sa.Column('role', sa.String(length=16), nullable=False, default='master'))
    op.create_primary_key('pk_kernels', 'kernels', ['id'])
    op.alter_column(
        'kernels', 'sess_id',
        existing_type=postgresql.UUID(),
        type_=sa.String(length=64),
        nullable=True,
        existing_server_default=sa.text('uuid_generate_v4()'))
    op.create_index(op.f('ix_kernels_sess_id'), 'kernels', ['sess_id'], unique=False)
    op.create_index(op.f('ix_kernels_sess_id_role'), 'kernels', ['sess_id', 'role'], unique=False)
    op.create_foreign_key('fk_vfolder_attachment_vfolder_vfolders',
                          'vfolder_attachment', 'vfolders',
                          ['vfolder'], ['id'], onupdate='CASCADE', ondelete='CASCADE')
    op.create_foreign_key('fk_vfolder_attachment_kernel_kernels',
                          'vfolder_attachment', 'kernels',
                          ['kernel'], ['id'], onupdate='CASCADE', ondelete='CASCADE') 

def upgrade():
    conn = op.get_bind()
    conn.execute('DROP INDEX IF EXISTS ix_kernels_unique_sess_token;')
    conn.execute('ALTER TYPE kernelstatus RENAME TO kernelstatus_old;')
    kernelstatus_new.create(conn)
    conn.execute(textwrap.dedent('''\
    ALTER TABLE kernels
        ALTER COLUMN "status" DROP DEFAULT,
        ALTER COLUMN "status" TYPE kernelstatus USING "status"::text::kernelstatus,
        ALTER COLUMN "status" SET DEFAULT 'PENDING'::kernelstatus;
    DROP TYPE kernelstatus_old;
    '''))
    op.create_index(
        'ix_kernels_unique_sess_token', 'kernels', ['access_key', 'sess_id'],
        unique=True, postgresql_where=sa.text(
            "status NOT IN ('TERMINATED', 'CANCELLED') and role = 'master'"
        )) 

def downgrade():
    op.drop_index('ix_kernels_unique_sess_token', table_name='kernels')
    conn = op.get_bind()
    conn.execute('ALTER TYPE kernelstatus RENAME TO kernelstatus_new;')
    kernelstatus_old.create(conn)
    conn.execute(textwrap.dedent('''\
    ALTER TABLE kernels
        ALTER COLUMN "status" DROP DEFAULT,
        ALTER COLUMN "status" TYPE kernelstatus USING (
            CASE "status"::text
                WHEN 'CANCELLED' THEN 'TERMINATED'
                ELSE "status"::text
            END
        )::kernelstatus,
        ALTER COLUMN "status" SET DEFAULT 'PREPARING'::kernelstatus;
    DROP TYPE kernelstatus_new;
    '''))
    op.create_index(
        'ix_kernels_unique_sess_token', 'kernels', ['access_key', 'sess_id'],
        unique=True, postgresql_where=sa.text(
            "status != 'TERMINATED' and role = 'master'"
        )) 

def get_most_relevant_transfer_usages(self):
        '''Finds the transfer usage/business categories there are most relevant for the user
        based on the last number of send and completed credit transfers supplemented with the
        defaul business categories
        :return: list of most relevant transfer usage objects for the usage
        """
        '''

        sql = text('''
            SELECT *, COUNT(*) FROM
                (SELECT c.transfer_use::text FROM credit_transfer c
                WHERE c.sender_user_id = {} AND c.transfer_status = 'COMPLETE'
                ORDER BY c.updated DESC
                LIMIT 20)
            C GROUP BY transfer_use ORDER BY count DESC
        '''.format(self.id))
        result = db.session.execute(sql)
        most_common_uses = {}
        for row in result:
            if row[0] is not None:
                for use in json.loads(row[0]):
                    most_common_uses[use] = row[1]

        return most_common_uses 

def get_schema_version(engine):
	results = None
	# try the new style storage
	try:
		results = engine.execute(
			sqlalchemy.text("SELECT value FROM storage_data WHERE namespace = :namespace AND key = 'schema_version'"),
			namespace=_metadata_namespace
		).fetchone()
	except sqlalchemy.exc.DatabaseError:
		pass
	if results:
		return _metadata_serializer.loads(results[0])

	# try the old style storage
	try:
		results = engine.execute(sqlalchemy.text("SELECT value_type, value FROM meta_data WHERE id = 'schema_version'")).fetchone()
	except sqlalchemy.exc.DatabaseError:
		pass
	if results:
		value_type, value = results
		if value_type != 'int':
			raise TypeError('the value is not of type: int')
		return int(value)
	return models.SCHEMA_VERSION 

def _exec(self, construct, execution_options=None,
              multiparams=(),
              params=util.immutabledict()):
        if isinstance(construct, string_types):
            construct = text(construct)
        if self.as_sql:
            if multiparams or params:
                # TODO: coverage
                raise Exception("Execution arguments not allowed with as_sql")

            if self.literal_binds and not isinstance(
                    construct, schema.DDLElement):
                compile_kw = dict(compile_kwargs={"literal_binds": True})
            else:
                compile_kw = {}

            self.static_output(text_type(
                construct.compile(dialect=self.dialect, **compile_kw)
            ).replace("\t", "    ").strip() + self.command_terminator)
        else:
            conn = self.connection
            if execution_options:
                conn = conn.execution_options(**execution_options)
            return conn.execute(construct, *multiparams, **params) 

def upgrade():
    op.add_column(
        "users",
        sa.Column(
            "is_restricted",
            sa.Boolean(),
            server_default=sa.text("false"),
            nullable=False,
        ),
    )
    op.create_index(
        op.f("ix_users_is_restricted"), "users", ["is_restricted"], unique=False
    ) 

def upgrade():
    op.add_column(
        "users",
        sa.Column(
            "has_2fa", sa.Boolean(), server_default=sa.text("false"), nullable=False
        ),
    )
    op.create_index(op.f("ix_users_has_2fa"), "users", ["has_2fa"], unique=False) 

def upgrade():
    op.add_column(
        "users",
        sa.Column(
            "age_consent", sa.Boolean(), server_default=sa.text("false"), nullable=False
        ),
    )
    op.add_column("users", sa.Column("consented_at", sa.DateTime(), nullable=True))
    op.add_column(
        "users",
        sa.Column(
            "cookies_consent",
            sa.Boolean(),
            server_default=sa.text("false"),
            nullable=False,
        ),
    )
    op.add_column("users", sa.Column("joined_at", sa.DateTime(), nullable=True))
    op.add_column("users", sa.Column("left_at", sa.DateTime(), nullable=True))
    op.add_column(
        "users",
        sa.Column(
            "org_consent", sa.Boolean(), server_default=sa.text("false"), nullable=False
        ),
    )
    op.add_column(
        "users",
        sa.Column(
            "profile_consent",
            sa.Boolean(),
            server_default=sa.text("false"),
            nullable=False,
        ),
    ) 

def version_validity_subquery(self, parent, version_obj, alias=None):
        """
        Return the subquery needed by :func:`update_version_validity`.

        This method is only used when using 'validity' versioning strategy.

        :param parent: SQLAlchemy declarative parent object
        :parem version_obj: SQLAlchemy declarative version object

        .. seealso:: :func:`update_version_validity`
        """
        fetcher = self.manager.fetcher(parent)
        session = sa.orm.object_session(version_obj)

        subquery = fetcher._transaction_id_subquery(
            version_obj,
            next_or_prev='prev',
            alias=alias
        )
        if session.connection().engine.dialect.name == 'mysql':
            return sa.select(
                [sa.text('max_1')],
                from_obj=[
                    sa.sql.expression.alias(subquery, name='subquery')
                ]
            )
        return subquery 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('nu_release_item', 'reviewed',
               existing_type=postgresql.ENUM('unverified', 'valid', 'rejected', name='nu_item_enum'),
               type_=sa.BOOLEAN(),
               existing_nullable=False,
               nullable=False,
               existing_server_default=sa.text('false'))
    ENUM(name="pgenum").drop(op.get_bind(), checkfirst=True)
    ### end Alembic commands ### 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    enum = ENUM('unverified', 'valid', 'rejected',          name='nu_item_enum', create_type=False)
    enum.create(op.get_bind(), checkfirst=True)

    op.execute("ALTER TABLE nu_release_item ALTER COLUMN reviewed DROP DEFAULT")

    op.alter_column('nu_release_item', 'reviewed',
               existing_type=sa.BOOLEAN(),
               type_=enum,
               existing_nullable=False,
               nullable=False,
               server_default=sa.text("'unverified'"),
               existing_server_default=sa.text('false'),
               postgresql_using="CASE WHEN reviewed = false THEN 'unverified'::nu_item_enum ELSE 'valid'::nu_item_enum END"
               )

    op.execute("ALTER TABLE nu_release_item ALTER COLUMN reviewed SET DEFAULT 'unverified'")
    ### end Alembic commands ### 

def dispatch_slack(message):    
    """Dispatches the message to Slack."""
    result = requests.post(saq.CONFIG['slack']['alert_url'],
                      headers={'Content-Type': 'application/json'},
                      data=message.value.decode('utf8', errors='ignore'))

    logging.info(f"dispatched message {message} to slack: {result} {result.text}") 

def static_output(self, text):
        self.output_buffer.write(text_type(text + "\n\n"))
        self.output_buffer.flush() 

def autogen_column_reflect(self, inspector, table, column_info):
        if column_info.get('default') and \
                isinstance(column_info['type'], (INTEGER, BIGINT)):
            seq_match = re.match(
                r"nextval\('(.+?)'::regclass\)",
                column_info['default'])
            if seq_match:
                info = inspector.bind.execute(text(
                    "select c.relname, a.attname "
                    "from pg_class as c join pg_depend d on d.objid=c.oid and "
                    "d.classid='pg_class'::regclass and "
                    "d.refclassid='pg_class'::regclass "
                    "join pg_class t on t.oid=d.refobjid "
                    "join pg_attribute a on a.attrelid=t.oid and "
                    "a.attnum=d.refobjsubid "
                    "where c.relkind='S' and c.relname=:seqname"
                ), seqname=seq_match.group(1)).first()
                if info:
                    seqname, colname = info
                    if colname == column_info['name']:
                        log.info(
                            "Detected sequence named '%s' as "
                            "owned by integer column '%s(%s)', "
                            "assuming SERIAL and omitting",
                            seqname, table.name, colname)
                        # sequence, and the owner is this column,
                        # its a SERIAL - whack it!
                        del column_info['default'] 

def _interpret_as_column_or_from(element):
    if isinstance(element, Visitable):
        return element
    elif hasattr(element, '__clause_element__'):
        return element.__clause_element__()

    insp = inspection.inspect(element, raiseerr=False)
    if insp is None:
        if isinstance(element, (util.NoneType, bool)):
            return _const_expr(element)
    elif hasattr(insp, "selectable"):
        return insp.selectable

    # be forgiving as this is an extremely common
    # and known expression
    if element == "*":
        guess_is_literal = True
    elif isinstance(element, (numbers.Number)):
        return ColumnClause(str(element), is_literal=True)
    else:
        element = str(element)
        # give into temptation, as this fact we are guessing about
        # is not one we've previously ever needed our users tell us;
        # but let them know we are not happy about it
        guess_is_literal = not _guess_straight_column.match(element)
        util.warn_limited(
            "Textual column expression %(column)r should be "
            "explicitly declared with text(%(column)r), "
            "or use %(literal_column)s(%(column)r) "
            "for more specificity",
            {
                "column": util.ellipses_string(element),
                "literal_column": "literal_column"
                if guess_is_literal else "column"
            })
    return ColumnClause(
        element,
        is_literal=guess_is_literal) 

def _no_literals(element):
    if hasattr(element, '__clause_element__'):
        return element.__clause_element__()
    elif not isinstance(element, Visitable):
        raise exc.ArgumentError("Ambiguous literal: %r.  Use the 'text()' "
                                "function to indicate a SQL expression "
                                "literal, or 'literal()' to indicate a "
                                "bound value." % element)
    else:
        return element 

def _resource_exists(context, data_dict):
    ''' Returns true if the resource exists in CKAN and in the datastore '''
    model = _get_or_bust(context, 'model')
    res_id = _get_or_bust(data_dict, 'resource_id')
    if not model.Resource.get(res_id):
        return False

    resources_sql = sqlalchemy.text(u'''SELECT 1 FROM "_table_metadata"
                                        WHERE name = :id AND alias_of IS NULL''')
    results = db._get_engine(data_dict).execute(resources_sql, id=res_id)
    return results.rowcount > 0 

def literal_column(text, type_=None):
    """Produce a :class:`.ColumnClause` object that has the
    :paramref:`.column.is_literal` flag set to True.

    :func:`.literal_column` is similar to :func:`.column`, except that
    it is more often used as a "standalone" column expression that renders
    exactly as stated; while :func:`.column` stores a string name that
    will be assumed to be part of a table and may be quoted as such,
    :func:`.literal_column` can be that, or any other arbitrary column-oriented
    expression.

    :param text: the text of the expression; can be any SQL expression.
      Quoting rules will not be applied. To specify a column-name expression
      which should be subject to quoting rules, use the :func:`column`
      function.

    :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine`
      object which will
      provide result-set translation and additional expression semantics for
      this column. If left as None the type will be NullType.

    .. seealso::

        :func:`.column`

        :func:`.text`

        :ref:`sqlexpression_literal_column`

    """
    return ColumnClause(text, type_=type_, is_literal=True) 

def compare(self, other):
        return isinstance(other, TextClause) and other.text == self.text 

def __init__(
            self,
            text,
            bind=None):
        self._bind = bind
        self._bindparams = {}

        def repl(m):
            self._bindparams[m.group(1)] = BindParameter(m.group(1))
            return ':%s' % m.group(1)

        # scan the string and search for bind parameter names, add them
        # to the list of bindparams
        self.text = self._bind_params_regex.sub(repl, text) 

def selectable(self):
        # allows text() to be considered by
        # _interpret_as_from
        return self 

def do_commit_twophase(self, connection, xid, is_prepared=True,
                           recover=False):
        if not is_prepared:
            self.do_prepare_twophase(connection, xid)
        connection.execute(sql.text("XA COMMIT :xid"), xid=xid) 

def upgrade():
	# Create an auto-increment sequence for cards.id
	conn = alembic.context.get_context().bind
	meta = sqlalchemy.MetaData(bind=conn)
	meta.reflect()
	cards = meta.tables['cards']
	# This table already has a (not-previously-used) auto-increment sequence in
	# the production DB, but new DBs created from scratch via the alembic setup
	# won't have it, so check if it already exists and create if it's missing
	# to bring everything back into sync
	if not cards.c.id.server_default or 'cards_id_seq' not in cards.c.id.server_default.arg.text:
		maxid, = conn.execute(sqlalchemy.select([sqlalchemy.func.max(cards.c.id)])).first()
		if maxid is None:
			maxid = 0
		alembic.op.execute(CreateSequence(Sequence('cards_id_seq', start=maxid + 1)))
		alembic.op.alter_column("cards", "id", nullable=False, server_default=sqlalchemy.text("nextval('cards_id_seq'::regclass)"))

	# Add cards.game column
	# create it with a default but then remove the default, to set the value on
	# all existing rows, but have the column mandatory in the future
	alembic.op.drop_index("cards_name_idx")
	alembic.op.add_column('cards',
		sqlalchemy.Column('game', sqlalchemy.Integer, nullable=False, server_default='1')
	)
	alembic.op.alter_column("cards", "game", server_default=None)
	alembic.op.create_index("cards_name_idx", "cards", ["game", "filteredname"], unique=True)