Python sqlalchemy.sql.expression.bindparam() Examples

def filter_thread_work(session, query, total_threads, thread_id, hash_variable=None):
    """ Filters a query to partition thread workloads based on the thread id and total number of threads """
    if thread_id is not None and total_threads is not None and (total_threads - 1) > 0:
        if session.bind.dialect.name == 'oracle':
            bindparams = [bindparam('thread_id', thread_id), bindparam('total_threads', total_threads - 1)]
            if not hash_variable:
                query = query.filter(text('ORA_HASH(id, :total_threads) = :thread_id', bindparams=bindparams))
            else:
                query = query.filter(text('ORA_HASH(%s, :total_threads) = :thread_id' % (hash_variable), bindparams=bindparams))
        elif session.bind.dialect.name == 'mysql':
            if not hash_variable:
                query = query.filter(text('mod(md5(id), %s) = %s' % (total_threads, thread_id)))
            else:
                query = query.filter(text('mod(md5(%s), %s) = %s' % (hash_variable, total_threads, thread_id)))
        elif session.bind.dialect.name == 'postgresql':
            if not hash_variable:
                query = query.filter(text('mod(abs((\'x\'||md5(id::text))::bit(32)::int), %s) = %s' % (total_threads, thread_id)))
            else:
                query = query.filter(text('mod(abs((\'x\'||md5(%s::text))::bit(32)::int), %s) = %s' % (hash_variable, total_threads, thread_id)))
    return query 

def replace(self, column):
        if not isinstance(column, sa.Column):
            return
        try:
            table = version_table(column.table)
        except KeyError:
            reflected_column = column
        else:
            reflected_column = table.c[column.name]
            if (
                column in self.relationship.local_columns and
                table == self.parent.__table__
            ):
                reflected_column = bindparam(
                    column.key,
                    getattr(self.parent, column.key)
                )

        return reflected_column 

def test_unicode(engine, table_one_row):
    unicode_str = "白人看不懂"
    returned_str = sqlalchemy.select(
        [expression.bindparam("好", unicode_str)],
        from_obj=table_one_row,
    ).scalar()
    assert returned_str == unicode_str 

def test_unicode(self, engine, conn):
        unicode_str = "密林"
        one_row = Table("one_row", MetaData(bind=engine))
        returned_str = sqlalchemy.select(
            [expression.bindparam("あまぞん", unicode_str)], from_obj=one_row,
        ).scalar()
        self.assertEqual(returned_str, unicode_str) 

def test_unicode(self, engine, conn):
        unicode_str = "密林"
        one_row = Table("one_row", MetaData(bind=engine))
        returned_str = sqlalchemy.select(
            [expression.bindparam("あまぞん", unicode_str)], from_obj=one_row,
        ).scalar()
        self.assertEqual(returned_str, unicode_str) 

def test_raw_insert_with_executemany(sa_connect):
    conn = await sa_connect()
    # with pytest.raises(sa.ArgumentError):
    await conn.execute(
        "INSERT INTO sa_tbl (id, name) VALUES (%(id)s, %(name)s)",
        [{"id": 2, "name": 'third'}, {"id": 3, "name": 'forth'}])
    await conn.execute(
        tbl.update().where(
            tbl.c.id == bindparam("id")
        ).values(
            {"name": bindparam("name")}
        ),
        [
            {"id": 2, "name": "t2"},
            {"id": 3, "name": "t3"}
        ]
    )
    with pytest.raises(sa.ArgumentError):
        await conn.execute(
            DropTable(tbl),
            [{}, {}]
        )
    with pytest.raises(sa.ArgumentError):
        await conn.execute(
            {},
            [{}, {}]
        ) 

def upgrade():
    metadata = sa.MetaData(naming_convention=convention)
    # partial table to be preserved and referred
    users = sa.Table(
        'users', metadata,
        IDColumn('uuid'),
        sa.Column('created_at', sa.DateTime(timezone=True),
                  server_default=sa.func.now()),
        sa.Column('modified_at', sa.DateTime(timezone=True),
                  server_default=sa.func.now(), onupdate=sa.func.current_timestamp()),
    )
    keypairs = sa.Table(
        'keypairs', metadata,
        sa.Column('access_key', sa.String(length=20), primary_key=True),
        sa.Column('created_at', sa.DateTime(timezone=True),
                  server_default=sa.func.now()),
        sa.Column('modified_at', sa.DateTime(timezone=True),
                  server_default=sa.func.now(), onupdate=sa.func.current_timestamp()),
    )

    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('keypairs', sa.Column('modified_at', sa.DateTime(timezone=True),
                                        server_default=sa.text('now()'), nullable=True))
    op.add_column('users', sa.Column('modified_at', sa.DateTime(timezone=True),
                                     server_default=sa.text('now()'), nullable=True))
    # ### end Alembic commands ###

    conn = op.get_bind()

    # Set user's modified_at with the value of created_at.
    query = sa.select([users.c.uuid, users.c.created_at]).select_from(users)
    updates = []
    for row in conn.execute(query).fetchall():
        updates.append({'b_uuid': row['uuid'], 'modified_at': row['created_at']})
    if updates:
        query = (sa.update(users)
                   .values(modified_at=bindparam('modified_at'))
                   .where(users.c.uuid == bindparam('b_uuid')))
        conn.execute(query, updates)

    # Set keypairs's modified_at with the value of created_at.
    query = sa.select([keypairs.c.access_key, keypairs.c.created_at]).select_from(keypairs)
    updates = []
    for row in conn.execute(query).fetchall():
        updates.append({'b_access_key': row['access_key'], 'modified_at': row['created_at']})
    if updates:
        query = (sa.update(keypairs)
                   .values(modified_at=bindparam('modified_at'))
                   .where(keypairs.c.access_key == bindparam('b_access_key')))
        conn.execute(query, updates)