sqlalchemy.sql.elements.BindParameter

Produce a "bound expression".

The return value is an instance of .BindParameter; this is a _expression.ColumnElement subclass which represents a so-called "placeholder" value in a SQL expression, the value of which is supplied at the point at which the statement in executed against a database connection.

In SQLAlchemy, the .bindparam construct has the ability to carry along the actual value that will be ultimately used at expression time. In this way, it serves not just as a "placeholder" for eventual population, but also as a means of representing so-called "unsafe" values which should not be rendered directly in a SQL statement, but rather should be passed along to the :term:`DBAPI` as values which need to be correctly escaped and potentially handled for type-safety.

When using .bindparam explicitly, the use case is typically one of traditional deferment of parameters; the .bindparam construct accepts a name which can then be referred to at execution time:

from sqlalchemy import bindparam

stmt = select(users_table).\
            where(users_table.c.name == bindparam('username'))

The above statement, when rendered, will produce SQL similar to:

SELECT id, name FROM user WHERE name = :username

In order to populate the value of :username above, the value would typically be applied at execution time to a method like _engine.Connection.execute:

result = connection.execute(stmt, username='wendy')

Explicit use of .bindparam is also common when producing UPDATE or DELETE statements that are to be invoked multiple times, where the WHERE criterion of the statement is to change on each invocation, such as:

stmt = (users_table.update().
        where(user_table.c.name == bindparam('username')).
        values(fullname=bindparam('fullname'))
        )

connection.execute(
    stmt, [{"username": "wendy", "fullname": "Wendy Smith"},
           {"username": "jack", "fullname": "Jack Jones"},
           ]
)

SQLAlchemy's Core expression system makes wide use of .bindparam in an implicit sense. It is typical that Python literal values passed to virtually all SQL expression functions are coerced into fixed .bindparam constructs. For example, given a comparison operation such as:

expr = users_table.c.name == 'Wendy'

The above expression will produce a .BinaryExpression construct, where the left side is the _schema.Column object representing the name column, and the right side is a .BindParameter representing the literal value:

print(repr(expr.right))
BindParameter('%(4327771088 name)s', 'Wendy', type_=String())

The expression above will render SQL such as:

user.name = :name_1

Where the :name_1 parameter name is an anonymous name. The actual string Wendy is not in the rendered string, but is carried along where it is later used within statement execution. If we invoke a statement like the following:

stmt = select(users_table).where(users_table.c.name == 'Wendy')
result = connection.execute(stmt)

We would see SQL logging output as:

SELECT "user".id, "user".name
FROM "user"
WHERE "user".name = %(name_1)s
{'name_1': 'Wendy'}

Above, we see that Wendy is passed as a parameter to the database, while the placeholder :name_1 is rendered in the appropriate form for the target database, in this case the PostgreSQL database.

Similarly, .bindparam is invoked automatically when working with :term:`CRUD` statements as far as the "VALUES" portion is concerned. The _expression.insert construct produces an INSERT expression which will, at statement execution time, generate bound placeholders based on the arguments passed, as in:

stmt = users_table.insert()
result = connection.execute(stmt, name='Wendy')

The above will produce SQL output as:

INSERT INTO "user" (name) VALUES (%(name)s)
{'name': 'Wendy'}

The _expression.Insert construct, at compilation/execution time, rendered a single .bindparam mirroring the column name name as a result of the single name parameter we passed to the _engine.Connection.execute method.

Undocumented

Undocumented

Create a shallow copy of this ClauseElement.

This method may be used by a generative API. Its also used as part of the "deep" copy afforded by a traversal that combines the _copy_internals() method.

Undocumented

return an optional cache key.

The cache key is a tuple which can contain any series of objects that are hashable and also identifies this object uniquely within the presence of a larger SQL expression or statement, for the purposes of caching the resulting query.

The cache key should be based on the SQL compiled structure that would ultimately be produced. That is, two structures that are composed in exactly the same way should produce the same cache key; any difference in the structures that would affect the SQL string or the type handlers should result in a different cache key.

If a structure cannot produce a useful cache key, the NO_CACHE symbol should be added to the anon_map and the method should return None.

a hook to allow the right side of a binary expression to respond to a negation of the binary expression.

Used for the special case of expanding bind parameter with IN.

in the context of binary expression, convert the type of this object to the one given.

applies only to _expression.ColumnElement classes.

Produce a copy of this bound parameter that will enable the :paramref:`_sql.BindParameter.literal_execute` flag.

The :paramref:`_sql.BindParameter.literal_execute` flag will have the effect of the parameter rendered in the compiled SQL string using [POSTCOMPILE] form, which is a special form that is converted to be a rendering of the literal value of the parameter at SQL execution time. The rationale is to support caching of SQL statement strings that can embed per-statement literal values, such as LIMIT and OFFSET parameters, in the final SQL string that is passed to the DBAPI. Dialects in particular may want to use this method within custom compilation schemes.