Produce a “bound expression”.
The return value is an instance of BindParameter; this
is a 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 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 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 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:
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 CRUD statements as far as the “VALUES”
portion is concerned. The 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 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 Connection.execute() method.
- Parameters
key – the key (e.g. the name) for this bind param.
Will be used in the generated
SQL statement for dialects that use named parameters. This
value may be modified when part of a compilation operation,
if other BindParameter objects exist with the same
key, or if its length is too long and truncation is
required.
value – Initial value for this bind param. Will be used at statement
execution time as the value for this parameter passed to the
DBAPI, if no other value is indicated to the statement execution
method for this particular parameter name. Defaults to None.
callable_ – A callable function that takes the place of “value”. The function
will be called at statement execution time to determine the
ultimate value. Used for scenarios where the actual bind
value cannot be determined at the point at which the clause
construct is created, but embedded bind values are still desirable.
type_ –
A TypeEngine class or instance representing an optional
datatype for this bindparam(). If not passed, a type
may be determined automatically for the bind, based on the given
value; for example, trivial Python types such as str,
int, bool
may result in the String, Integer or
Boolean types being automatically selected.
The type of a bindparam() is significant especially in that
the type will apply pre-processing to the value before it is
passed to the database. For example, a bindparam() which
refers to a datetime value, and is specified as holding the
DateTime type, may apply conversion needed to the
value (such as stringification on SQLite) before passing the value
to the database.
unique – if True, the key name of this BindParameter will be
modified if another BindParameter of the same name
already has been located within the containing
expression. This flag is used generally by the internals
when producing so-called “anonymous” bound expressions, it
isn’t generally applicable to explicitly-named bindparam()
constructs.
required – If True, a value is required at execution time. If not passed,
it defaults to True if neither bindparam.value
or bindparam.callable were passed. If either of these
parameters are present, then bindparam.required
defaults to False.
quote – True if this parameter name requires quoting and is not
currently known as a SQLAlchemy reserved word; this currently
only applies to the Oracle backend, where bound names must
sometimes be quoted.
isoutparam – if True, the parameter should be treated like a stored procedure
“OUT” parameter. This applies to backends such as Oracle which
support OUT parameters.
expanding –
if True, this parameter will be treated as an “expanding” parameter
at execution time; the parameter value is expected to be a sequence,
rather than a scalar value, and the string SQL statement will
be transformed on a per-execution basis to accommodate the sequence
with a variable number of parameter slots passed to the DBAPI.
This is to allow statement caching to be used in conjunction with
an IN clause.
Note
The “expanding” feature does not support “executemany”-
style parameter sets.
Changed in version 1.3: the “expanding” bound parameter feature now
supports empty lists.