898
958
If you don't know what that SQL means, don't worry ! The secret tribe of
899
959
Oracle DBAs don't want their black magic being found out ;).
901
Intro to Generative Selects
902
================================================
904
We've now gained the ability to construct very sophisticated statements. We
905
can use all kinds of operators, table constructs, text, joins, and aliases.
906
The point of all of this, as mentioned earlier, is not that it's an "easier"
907
or "better" way to write SQL than just writing a SQL statement yourself; the
908
point is that it's better for writing *programmatically generated* SQL which
909
can be morphed and adapted as needed in automated scenarios.
911
To support this, the :func:`.select` construct we've been working with
912
supports piecemeal construction, in addition to the "all at once" method we've
913
been doing. Suppose you're writing a search function, which receives criterion
914
and then must construct a select from it. To accomplish this, upon each
915
criterion encountered, you apply "generative" criterion to an existing
916
:func:`.select` construct with new elements, one at a time. We start with a
917
basic :func:`.select` constructed with the shortcut method available on the
920
.. sourcecode:: pycon+sql
922
>>> query = users.select()
923
>>> print query # doctest: +NORMALIZE_WHITESPACE
924
SELECT users.id, users.name, users.fullname
927
We encounter search criterion of "name='jack'". So we apply WHERE criterion
930
.. sourcecode:: pycon+sql
932
>>> query = query.where(users.c.name=='jack')
934
Next, we encounter that they'd like the results in descending order by full
935
name. We apply ORDER BY, using an extra modifier ``desc``:
937
.. sourcecode:: pycon+sql
939
>>> query = query.order_by(users.c.fullname.desc())
941
We also come across that they'd like only users who have an address at MSN. A
942
quick way to tack this on is by using an EXISTS clause, which we correlate to
943
the ``users`` table in the enclosing SELECT:
945
.. sourcecode:: pycon+sql
947
>>> from sqlalchemy.sql import exists
948
>>> query = query.where(
949
... exists([addresses.c.id],
950
... and_(addresses.c.user_id==users.c.id, addresses.c.email_address.like('%@msn.com'))
951
... ).correlate(users))
953
And finally, the application also wants to see the listing of email addresses
954
at once; so to save queries, we outerjoin the ``addresses`` table (using an
955
outer join so that users with no addresses come back as well; since we're
956
programmatic, we might not have kept track that we used an EXISTS clause
957
against the ``addresses`` table too...). Additionally, since the ``users`` and
958
``addresses`` table both have a column named ``id``, let's isolate their names
959
from each other in the COLUMNS clause by using labels:
961
.. sourcecode:: pycon+sql
963
>>> query = query.column(addresses).select_from(users.outerjoin(addresses)).apply_labels()
965
Let's bake for .0001 seconds and see what rises:
967
.. sourcecode:: pycon+sql
969
>>> conn.execute(query).fetchall() # doctest: +NORMALIZE_WHITESPACE
970
{opensql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, addresses.email_address AS addresses_email_address
971
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
972
WHERE users.name = ? AND (EXISTS (SELECT addresses.id
974
WHERE addresses.user_id = users.id AND addresses.email_address LIKE ?)) ORDER BY users.fullname DESC
975
('jack', '%@msn.com')
976
{stop}[(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com'), (1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')]
978
The generative approach is about starting small, adding one thing at a time,
979
to arrive with a full statement.
981
Transforming a Statement
982
------------------------
984
We've seen how methods like :meth:`.Select.where` and :meth:`._SelectBase.order_by` are
985
part of the so-called *Generative* family of methods on the :func:`.select` construct,
986
where one :func:`.select` copies itself to return a new one with modifications.
987
SQL constructs also support another form of generative behavior which is
988
the *transformation*. This is an advanced technique that most core applications
989
won't use directly; however, it is a system which the ORM relies on heavily,
990
and can be useful for any system that deals with generalized behavior of Core SQL
993
Using a transformation we can take our ``users``/``addresses`` query and replace
994
all occurrences of ``addresses`` with an alias of itself. That is, anywhere
995
that ``addresses`` is referred to in the original query, the new query will
996
refer to ``addresses_1``, which is selected as ``addresses AS addresses_1``.
997
The :meth:`.FromClause.replace_selectable` method can achieve this:
999
.. sourcecode:: pycon+sql
1001
>>> a1 = addresses.alias()
1002
>>> query = query.replace_selectable(addresses, a1)
1003
>>> print query # doctest: +NORMALIZE_WHITESPACE
1004
{opensql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address
1005
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
1006
WHERE users.name = :name_1 AND (EXISTS (SELECT addresses_1.id
1007
FROM addresses AS addresses_1
1008
WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE :email_address_1)) ORDER BY users.fullname DESC
1010
For a query such as the above, we can access the columns referred
1011
to by the ``a1`` alias in a result set using the :class:`.Column` objects
1012
present directly on ``a1``:
1014
.. sourcecode:: pycon+sql
1016
{sql}>>> for row in conn.execute(query):
1017
... print "Name:", row[users.c.name], "; Email Address", row[a1.c.email_address] # doctest: +NORMALIZE_WHITESPACE
1018
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address
1019
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
1020
WHERE users.name = ? AND (EXISTS (SELECT addresses_1.id
1021
FROM addresses AS addresses_1
1022
WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE ?)) ORDER BY users.fullname DESC
1023
('jack', '%@msn.com')
1024
{stop}Name: jack ; Email Address jack@yahoo.com
1025
Name: jack ; Email Address jack@msn.com
1028
962
================
1245
1209
>>> u = except_(
1247
... addresses.select(addresses.c.email_address.like('%@yahoo.com')),
1248
... addresses.select(addresses.c.email_address.like('%@msn.com'))
1211
... addresses.select().
1212
... where(addresses.c.email_address.like('%@yahoo.com')),
1213
... addresses.select().
1214
... where(addresses.c.email_address.like('%@msn.com'))
1249
1215
... ).alias().select(), # apply subquery here
1250
1216
... addresses.select(addresses.c.email_address.like('%@msn.com'))
1252
{sql}>>> print conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE
1218
{sql}>>> conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE
1253
1219
SELECT anon_1.id, anon_1.user_id, anon_1.email_address
1254
1220
FROM (SELECT addresses.id AS id, addresses.user_id AS user_id,
1255
addresses.email_address AS email_address FROM addresses
1256
WHERE addresses.email_address LIKE ? UNION SELECT addresses.id AS id,
1257
addresses.user_id AS user_id, addresses.email_address AS email_address
1258
FROM addresses WHERE addresses.email_address LIKE ?) AS anon_1 EXCEPT
1221
addresses.email_address AS email_address
1223
WHERE addresses.email_address LIKE ?
1225
SELECT addresses.id AS id,
1226
addresses.user_id AS user_id,
1227
addresses.email_address AS email_address
1229
WHERE addresses.email_address LIKE ?) AS anon_1
1259
1231
SELECT addresses.id, addresses.user_id, addresses.email_address
1261
1233
WHERE addresses.email_address LIKE ?
1262
1234
('%@yahoo.com', '%@msn.com', '%@msn.com')
1263
1235
{stop}[(1, 1, u'jack@yahoo.com')]
1269
To embed a SELECT in a column expression, use
1270
:func:`~sqlalchemy.sql.expression._SelectBaseMixin.as_scalar`:
1272
.. sourcecode:: pycon+sql
1274
{sql}>>> print conn.execute(select([ # doctest: +NORMALIZE_WHITESPACE
1276
... select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).as_scalar()
1278
SELECT users.name, (SELECT count(addresses.id) AS count_1
1242
A scalar select is a SELECT that returns exactly one row and one
1243
column. It can then be used as a column expression. A scalar select
1244
is often a :term:`correlated subquery`, which relies upon the enclosing
1245
SELECT statement in order to acquire at least one of its FROM clauses.
1247
The :func:`.select` construct can be modified to act as a
1248
column expression by calling either the :meth:`~.SelectBase.as_scalar`
1249
or :meth:`~.SelectBase.label` method:
1251
.. sourcecode:: pycon+sql
1253
>>> stmt = select([func.count(addresses.c.id)]).\
1254
... where(users.c.id == addresses.c.user_id).\
1257
The above construct is now a :class:`~.expression.ScalarSelect` object,
1258
and is no longer part of the :class:`~.expression.FromClause` hierarchy;
1259
it instead is within the :class:`~.expression.ColumnElement` family of
1260
expression constructs. We can place this construct the same as any
1261
other column within another :func:`.select`:
1263
.. sourcecode:: pycon+sql
1265
>>> conn.execute(select([users.c.name, stmt])).fetchall() # doctest: +NORMALIZE_WHITESPACE
1266
{opensql}SELECT users.name, (SELECT count(addresses.id) AS count_1
1280
1268
WHERE users.id = addresses.user_id) AS anon_1
1283
{stop}[(u'jack', 2), (u'wendy', 2), (u'fred', 0), (u'mary', 0)]
1271
{stop}[(u'jack', 2), (u'wendy', 2)]
1285
Alternatively, applying a ``label()`` to a select evaluates it as a scalar as
1273
To apply a non-anonymous column name to our scalar select, we create
1274
it using :meth:`.SelectBase.label` instead:
1288
1276
.. sourcecode:: pycon+sql
1290
{sql}>>> print conn.execute(select([ # doctest: +NORMALIZE_WHITESPACE
1292
... select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).label('address_count')
1294
SELECT users.name, (SELECT count(addresses.id) AS count_1
1278
>>> stmt = select([func.count(addresses.c.id)]).\
1279
... where(users.c.id == addresses.c.user_id).\
1280
... label("address_count")
1281
>>> conn.execute(select([users.c.name, stmt])).fetchall() # doctest: +NORMALIZE_WHITESPACE
1282
{opensql}SELECT users.name, (SELECT count(addresses.id) AS count_1
1296
1284
WHERE users.id = addresses.user_id) AS address_count
1299
{stop}[(u'jack', 2), (u'wendy', 2), (u'fred', 0), (u'mary', 0)]
1287
{stop}[(u'jack', 2), (u'wendy', 2)]
1289
.. _correlated_subqueries:
1301
1291
Correlated Subqueries
1302
1292
---------------------
1304
Notice in the examples on "scalar selects", the FROM clause of each embedded
1294
Notice in the examples on :ref:`scalar_selects`, the FROM clause of each embedded
1305
1295
select did not contain the ``users`` table in its FROM clause. This is because
1306
SQLAlchemy automatically attempts to correlate embedded FROM objects to that
1307
of an enclosing query. To disable this, or to specify explicit FROM clauses to
1308
be correlated, use ``correlate()``::
1310
>>> s = select([users.c.name], users.c.id==select([users.c.id]).correlate(None))
1311
>>> print s # doctest: +NORMALIZE_WHITESPACE
1314
WHERE users.id = (SELECT users.id
1317
>>> s = select([users.c.name, addresses.c.email_address], users.c.id==
1318
... select([users.c.id], users.c.id==addresses.c.user_id).correlate(addresses)
1320
>>> print s # doctest: +NORMALIZE_WHITESPACE
1321
SELECT users.name, addresses.email_address
1322
FROM users, addresses
1323
WHERE users.id = (SELECT users.id
1325
WHERE users.id = addresses.user_id)
1296
SQLAlchemy automatically :term:`correlates` embedded FROM objects to that
1297
of an enclosing query, if present, and if the inner SELECT statement would
1298
still have at least one FROM clause of its own. For example:
1300
.. sourcecode:: pycon+sql
1302
>>> stmt = select([addresses.c.user_id]).\
1303
... where(addresses.c.user_id == users.c.id).\
1304
... where(addresses.c.email_address == 'jack@yahoo.com')
1305
>>> enclosing_stmt = select([users.c.name]).where(users.c.id == stmt)
1306
>>> conn.execute(enclosing_stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1307
{opensql}SELECT users.name
1309
WHERE users.id = (SELECT addresses.user_id
1311
WHERE addresses.user_id = users.id
1312
AND addresses.email_address = ?)
1316
Auto-correlation will usually do what's expected, however it can also be controlled.
1317
For example, if we wanted a statement to correlate only to the ``addresses`` table
1318
but not the ``users`` table, even if both were present in the enclosing SELECT,
1319
we use the :meth:`~.Select.correlate` method to specify those FROM clauses that
1322
.. sourcecode:: pycon+sql
1324
>>> stmt = select([users.c.id]).\
1325
... where(users.c.id == addresses.c.user_id).\
1326
... where(users.c.name == 'jack').\
1327
... correlate(addresses)
1328
>>> enclosing_stmt = select(
1329
... [users.c.name, addresses.c.email_address]).\
1330
... select_from(users.join(addresses)).\
1331
... where(users.c.id == stmt)
1332
>>> conn.execute(enclosing_stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1333
{opensql}SELECT users.name, addresses.email_address
1334
FROM users JOIN addresses ON users.id = addresses.user_id
1335
WHERE users.id = (SELECT users.id
1337
WHERE users.id = addresses.user_id AND users.name = ?)
1339
{stop}[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]
1341
To entirely disable a statement from correlating, we can pass ``None``
1344
.. sourcecode:: pycon+sql
1346
>>> stmt = select([users.c.id]).\
1347
... where(users.c.name == 'wendy').\
1349
>>> enclosing_stmt = select([users.c.name]).\
1350
... where(users.c.id == stmt)
1351
>>> conn.execute(enclosing_stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1352
{opensql}SELECT users.name
1354
WHERE users.id = (SELECT users.id
1356
WHERE users.name = ?)
1360
We can also control correlation via exclusion, using the :meth:`.Select.correlate_except`
1361
method. Such as, we can write our SELECT for the ``users`` table
1362
by telling it to correlate all FROM clauses except for ``users``:
1364
.. sourcecode:: pycon+sql
1366
>>> stmt = select([users.c.id]).\
1367
... where(users.c.id == addresses.c.user_id).\
1368
... where(users.c.name == 'jack').\
1369
... correlate_except(users)
1370
>>> enclosing_stmt = select(
1371
... [users.c.name, addresses.c.email_address]).\
1372
... select_from(users.join(addresses)).\
1373
... where(users.c.id == stmt)
1374
>>> conn.execute(enclosing_stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1375
{opensql}SELECT users.name, addresses.email_address
1376
FROM users JOIN addresses ON users.id = addresses.user_id
1377
WHERE users.id = (SELECT users.id
1379
WHERE users.id = addresses.user_id AND users.name = ?)
1381
{stop}[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]
1327
1383
Ordering, Grouping, Limiting, Offset...ing...
1328
1384
---------------------------------------------
1331
The :func:`.select` function can take keyword arguments ``order_by``,
1332
``group_by`` (as well as ``having``), ``limit``, and ``offset``. There's also
1333
``distinct=True``. These are all also available as generative functions.
1334
``order_by()`` expressions can use the modifiers ``asc()`` or ``desc()`` to
1335
indicate ascending or descending.
1337
.. sourcecode:: pycon+sql
1339
>>> s = select([addresses.c.user_id, func.count(addresses.c.id)]).\
1340
... group_by(addresses.c.user_id).having(func.count(addresses.c.id)>1)
1341
{sql}>>> print conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE
1342
SELECT addresses.user_id, count(addresses.id) AS count_1
1343
FROM addresses GROUP BY addresses.user_id
1344
HAVING count(addresses.id) > ?
1346
{stop}[(1, 2), (2, 2)]
1348
>>> s = select([addresses.c.email_address, addresses.c.id]).distinct().\
1349
... order_by(addresses.c.email_address.desc(), addresses.c.id)
1350
{sql}>>> conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE
1351
SELECT DISTINCT addresses.email_address, addresses.id
1352
FROM addresses ORDER BY addresses.email_address DESC, addresses.id
1354
{stop}[(u'www@www.org', 3), (u'wendy@aol.com', 4), (u'jack@yahoo.com', 1), (u'jack@msn.com', 2)]
1356
>>> s = select([addresses]).offset(1).limit(1)
1357
{sql}>>> print conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE
1358
SELECT addresses.id, addresses.user_id, addresses.email_address
1362
{stop}[(2, 1, u'jack@msn.com')]
1386
Ordering is done by passing column expressions to the
1387
:meth:`~.SelectBase.order_by` method:
1389
.. sourcecode:: pycon+sql
1391
>>> stmt = select([users.c.name]).order_by(users.c.name)
1392
>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1393
{opensql}SELECT users.name
1394
FROM users ORDER BY users.name
1396
{stop}[(u'jack',), (u'wendy',)]
1398
Ascending or descending can be controlled using the :meth:`~.ColumnElement.asc`
1399
and :meth:`~.ColumnElement.desc` modifiers:
1401
.. sourcecode:: pycon+sql
1403
>>> stmt = select([users.c.name]).order_by(users.c.name.desc())
1404
>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1405
{opensql}SELECT users.name
1406
FROM users ORDER BY users.name DESC
1408
{stop}[(u'wendy',), (u'jack',)]
1410
Grouping refers to the GROUP BY clause, and is usually used in conjunction
1411
with aggregate functions to establish groups of rows to be aggregated.
1412
This is provided via the :meth:`~.SelectBase.group_by` method:
1414
.. sourcecode:: pycon+sql
1416
>>> stmt = select([users.c.name, func.count(addresses.c.id)]).\
1417
... select_from(users.join(addresses)).\
1418
... group_by(users.c.name)
1419
>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1420
{opensql}SELECT users.name, count(addresses.id) AS count_1
1421
FROM users JOIN addresses
1422
ON users.id = addresses.user_id
1425
{stop}[(u'jack', 2), (u'wendy', 2)]
1427
HAVING can be used to filter results on an aggregate value, after GROUP BY has
1428
been applied. It's available here via the :meth:`~.Select.having`
1431
.. sourcecode:: pycon+sql
1433
>>> stmt = select([users.c.name, func.count(addresses.c.id)]).\
1434
... select_from(users.join(addresses)).\
1435
... group_by(users.c.name).\
1436
... having(func.length(users.c.name) > 4)
1437
>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1438
{opensql}SELECT users.name, count(addresses.id) AS count_1
1439
FROM users JOIN addresses
1440
ON users.id = addresses.user_id
1442
HAVING length(users.name) > ?
1444
{stop}[(u'wendy', 2)]
1446
A common system of dealing with duplicates in composed SELECT statments
1447
is the DISTINCT modifier. A simple DISTINCT clause can be added using the
1448
:meth:`.Select.distinct` method:
1450
.. sourcecode:: pycon+sql
1452
>>> stmt = select([users.c.name]).\
1453
... where(addresses.c.email_address.
1454
... contains(users.c.name)).\
1456
>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1457
{opensql}SELECT DISTINCT users.name
1458
FROM users, addresses
1459
WHERE addresses.email_address LIKE '%%' || users.name || '%%'
1461
{stop}[(u'jack',), (u'wendy',)]
1463
Most database backends support a system of limiting how many rows
1464
are returned, and the majority also feature a means of starting to return
1465
rows after a given "offset". While common backends like Postgresql,
1466
MySQL and SQLite support LIMIT and OFFSET keywords, other backends
1467
need to refer to more esoteric features such as "window functions"
1468
and row ids to achieve the same effect. The :meth:`~.Select.limit`
1469
and :meth:`~.Select.offset` methods provide an easy abstraction
1470
into the current backend's methodology:
1472
.. sourcecode:: pycon+sql
1474
>>> stmt = select([users.c.name, addresses.c.email_address]).\
1475
... select_from(users.join(addresses)).\
1476
... limit(1).offset(1)
1477
>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
1478
{opensql}SELECT users.name, addresses.email_address
1479
FROM users JOIN addresses ON users.id = addresses.user_id
1482
{stop}[(u'jack', u'jack@msn.com')]
1364
1485
.. _inserts_and_updates:
1369
Finally, we're back to INSERT for some more detail. The
1370
:func:`~sqlalchemy.sql.expression.insert` construct provides a :meth:`~.ValuesBase.values`
1371
method which can be used to send any value or clause expression to the VALUES
1372
portion of the INSERT::
1374
# insert from a function
1375
users.insert().values(id=12, name=func.upper('jack'))
1377
# insert from a concatenation expression
1378
addresses.insert().values(email_address = name + '@' + host)
1380
``values()`` can be mixed with per-execution values::
1383
users.insert().values(name=func.upper('jack')),
1384
fullname='Jack Jones'
1387
:func:`~sqlalchemy.sql.expression.bindparam` constructs can be passed, however
1388
the names of the table's columns are reserved for the "automatic" generation
1391
users.insert().values(id=bindparam('_id'), name=bindparam('_name'))
1393
# insert many rows at once:
1395
users.insert().values(id=bindparam('_id'), name=bindparam('_name')),
1397
{'_id':1, '_name':'name1'},
1398
{'_id':2, '_name':'name2'},
1399
{'_id':3, '_name':'name3'},
1403
An UPDATE statement is emitted using the :func:`.update` construct. These
1404
work much like an INSERT, except there is an additional WHERE clause
1487
Inserts, Updates and Deletes
1488
============================
1490
We've seen :meth:`~.TableClause.insert` demonstrated
1491
earlier in this tutorial. Where :meth:`~.TableClause.insert`
1492
prodces INSERT, the :meth:`~.TableClause.update`
1493
method produces UPDATE. Both of these constructs feature
1494
a method called :meth:`~.ValuesBase.values` which specifies
1495
the VALUES or SET clause of the statement.
1497
The :meth:`~.ValuesBase.values` method accommodates any column expression
1500
.. sourcecode:: pycon+sql
1502
>>> stmt = users.update().\
1503
... values(fullname="Fullname: " + users.c.name)
1504
>>> conn.execute(stmt) #doctest: +ELLIPSIS
1505
{opensql}UPDATE users SET fullname=(? || users.name)
1508
{stop}<sqlalchemy.engine.result.ResultProxy object at 0x...>
1510
When using :meth:`~.TableClause.insert` or :meth:`~.TableClause.update`
1511
in an "execute many" context, we may also want to specify named
1512
bound parameters which we can refer to in the argument list.
1513
The two constructs will automatically generate bound placeholders
1514
for any column names passed in the dictionaries sent to
1515
:meth:`~.Connection.execute` at execution time. However, if we
1516
wish to use explicitly targeted named parameters with composed expressions,
1517
we need to use the :func:`~.expression.bindparam` construct.
1518
When using :func:`~.expression.bindparam` with
1519
:meth:`~.TableClause.insert` or :meth:`~.TableClause.update`,
1520
the names of the table's columns themselves are reserved for the
1521
"automatic" generation of bind names. We can combine the usage
1522
of implicitly available bind names and explicitly named parameters
1523
as in the example below:
1525
.. sourcecode:: pycon+sql
1527
>>> stmt = users.insert().\
1528
... values(name=bindparam('_name') + " .. name")
1529
>>> conn.execute(stmt, [ # doctest: +ELLIPSIS
1530
... {'id':4, '_name':'name1'},
1531
... {'id':5, '_name':'name2'},
1532
... {'id':6, '_name':'name3'},
1534
{opensql}INSERT INTO users (id, name) VALUES (?, (? || ?))
1535
((4, 'name1', ' .. name'), (5, 'name2', ' .. name'), (6, 'name3', ' .. name'))
1537
<sqlalchemy.engine.result.ResultProxy object at 0x...>
1539
An UPDATE statement is emitted using the :meth:`~.TableClause.update` construct. This
1540
works much like an INSERT, except there is an additional WHERE clause
1405
1541
that can be specified:
1407
1543
.. sourcecode:: pycon+sql
1409
>>> # change 'jack' to 'ed'
1410
{sql}>>> conn.execute(users.update().
1411
... where(users.c.name=='jack').
1412
... values(name='ed')
1413
... ) #doctest: +ELLIPSIS
1414
UPDATE users SET name=? WHERE users.name = ?
1545
>>> stmt = users.update().\
1546
... where(users.c.name == 'jack').\
1547
... values(name='ed')
1549
>>> conn.execute(stmt) #doctest: +ELLIPSIS
1550
{opensql}UPDATE users SET name=? WHERE users.name = ?
1417
{stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>
1419
>>> # use bind parameters
1420
>>> u = users.update().\
1421
... where(users.c.name==bindparam('oldname')).\
1553
{stop}<sqlalchemy.engine.result.ResultProxy object at 0x...>
1555
When using :meth:`~.TableClause.update` in an "execute many" context,
1556
we may wish to also use explicitly named bound parameters in the
1557
WHERE clause. Again, :func:`~.expression.bindparam` is the construct
1558
used to achieve this:
1560
.. sourcecode:: pycon+sql
1562
>>> stmt = users.update().\
1563
... where(users.c.name == bindparam('oldname')).\
1422
1564
... values(name=bindparam('newname'))
1423
{sql}>>> conn.execute(u, oldname='jack', newname='ed') #doctest: +ELLIPSIS
1424
UPDATE users SET name=? WHERE users.name = ?
1427
{stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>
1429
>>> # with binds, you can also update many rows at once
1430
{sql}>>> conn.execute(u,
1565
>>> conn.execute(stmt, [
1431
1566
... {'oldname':'jack', 'newname':'ed'},
1432
1567
... {'oldname':'wendy', 'newname':'mary'},
1433
1568
... {'oldname':'jim', 'newname':'jake'},
1434
... ) #doctest: +ELLIPSIS
1435
UPDATE users SET name=? WHERE users.name = ?
1436
[('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')]
1569
... ]) #doctest: +ELLIPSIS
1570
{opensql}UPDATE users SET name=? WHERE users.name = ?
1571
(('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim'))
1438
{stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>
1573
{stop}<sqlalchemy.engine.result.ResultProxy object at 0x...>
1440
>>> # update a column to an expression.:
1441
{sql}>>> conn.execute(users.update().
1442
... values(fullname="Fullname: " + users.c.name)
1443
... ) #doctest: +ELLIPSIS
1444
UPDATE users SET fullname=(? || users.name)
1447
{stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>
1449
1576
Correlated Updates
1450
1577
------------------
added
removed
doc/_sources/core/tutorial.txt
