~ubuntu-branches/debian/jessie/sqlalchemy/jessie : revision 35

20

21

var DOCUMENTATION_OPTIONS = {

22

URL_ROOT: '../',

23

VERSION: '0.8.2',

23

VERSION: '0.8.3',

24

COLLAPSE_MODINDEX: false,

25

FILE_SUFFIX: '.html'

26

};

67

</div>

68

69

70

Release: 0.8.2 | Release Date: July 3, 2013

70

Release: 0.8.3 | Release Date: October 26, 2013

71

72

73

</div>

240

<h2>Define and Create Tables<a class="headerlink" href="#define-and-create-tables" title="Permalink to this headline">¶</a></h2>

241

The SQL Expression Language constructs its expressions in most cases against

242

table columns. In SQLAlchemy, a column is most often represented by an object

243

called <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, and in all cases a

244

<a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> is associated with a

245

<a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. A collection of

246

<a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects and their associated child objects

243

called <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, and in all cases a

244

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> is associated with a

245

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. A collection of

246

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects and their associated child objects

247

is referred to as database metadata. In this tutorial we will explicitly

248

lay out several <a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects, but note that SA

249

can also “import” whole sets of <a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects

248

lay out several <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects, but note that SA

249

can also “import” whole sets of <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects

250

automatically from an existing database (this process is called table

251

reflection).

252

We define our tables all within a catalog called

253

<a class="reference internal" href="schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>, using the

254

<a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> construct, which resembles regular SQL

253

<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>, using the

254

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> construct, which resembles regular SQL

255

CREATE TABLE statements. We’ll make two tables, one of which represents

256

“users” in an application, and another which represents zero or more “email

257

addreses” for each row in the “users” table:

269

... Column('email_address', String, nullable=False)

270

... )</pre></div>

271

</div>

272

All about how to define <a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects, as well as

272

All about how to define <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects, as well as

273

how to create them from an existing database automatically, is described in

274

<a class="reference internal" href="schema.html">Schema Definition Language</a>.

275

Next, to tell the <a class="reference internal" href="schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> we’d actually like to

274

<a class="reference internal" href="metadata.html">Describing Databases with MetaData</a>.

275

Next, to tell the <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> we’d actually like to

276

create our selection of tables for real inside the SQLite database, we use

277

<a class="reference internal" href="schema.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal">create_all()</tt></a>, passing it the <tt class="docutils literal">engine</tt>

277

<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal">create_all()</tt></a>, passing it the <tt class="docutils literal">engine</tt>

278

instance which points to our database. This will check for the presence of

279

each table first before creating, so it’s safe to call multiple times:

280

<div class="highlight-pycon+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a>>>> metadata.create_all(engine)

315

SQLAlchemy other than when creating tables.

316

Additionally, Firebird and Oracle require sequences to generate new

317

primary key identifiers, and SQLAlchemy doesn’t generate or assume these

318

without being instructed. For that, you use the <a class="reference internal" href="schema.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> construct:

318

without being instructed. For that, you use the <a class="reference internal" href="defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> construct:

319

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import Sequence

320

Column('id', Integer, Sequence('user_id_seq'), primary_key=True)</pre></div>

321

</div>

322

A full, foolproof <a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is therefore:

322

A full, foolproof <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is therefore:

323

<div class="highlight-python"><div class="highlight"><pre>users = Table('users', metadata,

324

Column('id', Integer, Sequence('user_id_seq'), primary_key=True),

325

Column('name', String(50)),

327

Column('password', String(12))

328

)</pre></div>

329

</div>

330

We include this more verbose <a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> construct separately

330

We include this more verbose <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> construct separately

331

to highlight the difference between a minimal construct geared primarily

332

towards in-Python usage only, versus one that will be used to emit CREATE

333

TABLE statements on a particular set of backends with more stringent

337

338

<h2>Insert Expressions<a class="headerlink" href="#insert-expressions" title="Permalink to this headline">¶</a></h2>

339

The first SQL expression we’ll create is the

340

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> construct, which represents an

340

<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> construct, which represents an

341

INSERT statement. This is typically created relative to its target table:

342

<div class="highlight-python"><div class="highlight"><pre>>>> ins = users.insert()</pre></div>

343

</div>

356

Above, while the <tt class="docutils literal">values</tt> method limited the VALUES clause to just two

357

columns, the actual data we placed in <tt class="docutils literal">values</tt> didn’t get rendered into the

358

string; instead we got named bind parameters. As it turns out, our data is

359

stored within our <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> construct, but it

359

stored within our <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> construct, but it

360

typically only comes out when the statement is actually executed; since the

361

data consists of literal values, SQLAlchemy automatically generates bind

362

parameters for them. We can peek at this data for now by looking at the

367

</div>

368

369

<h2>Executing<a class="headerlink" href="#executing" title="Permalink to this headline">¶</a></h2>

370

The interesting part of an <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> is

370

The interesting part of an <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> is

371

executing it. In this tutorial, we will generally focus on the most explicit

372

method of executing a SQL construct, and later touch upon some “shortcut” ways

373

to do it. The <tt class="docutils literal">engine</tt> object we created is a repository for database

379

</div>

380

The <tt class="xref py py-class docutils literal">Connection</tt> object represents an actively

381

checked out DBAPI connection resource. Lets feed it our

382

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> object and see what happens:

382

<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> object and see what happens:

383

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> result = conn.execute(ins)

384

<div class='show_sql'>INSERT INTO users (name, fullname) VALUES (?, ?)

385

('jack', 'Jack Jones')

407

</div>

408

The value of <tt class="docutils literal">1</tt> was automatically generated by SQLite, but only because we

409

did not specify the <tt class="docutils literal">id</tt> column in our

410

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> statement; otherwise, our explicit

410

<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> statement; otherwise, our explicit

411

value would have been used. In either case, SQLAlchemy always knows how to get

412

at a newly generated primary key value, even though the method of generating

413

them is different across different databases; each database’s

419

<h2>Executing Multiple Statements<a class="headerlink" href="#executing-multiple-statements" title="Permalink to this headline">¶</a></h2>

420

Our insert example above was intentionally a little drawn out to show some

421

various behaviors of expression language constructs. In the usual case, an

422

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> statement is usually compiled

