From 6899c0dc1bb01c6136290ba5e45965bb9cbf84d3 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 8 Aug 2007 00:17:29 +0000 Subject: [PATCH] added docs on connectionless/implicit --- doc/build/content/sqlexpression.txt | 24 ++++++++++++++++++++---- 1 file changed, 20 insertions(+), 4 deletions(-) diff --git a/doc/build/content/sqlexpression.txt b/doc/build/content/sqlexpression.txt index 702bc50990..3847968bff 100644 --- a/doc/build/content/sqlexpression.txt +++ b/doc/build/content/sqlexpression.txt @@ -97,7 +97,7 @@ Above, while the `values` keyword limited the VALUES clause to just two columns, >>> ins.compile().params #doctest: +NORMALIZE_WHITESPACE ClauseParameters:{'fullname': 'Jack Jones', 'name': 'jack'} -## Executing Inserts {@name=executing} +## Executing {@name=executing} The interesting part of an `Insert` is executing it. In this tutorial, we will generally focus on the most explicit method of executing a SQL construct, and later touch upon some "shortcut" ways to do it. The `engine` object we created is a repository for database connections capable of issuing SQL to the database. To acquire a connection, we use the `connect()` method: @@ -130,7 +130,7 @@ What about the `result` variable we got when we called `execute()` ? As the SQL The value of `1` was automatically generated by SQLite, but only because we did not specify the `id` column in our `Insert` statement; otherwise, our explicit value would have been used. In either case, SQLAlchemy always knows how to get at a newly generated primary key value, even though the method of generating them is different across different databases; each databases' `Dialect` knows the specific steps needed to determine the correct value (or values; note that `last_inserted_ids()` returns a list so that it supports composite primary keys). -## Executing Multiple Inserts {@name=execmany} +## Executing Multiple Statements {@name=execmany} Our insert example above was intentionally a little drawn out to show some various behaviors of expression language constructs. In the usual case, an `Insert` statement is usually compiled against the parameters sent to the `execute()` method on `Connection`, so that there's no need to use the `values` keyword with `Insert`. Lets create a generic `Insert` statement again and use it in the "normal" way: @@ -162,6 +162,20 @@ Above, we again relied upon SQLite's automatic generation of primary key identif When executing multiple sets of parameters, each dictionary must have the **same** set of keys; i.e. you cant have fewer keys in some dictionaries than others. This is because the `Insert` statement is compiled against the **first** dictionary in the list, and its assumed that all subsequent argument dictionaries are compatible with that statement. +## Connectionless / Implicit Execution {@name=connectionless} + +We're executing our `Insert` using a `Connection`. There's two options that allow you to not have to deal with the connection part. You can execute in the **connectionless** style, using the engine, which opens and closes a connection for you: + + {python} + result = engine.execute(users.insert(), name='fred', fullname="Fred Flintstone") + +and you can save even more steps than that, if you connect the `Engine` to the `MetaData` object we created earlier. When this is done, all SQL expressions which involve tables within the `MetaData` object will be automatically **bound** to the `Engine`. In this case, we call it **implicit execution**: + + {python} + metadata.bind = engine + result = users.insert().execute(name="mary", fullname="Mary Contrary") + + ## Selecting {@name=selecting} We began with inserts just so that our test database had some data in it. The more interesting part of the data is selecting it ! We'll cover UPDATE and DELETE statements later. The primary construct used to generate SELECT statements is the `select()` function: @@ -327,6 +341,8 @@ Where `||` is the string concatenation operator used on most databases. But not >>> print (users.c.name + users.c.fullname).compile(dialect=MySQLDialect()) concat(users.name, users.fullname) +The above illustrates the SQL that's generated for an `Engine` that's connected to a MySQL database (note that the `Dialect` is normally created behind the scenes; we created one above just to illustrate without using an engine). + If you have come across an operator which really isn't available, you can always use the `op()` method; this generates whatever operator you need: {python} @@ -498,7 +514,7 @@ When we create a `select()` construct, SQLAlchemy looks around at the tables we' SELECT users.fullname FROM users JOIN addresses ON addresses.email_address LIKE users.name || ? ['%'] - [(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)] + {stop}[(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)] The `outerjoin()` function just creates `LEFT OUTER JOIN` constructs. It's used just like `join()`: @@ -508,7 +524,7 @@ The `outerjoin()` function just creates `LEFT OUTER JOIN` constructs. It's used SELECT users.fullname FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id -Unless, of course, you're stuck in a gig using Oracle prior to version 9: +That's the output `outerjoin()` produces, unless, of course, you're stuck in a gig using Oracle prior to version 9, and you've set up your engine (which would be using `OracleDialect`) to use Oracle-specific SQL: {python} >>> from sqlalchemy.databases.oracle import OracleDialect -- 2.47.3