Python sqlalchemy.text方法代码示例

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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)} 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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 ### 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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') 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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'"
        )) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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'"
        )) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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() 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
def upgrade():
    bind = op.get_bind()
    session = Session(bind=bind)

    department_query = text("SELECT * FROM departments")
    for department in session.execute(department_query):
        short_name = "".join([x[0] for x in department.name.split(" ")])
        department.short_name = short_name
    session.commit()

    op.alter_column('departments', 'short_name', existing_type=sa.VARCHAR(length=80), nullable=False) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('users', sa.Column('can_use_multiple_mailbox', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=False))
    op.add_column('users', sa.Column('full_mailbox', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=False))
    # ### end Alembic commands ### 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('users', sa.Column('is_developer', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=False))
    # ### end Alembic commands ### 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import text [as 别名]
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 ###