422

<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> statement is usually compiled

423

against the parameters sent to the <tt class="docutils literal">execute()</tt> method on

424

<tt class="xref py py-class docutils literal">Connection</tt>, so that there’s no need to use

425

the <tt class="docutils literal">values</tt> keyword with <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a>. Lets

426

create a generic <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> statement again

425

the <tt class="docutils literal">values</tt> keyword with <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a>. Lets

426

create a generic <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> statement again

427

and use it in the “normal” way:

428

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> ins = users.insert()

429

>>> conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams')

432

COMMIT</div><sqlalchemy.engine.result.ResultProxy object at 0x...></pre></div>

433

</div>

434

Above, because we specified all three columns in the <tt class="docutils literal">execute()</tt> method,

435

the compiled <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> included all three

436

columns. The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> statement is compiled

435

the compiled <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> included all three

436

columns. The <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> statement is compiled

437

at execution time based on the parameters we specified; if we specified fewer

438

parameters, the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> would have fewer

438

parameters, the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> would have fewer

439

entries in its VALUES clause.

440

To issue many inserts using DBAPI’s <tt class="docutils literal">executemany()</tt> method, we can send in a

441

list of dictionaries each containing a distinct set of parameters to be

454

identifiers for each <tt class="docutils literal">addresses</tt> row.

455

When executing multiple sets of parameters, each dictionary must have the

456

same set of keys; i.e. you cant have fewer keys in some dictionaries than

457

others. This is because the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a>

457

others. This is because the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a>

458

statement is compiled against the first dictionary in the list, and it’s

459

assumed that all subsequent argument dictionaries are compatible with that

460

statement.

464

We began with inserts just so that our test database had some data in it. The

465

more interesting part of the data is selecting it ! We’ll cover UPDATE and

466

DELETE statements later. The primary construct used to generate SELECT

467

statements is the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> function:

467

statements is the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> function:

468

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> from sqlalchemy.sql import select

469

>>> s = select([users])

470

>>> result = conn.execute(s)

472

FROM users

473

()</div></pre></div>

474

</div>

475

Above, we issued a basic <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> call, placing the <tt class="docutils literal">users</tt> table

475

Above, we issued a basic <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> call, placing the <tt class="docutils literal">users</tt> table

476

within the COLUMNS clause of the select, and then executing. SQLAlchemy

477

expanded the <tt class="docutils literal">users</tt> table into the set of each of its columns, and also

478

generated a FROM clause for us. The result returned is again a

502

name: wendy ; fullname: Wendy Williams</pre></div>

503

</div>

504

But another way, whose usefulness will become apparent later on, is to use the

505

<a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects directly as keys:

505

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects directly as keys:

506

<div class="highlight-pycon+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a>>>> for row in conn.execute(s):

507

... print "name:", row[users.c.name], "; fullname:", row[users.c.fullname]

508

<div class='popup_sql'>SELECT users.id, users.name, users.fullname

520

</div>

521

If we’d like to more carefully control the columns which are placed in the

522

COLUMNS clause of the select, we reference individual

523

<a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects from our

524

<a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. These are available as named attributes off

525

the <tt class="docutils literal">c</tt> attribute of the <a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object:

523

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects from our

524

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. These are available as named attributes off

525

the <tt class="docutils literal">c</tt> attribute of the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object:

526

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> s = select([users.c.name, users.c.fullname])

527

<a href='#' class='sql_link'>sql</a>>>> result = conn.execute(s)

528

<div class='popup_sql'>SELECT users.name, users.fullname

534

</div>

535

Lets observe something interesting about the FROM clause. Whereas the

536

generated statement contains two distinct sections, a “SELECT columns” part

537

and a “FROM table” part, our <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct only has a list

537

and a “FROM table” part, our <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct only has a list

538

containing columns. How does this work ? Let’s try putting two tables into

539

our <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> statement:

539

our <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> statement:

540

<div class="highlight-pycon+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a>>>> for row in conn.execute(select([users, addresses])):

541

... print row

542

<div class='popup_sql'>SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address

554

Those who are familiar with SQL joins know that this is a Cartesian

555

product; each row from the <tt class="docutils literal">users</tt> table is produced against each row from

556

the <tt class="docutils literal">addresses</tt> table. So to put some sanity into this statement, we need a

557

WHERE clause. We do that using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal">Select.where()</tt></a>:

557

WHERE clause. We do that using <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal">Select.where()</tt></a>:

558

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> s = select([users, addresses]).where(users.c.id == addresses.c.user_id)

559

<a href='#' class='sql_link'>sql</a>>>> for row in conn.execute(s):

560

... print row

567

