The concepts of creating SQL expressions have been introduced. What’s left are more variants of the same themes. So now we’ll catalog the rest of the important things we’ll need to know.
Bind Parameter Objects
Throughout all these examples, SQLAlchemy is busy creating bind parameters wherever literal expressions occur. You can also specify your own bind parameters with your own names, and use the same statement repeatedly. The bindparam() construct is used to produce a bound parameter with a given name. While SQLAlchemy always refers to bound parameters by name on the API side, the database dialect converts to the appropriate named or positional style at execution time, as here where it converts to positional for SQLite:
Another important aspect of bindparam() is that it may be assigned a type. The type of the bind parameter will determine its behavior within expressions and also how the data bound to it is processed before being sent off to the database:
bindparam() constructs of the same name can also be used multiple times, where only a single named value is needed in the execute parameters:
SQL functions are created using the func keyword, which generates functions using attribute access:
By “generates”, we mean that any SQL function is created based on the word you choose:
Certain function names are known by SQLAlchemy, allowing special behavioral rules to be applied. Some for example are “ANSI” functions, which mean they don’t get the parenthesis added after them, such as CURRENT_TIMESTAMP:
Functions are most typically used in the columns clause of a select statement, and can also be labeled as well as given a type. Labeling a function is recommended so that the result can be targeted in a result row based on a string name, and assigning it a type is required when you need result-set processing to occur, such as for Unicode conversion and date conversions. Below, we use the result function scalar() to just read the first column of the first row and then close the result; the label, even though present, is not important in this case:
Inserts, Updates and Deletes
We’ve seen insert() demonstrated earlier in this tutorial. Where insert() produces INSERT, the update() method produces UPDATE. Both of these constructs feature a method called values() which specifies the VALUES or SET clause of the statement.
The values() method accommodates any column expression as a value:
When using insert() or update() in an “execute many” context, we may also want to specify named bound parameters which we can refer to in the argument list. The two constructs will automatically generate bound placeholders for any column names passed in the dictionaries sent to execute() at execution time. However, if we wish to use explicitly targeted named parameters with composed expressions, we need to use the bindparam() construct. When using bindparam()with insert() or update(), the names of the table’s columns themselves are reserved for the “automatic” generation of bind names. We can combine the usage of implicitly available bind names and explicitly named parameters as in the example below:
A correlated update lets you update a table using selection from another table, or the same table:
Multiple Table Updates
The PostgreSQL, Microsoft SQL Server, and MySQL backends all support UPDATE statements that refer to multiple tables. For PG and MSSQL, this is the “UPDATE FROM” syntax, which updates one table at a time, but can reference additional tables in an additional “FROM” clause that can then be referenced in the WHERE clause directly. On MySQL, multiple tables can be embedded into a single UPDATE statement separated by a comma. The SQLAlchemy update() construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause:
When using MySQL, columns from each table can be assigned to in the SET clause directly, using the dictionary form passed to Update.values():
SQLAlchemy doesn’t do anything special when these constructs are used on a non-supporting database. The UPDATE FROM syntax generates by default when multiple tables are present, and the statement will be rejected by the database if this syntax is not supported.
The default behavior of the update() construct when rendering the SET clauses is to render them using the column ordering given in the originating Table object. This is an important behavior, since it means that the rendering of a particular UPDATE statement with particular columns will be rendered the same each time, which has an impact on query caching systems that rely on the form of the statement, either client side or server side. Since the parameters themselves are passed to the Update.values() method as Python dictionary keys, there is no other fixed ordering available.
However in some cases, the order of parameters rendered in the SET clause of an UPDATE statement can be significant. The main example of this is when using MySQL and providing updates to column values based on that of other column values. The end result of the following statement:
Finally, a delete. This is accomplished easily enough using the delete() construct:
Both of update() and delete() are associated with matched row counts. This is a number indicating the number of rows that were matched by the WHERE clause. Note that by “matched”, this includes rows where no UPDATE actually took place. The value is available as rowcount:
* Expression Language Reference: SQL Statements and Expressions API
* Database Metadata Reference: Describing Databases with MetaData
* Engine Reference: Engine Configuration
* Connection Reference: Working with Engines and Connections
* Types Reference: Column and Data Types