(2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')

568

(2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')</pre></div>

569

</div>

570

So that looks a lot better, we added an expression to our <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a>

570

So that looks a lot better, we added an expression to our <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a>

571

which had the effect of adding <tt class="docutils literal">WHERE users.id = addresses.user_id</tt> to our

572

statement, and our results were managed down so that the join of <tt class="docutils literal">users</tt> and

573

<tt class="docutils literal">addresses</tt> rows made sense. But let’s look at that expression? It’s using

574

just a Python equality operator between two different

575

<a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects. It should be clear that something

575

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects. It should be clear that something

576

is up. Saying <tt class="docutils literal">1 == 1</tt> produces <tt class="docutils literal">True</tt>, and <tt class="docutils literal">1 == 2</tt> produces <tt class="docutils literal">False</tt>, not

577

a WHERE clause. So lets see exactly what that expression is doing:

578

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> users.c.id == addresses.c.user_id

583

'users.id = addresses.user_id'</pre></div>

584

</div>

585

As you can see, the <tt class="docutils literal">==</tt> operator is producing an object that is very much

586

like the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a>

586

like the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a>

587

objects we’ve made so far, thanks to Python’s <tt class="docutils literal">__eq__()</tt> builtin; you call

588

<tt class="docutils literal">str()</tt> on it and it produces SQL. By now, one can see that everything we

589

are working with is ultimately the same type of object. SQLAlchemy terms the

590

base class of all of these expressions as <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal">ColumnElement</tt></a>.

590

base class of all of these expressions as <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal">ColumnElement</tt></a>.

591

</div>

592

593

<h2>Operators<a class="headerlink" href="#operators" title="Permalink to this headline">¶</a></h2>

602

users.id = :id_1</pre></div>

603

</div>

604

The <tt class="docutils literal">7</tt> literal is embedded the resulting

605

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal">ColumnElement</tt></a>; we can use the same trick

606

we did with the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> object to see it:

605

<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal">ColumnElement</tt></a>; we can use the same trick

606

we did with the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal">Insert</tt></a> object to see it:

607

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> (users.c.id == 7).compile().params

608

{u'id_1': 7}</pre></div>

609

</div>

624

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> print users.c.id + addresses.c.id

625

users.id + addresses.id</pre></div>

626

</div>

627

Interestingly, the type of the <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> is important!

627

Interestingly, the type of the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> is important!

628

If we use <tt class="docutils literal">+</tt> with two string based columns (recall we put types like

629

<a class="reference internal" href="types.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><tt class="xref py py-class docutils literal">Integer</tt></a> and <a class="reference internal" href="types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal">String</tt></a> on

630

our <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects at the beginning), we get

630

our <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects at the beginning), we get

631

something different:

632

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> print users.c.name + users.c.fullname

633

users.name || users.fullname</pre></div>

642

<a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal">Engine</tt></a> that’s connected to a MySQL database;

643

the <tt class="docutils literal">||</tt> operator now compiles as MySQL’s <tt class="docutils literal">concat()</tt> function.

644

If you have come across an operator which really isn’t available, you can

645

always use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.op" title="sqlalchemy.sql.operators.ColumnOperators.op"><tt class="xref py py-meth docutils literal">ColumnOperators.op()</tt></a> method; this generates whatever operator you need:

645

always use the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.op" title="sqlalchemy.sql.operators.ColumnOperators.op"><tt class="xref py py-meth docutils literal">ColumnOperators.op()</tt></a> method; this generates whatever operator you need:

646

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> print users.c.name.op('tiddlywinks')('foo')

647

users.name tiddlywinks :name_1</pre></div>

648

</div>

652

is a bitwise AND of the value in <cite>somecolumn</cite>.

653

654

<h3>Operator Customization<a class="headerlink" href="#operator-customization" title="Permalink to this headline">¶</a></h3>

655

While <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.op" title="sqlalchemy.sql.operators.ColumnOperators.op"><tt class="xref py py-meth docutils literal">ColumnOperators.op()</tt></a> is handy to get at a custom operator in a hurry,

655

While <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.op" title="sqlalchemy.sql.operators.ColumnOperators.op"><tt class="xref py py-meth docutils literal">ColumnOperators.op()</tt></a> is handy to get at a custom operator in a hurry,

656

the Core supports fundamental customization and extension of the operator system at

657

the type level. The behavior of existing operators can be modified on a per-type

658

basis, and new operations can be defined which become available for all column

662

</div>

663

664

<h2>Conjunctions<a class="headerlink" href="#conjunctions" title="Permalink to this headline">¶</a></h2>

665

We’d like to show off some of our operators inside of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a>

665

We’d like to show off some of our operators inside of <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a>

666

constructs. But we need to lump them together a little more, so let’s first

667

introduce some conjunctions. Conjunctions are those little words like AND and

668

OR that put things together. We’ll also hit upon NOT. <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal">and_()</tt></a>, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.or_" title="sqlalchemy.sql.expression.or_"><tt class="xref py py-func docutils literal">or_()</tt></a>,

669

and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.not_" title="sqlalchemy.sql.expression.not_"><tt class="xref py py-func docutils literal">not_()</tt></a> can work

668

OR that put things together. We’ll also hit upon NOT. <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal">and_()</tt></a>, <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.or_" title="sqlalchemy.sql.expression.or_"><tt class="xref py py-func docutils literal">or_()</tt></a>,

669

and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.not_" title="sqlalchemy.sql.expression.not_"><tt class="xref py py-func docutils literal">not_()</tt></a> can work

670

from the corresponding functions SQLAlchemy provides (notice we also throw in

671

a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><tt class="xref py py-meth docutils literal">like()</tt></a>):

671

a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><tt class="xref py py-meth docutils literal">like()</tt></a>):

672

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> from sqlalchemy.sql import and_, or_, not_

673

>>> print and_(

674

... users.c.name.like('j%'),

702

address at AOL or MSN, whose name starts with a letter between “m” and “z”,

703

and we’ll also generate a column containing their full name combined with

704

their email address. We will add two new constructs to this statement,

705

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><tt class="xref py py-meth docutils literal">between()</tt></a> and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><tt class="xref py py-meth docutils literal">label()</tt></a>.

706

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><tt class="xref py py-meth docutils literal">between()</tt></a> produces a BETWEEN clause, and

707

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><tt class="xref py py-meth docutils literal">label()</tt></a> is used in a column expression to produce labels using the <tt class="docutils literal">AS</tt>

705

<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><tt class="xref py py-meth docutils literal">between()</tt></a> and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><tt class="xref py py-meth docutils literal">label()</tt></a>.

706

<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><tt class="xref py py-meth docutils literal">between()</tt></a> produces a BETWEEN clause, and

707

<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><tt class="xref py py-meth docutils literal">label()</tt></a> is used in a column expression to produce labels using the <tt class="docutils literal">AS</tt>

708

keyword; it’s recommended when selecting from expressions that otherwise would

709

not have a name:

710

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> s = select([(users.c.fullname +

732

it will determine the FROM clause based on all of its other bits; the columns

733

clause, the where clause, and also some other elements which we haven’t

734

covered yet, which include ORDER BY, GROUP BY, and HAVING.

735

A shortcut to using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal">and_()</tt></a> is to chain together multiple

736

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal">where()</tt></a> clauses. The above can also be written as:

735

A shortcut to using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal">and_()</tt></a> is to chain together multiple

736

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal">where()</tt></a> clauses. The above can also be written as:

737

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> s = select([(users.c.fullname +

738

... ", " + addresses.c.email_address).

739

... label('title')]).\

753

(', ', 'm', 'z', '%@aol.com', '%@msn.com')

754

[(u'Wendy Williams, wendy@aol.com',)]</pre></div>

755

</div>

756

The way that we can build up a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct through successive

756

The way that we can build up a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct through successive

757

method calls is called <a class="reference internal" href="../glossary.html#term-method-chaining">method chaining</a>.

758

</div>

759

761

Our last example really became a handful to type. Going from what one

762

understands to be a textual SQL expression into a Python construct which

763

groups components together in a programmatic style can be hard. That’s why

764

SQLAlchemy lets you just use strings too. The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a> construct represents

764

SQLAlchemy lets you just use strings too. The <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a> construct represents

765

any textual statement, in a backend-agnostic way.

766

To use bind parameters with <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a>, always use the

767

named colon format. Such as below, we create a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a> and execute it,

766

To use bind parameters with <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a>, always use the

767

named colon format. Such as below, we create a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a> and execute it,

768

feeding in the bind parameters to the <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><tt class="xref py py-meth docutils literal">execute()</tt></a> method:

769

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> from sqlalchemy.sql import text

770

>>> s = text(

781

(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)

782

('m', 'z', '%@aol.com', '%@msn.com')</div>[(u'Wendy Williams, wendy@aol.com',)]</pre></div>

783

</div>

784

To gain a “hybrid” approach, the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct accepts strings for most

784

To gain a “hybrid” approach, the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct accepts strings for most

785

of its arguments. Below we combine the usage of strings with our constructed

786

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> object, by using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> object to structure the

786

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> object, by using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> object to structure the

787

statement, and strings to provide all the content within the structure. For

788

this example, SQLAlchemy is not given any <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>

789

or <a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects in any of its expressions, so it

790

cannot generate a FROM clause. So we also use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.select_from" title="sqlalchemy.sql.expression.Select.select_from"><tt class="xref py py-meth docutils literal">select_from()</tt></a>

791

method, which accepts a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause" title="sqlalchemy.sql.expression.FromClause"><tt class="xref py py-class docutils literal">FromClause</tt></a> or string expression

788

this example, SQLAlchemy is not given any <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>

789

or <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects in any of its expressions, so it

790

cannot generate a FROM clause. So we also use the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.select_from" title="sqlalchemy.sql.expression.Select.select_from"><tt class="xref py py-meth docutils literal">select_from()</tt></a>

791

method, which accepts a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause" title="sqlalchemy.sql.expression.FromClause"><tt class="xref py py-class docutils literal">FromClause</tt></a> or string expression

792

to be placed within the FROM clause:

793

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> s = select([

794

... "users.fullname || ', ' || addresses.email_address AS title"

814

datatypes in use; for example, if our bind parameters required UTF-8 encoding

815

before going in, or conversion from a Python <tt class="docutils literal">datetime</tt> into a string (as is

816

required with SQLite), we would have to add extra information to our

817

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a> construct. Similar issues arise on the result set side, where

817

<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a> construct. Similar issues arise on the result set side, where

818

SQLAlchemy also performs type-specific data conversion in some cases; still

819

more information can be added to <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a> to work around this. But what we

819

more information can be added to <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal">text()</tt></a> to work around this. But what we

820

really lose from our statement is the ability to manipulate it, transform it,

821

and analyze it. These features are critical when using the ORM, which makes

822

heavy usage of relational transformations. To show off what we mean, we’ll

833

FROM clause multiple times. In the case of a SELECT statement, it provides a

834

parent name for the columns represented by the statement, allowing them to be

835

referenced relative to this name.

836

In SQLAlchemy, any <a class="reference internal" href="schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct, or

837

other selectable can be turned into an alias using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.alias" title="sqlalchemy.sql.expression.FromClause.alias"><tt class="xref py py-meth docutils literal">FromClause.alias()</tt></a>

838

method, which produces a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal">Alias</tt></a> construct. As an example, suppose we know that our user <tt class="docutils literal">jack</tt> has two

836

In SQLAlchemy, any <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct, or

837

other selectable can be turned into an alias using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.alias" title="sqlalchemy.sql.expression.FromClause.alias"><tt class="xref py py-meth docutils literal">FromClause.alias()</tt></a>

838

method, which produces a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal">Alias</tt></a> construct. As an example, suppose we know that our user <tt class="docutils literal">jack</tt> has two

839

particular email addresses. How can we locate jack based on the combination of those two

840

addresses? To accomplish this, we’d use a join to the <tt class="docutils literal">addresses</tt> table,

841

once for each address. We create two <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal">Alias</tt></a> constructs against

842

<tt class="docutils literal">addresses</tt>, and then use them both within a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct:

841

once for each address. We create two <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal">Alias</tt></a> constructs against

842

<tt class="docutils literal">addresses</tt>, and then use them both within a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct:

843

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> a1 = addresses.alias()

844

>>> a2 = addresses.alias()

845

>>> s = select([users]).\

858

AND addresses_2.email_address = ?

859

('jack@msn.com', 'jack@yahoo.com')</div>[(1, u'jack', u'Jack Jones')]</pre></div>

860

</div>

861

Note that the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal">Alias</tt></a> construct generated the names <tt class="docutils literal">addresses_1</tt> and

861

Note that the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal">Alias</tt></a> construct generated the names <tt class="docutils literal">addresses_1</tt> and

862

<tt class="docutils literal">addresses_2</tt> in the final SQL result. The generation of these names is determined

863

by the position of the construct within the statement. If we created a query using

864

only the second <tt class="docutils literal">a2</tt> alias, the name would come out as <tt class="docutils literal">addresses_1</tt>. The

865

generation of the names is also deterministic, meaning the same SQLAlchemy

866

statement construct will produce the identical SQL string each time it is

867

rendered for a particular dialect.

868

Since on the outside, we refer to the alias using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal">Alias</tt></a> construct

868

Since on the outside, we refer to the alias using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal">Alias</tt></a> construct

869

itself, we don’t need to be concerned about the generated name. However, for

870

the purposes of debugging, it can be specified by passing a string name

871

to the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.alias" title="sqlalchemy.sql.expression.FromClause.alias"><tt class="xref py py-meth docutils literal">FromClause.alias()</tt></a> method:

871

to the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.alias" title="sqlalchemy.sql.expression.FromClause.alias"><tt class="xref py py-meth docutils literal">FromClause.alias()</tt></a> method:

872

<div class="highlight-python"><div class="highlight"><pre>>>> a1 = addresses.alias('a1')</pre></div>

873

</div>

874

Aliases can of course be used for anything which you can SELECT from,

875

including SELECT statements themselves. We can self-join the <tt class="docutils literal">users</tt> table

876

back to the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> we’ve created by making an alias of the entire

876

back to the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> we’ve created by making an alias of the entire

877

statement. The <tt class="docutils literal">correlate(None)</tt> directive is to avoid SQLAlchemy’s attempt

878

to “correlate” the inner <tt class="docutils literal">users</tt> table with the outer one:

879

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> a1 = s.correlate(None).alias()

895

We’re halfway along to being able to construct any SELECT expression. The next

896

cornerstone of the SELECT is the JOIN expression. We’ve already been doing

897

joins in our examples, by just placing two tables in either the columns clause

898

or the where clause of the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct. But if we want to make a

899

real “JOIN” or “OUTERJOIN” construct, we use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.join" title="sqlalchemy.sql.expression.FromClause.join"><tt class="xref py py-meth docutils literal">join()</tt></a> and

900

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><tt class="xref py py-meth docutils literal">outerjoin()</tt></a> methods, most commonly accessed from the left table in the

898

or the where clause of the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct. But if we want to make a

899

real “JOIN” or “OUTERJOIN” construct, we use the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.join" title="sqlalchemy.sql.expression.FromClause.join"><tt class="xref py py-meth docutils literal">join()</tt></a> and

900

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><tt class="xref py py-meth docutils literal">outerjoin()</tt></a> methods, most commonly accessed from the left table in the

901

join:

902

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> print users.join(addresses)

903

users JOIN addresses ON users.id = addresses.user_id</pre></div>

904

</div>

905

The alert reader will see more surprises; SQLAlchemy figured out how to JOIN

906

the two tables ! The ON condition of the join, as it’s called, was

907

automatically generated based on the <a class="reference internal" href="schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>

907

automatically generated based on the <a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>

908

object which we placed on the <tt class="docutils literal">addresses</tt> table way at the beginning of this

909

tutorial. Already the <tt class="docutils literal">join()</tt> construct is looking like a much better way

910

to join tables.

916

... )

917

users JOIN addresses ON addresses.email_address LIKE (users.name || :name_1)</pre></div>

918

</div>

919

When we create a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct, SQLAlchemy looks around at the

919

When we create a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct, SQLAlchemy looks around at the

920

tables we’ve mentioned and then places them in the FROM clause of the

921

statement. When we use JOINs however, we know what FROM clause we want, so

922

here we make use of the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.select_from" title="sqlalchemy.sql.expression.Select.select_from"><tt class="xref py py-meth docutils literal">select_from()</tt></a> method:

922

here we make use of the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.select_from" title="sqlalchemy.sql.expression.Select.select_from"><tt class="xref py py-meth docutils literal">select_from()</tt></a> method:

923

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> s = select([users.c.fullname]).select_from(

924

... users.join(addresses,

925

... addresses.c.email_address.like(users.c.name + '%'))

929

FROM users JOIN addresses ON addresses.email_address LIKE (users.name || ?)

930

('%',)</div>[(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)]</pre></div>

931

</div>

932

The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><tt class="xref py py-meth docutils literal">outerjoin()</tt></a> method creates <tt class="docutils literal">LEFT OUTER JOIN</tt> constructs,

933

and is used in the same way as <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.join" title="sqlalchemy.sql.expression.FromClause.join"><tt class="xref py py-meth docutils literal">join()</tt></a>:

932

The <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><tt class="xref py py-meth docutils literal">outerjoin()</tt></a> method creates <tt class="docutils literal">LEFT OUTER JOIN</tt> constructs,

933

and is used in the same way as <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.join" title="sqlalchemy.sql.expression.FromClause.join"><tt class="xref py py-meth docutils literal">join()</tt></a>:

934

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> s = select([users.c.fullname]).select_from(users.outerjoin(addresses))

935

>>> print s

936

SELECT users.fullname

1004

</div>

1005

1006

<h3>Functions<a class="headerlink" href="#functions" title="Permalink to this headline">¶</a></h3>

1007

SQL functions are created using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-data docutils literal">func</tt></a> keyword, which

1007

SQL functions are created using the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-data docutils literal">func</tt></a> keyword, which

1008

generates functions using attribute access:

1009

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> from sqlalchemy.sql import func

1010

>>> print func.now()

1064

WHERE users.id > anon_1.z</pre></div>

1065

</div>

1066

If we wanted to use our <tt class="docutils literal">calculate</tt> statement twice with different bind

1067

parameters, the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement.unique_params" title="sqlalchemy.sql.expression.ClauseElement.unique_params"><tt class="xref py py-func docutils literal">unique_params()</tt></a>

1067

parameters, the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ClauseElement.unique_params" title="sqlalchemy.sql.expression.ClauseElement.unique_params"><tt class="xref py py-func docutils literal">unique_params()</tt></a>

1068

function will create copies for us, and mark the bind parameters as “unique”

1069

so that conflicting names are isolated. Note we also make two separate aliases

1070

of our selectable:

1085

</div>

1086

1087

<h3>Window Functions<a class="headerlink" href="#window-functions" title="Permalink to this headline">¶</a></h3>

1088

Any <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FunctionElement" title="sqlalchemy.sql.expression.FunctionElement"><tt class="xref py py-class docutils literal">FunctionElement</tt></a>, including functions generated by

1089

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-data docutils literal">func</tt></a>, can be turned into a “window function”, that is an

1090

OVER clause, using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FunctionElement.over" title="sqlalchemy.sql.expression.FunctionElement.over"><tt class="xref py py-meth docutils literal">over()</tt></a> method:

1088

Any <tt class="xref py py-class docutils literal">FunctionElement</tt>, including functions generated by

1089

<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-data docutils literal">func</tt></a>, can be turned into a “window function”, that is an

1090

OVER clause, using the <tt class="xref py py-meth docutils literal">over()</tt> method:

1091

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> s = select([

1092

... users.c.id,

1093

... func.row_number().over(order_by=users.c.name)

1100

1101

<h3>Unions and Other Set Operations<a class="headerlink" href="#unions-and-other-set-operations" title="Permalink to this headline">¶</a></h3>

1102

Unions come in two flavors, UNION and UNION ALL, which are available via

1103

module level functions <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.union" title="sqlalchemy.sql.expression.union"><tt class="xref py py-func docutils literal">union()</tt></a> and

1104

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.union_all" title="sqlalchemy.sql.expression.union_all"><tt class="xref py py-func docutils literal">union_all()</tt></a>:

1103

module level functions <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union" title="sqlalchemy.sql.expression.union"><tt class="xref py py-func docutils literal">union()</tt></a> and

1104

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union_all" title="sqlalchemy.sql.expression.union_all"><tt class="xref py py-func docutils literal">union_all()</tt></a>:

1105

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> from sqlalchemy.sql import union

1106

>>> u = union(

1107

... addresses.select().

1121

('foo@bar.com', '%@yahoo.com')</div>[(1, 1, u'jack@yahoo.com')]</pre></div>

1122

</div>

1123

Also available, though not supported on all databases, are

1124

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.intersect" title="sqlalchemy.sql.expression.intersect"><tt class="xref py py-func docutils literal">intersect()</tt></a>,

1125

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.intersect_all" title="sqlalchemy.sql.expression.intersect_all"><tt class="xref py py-func docutils literal">intersect_all()</tt></a>,

1126

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.except_" title="sqlalchemy.sql.expression.except_"><tt class="xref py py-func docutils literal">except_()</tt></a>, and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.except_all" title="sqlalchemy.sql.expression.except_all"><tt class="xref py py-func docutils literal">except_all()</tt></a>:

1124

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect" title="sqlalchemy.sql.expression.intersect"><tt class="xref py py-func docutils literal">intersect()</tt></a>,

1125

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect_all" title="sqlalchemy.sql.expression.intersect_all"><tt class="xref py py-func docutils literal">intersect_all()</tt></a>,

1126

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_" title="sqlalchemy.sql.expression.except_"><tt class="xref py py-func docutils literal">except_()</tt></a>, and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_all" title="sqlalchemy.sql.expression.except_all"><tt class="xref py py-func docutils literal">except_all()</tt></a>:

1127

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> from sqlalchemy.sql import except_

1128

>>> u = except_(

1129

... addresses.select().

1183

column. It can then be used as a column expression. A scalar select

1184

is often a <a class="reference internal" href="../glossary.html#term-correlated-subquery">correlated subquery</a>, which relies upon the enclosing

1185

SELECT statement in order to acquire at least one of its FROM clauses.

1186

The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct can be modified to act as a

1187

column expression by calling either the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.as_scalar" title="sqlalchemy.sql.expression.SelectBase.as_scalar"><tt class="xref py py-meth docutils literal">as_scalar()</tt></a>

1188

or <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><tt class="xref py py-meth docutils literal">label()</tt></a> method:

1186

The <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a> construct can be modified to act as a

1187

column expression by calling either the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.as_scalar" title="sqlalchemy.sql.expression.SelectBase.as_scalar"><tt class="xref py py-meth docutils literal">as_scalar()</tt></a>

1188

or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><tt class="xref py py-meth docutils literal">label()</tt></a> method:

1189

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([func.count(addresses.c.id)]).\

1190

... where(users.c.id == addresses.c.user_id).\

1191

... as_scalar()</pre></div>

1192

</div>

1193

The above construct is now a <tt class="xref py py-class docutils literal">ScalarSelect</tt> object,

1194

and is no longer part of the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause" title="sqlalchemy.sql.expression.FromClause"><tt class="xref py py-class docutils literal">FromClause</tt></a> hierarchy;

1195

it instead is within the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal">ColumnElement</tt></a> family of

1194

and is no longer part of the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause" title="sqlalchemy.sql.expression.FromClause"><tt class="xref py py-class docutils literal">FromClause</tt></a> hierarchy;

1195

it instead is within the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal">ColumnElement</tt></a> family of

1196

expression constructs. We can place this construct the same as any

1197

other column within another <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a>:

1197

other column within another <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal">select()</tt></a>:

1198

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> conn.execute(select([users.c.name, stmt])).fetchall()

1199

<div class='show_sql'>SELECT users.name, (SELECT count(addresses.id) AS count_1

1200

FROM addresses

1203

()</div>[(u'jack', 2), (u'wendy', 2)]</pre></div>

1204

</div>

1205

To apply a non-anonymous column name to our scalar select, we create

1206

it using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><tt class="xref py py-meth docutils literal">SelectBase.label()</tt></a> instead:

1206

it using <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><tt class="xref py py-meth docutils literal">SelectBase.label()</tt></a> instead:

1207

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([func.count(addresses.c.id)]).\

1208

... where(users.c.id == addresses.c.user_id).\

1209

... label("address_count")

1238

Auto-correlation will usually do what’s expected, however it can also be controlled.

1239

For example, if we wanted a statement to correlate only to the <tt class="docutils literal">addresses</tt> table

1240

but not the <tt class="docutils literal">users</tt> table, even if both were present in the enclosing SELECT,

1241

we use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.correlate" title="sqlalchemy.sql.expression.Select.correlate"><tt class="xref py py-meth docutils literal">correlate()</tt></a> method to specify those FROM clauses that

1241

we use the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.correlate" title="sqlalchemy.sql.expression.Select.correlate"><tt class="xref py py-meth docutils literal">correlate()</tt></a> method to specify those FROM clauses that

1242

may be correlated:

1243

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([users.c.id]).\

1244

... where(users.c.id == addresses.c.user_id).\

1272

WHERE users.name = ?)

1273

('wendy',)</div>[(u'wendy',)]</pre></div>

1274

</div>

1275

We can also control correlation via exclusion, using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.correlate_except" title="sqlalchemy.sql.expression.Select.correlate_except"><tt class="xref py py-meth docutils literal">Select.correlate_except()</tt></a>

1275

We can also control correlation via exclusion, using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.correlate_except" title="sqlalchemy.sql.expression.Select.correlate_except"><tt class="xref py py-meth docutils literal">Select.correlate_except()</tt></a>

1276

method. Such as, we can write our SELECT for the <tt class="docutils literal">users</tt> table

1277

by telling it to correlate all FROM clauses except for <tt class="docutils literal">users</tt>:

1278

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([users.c.id]).\

1296

1297

<h3>Ordering, Grouping, Limiting, Offset...ing...<a class="headerlink" href="#ordering-grouping-limiting-offset-ing" title="Permalink to this headline">¶</a></h3>

1298

Ordering is done by passing column expressions to the

1299

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.order_by" title="sqlalchemy.sql.expression.SelectBase.order_by"><tt class="xref py py-meth docutils literal">order_by()</tt></a> method:

1299

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.order_by" title="sqlalchemy.sql.expression.SelectBase.order_by"><tt class="xref py py-meth docutils literal">order_by()</tt></a> method:

1300

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([users.c.name]).order_by(users.c.name)

1301

>>> conn.execute(stmt).fetchall()

1302

<div class='show_sql'>SELECT users.name

1303

FROM users ORDER BY users.name

1304

()</div>[(u'jack',), (u'wendy',)]</pre></div>

1305

</div>

1306

Ascending or descending can be controlled using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.asc" title="sqlalchemy.sql.expression.ColumnElement.asc"><tt class="xref py py-meth docutils literal">asc()</tt></a>

1307

and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.desc" title="sqlalchemy.sql.expression.ColumnElement.desc"><tt class="xref py py-meth docutils literal">desc()</tt></a> modifiers:

1306

Ascending or descending can be controlled using the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.asc" title="sqlalchemy.sql.expression.ColumnElement.asc"><tt class="xref py py-meth docutils literal">asc()</tt></a>

1307

and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.desc" title="sqlalchemy.sql.expression.ColumnElement.desc"><tt class="xref py py-meth docutils literal">desc()</tt></a> modifiers:

1308

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([users.c.name]).order_by(users.c.name.desc())

1309

>>> conn.execute(stmt).fetchall()

1310

<div class='show_sql'>SELECT users.name

1313

</div>

1314

Grouping refers to the GROUP BY clause, and is usually used in conjunction

1315

with aggregate functions to establish groups of rows to be aggregated.

1316

This is provided via the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.group_by" title="sqlalchemy.sql.expression.SelectBase.group_by"><tt class="xref py py-meth docutils literal">group_by()</tt></a> method:

1316

This is provided via the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.group_by" title="sqlalchemy.sql.expression.SelectBase.group_by"><tt class="xref py py-meth docutils literal">group_by()</tt></a> method:

1317

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([users.c.name, func.count(addresses.c.id)]).\

1318

... select_from(users.join(addresses)).\

1319

... group_by(users.c.name)

1325

()</div>[(u'jack', 2), (u'wendy', 2)]</pre></div>

1326

</div>

1327

HAVING can be used to filter results on an aggregate value, after GROUP BY has

1328

been applied. It’s available here via the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.having" title="sqlalchemy.sql.expression.Select.having"><tt class="xref py py-meth docutils literal">having()</tt></a>

1328

been applied. It’s available here via the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.having" title="sqlalchemy.sql.expression.Select.having"><tt class="xref py py-meth docutils literal">having()</tt></a>

1329

method:

1330

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([users.c.name, func.count(addresses.c.id)]).\

1331

... select_from(users.join(addresses)).\

1341

</div>

1342

A common system of dealing with duplicates in composed SELECT statments

1343

is the DISTINCT modifier. A simple DISTINCT clause can be added using the

1344

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.distinct" title="sqlalchemy.sql.expression.Select.distinct"><tt class="xref py py-meth docutils literal">Select.distinct()</tt></a> method:

1344

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.distinct" title="sqlalchemy.sql.expression.Select.distinct"><tt class="xref py py-meth docutils literal">Select.distinct()</tt></a> method:

1345

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([users.c.name]).\

1346

... where(addresses.c.email_address.

1347

... contains(users.c.name)).\

1357

rows after a given “offset”. While common backends like Postgresql,

1358

MySQL and SQLite support LIMIT and OFFSET keywords, other backends

1359

need to refer to more esoteric features such as “window functions”

1360

and row ids to achieve the same effect. The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.limit" title="sqlalchemy.sql.expression.Select.limit"><tt class="xref py py-meth docutils literal">limit()</tt></a>

1361

and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.offset" title="sqlalchemy.sql.expression.Select.offset"><tt class="xref py py-meth docutils literal">offset()</tt></a> methods provide an easy abstraction

1360

and row ids to achieve the same effect. The <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.limit" title="sqlalchemy.sql.expression.Select.limit"><tt class="xref py py-meth docutils literal">limit()</tt></a>

1361

and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.offset" title="sqlalchemy.sql.expression.Select.offset"><tt class="xref py py-meth docutils literal">offset()</tt></a> methods provide an easy abstraction

1362

into the current backend’s methodology:

1363

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = select([users.c.name, addresses.c.email_address]).\

1364

... select_from(users.join(addresses)).\

1373

</div>

1374

1375

<h2>Inserts, Updates and Deletes<a class="headerlink" href="#inserts-updates-and-deletes" title="Permalink to this headline">¶</a></h2>

1376

We’ve seen <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal">insert()</tt></a> demonstrated

1377

earlier in this tutorial. Where <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal">insert()</tt></a>

1378

prodces INSERT, the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a>

1376

We’ve seen <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal">insert()</tt></a> demonstrated

1377

earlier in this tutorial. Where <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal">insert()</tt></a>

1378

prodces INSERT, the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a>

1379

method produces UPDATE. Both of these constructs feature

1380

a method called <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><tt class="xref py py-meth docutils literal">values()</tt></a> which specifies

1380

a method called <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><tt class="xref py py-meth docutils literal">values()</tt></a> which specifies

1381

the VALUES or SET clause of the statement.

1382

The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><tt class="xref py py-meth docutils literal">values()</tt></a> method accommodates any column expression

1382

The <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><tt class="xref py py-meth docutils literal">values()</tt></a> method accommodates any column expression

1383

as a value:

1384

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = users.update().\

1385

... values(fullname="Fullname: " + users.c.name)

1388

('Fullname: ',)

1389

COMMIT</div><sqlalchemy.engine.result.ResultProxy object at 0x...></pre></div>

1390

</div>

1391

When using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal">insert()</tt></a> or <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a>

1391

When using <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal">insert()</tt></a> or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a>

1392

in an “execute many” context, we may also want to specify named

1393

bound parameters which we can refer to in the argument list.

1394

The two constructs will automatically generate bound placeholders

1395

for any column names passed in the dictionaries sent to

1396

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><tt class="xref py py-meth docutils literal">execute()</tt></a> at execution time. However, if we

1397

wish to use explicitly targeted named parameters with composed expressions,

1398

we need to use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal">bindparam()</tt></a> construct.

1399

When using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal">bindparam()</tt></a> with

1400

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal">insert()</tt></a> or <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a>,

1398

we need to use the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal">bindparam()</tt></a> construct.

1399

When using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal">bindparam()</tt></a> with

1400

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal">insert()</tt></a> or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a>,

1401

the names of the table’s columns themselves are reserved for the

1402

“automatic” generation of bind names. We can combine the usage

1403

of implicitly available bind names and explicitly named parameters

1414

COMMIT

1415

<sqlalchemy.engine.result.ResultProxy object at 0x...></div></pre></div>

1416

</div>

1417

An UPDATE statement is emitted using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a> construct. This

1417

An UPDATE statement is emitted using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a> construct. This

1418

works much like an INSERT, except there is an additional WHERE clause

1419

that can be specified:

1420

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = users.update().\

1426

('ed', 'jack')

1427

COMMIT</div><sqlalchemy.engine.result.ResultProxy object at 0x...></pre></div>

1428

</div>

1429

When using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a> in an “execute many” context,

1429

When using <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a> in an “execute many” context,

1430

we may wish to also use explicitly named bound parameters in the

1431

WHERE clause. Again, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal">bindparam()</tt></a> is the construct

1431

WHERE clause. Again, <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal">bindparam()</tt></a> is the construct

1432

used to achieve this:

1433

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> stmt = users.update().\

1434

... where(users.c.name == bindparam('oldname')).\

1459

</div>

1460

</div>

1461

1462

<h3>Multiple Table Updates<a class="headerlink" href="#multiple-table-updates" title="Permalink to this headline">¶</a></h3>

1463

1464

New in version 0.7.4.

1462

<h3>Multiple Table Updates<a class="headerlink" href="#multiple-table-updates" title="Permalink to this headline">¶</a></h3>

1463

1464

New in version 0.7.4.

1465

</div>

1465

1466

The Postgresql, Microsoft SQL Server, and MySQL backends all support UPDATE statements

1466

1467

that refer to multiple tables. For PG and MSSQL, this is the “UPDATE FROM” syntax,

1467

1468

which updates one table at a time, but can reference additional tables in an additional

1468

1469

“FROM” clause that can then be referenced in the WHERE clause directly. On MySQL,

1469

1470

multiple tables can be embedded into a single UPDATE statement separated by a comma.

1470

The SQLAlchemy <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.update" title="sqlalchemy.sql.expression.update"><tt class="xref py py-func docutils literal">update()</tt></a> construct supports both of these modes

1471

The SQLAlchemy <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.update" title="sqlalchemy.sql.expression.update"><tt class="xref py py-func docutils literal">update()</tt></a> construct supports both of these modes

1471

1472

implicitly, by specifying multiple tables in the WHERE clause:

1472

1473

<div class="highlight-python"><div class="highlight"><pre>stmt = users.update().\

1473

1474

values(name='ed wood').\

1481

1482

addresses.email_address LIKE :email_address_1 || '%%'</pre>

1482

1483

</div>

1483

1484

When using MySQL, columns from each table can be assigned to in the

1484

SET clause directly, using the dictionary form passed to <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Update.values" title="sqlalchemy.sql.expression.Update.values"><tt class="xref py py-meth docutils literal">Update.values()</tt></a>:

1485

SET clause directly, using the dictionary form passed to <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Update.values" title="sqlalchemy.sql.expression.Update.values"><tt class="xref py py-meth docutils literal">Update.values()</tt></a>:

1485

1486

<div class="highlight-python"><div class="highlight"><pre>stmt = users.update().\

1486

1487

values({

1487

1488

users.c.name:'ed wood',

1503

1504

1504

1505

<h3>Deletes<a class="headerlink" href="#deletes" title="Permalink to this headline">¶</a></h3>

1505

1506

Finally, a delete. This is accomplished easily enough using the

1506

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><tt class="xref py py-meth docutils literal">delete()</tt></a> construct:

1507

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><tt class="xref py py-meth docutils literal">delete()</tt></a> construct:

1507

1508

<div class="highlight-pycon+sql"><div class="highlight"><pre>>>> conn.execute(addresses.delete())

1508

1509

<div class='show_sql'>DELETE FROM addresses

1509

1510

()

1517

1518

</div>

1518

1519

1519

1520

<h3>Matched Row Counts<a class="headerlink" href="#matched-row-counts" title="Permalink to this headline">¶</a></h3>

1520

Both of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a> and

1521

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><tt class="xref py py-meth docutils literal">delete()</tt></a> are associated with matched row counts. This is a

1521

Both of <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal">update()</tt></a> and

1522

<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><tt class="xref py py-meth docutils literal">delete()</tt></a> are associated with matched row counts. This is a

1522

1523

number indicating the number of rows that were matched by the WHERE clause.

1523

1524

Note that by “matched”, this includes rows where no UPDATE actually took place.

1524

1525

The value is available as <a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><tt class="xref py py-attr docutils literal">rowcount</tt></a>:

1533

1534

1534

1535

<h2>Further Reference<a class="headerlink" href="#further-reference" title="Permalink to this headline">¶</a></h2>

1535

1536

Expression Language Reference: <a class="reference internal" href="expression_api.html">SQL Statements and Expressions API</a>

1536

Database Metadata Reference: <a class="reference internal" href="schema.html">Schema Definition Language</a>

1537

Database Metadata Reference: <a class="reference internal" href="metadata.html">Describing Databases with MetaData</a>

1537

1538

Engine Reference: <a class="reference internal" href="engines.html">Engine Configuration</a>

1538

1539

Connection Reference: <a class="reference internal" href="connections.html">Working with Engines and Connections</a>

1539

1540

Types Reference: <a class="reference internal" href="types.html">Column and Data Types</a>

1552

1553

1554

1554

1555

Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.

1556

Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.

1556

1557

</div>

1557

1558

</div>

1558

1559