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

206

154

already exist within the database.

207

155

Database metadata can be expressed by explicitly naming the various components

208

156

and their properties, using constructs such as

209

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>,

210

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> and

211

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a>, all of which are imported from the

157

<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="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>,

158

<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> and

159

<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>, all of which are imported from the

212

160

<tt class="docutils literal">sqlalchemy.schema</tt> package. It can also be generated by SQLAlchemy using a

213

161

process called reflection, which means you start with a single object such

214

as <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, assign it a name, and then instruct

162

as <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>, assign it a name, and then instruct

215

163

SQLAlchemy to load all the additional information related to that name from a

216

164

particular engine source.

217

165

A key feature of SQLAlchemy’s database metadata constructs is that they are

218

166

designed to be used in a declarative style which closely resembles that of

219

167

real DDL. They are therefore most intuitive to those who have some background

220

168

in creating real schema generation scripts.

221

A collection of metadata entities is stored in an object aptly named

222

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

223

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

224

225

metadata = MetaData()</pre></div>

226

</div>

227

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> is a container object that keeps together

228

many different features of a database (or multiple databases) being described.

229

To represent a table, use the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> class. Its two

230

primary arguments are the table name, then the

231

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object which it will be associated with.

232

The remaining positional arguments are mostly

233

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects describing each column:

234

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

235

Column('user_id', Integer, primary_key = True),

236

Column('user_name', String(16), nullable = False),

237

Column('email_address', String(60)),

238

Column('password', String(20), nullable = False)

239

)</pre></div>

240

</div>

241

Above, a table called <tt class="docutils literal">user</tt> is described, which contains four columns. The

242

primary key of the table consists of the <tt class="docutils literal">user_id</tt> column. Multiple columns

243

may be assigned the <tt class="docutils literal">primary_key=True</tt> flag which denotes a multi-column

244

primary key, known as a composite primary key.

245

Note also that each column describes its datatype using objects corresponding

246

to genericized types, such as <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

247

<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>. SQLAlchemy features dozens of types of

248

varying levels of specificity as well as the ability to create custom types.

249

Documentation on the type system can be found at types.

250

251

<h3>Accessing Tables and Columns<a class="headerlink" href="#accessing-tables-and-columns" title="Permalink to this headline">¶</a></h3>

252

The <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object contains all of the schema

253

constructs we’ve associated with it. It supports a few methods of accessing

254

these table objects, such as the <tt class="docutils literal">sorted_tables</tt> accessor which returns a

255

list of each <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object in order of foreign key

256

dependency (that is, each table is preceded by all tables which it

257

references):

258

<div class="highlight-python"><div class="highlight"><pre>>>> for t in metadata.sorted_tables:

259

... print t.name

260

user

261

user_preference

262

invoice

263

invoice_item</pre></div>

264

</div>

265

In most cases, individual <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects have been

266

explicitly declared, and these objects are typically accessed directly as

267

module-level variables in an application. Once a

268

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> has been defined, it has a full set of

269

accessors which allow inspection of its properties. Given the following

270

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> definition:

271

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

272

Column('employee_id', Integer, primary_key=True),

273

Column('employee_name', String(60), nullable=False),

274

Column('employee_dept', Integer, ForeignKey("departments.department_id"))

275

)</pre></div>

276

</div>

277

Note the <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object used in this table -

278

this construct defines a reference to a remote table, and is fully described

279

in <a class="reference internal" href="#metadata-foreignkeys">Defining Foreign Keys</a>. Methods of accessing information about this

280

table include:

281

<div class="highlight-python"><div class="highlight"><pre># access the column "EMPLOYEE_ID":

282

employees.columns.employee_id

283

284

# or just

285

employees.c.employee_id

286

287

# via string

288

employees.c['employee_id']

289

290

# iterate through all columns

291

for c in employees.c:

292

print c

293

294

# get the table's primary key columns

295

for primary_key in employees.primary_key:

296

print primary_key

297

298

# get the table's foreign key objects:

299

for fkey in employees.foreign_keys:

300

print fkey

301

302

# access the table's MetaData:

303

employees.metadata

304

305

# access the table's bound Engine or Connection, if its MetaData is bound:

306

employees.bind

307

308

# access a column's name, type, nullable, primary key, foreign key

309

employees.c.employee_id.name

310

employees.c.employee_id.type

311

employees.c.employee_id.nullable

312

employees.c.employee_id.primary_key

313

employees.c.employee_dept.foreign_keys

314

315

# get the "key" of a column, which defaults to its name, but can

316

# be any user-defined string:

317

employees.c.employee_name.key

318

319

# access a column's table:

320

employees.c.employee_id.table is employees

321

322

# get the table related by a foreign key

323

list(employees.c.employee_dept.foreign_keys)[0].column.table</pre></div>

324

</div>

325

</div>

326

327

<h3>Creating and Dropping Database Tables<a class="headerlink" href="#creating-and-dropping-database-tables" title="Permalink to this headline">¶</a></h3>

328

Once you’ve defined some <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects, assuming

329

you’re working with a brand new database one thing you might want to do is

330

issue CREATE statements for those tables and their related constructs (as an

331

aside, it’s also quite possible that you don’t want to do this, if you

332

already have some preferred methodology such as tools included with your

333

database or an existing scripting system - if that’s the case, feel free to

334

skip this section - SQLAlchemy has no requirement that it be used to create

335

your tables).

336

The usual way to issue CREATE is to use

337

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

338

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object. This method will issue queries

339

that first check for the existence of each individual table, and if not found

340

will issue the CREATE statements:

341

342

<div><div class="highlight-python+sql"><div class="highlight"><pre>engine = create_engine('sqlite:///:memory:')

343

344

metadata = MetaData()

345

346

user = Table('user', metadata,

347

Column('user_id', Integer, primary_key = True),

348

Column('user_name', String(16), nullable = False),

349

Column('email_address', String(60), key='email'),

350

Column('password', String(20), nullable = False)

351

)

352

353

user_prefs = Table('user_prefs', metadata,

354

Column('pref_id', Integer, primary_key=True),

355

Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),

356

Column('pref_name', String(40), nullable=False),

357

Column('pref_value', String(100))

358

)

359

360

<a href='#' class='sql_link'>sql</a>metadata.create_all(engine)

361

<div class='popup_sql'>PRAGMA table_info(user){}

362

CREATE TABLE user(

363

user_id INTEGER NOT NULL PRIMARY KEY,

364

user_name VARCHAR(16) NOT NULL,

365

email_address VARCHAR(60),

366

password VARCHAR(20) NOT NULL

367

)

368

PRAGMA table_info(user_prefs){}

369

CREATE TABLE user_prefs(

370

pref_id INTEGER NOT NULL PRIMARY KEY,

371

user_id INTEGER NOT NULL REFERENCES user(user_id),

372

pref_name VARCHAR(40) NOT NULL,

373

pref_value VARCHAR(100)

374

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

375

</div>

376

</div></blockquote>

377

<a class="reference internal" href="#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal">create_all()</tt></a> creates foreign key constraints

378

between tables usually inline with the table definition itself, and for this

379

reason it also generates the tables in order of their dependency. There are

380

options to change this behavior such that <tt class="docutils literal">ALTER TABLE</tt> is used instead.

381

Dropping all tables is similarly achieved using the

382

<a class="reference internal" href="#sqlalchemy.schema.MetaData.drop_all" title="sqlalchemy.schema.MetaData.drop_all"><tt class="xref py py-func docutils literal">drop_all()</tt></a> method. This method does the

383

exact opposite of <a class="reference internal" href="#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal">create_all()</tt></a> - the

384

presence of each table is checked first, and tables are dropped in reverse

385

order of dependency.

386

Creating and dropping individual tables can be done via the <tt class="docutils literal">create()</tt> and

387

<tt class="docutils literal">drop()</tt> methods of <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. These methods by

388

default issue the CREATE or DROP regardless of the table being present:

389

<div class="highlight-python+sql"><div class="highlight"><pre>engine = create_engine('sqlite:///:memory:')

390

391

meta = MetaData()

392

393

employees = Table('employees', meta,

394

Column('employee_id', Integer, primary_key=True),

395

Column('employee_name', String(60), nullable=False, key='name'),

396

Column('employee_dept', Integer, ForeignKey("departments.department_id"))

397

)

398

<a href='#' class='sql_link'>sql</a>employees.create(engine)

399

<div class='popup_sql'>CREATE TABLE employees(

400

employee_id SERIAL NOT NULL PRIMARY KEY,

401

employee_name VARCHAR(60) NOT NULL,

402

employee_dept INTEGER REFERENCES departments(department_id)

403

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

404

</div>

405

<tt class="docutils literal">drop()</tt> method:

406

<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a>employees.drop(engine)

407

<div class='popup_sql'>DROP TABLE employee</div></pre></div>

408

</div>

409

To enable the “check first for the table existing” logic, add the

410

<tt class="docutils literal">checkfirst=True</tt> argument to <tt class="docutils literal">create()</tt> or <tt class="docutils literal">drop()</tt>:

411

<div class="highlight-python"><div class="highlight"><pre>employees.create(engine, checkfirst=True)

412

employees.drop(engine, checkfirst=False)</pre></div>

413

</div>

414

</div>

415

416

<h3>Altering Schemas through Migrations<a class="headerlink" href="#altering-schemas-through-migrations" title="Permalink to this headline">¶</a></h3>

417

While SQLAlchemy directly supports emitting CREATE and DROP statements for schema

418

constructs, the ability to alter those constructs, usually via the ALTER statement

419

as well as other database-specific constructs, is outside of the scope of SQLAlchemy

420

itself. While it’s easy enough to emit ALTER statements and similar by hand,

421

such as by passing a string to <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><tt class="xref py py-meth docutils literal">Connection.execute()</tt></a> or by using the

422

<a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><tt class="xref py py-class docutils literal">DDL</tt></a> construct, it’s a common practice to automate the maintenance of

423

database schemas in relation to application code using schema migration tools.

424

There are two major migration tools available for SQLAlchemy:

425

426

<li><a class="reference external" href="http://alembic.readthedocs.org">Alembic</a> - Written by the author of SQLAlchemy,

427

Alembic features a highly customizable environment and a minimalistic usage pattern,

428

supporting such features as transactional DDL, automatic generation of “candidate”

429

migrations, an “offline” mode which generates SQL scripts, and support for branch

430

resolution.</li>

431

<li><a class="reference external" href="http://code.google.com/p/sqlalchemy-migrate/">SQLAlchemy-Migrate</a> - The original

432

migration tool for SQLAlchemy, SQLAlchemy-Migrate is widely used and continues

433

under active development. SQLAlchemy-Migrate includes features such as

434

SQL script generation, ORM class generation, ORM model comparison, and extensive

435

support for SQLite migrations.</li>

436

</ul>

437

</div>

438

439

<h3>Specifying the Schema Name<a class="headerlink" href="#specifying-the-schema-name" title="Permalink to this headline">¶</a></h3>

440

Some databases support the concept of multiple schemas. A

441

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> can reference this by specifying the

442

<tt class="docutils literal">schema</tt> keyword argument:

443

<div class="highlight-python"><div class="highlight"><pre>financial_info = Table('financial_info', meta,

444

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

445

Column('value', String(100), nullable=False),

446

schema='remote_banks'

447

)</pre></div>

448

</div>

449

Within the <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> collection, this table will be

450

identified by the combination of <tt class="docutils literal">financial_info</tt> and <tt class="docutils literal">remote_banks</tt>. If

451

another table called <tt class="docutils literal">financial_info</tt> is referenced without the

452

<tt class="docutils literal">remote_banks</tt> schema, it will refer to a different

453

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

454

objects can specify references to columns in this table using the form

455

<tt class="docutils literal">remote_banks.financial_info.id</tt>.

456

The <tt class="docutils literal">schema</tt> argument should be used for any name qualifiers required,

457

including Oracle’s “owner” attribute and similar. It also can accommodate a

458

dotted name for longer schemes:

459

<div class="highlight-python"><div class="highlight"><pre>schema="dbo.scott"</pre></div>

460

</div>

461

</div>

462

463

<h3>Backend-Specific Options<a class="headerlink" href="#backend-specific-options" title="Permalink to this headline">¶</a></h3>

464

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> supports database-specific options. For

465

example, MySQL has different table backend types, including “MyISAM” and

466

“InnoDB”. This can be expressed with <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> using

467

<tt class="docutils literal">mysql_engine</tt>:

468

<div class="highlight-python"><div class="highlight"><pre>addresses = Table('engine_email_addresses', meta,

469

Column('address_id', Integer, primary_key = True),

470

Column('remote_user_id', Integer, ForeignKey(users.c.user_id)),

471

Column('email_address', String(20)),

472

mysql_engine='InnoDB'

473

)</pre></div>

474

</div>

475

Other backends may support table-level options as well - these would be

476

described in the individual documentation sections for each dialect.

477

</div>

478

479

<h3>Column, Table, MetaData API<a class="headerlink" href="#column-table-metadata-api" title="Permalink to this headline">¶</a></h3>

480

481

482

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">Column</tt><big>(</big>*args, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column" title="Permalink to this definition">¶</a></dt>

483

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnClause" title="sqlalchemy.sql.expression.ColumnClause"><tt class="xref py py-class docutils literal">sqlalchemy.sql.expression.ColumnClause</tt></a>

484

Represents a column in a database table.

485

486

487

<tt class="descname">__eq__</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.__eq__" title="Permalink to this definition">¶</a></dt>

488

489

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.__eq__" title="sqlalchemy.sql.operators.ColumnOperators.__eq__"><tt class="xref py py-meth docutils literal">__eq__()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

490

Implement the <tt class="docutils literal">==</tt> operator.

491

In a column context, produces the clause <tt class="docutils literal">a = b</tt>.

492

If the target is <tt class="docutils literal">None</tt>, produces <tt class="docutils literal">a IS NULL</tt>.

493

</dd></dl>

494

495

496

497

<tt class="descname">__init__</tt><big>(</big>*args, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.__init__" title="Permalink to this definition">¶</a></dt>

498

<dd>Construct a new <tt class="docutils literal">Column</tt> object.

499

500

501

502

503

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

504

<li>name – The name of this column as represented in the database.

505

This argument may be the first positional argument, or specified

506

via keyword.

507

Names which contain no upper case characters

508

will be treated as case insensitive names, and will not be quoted

509

unless they are a reserved word. Names with any number of upper

510

case characters will be quoted and sent exactly. Note that this

511

behavior applies even for databases which standardize upper

512

case names as case insensitive such as Oracle.

513

The name field may be omitted at construction time and applied

514

later, at any time before the Column is associated with a

515

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. This is to support convenient

516

usage within the <a class="reference internal" href="../orm/extensions/declarative.html#module-sqlalchemy.ext.declarative" title="sqlalchemy.ext.declarative"><tt class="xref py py-mod docutils literal">declarative</tt></a> extension.

517

</li>

518

<li>type_ – The column’s type, indicated using an instance which

519

subclasses <a class="reference internal" href="types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal">TypeEngine</tt></a>. If no arguments

520

are required for the type, the class of the type can be sent

521

as well, e.g.:

522

<div class="highlight-python"><div class="highlight"><pre># use a type with arguments

523

Column('data', String(50))

524

525

# use no arguments

526

Column('level', Integer)</pre></div>

527

</div>

528

The <tt class="docutils literal">type</tt> argument may be the second positional argument

529

or specified by keyword.

530

There is partial support for automatic detection of the

531

type based on that of a <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> associated

532

with this column, if the type is specified as <tt class="docutils literal">None</tt>.

533

However, this feature is not fully implemented and

534

may not function in all cases.

535

</li>

536

<li>*args – Additional positional arguments include various

537

<a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">SchemaItem</tt></a> derived constructs which will be applied

538

as options to the column. These include instances of

539

<a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">Constraint</tt></a>, <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>, <a class="reference internal" href="#sqlalchemy.schema.ColumnDefault" title="sqlalchemy.schema.ColumnDefault"><tt class="xref py py-class docutils literal">ColumnDefault</tt></a>,

540

and <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a>. In some cases an equivalent keyword

541

argument is available such as <tt class="docutils literal">server_default</tt>, <tt class="docutils literal">default</tt>

542

and <tt class="docutils literal">unique</tt>.</li>

543

<li>autoincrement – This flag may be set to <tt class="docutils literal">False</tt> to

544

indicate an integer primary key column that should not be

545

considered to be the “autoincrement” column, that is

546

the integer primary key column which generates values

547

implicitly upon INSERT and whose value is usually returned

548

via the DBAPI cursor.lastrowid attribute. It defaults

549

to <tt class="docutils literal">True</tt> to satisfy the common use case of a table

550

with a single integer primary key column. If the table

551

has a composite primary key consisting of more than one

552

integer column, set this flag to True only on the

553

column that should be considered “autoincrement”.

554

The setting only has an effect for columns which are:

555

<ul>

556

<li>Integer derived (i.e. INT, SMALLINT, BIGINT).</li>

557

<li>Part of the primary key</li>

558

<li>Are not referenced by any foreign keys, unless

559

the value is specified as <tt class="docutils literal">'ignore_fk'</tt>

560

New in version 0.7.4.

561

</li>

562

<li>have no server side or client side defaults (with the exception

563

of Postgresql SERIAL).</li>

564

</ul>

565

The setting has these two effects on columns that meet the

566

above criteria:

567

<ul>

568

<li>DDL issued for the column will include database-specific

569

keywords intended to signify this column as an

570

“autoincrement” column, such as AUTO INCREMENT on MySQL,

571

SERIAL on Postgresql, and IDENTITY on MS-SQL. It does

572

not issue AUTOINCREMENT for SQLite since this is a

573

special SQLite flag that is not required for autoincrementing

574

behavior. See the SQLite dialect documentation for

575

information on SQLite’s AUTOINCREMENT.</li>

576

<li>The column will be considered to be available as

577

cursor.lastrowid or equivalent, for those dialects which

578

“post fetch” newly inserted identifiers after a row has

579

been inserted (SQLite, MySQL, MS-SQL). It does not have

580

any effect in this regard for databases that use sequences

581

to generate primary key identifiers (i.e. Firebird, Postgresql,

582

Oracle).</li>

583

</ul>

584

585

Changed in version 0.7.4: <tt class="docutils literal">autoincrement</tt> accepts a special value <tt class="docutils literal">'ignore_fk'</tt>

586

to indicate that autoincrementing status regardless of foreign

587

key references. This applies to certain composite foreign key

588

setups, such as the one demonstrated in the ORM documentation

589

at <a class="reference internal" href="../orm/relationships.html#post-update">Rows that point to themselves / Mutually Dependent Rows</a>.

590

</li>

591

<li>default – A scalar, Python callable, or

592

<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> expression representing the

593

default value for this column, which will be invoked upon insert

594

if this column is otherwise not specified in the VALUES clause of

595

the insert. This is a shortcut to using <a class="reference internal" href="#sqlalchemy.schema.ColumnDefault" title="sqlalchemy.schema.ColumnDefault"><tt class="xref py py-class docutils literal">ColumnDefault</tt></a> as

596

a positional argument; see that class for full detail on the

597

structure of the argument.

598

Contrast this argument to <tt class="docutils literal">server_default</tt> which creates a

599

default generator on the database side.

600

</li>

601

<li>doc – optional String that can be used by the ORM or similar

602

to document attributes. This attribute does not render SQL

603

comments (a future attribute ‘comment’ will achieve that).</li>

604

<li>key – An optional string identifier which will identify this

605

<tt class="docutils literal">Column</tt> object on the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. When a key is provided,

606

this is the only identifier referencing the <tt class="docutils literal">Column</tt> within the

607

application, including ORM attribute mapping; the <tt class="docutils literal">name</tt> field

608

is used only when rendering SQL.</li>

609

<li>index – When <tt class="docutils literal">True</tt>, indicates that the column is indexed.

610

This is a shortcut for using a <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct on the

611

table. To specify indexes with explicit names or indexes that

612

contain multiple columns, use the <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct

613

instead.</li>

614

<li>info – Optional data dictionary which will be populated into the

615

<a class="reference internal" href="#sqlalchemy.schema.SchemaItem.info" title="sqlalchemy.schema.SchemaItem.info"><tt class="xref py py-attr docutils literal">SchemaItem.info</tt></a> attribute of this object.</li>

616

<li>nullable – If set to the default of <tt class="docutils literal">True</tt>, indicates the

617

column will be rendered as allowing NULL, else it’s rendered as

618

NOT NULL. This parameter is only used when issuing CREATE TABLE

619

statements.</li>

620

<li>onupdate – A scalar, Python callable, or

621

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a> representing a

622

default value to be applied to the column within UPDATE

623

statements, which wil be invoked upon update if this column is not

624

present in the SET clause of the update. This is a shortcut to

625

using <a class="reference internal" href="#sqlalchemy.schema.ColumnDefault" title="sqlalchemy.schema.ColumnDefault"><tt class="xref py py-class docutils literal">ColumnDefault</tt></a> as a positional argument with

626

<tt class="docutils literal">for_update=True</tt>.</li>

627

<li>primary_key – If <tt class="docutils literal">True</tt>, marks this column as a primary key

628

column. Multiple columns can have this flag set to specify

629

composite primary keys. As an alternative, the primary key of a

630

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> can be specified via an explicit

631

<a class="reference internal" href="#sqlalchemy.schema.PrimaryKeyConstraint" title="sqlalchemy.schema.PrimaryKeyConstraint"><tt class="xref py py-class docutils literal">PrimaryKeyConstraint</tt></a> object.</li>

632

<li>server_default – A <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a> instance, str, Unicode

633

or <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 representing

634

the DDL DEFAULT value for the column.

635

String types will be emitted as-is, surrounded by single quotes:

636

<div class="highlight-python"><pre>Column('x', Text, server_default="val")

637

638

x TEXT DEFAULT 'val'</pre>

639

</div>

640

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> expression will be

641

rendered as-is, without quotes:

642

<div class="highlight-python"><pre>Column('y', DateTime, server_default=text('NOW()'))0

643

644

y DATETIME DEFAULT NOW()</pre>

645

</div>

646

Strings and text() will be converted into a <a class="reference internal" href="#sqlalchemy.schema.DefaultClause" title="sqlalchemy.schema.DefaultClause"><tt class="xref py py-class docutils literal">DefaultClause</tt></a>

647

object upon initialization.

648

Use <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a> to indicate that an already-existing

649

column will generate a default value on the database side which

650

will be available to SQLAlchemy for post-fetch after inserts. This

651

construct does not specify any DDL and the implementation is left

652

to the database, such as via a trigger.

653

</li>

654

<li>server_onupdate – A <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a> instance

655

representing a database-side default generation function. This

656

indicates to SQLAlchemy that a newly generated value will be

657

available after updates. This construct does not specify any DDL

658

and the implementation is left to the database, such as via a

659

trigger.</li>

660

<li>quote – Force quoting of this column’s name on or off,

661

corresponding to <tt class="docutils literal">True</tt> or <tt class="docutils literal">False</tt>. When left at its default

662

of <tt class="docutils literal">None</tt>, the column identifier will be quoted according to

663

whether the name is case sensitive (identifiers with at least one

664

upper case character are treated as case sensitive), or if it’s a

665

reserved word. This flag is only needed to force quoting of a

666

reserved word which is not known by the SQLAlchemy dialect.</li>

667

<li>unique – When <tt class="docutils literal">True</tt>, indicates that this column contains a

668

unique constraint, or if <tt class="docutils literal">index</tt> is <tt class="docutils literal">True</tt> as well, indicates

669

that the <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> should be created with the unique flag.

670

To specify multiple columns in the constraint/index or to specify

671

an explicit name, use the <a class="reference internal" href="#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><tt class="xref py py-class docutils literal">UniqueConstraint</tt></a> or

672

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> constructs explicitly.</li>

673

</ul>

674

</td>

675

</tr>

676

</tbody>

677

</table>

678

</dd></dl>

679

680

681

682

<tt class="descname">__le__</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.__le__" title="Permalink to this definition">¶</a></dt>

683

684

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.__le__" title="sqlalchemy.sql.operators.ColumnOperators.__le__"><tt class="xref py py-meth docutils literal">__le__()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

685

Implement the <tt class="docutils literal"><=</tt> operator.

686

In a column context, produces the clause <tt class="docutils literal">a <= b</tt>.

687

</dd></dl>

688

689

690

691

<tt class="descname">__lt__</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.__lt__" title="Permalink to this definition">¶</a></dt>

692

693

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.__lt__" title="sqlalchemy.sql.operators.ColumnOperators.__lt__"><tt class="xref py py-meth docutils literal">__lt__()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

694

Implement the <tt class="docutils literal"><</tt> operator.

695

In a column context, produces the clause <tt class="docutils literal">a < b</tt>.

696

</dd></dl>

697

698

699

700

<tt class="descname">__ne__</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.__ne__" title="Permalink to this definition">¶</a></dt>

701

702

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.__ne__" title="sqlalchemy.sql.operators.ColumnOperators.__ne__"><tt class="xref py py-meth docutils literal">__ne__()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

703

Implement the <tt class="docutils literal">!=</tt> operator.

704

In a column context, produces the clause <tt class="docutils literal">a != b</tt>.

705

If the target is <tt class="docutils literal">None</tt>, produces <tt class="docutils literal">a IS NOT NULL</tt>.

706

</dd></dl>

707

708

709

710

<tt class="descname">anon_label</tt><a class="headerlink" href="#sqlalchemy.schema.Column.anon_label" title="Permalink to this definition">¶</a></dt>

711

712

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.anon_label" title="sqlalchemy.sql.expression.ColumnElement.anon_label"><tt class="xref py py-attr docutils literal">anon_label</tt></a> attribute of <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></div>

713

provides a constant ‘anonymous label’ for this ColumnElement.

714

This is a label() expression which will be named at compile time.

715

The same label() is returned each time anon_label is called so

716

that expressions can reference anon_label multiple times, producing

717

the same label name at compile time.

718

the compiler uses this function automatically at compile time

719

for expressions that are known to be ‘unnamed’ like binary

720

expressions and function calls.

721

</dd></dl>

722

723

724

725

<tt class="descname">append_foreign_key</tt><big>(</big>fk<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.append_foreign_key" title="Permalink to this definition">¶</a></dt>

726

727

728

729

730

731

732

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.asc" title="sqlalchemy.sql.operators.ColumnOperators.asc"><tt class="xref py py-meth docutils literal">asc()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

733

Produce a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.asc" title="sqlalchemy.sql.expression.asc"><tt class="xref py py-func docutils literal">asc()</tt></a> clause against the

734

parent object.

735

</dd></dl>

736

737

738

739

<tt class="descname">base_columns</tt><a class="headerlink" href="#sqlalchemy.schema.Column.base_columns" title="Permalink to this definition">¶</a></dt>

740

741

inherited from the <tt class="xref py py-attr docutils literal">base_columns</tt> attribute of <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></div>

742

</dd></dl>

743

744

745

746

<tt class="descname">between</tt><big>(</big>cleft, cright<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.between" title="Permalink to this definition">¶</a></dt>

747

748

inherited from the <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> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

749

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

750

the parent object, given the lower and upper range.

751

</dd></dl>

752

753

754

755

756

757

758

759

760

<tt class="descname">collate</tt><big>(</big>collation<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.collate" title="Permalink to this definition">¶</a></dt>

761

762

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.collate" title="sqlalchemy.sql.operators.ColumnOperators.collate"><tt class="xref py py-meth docutils literal">collate()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

763

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

764

the parent object, given the collation string.

765

</dd></dl>

766

767

768

769

<tt class="descname">comparator</tt><a class="headerlink" href="#sqlalchemy.schema.Column.comparator" title="Permalink to this definition">¶</a></dt>

770

771

inherited from the <tt class="xref py py-attr docutils literal">comparator</tt> attribute of <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></div>

772

</dd></dl>

773

774

775

776

<tt class="descname">compare</tt><big>(</big>other, use_proxies=False, equivalents=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.compare" title="Permalink to this definition">¶</a></dt>

777

778

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.compare" title="sqlalchemy.sql.expression.ColumnElement.compare"><tt class="xref py py-meth docutils literal">compare()</tt></a> method of <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></div>

779

Compare this ColumnElement to another.

780

Special arguments understood:

781

782

783

784

785

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

786

<li>use_proxies – when True, consider two columns that

787

share a common base column as equivalent (i.e. shares_lineage())</li>

788

<li>equivalents – a dictionary of columns as keys mapped to sets

789

of columns. If the given “other” column is present in this

790

dictionary, if any of the columns in the corresponding set() pass the

791

comparison test, the result is True. This is used to expand the

792

comparison to other columns that may be known to be equivalent to

793

this one via foreign key or other criterion.</li>

794

</ul>

795

</td>

796

</tr>

797

</tbody>

798

</table>

799

</dd></dl>

800

801

802

803

<tt class="descname">compile</tt><big>(</big>bind=None, dialect=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.compile" title="Permalink to this definition">¶</a></dt>

804

805

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement.compile" title="sqlalchemy.sql.expression.ClauseElement.compile"><tt class="xref py py-meth docutils literal">compile()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a></div>

806

Compile this SQL expression.

807

The return value is a <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><tt class="xref py py-class docutils literal">Compiled</tt></a> object.

808

Calling <tt class="docutils literal">str()</tt> or <tt class="docutils literal">unicode()</tt> on the returned value will yield a

809

string representation of the result. The

810

<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><tt class="xref py py-class docutils literal">Compiled</tt></a> object also can return a

811

dictionary of bind parameter names and values

812

using the <tt class="docutils literal">params</tt> accessor.

813

814

815

816

817

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

818

<li>bind – An <tt class="docutils literal">Engine</tt> or <tt class="docutils literal">Connection</tt> from which a

819

<tt class="docutils literal">Compiled</tt> will be acquired. This argument takes precedence over

820

this <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a>‘s bound engine, if any.</li>

821

<li>column_keys – Used for INSERT and UPDATE statements, a list of

822

column names which should be present in the VALUES clause of the

823

compiled statement. If <tt class="docutils literal">None</tt>, all columns from the target table

824

object are rendered.</li>

825

<li>dialect – A <tt class="docutils literal">Dialect</tt> instance from which a <tt class="docutils literal">Compiled</tt>

826

will be acquired. This argument takes precedence over the <cite>bind</cite>

827

argument as well as this <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a>‘s bound engine, if

828

any.</li>

829

<li>inline – Used for INSERT statements, for a dialect which does

830

not support inline retrieval of newly generated primary key

831

columns, will force the expression used to create the new primary

832

key value to be rendered inline within the INSERT statement’s

833

VALUES clause. This typically refers to Sequence execution but may

834

also refer to any server-side default generation function

835

associated with a primary key <cite>Column</cite>.</li>

836

</ul>

837

</td>

838

</tr>

839

</tbody>

840

</table>

841

</dd></dl>

842

843

844

845

<tt class="descname">concat</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.concat" title="Permalink to this definition">¶</a></dt>

846

847

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.concat" title="sqlalchemy.sql.operators.ColumnOperators.concat"><tt class="xref py py-meth docutils literal">concat()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

848

Implement the ‘concat’ operator.

849

In a column context, produces the clause <tt class="docutils literal">a || b</tt>,

850

or uses the <tt class="docutils literal">concat()</tt> operator on MySQL.

851

</dd></dl>

852

853

854

855

<tt class="descname">contains</tt><big>(</big>other, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.contains" title="Permalink to this definition">¶</a></dt>

856

857

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.contains" title="sqlalchemy.sql.operators.ColumnOperators.contains"><tt class="xref py py-meth docutils literal">contains()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

858

Implement the ‘contains’ operator.

859

In a column context, produces the clause <tt class="docutils literal">LIKE '%<other>%'</tt>

860

</dd></dl>

861

862

863

864

865

<dd>Create a copy of this <tt class="docutils literal">Column</tt>, unitialized.

866

This is used in <tt class="docutils literal">Table.tometadata</tt>.

867

</dd></dl>

868

869

870

871

<tt class="descname">default</tt> = None<a class="headerlink" href="#sqlalchemy.schema.Column.default" title="Permalink to this definition">¶</a></dt>

872

873

874

875

876

877

878

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.desc" title="sqlalchemy.sql.operators.ColumnOperators.desc"><tt class="xref py py-meth docutils literal">desc()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

879

Produce a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.desc" title="sqlalchemy.sql.expression.desc"><tt class="xref py py-func docutils literal">desc()</tt></a> clause against the

880

parent object.

881

</dd></dl>

882

883

884

885

<tt class="descname">description</tt><a class="headerlink" href="#sqlalchemy.schema.Column.description" title="Permalink to this definition">¶</a></dt>

886

887

inherited from the <tt class="xref py py-attr docutils literal">description</tt> attribute of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnClause" title="sqlalchemy.sql.expression.ColumnClause"><tt class="xref py py-class docutils literal">ColumnClause</tt></a></div>

888

</dd></dl>

889

890

891

892

<tt class="descname">dispatch</tt><a class="headerlink" href="#sqlalchemy.schema.Column.dispatch" title="Permalink to this definition">¶</a></dt>

893

<dd>alias of <tt class="xref py py-class docutils literal">DDLEventsDispatch</tt>

894

</dd></dl>

895

896

897

898

<tt class="descname">distinct</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.distinct" title="Permalink to this definition">¶</a></dt>

899

900

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.distinct" title="sqlalchemy.sql.operators.ColumnOperators.distinct"><tt class="xref py py-meth docutils literal">distinct()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

901

Produce a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.distinct" title="sqlalchemy.sql.expression.distinct"><tt class="xref py py-func docutils literal">distinct()</tt></a> clause against the

902

parent object.

903

</dd></dl>

904

905

906

907

<tt class="descname">endswith</tt><big>(</big>other, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.endswith" title="Permalink to this definition">¶</a></dt>

908

909

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.endswith" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><tt class="xref py py-meth docutils literal">endswith()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

910

Implement the ‘endswith’ operator.

911

In a column context, produces the clause <tt class="docutils literal">LIKE '%<other>'</tt>

912

</dd></dl>

913

914

915

916

<tt class="descname">expression</tt><a class="headerlink" href="#sqlalchemy.schema.Column.expression" title="Permalink to this definition">¶</a></dt>

917

918

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.expression" title="sqlalchemy.sql.expression.ColumnElement.expression"><tt class="xref py py-attr docutils literal">expression</tt></a> attribute of <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></div>

919

Return a column expression.

920

Part of the inspection interface; returns self.

921

</dd></dl>

922

923

924

925

<tt class="descname">foreign_keys</tt> = []<a class="headerlink" href="#sqlalchemy.schema.Column.foreign_keys" title="Permalink to this definition">¶</a></dt>

926

927

928

929

930

<tt class="descname">get_children</tt><big>(</big>schema_visitor=False, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.get_children" title="Permalink to this definition">¶</a></dt>

931

932

933

934

935

<tt class="descname">ilike</tt><big>(</big>other, escape=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.ilike" title="Permalink to this definition">¶</a></dt>

936

937

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.ilike" title="sqlalchemy.sql.operators.ColumnOperators.ilike"><tt class="xref py py-meth docutils literal">ilike()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

938

Implement the <tt class="docutils literal">ilike</tt> operator.

939

In a column context, produces the clause <tt class="docutils literal">a ILIKE other</tt>.

940

E.g.:

941

<div class="highlight-python"><div class="highlight"><pre>select([sometable]).where(sometable.c.column.ilike("%foobar%"))</pre></div>

942

</div>

943

944

945

946

947

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

948

<li>other – expression to be compared</li>

949

<li>escape – optional escape character, renders the <tt class="docutils literal">ESCAPE</tt>

950

keyword, e.g.:

951

<div class="highlight-python"><div class="highlight"><pre>somecolumn.ilike("foo/%bar", escape="/")</pre></div>

952

</div>

953

</li>

954

</ul>

955

</td>

956

</tr>

957

</tbody>

958

</table>

959

960

See also

961

<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">ColumnOperators.like()</tt></a>

962

</div>

963

</dd></dl>

964

965

966

967

<tt class="descname">in_</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.in_" title="Permalink to this definition">¶</a></dt>

968

969

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><tt class="xref py py-meth docutils literal">in_()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

970

Implement the <tt class="docutils literal">in</tt> operator.

971

In a column context, produces the clause <tt class="docutils literal">a IN other</tt>.

972

“other” may be a tuple/list of column expressions,

973

or 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.

974

</dd></dl>

975

976

977

978

979

980

inherited from the <a class="reference internal" href="#sqlalchemy.schema.SchemaItem.info" title="sqlalchemy.schema.SchemaItem.info"><tt class="xref py py-attr docutils literal">info</tt></a> attribute of <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">SchemaItem</tt></a></div>

981

Info dictionary associated with the object, allowing user-defined

982

data to be associated with this <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">SchemaItem</tt></a>.

983

The dictionary is automatically generated when first accessed.

984

It can also be specified in the constructor of some objects,

985

such as <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> and <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>.

986

</dd></dl>

987

988

989

990

<tt class="descname">is_</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.is_" title="Permalink to this definition">¶</a></dt>

991

992

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.is_" title="sqlalchemy.sql.operators.ColumnOperators.is_"><tt class="xref py py-meth docutils literal">is_()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

993

Implement the <tt class="docutils literal">IS</tt> operator.

994

Normally, <tt class="docutils literal">IS</tt> is generated automatically when comparing to a

995

value of <tt class="docutils literal">None</tt>, which resolves to <tt class="docutils literal">NULL</tt>. However, explicit

996

usage of <tt class="docutils literal">IS</tt> may be desirable if comparing to boolean values

997

on certain platforms.

998

999

New in version 0.7.9.

1000

1001

See also

1002

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

1003

</div>

1004

</dd></dl>

1005

1006

1007

1008

<tt class="descname">is_clause_element</tt> = True<a class="headerlink" href="#sqlalchemy.schema.Column.is_clause_element" title="Permalink to this definition">¶</a></dt>

1009

1010

1011

1012

1013

<tt class="descname">is_selectable</tt> = False<a class="headerlink" href="#sqlalchemy.schema.Column.is_selectable" title="Permalink to this definition">¶</a></dt>

1014

1015

1016

1017

1018

<tt class="descname">isnot</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.isnot" title="Permalink to this definition">¶</a></dt>

1019

1020

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.isnot" title="sqlalchemy.sql.operators.ColumnOperators.isnot"><tt class="xref py py-meth docutils literal">isnot()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1021

Implement the <tt class="docutils literal">IS NOT</tt> operator.

1022

Normally, <tt class="docutils literal">IS NOT</tt> is generated automatically when comparing to a

1023

value of <tt class="docutils literal">None</tt>, which resolves to <tt class="docutils literal">NULL</tt>. However, explicit

1024

usage of <tt class="docutils literal">IS NOT</tt> may be desirable if comparing to boolean values

1025

on certain platforms.

1026

1027

New in version 0.7.9.

1028

1029

See also

1030

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

1031

</div>

1032

</dd></dl>

1033

1034

1035

1036

<tt class="descname">label</tt><big>(</big>name<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.label" title="Permalink to this definition">¶</a></dt>

1037

1038

inherited from the <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> method of <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></div>

1039

Produce a column label, i.e. <tt class="docutils literal"><columnname> AS <name></tt>.

1040

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

1041

if ‘name’ is None, an anonymous label name will be generated.

1042

</dd></dl>

1043

1044

1045

1046

<tt class="descname">like</tt><big>(</big>other, escape=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.like" title="Permalink to this definition">¶</a></dt>

1047

1048

inherited from the <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> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1049

Implement the <tt class="docutils literal">like</tt> operator.

1050

In a column context, produces the clause <tt class="docutils literal">a LIKE other</tt>.

1051

E.g.:

1052

<div class="highlight-python"><div class="highlight"><pre>select([sometable]).where(sometable.c.column.like("%foobar%"))</pre></div>

1053

</div>

1054

1055

1056

1057

1058

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1059

<li>other – expression to be compared</li>

1060

<li>escape – optional escape character, renders the <tt class="docutils literal">ESCAPE</tt>

1061

keyword, e.g.:

1062

<div class="highlight-python"><div class="highlight"><pre>somecolumn.like("foo/%bar", escape="/")</pre></div>

1063

</div>

1064

</li>

1065

</ul>

1066

</td>

1067

</tr>

1068

</tbody>

1069

</table>

1070

1071

See also

1072

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

1073

</div>

1074

</dd></dl>

1075

1076

1077

1078

<tt class="descname">match</tt><big>(</big>other, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.match" title="Permalink to this definition">¶</a></dt>

1079

1080

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.match" title="sqlalchemy.sql.operators.ColumnOperators.match"><tt class="xref py py-meth docutils literal">match()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1081

Implements the ‘match’ operator.

1082

In a column context, this produces a MATCH clause, i.e.

1083

<tt class="docutils literal">MATCH '<other>'</tt>. The allowed contents of <tt class="docutils literal">other</tt>

1084

are database backend specific.

1085

</dd></dl>

1086

1087

1088

1089

<tt class="descname">notilike</tt><big>(</big>other, escape=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.notilike" title="Permalink to this definition">¶</a></dt>

1090

1091

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.notilike" title="sqlalchemy.sql.operators.ColumnOperators.notilike"><tt class="xref py py-meth docutils literal">notilike()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1092

implement the <tt class="docutils literal">NOT ILIKE</tt> operator.

1093

This is equivalent to using negation with

1094

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.ilike" title="sqlalchemy.sql.operators.ColumnOperators.ilike"><tt class="xref py py-meth docutils literal">ColumnOperators.ilike()</tt></a>, i.e. <tt class="docutils literal">~x.ilike(y)</tt>.

1095

1096

New in version 0.8.

1097

1098

See also

1099

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

1100

</div>

1101

</dd></dl>

1102

1103

1104

1105

<tt class="descname">notin_</tt><big>(</big>other<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.notin_" title="Permalink to this definition">¶</a></dt>

1106

1107

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.notin_" title="sqlalchemy.sql.operators.ColumnOperators.notin_"><tt class="xref py py-meth docutils literal">notin_()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1108

implement the <tt class="docutils literal">NOT IN</tt> operator.

1109

This is equivalent to using negation with <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><tt class="xref py py-meth docutils literal">ColumnOperators.in_()</tt></a>,

1110

i.e. <tt class="docutils literal">~x.in_(y)</tt>.

1111

1112

New in version 0.8.

1113

1114

See also

1115

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

1116

</div>

1117

</dd></dl>

1118

1119

1120

1121

<tt class="descname">notlike</tt><big>(</big>other, escape=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.notlike" title="Permalink to this definition">¶</a></dt>

1122

1123

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.notlike" title="sqlalchemy.sql.operators.ColumnOperators.notlike"><tt class="xref py py-meth docutils literal">notlike()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1124

implement the <tt class="docutils literal">NOT LIKE</tt> operator.

1125

This is equivalent to using negation with

1126

<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">ColumnOperators.like()</tt></a>, i.e. <tt class="docutils literal">~x.like(y)</tt>.

1127

1128

New in version 0.8.

1129

1130

See also

1131

<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">ColumnOperators.like()</tt></a>

1132

</div>

1133

</dd></dl>

1134

1135

1136

1137

<tt class="descname">nullsfirst</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.nullsfirst" title="Permalink to this definition">¶</a></dt>

1138

1139

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.nullsfirst" title="sqlalchemy.sql.operators.ColumnOperators.nullsfirst"><tt class="xref py py-meth docutils literal">nullsfirst()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1140

Produce a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.nullsfirst" title="sqlalchemy.sql.expression.nullsfirst"><tt class="xref py py-func docutils literal">nullsfirst()</tt></a> clause against the

1141

parent object.

1142

</dd></dl>

1143

1144

1145

1146

<tt class="descname">nullslast</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.nullslast" title="Permalink to this definition">¶</a></dt>

1147

1148

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.nullslast" title="sqlalchemy.sql.operators.ColumnOperators.nullslast"><tt class="xref py py-meth docutils literal">nullslast()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1149

Produce a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.nullslast" title="sqlalchemy.sql.expression.nullslast"><tt class="xref py py-func docutils literal">nullslast()</tt></a> clause against the

1150

parent object.

1151

</dd></dl>

1152

1153

1154

1155

<tt class="descname">onupdate</tt> = None<a class="headerlink" href="#sqlalchemy.schema.Column.onupdate" title="Permalink to this definition">¶</a></dt>

1156

1157

1158

1159

1160

<tt class="descname">op</tt><big>(</big>opstring, precedence=0<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.op" title="Permalink to this definition">¶</a></dt>

1161

1162

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><tt class="xref py py-meth docutils literal">op()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.Operators" title="sqlalchemy.sql.operators.Operators"><tt class="xref py py-class docutils literal">Operators</tt></a></div>

1163

produce a generic operator function.

1164

e.g.:

1165

<div class="highlight-python"><div class="highlight"><pre>somecolumn.op("*")(5)</pre></div>

1166

</div>

1167

produces:

1168

<div class="highlight-python"><div class="highlight"><pre>somecolumn * 5</pre></div>

1169

</div>

1170

This function can also be used to make bitwise operators explicit. For

1171

example:

1172

<div class="highlight-python"><div class="highlight"><pre>somecolumn.op('&')(0xff)</pre></div>

1173

</div>

1174

is a bitwise AND of the value in <tt class="docutils literal">somecolumn</tt>.

1175

1176

1177

1178

1179

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1180

<li>operator – a string which will be output as the infix operator

1181

between this element and the expression passed to the

1182

generated function.</li>

1183

<li>precedence – precedence to apply to the operator, when

1184

parenthesizing expressions. A lower number will cause the expression

1185

to be parenthesized when applied against another operator with

1186

higher precedence. The default value of <tt class="docutils literal">0</tt> is lower than all

1187

operators except for the comma (<tt class="docutils literal">,</tt>) and <tt class="docutils literal">AS</tt> operators.

1188

A value of 100 will be higher or equal to all operators, and -100

1189

will be lower than or equal to all operators.

1190

1191

New in version 0.8: - added the ‘precedence’ argument.

1192

</li>

1193

</ul>

1194

</td>

1195

</tr>

1196

</tbody>

1197

</table>

1198

1199

See also

1200

<a class="reference internal" href="types.html#types-operators">Redefining and Creating New Operators</a>

1201

</div>

1202

</dd></dl>

1203

1204

1205

1206

<tt class="descname">operate</tt><big>(</big>op, *other, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.operate" title="Permalink to this definition">¶</a></dt>

1207

1208

inherited from the <tt class="xref py py-meth docutils literal">operate()</tt> method of <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></div>

1209

</dd></dl>

1210

1211

1212

1213

<tt class="descname">params</tt><big>(</big>*optionaldict, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.params" title="Permalink to this definition">¶</a></dt>

1214

1215

inherited from the <tt class="xref py py-meth docutils literal">params()</tt> method of <tt class="xref py py-class docutils literal">Immutable</tt></div>

1216

</dd></dl>

1217

1218

1219

1220

<tt class="descname">primary_key</tt> = False<a class="headerlink" href="#sqlalchemy.schema.Column.primary_key" title="Permalink to this definition">¶</a></dt>

1221

1222

1223

1224

1225

<tt class="descname">proxy_set</tt><a class="headerlink" href="#sqlalchemy.schema.Column.proxy_set" title="Permalink to this definition">¶</a></dt>

1226

1227

inherited from the <tt class="xref py py-attr docutils literal">proxy_set</tt> attribute of <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></div>

1228

</dd></dl>

1229

1230

1231

1232

<tt class="descname">quote</tt> = None<a class="headerlink" href="#sqlalchemy.schema.Column.quote" title="Permalink to this definition">¶</a></dt>

1233

1234

1235

1236

1237

<tt class="descname">references</tt><big>(</big>column<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.references" title="Permalink to this definition">¶</a></dt>

1238

<dd>Return True if this Column references the given column via foreign

1239

key.

1240

</dd></dl>

1241

1242

1243

1244

<tt class="descname">reverse_operate</tt><big>(</big>op, other, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.reverse_operate" title="Permalink to this definition">¶</a></dt>

1245

1246

inherited from the <tt class="xref py py-meth docutils literal">reverse_operate()</tt> method of <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></div>

1247

</dd></dl>

1248

1249

1250

1251

<tt class="descname">self_group</tt><big>(</big>against=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.self_group" title="Permalink to this definition">¶</a></dt>

1252

1253

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement.self_group" title="sqlalchemy.sql.expression.ClauseElement.self_group"><tt class="xref py py-meth docutils literal">self_group()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a></div>

1254

Apply a ‘grouping’ to this <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a>.

1255

This method is overridden by subclasses to return a

1256

“grouping” construct, i.e. parenthesis. In particular

1257

it’s used by “binary” expressions to provide a grouping

1258

around themselves when placed into a larger expression,

1259

as well as by <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> constructs when placed into

1260

the FROM clause of 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>. (Note that

1261

subqueries should be normally created using the

1262

<tt class="xref py py-func docutils literal">Select.alias()</tt> method, as many platforms require

1263

nested SELECT statements to be named).

1264

As expressions are composed together, the application of

1265

<a class="reference internal" href="#sqlalchemy.schema.Column.self_group" title="sqlalchemy.schema.Column.self_group"><tt class="xref py py-meth docutils literal">self_group()</tt></a> is automatic - end-user code should never

1266

need to use this method directly. Note that SQLAlchemy’s

1267

clause constructs take operator precedence into account -

1268

so parenthesis might not be needed, for example, in

1269

an expression like <tt class="docutils literal">x OR (y AND z)</tt> - AND takes precedence

1270

over OR.

1271

The base <a class="reference internal" href="#sqlalchemy.schema.Column.self_group" title="sqlalchemy.schema.Column.self_group"><tt class="xref py py-meth docutils literal">self_group()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a>

1272

just returns self.

1273

</dd></dl>

1274

1275

1276

1277

<tt class="descname">server_default</tt> = None<a class="headerlink" href="#sqlalchemy.schema.Column.server_default" title="Permalink to this definition">¶</a></dt>

1278

1279

1280

1281

1282

<tt class="descname">server_onupdate</tt> = None<a class="headerlink" href="#sqlalchemy.schema.Column.server_onupdate" title="Permalink to this definition">¶</a></dt>

1283

1284

1285

1286

1287

<tt class="descname">shares_lineage</tt><big>(</big>othercolumn<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.shares_lineage" title="Permalink to this definition">¶</a></dt>

1288

1289

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.shares_lineage" title="sqlalchemy.sql.expression.ColumnElement.shares_lineage"><tt class="xref py py-meth docutils literal">shares_lineage()</tt></a> method of <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></div>

1290

Return True if the given <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>

1291

has a common ancestor to this <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>.

1292

</dd></dl>

1293

1294

1295

1296

<tt class="descname">startswith</tt><big>(</big>other, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.startswith" title="Permalink to this definition">¶</a></dt>

1297

1298

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.startswith" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><tt class="xref py py-meth docutils literal">startswith()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><tt class="xref py py-class docutils literal">ColumnOperators</tt></a></div>

1299

Implement the <tt class="docutils literal">startwith</tt> operator.

1300

In a column context, produces the clause <tt class="docutils literal">LIKE '<other>%'</tt>

1301

</dd></dl>

1302

1303

1304

1305

<tt class="descname">supports_execution</tt> = False<a class="headerlink" href="#sqlalchemy.schema.Column.supports_execution" title="Permalink to this definition">¶</a></dt>

1306

1307

1308

1309

1310

<tt class="descname">table</tt><a class="headerlink" href="#sqlalchemy.schema.Column.table" title="Permalink to this definition">¶</a></dt>

1311

1312

inherited from the <tt class="xref py py-attr docutils literal">table</tt> attribute of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnClause" title="sqlalchemy.sql.expression.ColumnClause"><tt class="xref py py-class docutils literal">ColumnClause</tt></a></div>

1313

</dd></dl>

1314

1315

1316

1317

<tt class="descname">timetuple</tt> = None<a class="headerlink" href="#sqlalchemy.schema.Column.timetuple" title="Permalink to this definition">¶</a></dt>

1318

1319

1320

1321

1322

1323

1324

inherited from the <tt class="xref py py-attr docutils literal">type</tt> attribute of <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></div>

1325

</dd></dl>

1326

1327

1328

1329

<tt class="descname">unique_params</tt><big>(</big>*optionaldict, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Column.unique_params" title="Permalink to this definition">¶</a></dt>

1330

1331

inherited from the <tt class="xref py py-meth docutils literal">unique_params()</tt> method of <tt class="xref py py-class docutils literal">Immutable</tt></div>

1332

</dd></dl>

1333

1334

</dd></dl>

1335

1336

1337

1338

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">MetaData</tt><big>(</big>bind=None, reflect=False, schema=None, quote_schema=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData" title="Permalink to this definition">¶</a></dt>

1339

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>

1340

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

1341

constructs.

1342

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

1343

an optional binding to an <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> or

1344

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a>. If bound, the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects

1345

in the collection and their columns may participate in implicit SQL

1346

execution.

1347

The <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects themselves are stored in the

1348

<tt class="docutils literal">metadata.tables</tt> dictionary.

1349

The <tt class="docutils literal">bind</tt> property may be assigned to dynamically. A common pattern is

1350

to start unbound and then bind later when an engine is available:

1351

<div class="highlight-python"><div class="highlight"><pre>metadata = MetaData()

1352

# define tables

1353

Table('mytable', metadata, ...)

1354

# connect to an engine later, perhaps after loading a URL from a

1355

# configuration file

1356

metadata.bind = an_engine</pre></div>

1357

</div>

1358

MetaData is a thread-safe object after tables have been explicitly defined

1359

or loaded via reflection.

1360

See also:

1361

<a class="reference internal" href="#metadata-describing">Describing Databases with MetaData</a> - Introduction to database metadata

1362

1363

1364

<tt class="descname">__init__</tt><big>(</big>bind=None, reflect=False, schema=None, quote_schema=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData.__init__" title="Permalink to this definition">¶</a></dt>

1365

<dd>Create a new MetaData object.

1366

1367

1368

1369

1370

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1371

<li>bind – An Engine or Connection to bind to. May also be a string or URL

1372

instance, these are passed to create_engine() and this MetaData will

1373

be bound to the resulting engine.</li>

1374

<li>reflect – Optional, automatically load all tables from the bound database.

1375

Defaults to False. <tt class="docutils literal">bind</tt> is required when this option is set.

1376

1377

Deprecated since version 0.8: Please use the <a class="reference internal" href="#sqlalchemy.schema.MetaData.reflect" title="sqlalchemy.schema.MetaData.reflect"><tt class="xref py py-meth docutils literal">MetaData.reflect()</tt></a> method.

1378

</li>

1379

<li>schema – The default schema to use for the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

1380

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a>, and other objects associated with this

1381

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>. Defaults to <tt class="docutils literal">None</tt>.</li>

1382

<li>quote_schema – Sets the <tt class="docutils literal">quote_schema</tt> flag for those <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

1383

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a>, and other objects which make usage of the

1384

local <tt class="docutils literal">schema</tt> name.</li>

1385

</ul>

1386

</td>

1387

</tr>

1388

</tbody>

1389

</table>

1390

1391

New in version 0.7.4: <tt class="docutils literal">schema</tt> and <tt class="docutils literal">quote_schema</tt> parameters.

1392

</dd></dl>

1393

1394

1395

1396

<tt class="descname">append_ddl_listener</tt><big>(</big>event_name, listener<big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData.append_ddl_listener" title="Permalink to this definition">¶</a></dt>

1397

<dd>Append a DDL event listener to this <tt class="docutils literal">MetaData</tt>.

1398

Deprecated. See <a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><tt class="xref py py-class docutils literal">DDLEvents</tt></a>.

1399

</dd></dl>

1400

1401

1402

1403

1404

<dd>An <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> or <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a> to which this

1405

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> is bound.

1406

Typically, a <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> is assigned to this attribute

1407

so that “implicit execution” may be used, or alternatively

1408

as a means of providing engine binding information to an

1409

ORM <a class="reference internal" href="../orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a> object:

1410

<div class="highlight-python"><div class="highlight"><pre>engine = create_engine("someurl://")

1411

metadata.bind = engine</pre></div>

1412

</div>

1413

1414

See also

1415

<a class="reference internal" href="connections.html#dbengine-implicit">Connectionless Execution, Implicit Execution</a> - background on “bound metadata”

1416

</div>

1417

</dd></dl>

1418

1419

1420

1421

<tt class="descname">clear</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData.clear" title="Permalink to this definition">¶</a></dt>

1422

<dd>Clear all Table objects from this MetaData.

1423

</dd></dl>

1424

1425

1426

1427

<tt class="descname">create_all</tt><big>(</big>bind=None, tables=None, checkfirst=True<big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData.create_all" title="Permalink to this definition">¶</a></dt>

1428

<dd>Create all tables stored in this metadata.

1429

Conditional by default, will not attempt to recreate tables already

1430

present in the target database.

1431

1432

1433

1434

1435

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1436

<li>bind – A <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a> used to access the

1437

database; if None, uses the existing bind on this <tt class="docutils literal">MetaData</tt>, if

1438

any.</li>

1439

<li>tables – Optional list of <tt class="docutils literal">Table</tt> objects, which is a subset of the total

1440

tables in the <tt class="docutils literal">MetaData</tt> (others are ignored).</li>

1441

<li>checkfirst – Defaults to True, don’t issue CREATEs for tables already present

1442

in the target database.</li>

1443

</ul>

1444

</td>

1445

</tr>

1446

</tbody>

1447

</table>

1448

</dd></dl>

1449

1450

1451

1452

<tt class="descname">drop_all</tt><big>(</big>bind=None, tables=None, checkfirst=True<big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData.drop_all" title="Permalink to this definition">¶</a></dt>

1453

<dd>Drop all tables stored in this metadata.

1454

Conditional by default, will not attempt to drop tables not present in

1455

the target database.

1456

1457

1458

1459

1460

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1461

<li>bind – A <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a> used to access the

1462

database; if None, uses the existing bind on this <tt class="docutils literal">MetaData</tt>, if

1463

any.</li>

1464

<li>tables – Optional list of <tt class="docutils literal">Table</tt> objects, which is a subset of the

1465

total tables in the <tt class="docutils literal">MetaData</tt> (others are ignored).</li>

1466

<li>checkfirst – Defaults to True, only issue DROPs for tables confirmed to be

1467

present in the target database.</li>

1468

</ul>

1469

</td>

1470

</tr>

1471

</tbody>

1472

</table>

1473

</dd></dl>

1474

1475

1476

1477

<tt class="descname">is_bound</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData.is_bound" title="Permalink to this definition">¶</a></dt>

1478

<dd>True if this MetaData is bound to an Engine or Connection.

1479

</dd></dl>

1480

1481

1482

1483

<tt class="descname">reflect</tt><big>(</big>bind=None, schema=None, views=False, only=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData.reflect" title="Permalink to this definition">¶</a></dt>

1484

<dd>Load all available table definitions from the database.

1485

Automatically creates <tt class="docutils literal">Table</tt> entries in this <tt class="docutils literal">MetaData</tt> for any

1486

table available in the database but not yet present in the

1487

<tt class="docutils literal">MetaData</tt>. May be called multiple times to pick up tables recently

1488

added to the database, however no special action is taken if a table

1489

in this <tt class="docutils literal">MetaData</tt> no longer exists in the database.

1490

1491

1492

1493

1494

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1495

<li>bind – A <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a> used to access the database; if None, uses

1496

the existing bind on this <tt class="docutils literal">MetaData</tt>, if any.</li>

1497

<li>schema – Optional, query and reflect tables from an alterate schema.

1498

If None, the schema associated with this <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>

1499

is used, if any.</li>

1500

<li>views – If True, also reflect views.</li>

1501

<li>only – Optional. Load only a sub-set of available named tables. May be

1502

specified as a sequence of names or a callable.

1503

If a sequence of names is provided, only those tables will be

1504

reflected. An error is raised if a table is requested but not

1505

available. Named tables already present in this <tt class="docutils literal">MetaData</tt> are

1506

ignored.

1507

If a callable is provided, it will be used as a boolean predicate to

1508

filter the list of potential table names. The callable is called

1509

with a table name and this <tt class="docutils literal">MetaData</tt> instance as positional

1510

arguments and should return a true value for any table to reflect.

1511

</li>

1512

</ul>

1513

</td>

1514

</tr>

1515

</tbody>

1516

</table>

1517

</dd></dl>

1518

1519

1520

1521

<tt class="descname">remove</tt><big>(</big>table<big>)</big><a class="headerlink" href="#sqlalchemy.schema.MetaData.remove" title="Permalink to this definition">¶</a></dt>

1522

<dd>Remove the given Table object from this MetaData.

1523

</dd></dl>

1524

1525

1526

1527

<tt class="descname">sorted_tables</tt><a class="headerlink" href="#sqlalchemy.schema.MetaData.sorted_tables" title="Permalink to this definition">¶</a></dt>

1528

<dd>Returns a list of <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects sorted in order of

1529

foreign key dependency.

1530

The sorting will place <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> objects that have dependencies

1531

first, before the dependencies themselves, representing the

1532

order in which they can be created. To get the order in which

1533

the tables would be dropped, use the <tt class="docutils literal">reversed()</tt> Python built-in.

1534

1535

See also

1536

<tt class="xref py py-meth docutils literal">Inspector.sorted_tables()</tt>

1537

</div>

1538

</dd></dl>

1539

1540

</dd></dl>

1541

1542

1543

1544

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">SchemaItem</tt><a class="headerlink" href="#sqlalchemy.schema.SchemaItem" title="Permalink to this definition">¶</a></dt>

1545

<dd>Bases: <a class="reference internal" href="events.html#sqlalchemy.events.SchemaEventTarget" title="sqlalchemy.events.SchemaEventTarget"><tt class="xref py py-class docutils literal">sqlalchemy.events.SchemaEventTarget</tt></a>, <tt class="xref py py-class docutils literal">sqlalchemy.sql.visitors.Visitable</tt>

1546

Base class for items that define a database schema.

1547

1548

1549

<tt class="descname">get_children</tt><big>(</big>**kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.SchemaItem.get_children" title="Permalink to this definition">¶</a></dt>

1550

<dd>used to allow SchemaVisitor access

1551

</dd></dl>

1552

1553

1554

1555

1556

<dd>Info dictionary associated with the object, allowing user-defined

1557

data to be associated with this <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">SchemaItem</tt></a>.

1558

The dictionary is automatically generated when first accessed.

1559

It can also be specified in the constructor of some objects,

1560

such as <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> and <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>.

1561

</dd></dl>

1562

1563

</dd></dl>

1564

1565

1566

1567

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">Table</tt><big>(</big>*args, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table" title="Permalink to this definition">¶</a></dt>

1568

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause" title="sqlalchemy.sql.expression.TableClause"><tt class="xref py py-class docutils literal">sqlalchemy.sql.expression.TableClause</tt></a>

1569

Represent a table in a database.

1570

e.g.:

1571

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

1572

Column('mytable_id', Integer, primary_key=True),

1573

Column('value', String(50))

1574

)</pre></div>

1575

</div>

1576

The <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object constructs a unique instance of itself based

1577

on its name and optional schema name within the given

1578

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object. Calling the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

1579

constructor with the same name and same <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> argument

1580

a second time will return the same <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object - in this way

1581

the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> constructor acts as a registry function.

1582

See also:

1583

<a class="reference internal" href="#metadata-describing">Describing Databases with MetaData</a> - Introduction to database metadata

1584

Constructor arguments are as follows:

1585

1586

1587

1588

1589

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1590

<li>name – The name of this table as represented in the database.

1591

This property, along with the schema, indicates the singleton

1592

identity of this table in relation to its parent <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>.

1593

Additional calls to <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> with the same name, metadata,

1594

and schema name will return the same <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object.

1595

Names which contain no upper case characters

1596

will be treated as case insensitive names, and will not be quoted

1597

unless they are a reserved word. Names with any number of upper

1598

case characters will be quoted and sent exactly. Note that this

1599

behavior applies even for databases which standardize upper

1600

case names as case insensitive such as Oracle.

1601

</li>

1602

<li>metadata – a <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object which will contain this

1603

table. The metadata is used as a point of association of this table

1604

with other tables which are referenced via foreign key. It also

1605

may be used to associate this table with a particular

1606

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a>.</li>

1607

<li>*args – Additional positional arguments are used primarily

1608

to add the list of <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects contained within this

1609

table. Similar to the style of a CREATE TABLE statement, other

1610

<a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">SchemaItem</tt></a> constructs may be added here, including

1611

<a class="reference internal" href="#sqlalchemy.schema.PrimaryKeyConstraint" title="sqlalchemy.schema.PrimaryKeyConstraint"><tt class="xref py py-class docutils literal">PrimaryKeyConstraint</tt></a>, and <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a>.</li>

1612

<li>autoload – Defaults to False: the Columns for this table should

1613

be reflected from the database. Usually there will be no Column

1614

objects in the constructor if this property is set.</li>

1615

<li>autoload_replace – If <tt class="docutils literal">True</tt>, when using <tt class="docutils literal">autoload=True</tt>

1616

and <tt class="docutils literal">extend_existing=True</tt>,

1617

replace <tt class="docutils literal">Column</tt> objects already present in the <tt class="docutils literal">Table</tt> that’s

1618

in the <tt class="docutils literal">MetaData</tt> registry with

1619

what’s reflected. Otherwise, all existing columns will be

1620

excluded from the reflection process. Note that this does

1621

not impact <tt class="docutils literal">Column</tt> objects specified in the same call to <tt class="docutils literal">Table</tt>

1622

which includes <tt class="docutils literal">autoload</tt>, those always take precedence.

1623

Defaults to <tt class="docutils literal">True</tt>.

1624

1625

New in version 0.7.5.

1626

</li>

1627

<li>autoload_with – If autoload==True, this is an optional Engine

1628

or Connection instance to be used for the table reflection. If

1629

<tt class="docutils literal">None</tt>, the underlying MetaData’s bound connectable will be used.</li>

1630

<li>extend_existing – When <tt class="docutils literal">True</tt>, indicates that if this

1631

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is already present in the given <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>,

1632

apply further arguments within the constructor to the existing

1633

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

1634

If <tt class="docutils literal">extend_existing</tt> or <tt class="docutils literal">keep_existing</tt> are not set, an error is

1635

raised if additional table modifiers are specified when

1636

the given <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is already present in the <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>.

1637

1638

Changed in version 0.7.4: <tt class="docutils literal">extend_existing</tt> will work in conjunction

1639

with <tt class="docutils literal">autoload=True</tt> to run a new reflection operation against

1640

the database; new <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects will be produced

1641

from database metadata to replace those existing with the same

1642

name, and additional <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects not present

1643

in the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> will be added.

1644

As is always the case with <tt class="docutils literal">autoload=True</tt>, <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>

1645

objects can be specified in the same <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> constructor,

1646

which will take precedence. I.e.:

1647

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

1648

Column('y', Integer),

1649

extend_existing=True,

1650

autoload=True,

1651

autoload_with=engine

1652

)</pre></div>

1653

</div>

1654

The above will overwrite all columns within <tt class="docutils literal">mytable</tt> which

1655

are present in the database, except for <tt class="docutils literal">y</tt> which will be used as is

1656

from the above definition. If the <tt class="docutils literal">autoload_replace</tt> flag

1657

is set to False, no existing columns will be replaced.

1658

</li>

1659

<li>implicit_returning – True by default - indicates that

1660

RETURNING can be used by default to fetch newly inserted primary key

1661

values, for backends which support this. Note that

1662

create_engine() also provides an implicit_returning flag.</li>

1663

<li>include_columns – A list of strings indicating a subset of

1664

columns to be loaded via the <tt class="docutils literal">autoload</tt> operation; table columns who

1665

aren’t present in this list will not be represented on the resulting

1666

<tt class="docutils literal">Table</tt> object. Defaults to <tt class="docutils literal">None</tt> which indicates all columns

1667

should be reflected.</li>

1668

<li>info – Optional data dictionary which will be populated into the

1669

<a class="reference internal" href="#sqlalchemy.schema.SchemaItem.info" title="sqlalchemy.schema.SchemaItem.info"><tt class="xref py py-attr docutils literal">SchemaItem.info</tt></a> attribute of this object.</li>

1670

<li>keep_existing – When <tt class="docutils literal">True</tt>, indicates that if this Table

1671

is already present in the given <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>, ignore

1672

further arguments within the constructor to the existing

1673

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, and return the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object as

1674

originally created. This is to allow a function that wishes

1675

to define a new <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> on first call, but on

1676

subsequent calls will return the same <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

1677

without any of the declarations (particularly constraints)

1678

being applied a second time. Also see extend_existing.

1679

If extend_existing or keep_existing are not set, an error is

1680

raised if additional table modifiers are specified when

1681

the given <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is already present in the <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>.

1682

</li>

1683

<li>listeners – A list of tuples of the form <tt class="docutils literal">(<eventname>, <fn>)</tt>

1684

which will be passed to <a class="reference internal" href="event.html#sqlalchemy.event.listen" title="sqlalchemy.event.listen"><tt class="xref py py-func docutils literal">event.listen()</tt></a> upon construction.

1685

This alternate hook to <a class="reference internal" href="event.html#sqlalchemy.event.listen" title="sqlalchemy.event.listen"><tt class="xref py py-func docutils literal">event.listen()</tt></a> allows the establishment

1686

of a listener function specific to this <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> before

1687

the “autoload” process begins. Particularly useful for

1688

the <a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents.column_reflect" title="sqlalchemy.events.DDLEvents.column_reflect"><tt class="xref py py-meth docutils literal">DDLEvents.column_reflect()</tt></a> event:

1689

<div class="highlight-python"><div class="highlight"><pre>def listen_for_reflect(table, column_info):

1690

"handle the column reflection event"

1691

# ...

1692

1693

t = Table(

1694

'sometable',

1695

autoload=True,

1696

listeners=[

1697

('column_reflect', listen_for_reflect)

1698

])</pre></div>

1699

</div>

1700

</li>

1701

<li>mustexist – When <tt class="docutils literal">True</tt>, indicates that this Table must already

1702

be present in the given <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> collection, else

1703

an exception is raised.</li>

1704

<li>prefixes – A list of strings to insert after CREATE in the CREATE TABLE

1705

statement. They will be separated by spaces.</li>

1706

<li>quote – Force quoting of this table’s name on or off, corresponding

1707

to <tt class="docutils literal">True</tt> or <tt class="docutils literal">False</tt>. When left at its default of <tt class="docutils literal">None</tt>,

1708

the column identifier will be quoted according to whether the name is

1709

case sensitive (identifiers with at least one upper case character are

1710

treated as case sensitive), or if it’s a reserved word. This flag

1711

is only needed to force quoting of a reserved word which is not known

1712

by the SQLAlchemy dialect.</li>

1713

<li>quote_schema – same as ‘quote’ but applies to the schema identifier.</li>

1714

<li>schema – The schema name for this table, which is required if

1715

the table resides in a schema other than the default selected schema

1716

for the engine’s database connection. Defaults to <tt class="docutils literal">None</tt>.</li>

1717

<li>useexisting – Deprecated. Use extend_existing.</li>

1718

</ul>

1719

</td>

1720

</tr>

1721

</tbody>

1722

</table>

1723

1724

1725

1726

<dd>Constructor for <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>.

1727

This method is a no-op. See the top-level

1728

documentation for <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

1729

for constructor arguments.

1730

</dd></dl>

1731

1732

1733

1734

<tt class="descname">add_is_dependent_on</tt><big>(</big>table<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.add_is_dependent_on" title="Permalink to this definition">¶</a></dt>

1735

<dd>Add a ‘dependency’ for this Table.

1736

This is another Table object which must be created

1737

first before this one can, or dropped after this one.

1738

Usually, dependencies between tables are determined via

1739

ForeignKey objects. However, for other situations that

1740

create dependencies outside of foreign keys (rules, inheriting),

1741

this method can manually establish such a link.

1742

</dd></dl>

1743

1744

1745

1746

<tt class="descname">alias</tt><big>(</big>name=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.alias" title="Permalink to this definition">¶</a></dt>

1747

1748

inherited from 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">alias()</tt></a> method of <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></div>

1749

return an alias of this <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>.

1750

This is shorthand for calling:

1751

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

1752

a = alias(self, name=name)</pre></div>

1753

</div>

1754

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

1755

</dd></dl>

1756

1757

1758

1759

<tt class="descname">append_column</tt><big>(</big>column<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.append_column" title="Permalink to this definition">¶</a></dt>

1760

<dd>Append a <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> to this <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>.

1761

The “key” of the newly added <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, i.e. the

1762

value of its <tt class="docutils literal">.key</tt> attribute, will then be available

1763

in the <tt class="docutils literal">.c</tt> collection of this <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, and the

1764

column definition will be included in any CREATE TABLE, SELECT,

1765

UPDATE, etc. statements generated from this <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

1766

construct.

1767

Note that this does not change the definition of the table

1768

as it exists within any underlying database, assuming that

1769

table has already been created in the database. Relational

1770

databases support the addition of columns to existing tables

1771

using the SQL ALTER command, which would need to be

1772

emitted for an already-existing table that doesn’t contain

1773

the newly added column.

1774

</dd></dl>

1775

1776

1777

1778

<tt class="descname">append_constraint</tt><big>(</big>constraint<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.append_constraint" title="Permalink to this definition">¶</a></dt>

1779

<dd>Append a <a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">Constraint</tt></a> to this

1780

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

1781

This has the effect of the constraint being included in any

1782

future CREATE TABLE statement, assuming specific DDL creation

1783

events have not been associated with the given

1784

<a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">Constraint</tt></a> object.

1785

Note that this does not produce the constraint within the

1786

relational database automatically, for a table that already exists

1787

in the database. To add a constraint to an

1788

existing relational database table, the SQL ALTER command must

1789

be used. SQLAlchemy also provides the

1790

<a class="reference internal" href="#sqlalchemy.schema.AddConstraint" title="sqlalchemy.schema.AddConstraint"><tt class="xref py py-class docutils literal">AddConstraint</tt></a> construct which can produce this SQL when

1791

invoked as an executable clause.

1792

</dd></dl>

1793

1794

1795

1796

<tt class="descname">append_ddl_listener</tt><big>(</big>event_name, listener<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.append_ddl_listener" title="Permalink to this definition">¶</a></dt>

1797

<dd>Append a DDL event listener to this <tt class="docutils literal">Table</tt>.

1798

Deprecated. See <a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><tt class="xref py py-class docutils literal">DDLEvents</tt></a>.

1799

</dd></dl>

1800

1801

1802

1803

1804

<dd>Return the connectable associated with this Table.

1805

</dd></dl>

1806

1807

1808

1809

1810

1811

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.c" title="sqlalchemy.sql.expression.FromClause.c"><tt class="xref py py-attr docutils literal">c</tt></a> attribute of <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></div>

1812

An alias for the <a class="reference internal" href="#sqlalchemy.schema.Table.columns" title="sqlalchemy.schema.Table.columns"><tt class="xref py py-attr docutils literal">columns</tt></a> attribute.

1813

</dd></dl>

1814

1815

1816

1817

<tt class="descname">columns</tt><a class="headerlink" href="#sqlalchemy.schema.Table.columns" title="Permalink to this definition">¶</a></dt>

1818

1819

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.columns" title="sqlalchemy.sql.expression.FromClause.columns"><tt class="xref py py-attr docutils literal">columns</tt></a> attribute of <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></div>

1820

A named-based collection of <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> objects

1821

maintained by this <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>.

1822

The <a class="reference internal" href="#sqlalchemy.schema.Table.columns" title="sqlalchemy.schema.Table.columns"><tt class="xref py py-attr docutils literal">columns</tt></a>, or <a class="reference internal" href="#sqlalchemy.schema.Table.c" title="sqlalchemy.schema.Table.c"><tt class="xref py py-attr docutils literal">c</tt></a> collection, is the gateway

1823

to the construction of SQL expressions using table-bound or

1824

other selectable-bound columns:

1825

<div class="highlight-python"><div class="highlight"><pre>select([mytable]).where(mytable.c.somecolumn == 5)</pre></div>

1826

</div>

1827

</dd></dl>

1828

1829

1830

1831

<tt class="descname">compare</tt><big>(</big>other, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.compare" title="Permalink to this definition">¶</a></dt>

1832

1833

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement.compare" title="sqlalchemy.sql.expression.ClauseElement.compare"><tt class="xref py py-meth docutils literal">compare()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a></div>

1834

Compare this ClauseElement to the given ClauseElement.

1835

Subclasses should override the default behavior, which is a

1836

straight identity comparison.

1837

**kw are arguments consumed by subclass compare() methods and

1838

may be used to modify the criteria for comparison.

1839

(see <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>)

1840

</dd></dl>

1841

1842

1843

1844

<tt class="descname">compile</tt><big>(</big>bind=None, dialect=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.compile" title="Permalink to this definition">¶</a></dt>

1845

1846

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement.compile" title="sqlalchemy.sql.expression.ClauseElement.compile"><tt class="xref py py-meth docutils literal">compile()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a></div>

1847

Compile this SQL expression.

1848

The return value is a <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><tt class="xref py py-class docutils literal">Compiled</tt></a> object.

1849

Calling <tt class="docutils literal">str()</tt> or <tt class="docutils literal">unicode()</tt> on the returned value will yield a

1850

string representation of the result. The

1851

<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><tt class="xref py py-class docutils literal">Compiled</tt></a> object also can return a

1852

dictionary of bind parameter names and values

1853

using the <tt class="docutils literal">params</tt> accessor.

1854

1855

1856

1857

1858

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1859

<li>bind – An <tt class="docutils literal">Engine</tt> or <tt class="docutils literal">Connection</tt> from which a

1860

<tt class="docutils literal">Compiled</tt> will be acquired. This argument takes precedence over

1861

this <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a>‘s bound engine, if any.</li>

1862

<li>column_keys – Used for INSERT and UPDATE statements, a list of

1863

column names which should be present in the VALUES clause of the

1864

compiled statement. If <tt class="docutils literal">None</tt>, all columns from the target table

1865

object are rendered.</li>

1866

<li>dialect – A <tt class="docutils literal">Dialect</tt> instance from which a <tt class="docutils literal">Compiled</tt>

1867

will be acquired. This argument takes precedence over the <cite>bind</cite>

1868

argument as well as this <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a>‘s bound engine, if

1869

any.</li>

1870

<li>inline – Used for INSERT statements, for a dialect which does

1871

not support inline retrieval of newly generated primary key

1872

columns, will force the expression used to create the new primary

1873

key value to be rendered inline within the INSERT statement’s

1874

VALUES clause. This typically refers to Sequence execution but may

1875

also refer to any server-side default generation function

1876

associated with a primary key <cite>Column</cite>.</li>

1877

</ul>

1878

</td>

1879

</tr>

1880

</tbody>

1881

</table>

1882

</dd></dl>

1883

1884

1885

1886

<tt class="descname">correspond_on_equivalents</tt><big>(</big>column, equivalents<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.correspond_on_equivalents" title="Permalink to this definition">¶</a></dt>

1887

1888

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.correspond_on_equivalents" title="sqlalchemy.sql.expression.FromClause.correspond_on_equivalents"><tt class="xref py py-meth docutils literal">correspond_on_equivalents()</tt></a> method of <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></div>

1889

Return corresponding_column for the given column, or if None

1890

search for a match in the given dictionary.

1891

</dd></dl>

1892

1893

1894

1895

<tt class="descname">corresponding_column</tt><big>(</big>column, require_embedded=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.corresponding_column" title="Permalink to this definition">¶</a></dt>

1896

1897

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.corresponding_column" title="sqlalchemy.sql.expression.FromClause.corresponding_column"><tt class="xref py py-meth docutils literal">corresponding_column()</tt></a> method of <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></div>

1898

Given a <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>, return the exported

1899

<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> object from this <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Selectable" title="sqlalchemy.sql.expression.Selectable"><tt class="xref py py-class docutils literal">Selectable</tt></a>

1900

which corresponds to that original

1901

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> via a common ancestor

1902

column.

1903

1904

1905

1906

1907

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

1908

<li>column – the target <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> to be matched</li>

1909

<li>require_embedded – only return corresponding columns for</li>

1910

</ul>

1911

</td>

1912

</tr>

1913

</tbody>

1914

</table>

1915

the given <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>, if the given

1916

<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> is actually present within a sub-element

1917

of this <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>. Normally the column will match if

1918

it merely shares a common ancestor with one of the exported

1919

columns of this <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>.

1920

</dd></dl>

1921

1922

1923

1924

<tt class="descname">count</tt><big>(</big>whereclause=None, **params<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.count" title="Permalink to this definition">¶</a></dt>

1925

1926

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.count" title="sqlalchemy.sql.expression.TableClause.count"><tt class="xref py py-meth docutils literal">count()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause" title="sqlalchemy.sql.expression.TableClause"><tt class="xref py py-class docutils literal">TableClause</tt></a></div>

1927

return a SELECT COUNT generated against this

1928

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

1929

</dd></dl>

1930

1931

1932

1933

<tt class="descname">create</tt><big>(</big>bind=None, checkfirst=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.create" title="Permalink to this definition">¶</a></dt>

1934

<dd>Issue a <tt class="docutils literal">CREATE</tt> statement for this

1935

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

1936

for connectivity.

1937

See also <a class="reference internal" href="#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-meth docutils literal">MetaData.create_all()</tt></a>.

1938

</dd></dl>

1939

1940

1941

1942

<tt class="descname">delete</tt><big>(</big>whereclause=None, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.delete" title="Permalink to this definition">¶</a></dt>

1943

1944

inherited from the <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> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause" title="sqlalchemy.sql.expression.TableClause"><tt class="xref py py-class docutils literal">TableClause</tt></a></div>

1945

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

1946

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

1947

E.g.:

1948

<div class="highlight-python"><div class="highlight"><pre>table.delete().where(table.c.id==7)</pre></div>

1949

</div>

1950

See <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.delete" title="sqlalchemy.sql.expression.delete"><tt class="xref py py-func docutils literal">delete()</tt></a> for argument and usage information.

1951

</dd></dl>

1952

1953

1954

1955

<tt class="descname">description</tt><a class="headerlink" href="#sqlalchemy.schema.Table.description" title="Permalink to this definition">¶</a></dt>

1956

1957

inherited from the <tt class="xref py py-attr docutils literal">description</tt> attribute of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause" title="sqlalchemy.sql.expression.TableClause"><tt class="xref py py-class docutils literal">TableClause</tt></a></div>

1958

</dd></dl>

1959

1960

1961

1962

<tt class="descname">dispatch</tt><a class="headerlink" href="#sqlalchemy.schema.Table.dispatch" title="Permalink to this definition">¶</a></dt>

1963

<dd>alias of <tt class="xref py py-class docutils literal">DDLEventsDispatch</tt>

1964

</dd></dl>

1965

1966

1967

1968

<tt class="descname">drop</tt><big>(</big>bind=None, checkfirst=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.drop" title="Permalink to this definition">¶</a></dt>

1969

<dd>Issue a <tt class="docutils literal">DROP</tt> statement for this

1970

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

1971

for connectivity.

1972

See also <a class="reference internal" href="#sqlalchemy.schema.MetaData.drop_all" title="sqlalchemy.schema.MetaData.drop_all"><tt class="xref py py-meth docutils literal">MetaData.drop_all()</tt></a>.

1973

</dd></dl>

1974

1975

1976

1977

<tt class="descname">exists</tt><big>(</big>bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.exists" title="Permalink to this definition">¶</a></dt>

1978

<dd>Return True if this table exists.

1979

</dd></dl>

1980

1981

1982

1983

<tt class="descname">foreign_keys</tt><a class="headerlink" href="#sqlalchemy.schema.Table.foreign_keys" title="Permalink to this definition">¶</a></dt>

1984

1985

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.foreign_keys" title="sqlalchemy.sql.expression.FromClause.foreign_keys"><tt class="xref py py-attr docutils literal">foreign_keys</tt></a> attribute of <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></div>

1986

Return the collection of ForeignKey objects which this

1987

FromClause references.

1988

</dd></dl>

1989

1990

1991

1992

<tt class="descname">get_children</tt><big>(</big>column_collections=True, schema_visitor=False, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.get_children" title="Permalink to this definition">¶</a></dt>

1993

1994

1995

1996

1997

<tt class="descname">implicit_returning</tt> = False<a class="headerlink" href="#sqlalchemy.schema.Table.implicit_returning" title="Permalink to this definition">¶</a></dt>

1998

1999

2000

2001

2002

2003

2004

inherited from the <a class="reference internal" href="#sqlalchemy.schema.SchemaItem.info" title="sqlalchemy.schema.SchemaItem.info"><tt class="xref py py-attr docutils literal">info</tt></a> attribute of <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">SchemaItem</tt></a></div>

2005

Info dictionary associated with the object, allowing user-defined

2006

data to be associated with this <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">SchemaItem</tt></a>.

2007

The dictionary is automatically generated when first accessed.

2008

It can also be specified in the constructor of some objects,

2009

such as <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> and <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>.

2010

</dd></dl>

2011

2012

2013

2014

<tt class="descname">insert</tt><big>(</big>values=None, inline=False, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.insert" title="Permalink to this definition">¶</a></dt>

2015

2016

inherited from the <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> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause" title="sqlalchemy.sql.expression.TableClause"><tt class="xref py py-class docutils literal">TableClause</tt></a></div>

2017

Generate an <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><tt class="xref py py-func docutils literal">insert()</tt></a> construct against this

2018

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

2019

E.g.:

2020

<div class="highlight-python"><div class="highlight"><pre>table.insert().values(name='foo')</pre></div>

2021

</div>

2022

See <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><tt class="xref py py-func docutils literal">insert()</tt></a> for argument and usage information.

2023

</dd></dl>

2024

2025

2026

2027

<tt class="descname">is_clause_element</tt> = True<a class="headerlink" href="#sqlalchemy.schema.Table.is_clause_element" title="Permalink to this definition">¶</a></dt>

2028

2029

2030

2031

2032

<tt class="descname">is_derived_from</tt><big>(</big>fromclause<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.is_derived_from" title="Permalink to this definition">¶</a></dt>

2033

2034

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.is_derived_from" title="sqlalchemy.sql.expression.FromClause.is_derived_from"><tt class="xref py py-meth docutils literal">is_derived_from()</tt></a> method of <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></div>

2035

Return True if this FromClause is ‘derived’ from the given

2036

FromClause.

2037

An example would be an Alias of a Table is derived from that Table.

2038

</dd></dl>

2039

2040

2041

2042

<tt class="descname">is_selectable</tt> = True<a class="headerlink" href="#sqlalchemy.schema.Table.is_selectable" title="Permalink to this definition">¶</a></dt>

2043

2044

2045

2046

2047

<tt class="descname">join</tt><big>(</big>right, onclause=None, isouter=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.join" title="Permalink to this definition">¶</a></dt>

2048

2049

inherited from 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> method of <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></div>

2050

return a join of this <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> against another

2051

<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>.

2052

</dd></dl>

2053

2054

2055

2056

2057

2058

2059

2060

2061

<tt class="descname">named_with_column</tt> = True<a class="headerlink" href="#sqlalchemy.schema.Table.named_with_column" title="Permalink to this definition">¶</a></dt>

2062

2063

2064

2065

2066

<tt class="descname">outerjoin</tt><big>(</big>right, onclause=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.outerjoin" title="Permalink to this definition">¶</a></dt>

2067

2068

inherited from 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 of <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></div>

2069

return an outer join of this <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> against another

2070

<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>.

2071

</dd></dl>

2072

2073

2074

2075

<tt class="descname">params</tt><big>(</big>*optionaldict, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.params" title="Permalink to this definition">¶</a></dt>

2076

2077

inherited from the <tt class="xref py py-meth docutils literal">params()</tt> method of <tt class="xref py py-class docutils literal">Immutable</tt></div>

2078

</dd></dl>

2079

2080

2081

2082

<tt class="descname">primary_key</tt><a class="headerlink" href="#sqlalchemy.schema.Table.primary_key" title="Permalink to this definition">¶</a></dt>

2083

2084

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.primary_key" title="sqlalchemy.sql.expression.FromClause.primary_key"><tt class="xref py py-attr docutils literal">primary_key</tt></a> attribute of <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></div>

2085

Return the collection of Column objects which comprise the

2086

primary key of this FromClause.

2087

</dd></dl>

2088

2089

2090

2091

<tt class="descname">quote</tt> = None<a class="headerlink" href="#sqlalchemy.schema.Table.quote" title="Permalink to this definition">¶</a></dt>

2092

2093

2094

2095

2096

<tt class="descname">replace_selectable</tt><big>(</big>old, alias<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.replace_selectable" title="Permalink to this definition">¶</a></dt>

2097

2098

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.replace_selectable" title="sqlalchemy.sql.expression.FromClause.replace_selectable"><tt class="xref py py-meth docutils literal">replace_selectable()</tt></a> method of <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></div>

2099

replace all occurrences of FromClause ‘old’ with the given Alias

2100

object, returning a copy of this <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>.

2101

</dd></dl>

2102

2103

2104

2105

<tt class="descname">schema</tt> = None<a class="headerlink" href="#sqlalchemy.schema.Table.schema" title="Permalink to this definition">¶</a></dt>

2106

2107

2108

2109

2110

<tt class="descname">select</tt><big>(</big>whereclause=None, **params<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.select" title="Permalink to this definition">¶</a></dt>

2111

2112

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.select" title="sqlalchemy.sql.expression.FromClause.select"><tt class="xref py py-meth docutils literal">select()</tt></a> method of <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></div>

2113

return a SELECT of this <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>.

2114

2115

See also

2116

<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> - general purpose

2117

method which allows for arbitrary column lists.

2118

</div>

2119

</dd></dl>

2120

2121

2122

2123

<tt class="descname">selectable</tt><a class="headerlink" href="#sqlalchemy.schema.Table.selectable" title="Permalink to this definition">¶</a></dt>

2124

2125

inherited from the <tt class="xref py py-attr docutils literal">selectable</tt> attribute of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Selectable" title="sqlalchemy.sql.expression.Selectable"><tt class="xref py py-class docutils literal">Selectable</tt></a></div>

2126

</dd></dl>

2127

2128

2129

2130

<tt class="descname">self_group</tt><big>(</big>against=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.self_group" title="Permalink to this definition">¶</a></dt>

2131

2132

inherited from the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement.self_group" title="sqlalchemy.sql.expression.ClauseElement.self_group"><tt class="xref py py-meth docutils literal">self_group()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a></div>

2133

Apply a ‘grouping’ to this <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a>.

2134

This method is overridden by subclasses to return a

2135

“grouping” construct, i.e. parenthesis. In particular

2136

it’s used by “binary” expressions to provide a grouping

2137

around themselves when placed into a larger expression,

2138

as well as by <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> constructs when placed into

2139

the FROM clause of 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>. (Note that

2140

subqueries should be normally created using the

2141

<tt class="xref py py-func docutils literal">Select.alias()</tt> method, as many platforms require

2142

nested SELECT statements to be named).

2143

As expressions are composed together, the application of

2144

<a class="reference internal" href="#sqlalchemy.schema.Table.self_group" title="sqlalchemy.schema.Table.self_group"><tt class="xref py py-meth docutils literal">self_group()</tt></a> is automatic - end-user code should never

2145

need to use this method directly. Note that SQLAlchemy’s

2146

clause constructs take operator precedence into account -

2147

so parenthesis might not be needed, for example, in

2148

an expression like <tt class="docutils literal">x OR (y AND z)</tt> - AND takes precedence

2149

over OR.

2150

The base <a class="reference internal" href="#sqlalchemy.schema.Table.self_group" title="sqlalchemy.schema.Table.self_group"><tt class="xref py py-meth docutils literal">self_group()</tt></a> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal">ClauseElement</tt></a>

2151

just returns self.

2152

</dd></dl>

2153

2154

2155

2156

<tt class="descname">supports_execution</tt> = False<a class="headerlink" href="#sqlalchemy.schema.Table.supports_execution" title="Permalink to this definition">¶</a></dt>

2157

2158

2159

2160

2161

<tt class="descname">tometadata</tt><big>(</big>metadata, schema=<symbol 'retain_schema><big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.tometadata" title="Permalink to this definition">¶</a></dt>

2162

<dd>Return a copy of this <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> associated with a different

2163

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

2164

E.g.:

2165

<div class="highlight-python"><div class="highlight"><pre>some_engine = create_engine("sqlite:///some.db")

2166

2167

# create two metadata

2168

meta1 = MetaData()

2169

meta2 = MetaData()

2170

2171

# load 'users' from the sqlite engine

2172

users_table = Table('users', meta1, autoload=True,

2173

autoload_with=some_engine)

2174

2175

# create the same Table object for the plain metadata

2176

users_table_2 = users_table.tometadata(meta2)</pre></div>

2177

</div>

2178

2179

2180

2181

2182

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

2183

<li>metadata – Target <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object.</li>

2184

<li>schema – Optional string name of a target schema, or

2185

<tt class="docutils literal">None</tt> for no schema. The <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object will be

2186

given this schema name upon copy. Defaults to the special

2187

symbol <tt class="xref py py-attr docutils literal">RETAIN_SCHEMA</tt> which indicates no change should be

2188

made to the schema name of the resulting <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>.</li>

2189

</ul>

2190

</td>

2191

</tr>

2192

</tbody>

2193

</table>

2194

</dd></dl>

2195

2196

2197

2198

<tt class="descname">unique_params</tt><big>(</big>*optionaldict, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.unique_params" title="Permalink to this definition">¶</a></dt>

2199

2200

inherited from the <tt class="xref py py-meth docutils literal">unique_params()</tt> method of <tt class="xref py py-class docutils literal">Immutable</tt></div>

2201

</dd></dl>

2202

2203

2204

2205

<tt class="descname">update</tt><big>(</big>whereclause=None, values=None, inline=False, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Table.update" title="Permalink to this definition">¶</a></dt>

2206

2207

inherited from 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> method of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause" title="sqlalchemy.sql.expression.TableClause"><tt class="xref py py-class docutils literal">TableClause</tt></a></div>

2208

Generate an <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 against this

2209

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

2210

E.g.:

2211

<div class="highlight-python"><div class="highlight"><pre>table.update().where(table.c.id==7).values(name='foo')</pre></div>

2212

</div>

2213

See <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> for argument and usage information.

2214

</dd></dl>

2215

2216

</dd></dl>

2217

2218

2219

2220

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">ThreadLocalMetaData</tt><a class="headerlink" href="#sqlalchemy.schema.ThreadLocalMetaData" title="Permalink to this definition">¶</a></dt>

2221

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">sqlalchemy.schema.MetaData</tt></a>

2222

A MetaData variant that presents a different <tt class="docutils literal">bind</tt> in every thread.

2223

Makes the <tt class="docutils literal">bind</tt> property of the MetaData a thread-local value, allowing

2224

this collection of tables to be bound to different <tt class="docutils literal">Engine</tt>

2225

implementations or connections in each thread.

2226

The ThreadLocalMetaData starts off bound to None in each thread. Binds

2227

must be made explicitly by assigning to the <tt class="docutils literal">bind</tt> property or using

2228

<tt class="docutils literal">connect()</tt>. You can also re-bind dynamically multiple times per

2229

thread, just like a regular <tt class="docutils literal">MetaData</tt>.

2230

2231

2232

2233

<dd>Construct a ThreadLocalMetaData.

2234

</dd></dl>

2235

2236

2237

2238

2239

<dd>The bound Engine or Connection for this thread.

2240

This property may be assigned an Engine or Connection, or assigned a

2241

string or URL to automatically create a basic Engine for this bind

2242

with <tt class="docutils literal">create_engine()</tt>.

2243

</dd></dl>

2244

2245

2246

2247

<tt class="descname">dispose</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.schema.ThreadLocalMetaData.dispose" title="Permalink to this definition">¶</a></dt>

2248

<dd>Dispose all bound engines, in all thread contexts.

2249

</dd></dl>

2250

2251

2252

2253

<tt class="descname">is_bound</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.schema.ThreadLocalMetaData.is_bound" title="Permalink to this definition">¶</a></dt>

2254

<dd>True if there is a bind for this thread.

2255

</dd></dl>

2256

2257

</dd></dl>

2258

2259

</div>

2260

</div>

2261

2262

<h2>Reflecting Database Objects<a class="headerlink" href="#reflecting-database-objects" title="Permalink to this headline">¶</a></h2>

2263

A <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object can be instructed to load

2264

information about itself from the corresponding database schema object already

2265

existing within the database. This process is called reflection. In the

2266

most simple case you need only specify the table name, a <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>

2267

object, and the <tt class="docutils literal">autoload=True</tt> flag. If the

2268

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> is not persistently bound, also add the

2269

<tt class="docutils literal">autoload_with</tt> argument:

2270

<div class="highlight-python"><div class="highlight"><pre>>>> messages = Table('messages', meta, autoload=True, autoload_with=engine)

2271

>>> [c.name for c in messages.columns]

2272

['message_id', 'message_name', 'date']</pre></div>

2273

</div>

2274

The above operation will use the given engine to query the database for

2275

information about the <tt class="docutils literal">messages</tt> table, and will then generate

2276

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>,

2277

and other objects corresponding to this information as though the

2278

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object were hand-constructed in Python.

2279

When tables are reflected, if a given table references another one via foreign

2280

key, a second <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object is created within the

2281

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object representing the connection.

2282

Below, assume the table <tt class="docutils literal">shopping_cart_items</tt> references a table named

2283

<tt class="docutils literal">shopping_carts</tt>. Reflecting the <tt class="docutils literal">shopping_cart_items</tt> table has the

2284

effect such that the <tt class="docutils literal">shopping_carts</tt> table will also be loaded:

2285

<div class="highlight-python"><div class="highlight"><pre>>>> shopping_cart_items = Table('shopping_cart_items', meta, autoload=True, autoload_with=engine)

2286

>>> 'shopping_carts' in meta.tables:

2287

True</pre></div>

2288

</div>

2289

The <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> has an interesting “singleton-like”

2290

behavior such that if you requested both tables individually,

2291

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> will ensure that exactly one

2292

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object is created for each distinct table

2293

name. The <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> constructor actually returns to

2294

you the already-existing <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object if one

2295

already exists with the given name. Such as below, we can access the already

2296

generated <tt class="docutils literal">shopping_carts</tt> table just by naming it:

2297

<div class="highlight-python"><div class="highlight"><pre>shopping_carts = Table('shopping_carts', meta)</pre></div>

2298

</div>

2299

Of course, it’s a good idea to use <tt class="docutils literal">autoload=True</tt> with the above table

2300

regardless. This is so that the table’s attributes will be loaded if they have

2301

not been already. The autoload operation only occurs for the table if it

2302

hasn’t already been loaded; once loaded, new calls to

2303

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> with the same name will not re-issue any

2304

reflection queries.

2305

2306

<h3>Overriding Reflected Columns<a class="headerlink" href="#overriding-reflected-columns" title="Permalink to this headline">¶</a></h3>

2307

Individual columns can be overridden with explicit values when reflecting

2308

tables; this is handy for specifying custom datatypes, constraints such as

2309

primary keys that may not be configured within the database, etc.:

2310

<div class="highlight-python"><div class="highlight"><pre>>>> mytable = Table('mytable', meta,

2311

... Column('id', Integer, primary_key=True), # override reflected 'id' to have primary key

2312

... Column('mydata', Unicode(50)), # override reflected 'mydata' to be Unicode

2313

... autoload=True)</pre></div>

2314

</div>

2315

</div>

2316

2317

<h3>Reflecting Views<a class="headerlink" href="#reflecting-views" title="Permalink to this headline">¶</a></h3>

2318

The reflection system can also reflect views. Basic usage is the same as that

2319

of a table:

2320

<div class="highlight-python"><div class="highlight"><pre>my_view = Table("some_view", metadata, autoload=True)</pre></div>

2321

</div>

2322

Above, <tt class="docutils literal">my_view</tt> is a <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object with

2323

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects representing the names and types of

2324

each column within the view “some_view”.

2325

Usually, it’s desired to have at least a primary key constraint when

2326

reflecting a view, if not foreign keys as well. View reflection doesn’t

2327

extrapolate these constraints.

2328

Use the “override” technique for this, specifying explicitly those columns

2329

which are part of the primary key or have foreign key constraints:

2330

<div class="highlight-python"><div class="highlight"><pre>my_view = Table("some_view", metadata,

2331

Column("view_id", Integer, primary_key=True),

2332

Column("related_thing", Integer, ForeignKey("othertable.thing_id")),

2333

autoload=True

2334

)</pre></div>

2335

</div>

2336

</div>

2337

2338

<h3>Reflecting All Tables at Once<a class="headerlink" href="#reflecting-all-tables-at-once" title="Permalink to this headline">¶</a></h3>

2339

The <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object can also get a listing of

2340

tables and reflect the full set. This is achieved by using the

2341

<a class="reference internal" href="#sqlalchemy.schema.MetaData.reflect" title="sqlalchemy.schema.MetaData.reflect"><tt class="xref py py-func docutils literal">reflect()</tt></a> method. After calling it, all

2342

located tables are present within the <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>

2343

object’s dictionary of tables:

2344

<div class="highlight-python"><div class="highlight"><pre>meta = MetaData()

2345

meta.reflect(bind=someengine)

2346

users_table = meta.tables['users']

2347

addresses_table = meta.tables['addresses']</pre></div>

2348

</div>

2349

<tt class="docutils literal">metadata.reflect()</tt> also provides a handy way to clear or delete all the rows in a database:

2350

<div class="highlight-python"><div class="highlight"><pre>meta = MetaData()

2351

meta.reflect(bind=someengine)

2352

for table in reversed(meta.sorted_tables):

2353

someengine.execute(table.delete())</pre></div>

2354

</div>

2355

</div>

2356

2357

<h3>Fine Grained Reflection with Inspector<a class="headerlink" href="#fine-grained-reflection-with-inspector" title="Permalink to this headline">¶</a></h3>

2358

A low level interface which provides a backend-agnostic system of loading

2359

lists of schema, table, column, and constraint descriptions from a given

2360

database is also available. This is known as the “Inspector”:

2361

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

2362

from sqlalchemy.engine import reflection

2363

engine = create_engine('...')

2364

insp = reflection.Inspector.from_engine(engine)

2365

print insp.get_table_names()</pre></div>

2366

</div>

2367

2368

2369

class <tt class="descclassname">sqlalchemy.engine.reflection.</tt><tt class="descname">Inspector</tt><big>(</big>bind<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector" title="Permalink to this definition">¶</a></dt>

2370

<dd>Bases: <tt class="xref py py-class docutils literal">object</tt>

2371

Performs database schema inspection.

2372

The Inspector acts as a proxy to the reflection methods of the

2373

<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><tt class="xref py py-class docutils literal">Dialect</tt></a>, providing a

2374

consistent interface as well as caching support for previously

2375

fetched metadata.

2376

A <a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><tt class="xref py py-class docutils literal">Inspector</tt></a> object is usually created via the

2377

<a class="reference internal" href="inspection.html#sqlalchemy.inspection.inspect" title="sqlalchemy.inspection.inspect"><tt class="xref py py-func docutils literal">inspect()</tt></a> function:

2378

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import inspect, create_engine

2379

engine = create_engine('...')

2380

insp = inspect(engine)</pre></div>

2381

</div>

2382

The inspection method above is equivalent to using the

2383

<a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector.from_engine" title="sqlalchemy.engine.reflection.Inspector.from_engine"><tt class="xref py py-meth docutils literal">Inspector.from_engine()</tt></a> method, i.e.:

2384

<div class="highlight-python"><div class="highlight"><pre>engine = create_engine('...')

2385

insp = Inspector.from_engine(engine)</pre></div>

2386

</div>

2387

Where above, the <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><tt class="xref py py-class docutils literal">Dialect</tt></a> may opt

2388

to return an <a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><tt class="xref py py-class docutils literal">Inspector</tt></a> subclass that provides additional

2389

methods specific to the dialect’s target database.

2390

2391

2392

2393

<dd>Initialize a new <a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><tt class="xref py py-class docutils literal">Inspector</tt></a>.

2394

2395

2396

2397

2398

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body">bind – a <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a>,

2399

which is typically an instance of

2400

<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> or

2401

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a>.</td>

2402

</tr>

2403

</tbody>

2404

</table>

2405

For a dialect-specific instance of <a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><tt class="xref py py-class docutils literal">Inspector</tt></a>, see

2406

<a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector.from_engine" title="sqlalchemy.engine.reflection.Inspector.from_engine"><tt class="xref py py-meth docutils literal">Inspector.from_engine()</tt></a>

2407

</dd></dl>

2408

2409

2410

2411

<tt class="descname">default_schema_name</tt><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.default_schema_name" title="Permalink to this definition">¶</a></dt>

2412

<dd>Return the default schema name presented by the dialect

2413

for the current engine’s database user.

2414

E.g. this is typically <tt class="docutils literal">public</tt> for Postgresql and <tt class="docutils literal">dbo</tt>

2415

for SQL Server.

2416

</dd></dl>

2417

2418

2419

2420

classmethod <tt class="descname">from_engine</tt><big>(</big>bind<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.from_engine" title="Permalink to this definition">¶</a></dt>

2421

<dd>Construct a new dialect-specific Inspector object from the given

2422

engine or connection.

2423

2424

2425

2426

2427

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body">bind – a <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a>,

2428

which is typically an instance of

2429

<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> or

2430

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a>.</td>

2431

</tr>

2432

</tbody>

2433

</table>

2434

This method differs from direct a direct constructor call of

2435

<a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><tt class="xref py py-class docutils literal">Inspector</tt></a> in that the

2436

<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><tt class="xref py py-class docutils literal">Dialect</tt></a> is given a chance to

2437

provide a dialect-specific <a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><tt class="xref py py-class docutils literal">Inspector</tt></a> instance, which may

2438

provide additional methods.

2439

See the example at <a class="reference internal" href="#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><tt class="xref py py-class docutils literal">Inspector</tt></a>.

2440

</dd></dl>

2441

2442

2443

2444

<tt class="descname">get_columns</tt><big>(</big>table_name, schema=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_columns" title="Permalink to this definition">¶</a></dt>

2445

<dd>Return information about columns in <cite>table_name</cite>.

2446

Given a string <cite>table_name</cite> and an optional string <cite>schema</cite>, return

2447

column information as a list of dicts with these keys:

2448

2449

2450

<dd>the column’s name</dd>

2451

2452

<dd><a class="reference internal" href="types.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><tt class="xref py py-class docutils literal">TypeEngine</tt></a></dd>

2453

<dt>nullable</dt>

2454

<dd>boolean</dd>

2455

<dt>default</dt>

2456

<dd>the column’s default value</dd>

2457

<dt>attrs</dt>

2458

<dd>dict containing optional column attributes</dd>

2459

</dl>

2460

</dd></dl>

2461

2462

2463

2464

<tt class="descname">get_foreign_keys</tt><big>(</big>table_name, schema=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_foreign_keys" title="Permalink to this definition">¶</a></dt>

2465

<dd>Return information about foreign_keys in <cite>table_name</cite>.

2466

Given a string <cite>table_name</cite>, and an optional string <cite>schema</cite>, return

2467

foreign key information as a list of dicts with these keys:

2468

2469

<dt>constrained_columns</dt>

2470

<dd>a list of column names that make up the foreign key</dd>

2471

<dt>referred_schema</dt>

2472

<dd>the name of the referred schema</dd>

2473

<dt>referred_table</dt>

2474

<dd>the name of the referred table</dd>

2475

<dt>referred_columns</dt>

2476

<dd>a list of column names in the referred table that correspond to

2477

constrained_columns</dd>

2478

2479

<dd>optional name of the foreign key constraint.</dd>

2480

2481

<dd>other options passed to the dialect’s get_foreign_keys() method.</dd>

2482

</dl>

2483

</dd></dl>

2484

2485

2486

2487

<tt class="descname">get_indexes</tt><big>(</big>table_name, schema=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_indexes" title="Permalink to this definition">¶</a></dt>

2488

<dd>Return information about indexes in <cite>table_name</cite>.

2489

Given a string <cite>table_name</cite> and an optional string <cite>schema</cite>, return

2490

index information as a list of dicts with these keys:

2491

2492

2493

<dd>the index’s name</dd>

2494

<dt>column_names</dt>

2495

<dd>list of column names in order</dd>

2496

<dt>unique</dt>

2497

<dd>boolean</dd>

2498

2499

<dd>other options passed to the dialect’s get_indexes() method.</dd>

2500

</dl>

2501

</dd></dl>

2502

2503

2504

2505

<tt class="descname">get_pk_constraint</tt><big>(</big>table_name, schema=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_pk_constraint" title="Permalink to this definition">¶</a></dt>

2506

<dd>Return information about primary key constraint on <cite>table_name</cite>.

2507

Given a string <cite>table_name</cite>, and an optional string <cite>schema</cite>, return

2508

primary key information as a dictionary with these keys:

2509

2510

<dt>constrained_columns</dt>

2511

<dd>a list of column names that make up the primary key</dd>

2512

2513

<dd>optional name of the primary key constraint.</dd>

2514

</dl>

2515

</dd></dl>

2516

2517

2518

2519

<tt class="descname">get_primary_keys</tt><big>(</big>table_name, schema=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_primary_keys" title="Permalink to this definition">¶</a></dt>

2520

<dd>Return information about primary keys in <cite>table_name</cite>.

2521

2522

Deprecated since version 0.7: Call to deprecated method get_primary_keys. Use get_pk_constraint instead.

2523

Given a string <cite>table_name</cite>, and an optional string <cite>schema</cite>, return

2524

primary key information as a list of column names.

2525

</dd></dl>

2526

2527

2528

2529

<tt class="descname">get_schema_names</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_schema_names" title="Permalink to this definition">¶</a></dt>

2530

<dd>Return all schema names.

2531

</dd></dl>

2532

2533

2534

2535

<tt class="descname">get_table_names</tt><big>(</big>schema=None, order_by=None<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_table_names" title="Permalink to this definition">¶</a></dt>

2536

<dd>Return all table names in referred to within a particular schema.

2537

The names are expected to be real tables only, not views.

2538

Views are instead returned using the <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect.get_view_names" title="sqlalchemy.engine.interfaces.Dialect.get_view_names"><tt class="xref py py-meth docutils literal">get_view_names()</tt></a>

2539

method.

2540

2541

2542

2543

2544

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

2545

<li>schema – Schema name. If <tt class="docutils literal">schema</tt> is left at <tt class="docutils literal">None</tt>, the

2546

database’s default schema is

2547

used, else the named schema is searched. If the database does not

2548

support named schemas, behavior is undefined if <tt class="docutils literal">schema</tt> is not

2549

passed as <tt class="docutils literal">None</tt>.</li>

2550

<li>order_by – Optional, may be the string “foreign_key” to sort

2551

the result on foreign key dependencies.

2552

2553

Changed in version 0.8: the “foreign_key” sorting sorts tables

2554

in order of dependee to dependent; that is, in creation

2555

order, rather than in drop order. This is to maintain

2556

consistency with similar features such as

2557

<a class="reference internal" href="#sqlalchemy.schema.MetaData.sorted_tables" title="sqlalchemy.schema.MetaData.sorted_tables"><tt class="xref py py-attr docutils literal">MetaData.sorted_tables</tt></a> and <tt class="xref py py-func docutils literal">util.sort_tables()</tt>.

2558

</li>

2559

</ul>

2560

</td>

2561

</tr>

2562

</tbody>

2563

</table>

2564

2565

See also

2566

<a class="reference internal" href="#sqlalchemy.schema.MetaData.sorted_tables" title="sqlalchemy.schema.MetaData.sorted_tables"><tt class="xref py py-attr docutils literal">MetaData.sorted_tables</tt></a>

2567

</div>

2568

</dd></dl>

2569

2570

2571

2572

<tt class="descname">get_table_options</tt><big>(</big>table_name, schema=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_table_options" title="Permalink to this definition">¶</a></dt>

2573

<dd>Return a dictionary of options specified when the table of the

2574

given name was created.

2575

This currently includes some options that apply to MySQL tables.

2576

</dd></dl>

2577

2578

2579

2580

<tt class="descname">get_view_definition</tt><big>(</big>view_name, schema=None<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_view_definition" title="Permalink to this definition">¶</a></dt>

2581

<dd>Return definition for <cite>view_name</cite>.

2582

2583

2584

2585

2586

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body">schema – Optional, retrieve names from a non-default schema.</td>

2587

</tr>

2588

</tbody>

2589

</table>

2590

</dd></dl>

2591

2592

2593

2594

<tt class="descname">get_view_names</tt><big>(</big>schema=None<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.get_view_names" title="Permalink to this definition">¶</a></dt>

2595

<dd>Return all view names in <cite>schema</cite>.

2596

2597

2598

2599

2600

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body">schema – Optional, retrieve names from a non-default schema.</td>

2601

</tr>

2602

</tbody>

2603

</table>

2604

</dd></dl>

2605

2606

2607

2608

<tt class="descname">reflecttable</tt><big>(</big>table, include_columns, exclude_columns=()<big>)</big><a class="headerlink" href="#sqlalchemy.engine.reflection.Inspector.reflecttable" title="Permalink to this definition">¶</a></dt>

2609

<dd>Given a Table object, load its internal constructs based on

2610

introspection.

2611

This is the underlying method used by most dialects to produce

2612

table reflection. Direct usage is like:

2613

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import create_engine, MetaData, Table

2614

from sqlalchemy.engine import reflection

2615

2616

engine = create_engine('...')

2617

meta = MetaData()

2618

user_table = Table('user', meta)

2619

insp = Inspector.from_engine(engine)

2620

insp.reflecttable(user_table, None)</pre></div>

2621

</div>

2622

2623

2624

2625

2626

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

2627

<li>table – a <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> instance.</li>

2628

<li>include_columns – a list of string column names to include

2629

in the reflection process. If <tt class="docutils literal">None</tt>, all columns are reflected.</li>

2630

</ul>

2631

</td>

2632

</tr>

2633

</tbody>

2634

</table>

2635

</dd></dl>

2636

2637

</dd></dl>

2638

2639

</div>

2640

</div>

2641

2642

<h2>Column Insert/Update Defaults<a class="headerlink" href="#column-insert-update-defaults" title="Permalink to this headline">¶</a></h2>

2643

SQLAlchemy provides a very rich featureset regarding column level events which

2644

take place during INSERT and UPDATE statements. Options include:

2645

2646

<li>Scalar values used as defaults during INSERT and UPDATE operations</li>

2647

<li>Python functions which execute upon INSERT and UPDATE operations</li>

2648

<li>SQL expressions which are embedded in INSERT statements (or in some cases execute beforehand)</li>

2649

<li>SQL expressions which are embedded in UPDATE statements</li>

2650

<li>Server side default values used during INSERT</li>

2651

<li>Markers for server-side triggers used during UPDATE</li>

2652

</ul>

2653

The general rule for all insert/update defaults is that they only take effect

2654

if no value for a particular column is passed as an <tt class="docutils literal">execute()</tt> parameter;

2655

otherwise, the given value is used.

2656

2657

<h3>Scalar Defaults<a class="headerlink" href="#scalar-defaults" title="Permalink to this headline">¶</a></h3>

2658

The simplest kind of default is a scalar value used as the default value of a column:

2659

<div class="highlight-python"><div class="highlight"><pre>Table("mytable", meta,

2660

Column("somecolumn", Integer, default=12)

2661

)</pre></div>

2662

</div>

2663

Above, the value “12” will be bound as the column value during an INSERT if no

2664

other value is supplied.

2665

A scalar value may also be associated with an UPDATE statement, though this is

2666

not very common (as UPDATE statements are usually looking for dynamic

2667

defaults):

2668

<div class="highlight-python"><div class="highlight"><pre>Table("mytable", meta,

2669

Column("somecolumn", Integer, onupdate=25)

2670

)</pre></div>

2671

</div>

2672

</div>

2673

2674

<h3>Python-Executed Functions<a class="headerlink" href="#python-executed-functions" title="Permalink to this headline">¶</a></h3>

2675

The <tt class="docutils literal">default</tt> and <tt class="docutils literal">onupdate</tt> keyword arguments also accept Python

2676

functions. These functions are invoked at the time of insert or update if no

2677

other value for that column is supplied, and the value returned is used for

2678

the column’s value. Below illustrates a crude “sequence” that assigns an

2679

incrementing counter to a primary key column:

2680

<div class="highlight-python"><div class="highlight"><pre># a function which counts upwards

2681

i = 0

2682

def mydefault():

2683

global i

2684

i += 1

2685

return i

2686

2687

t = Table("mytable", meta,

2688

Column('id', Integer, primary_key=True, default=mydefault),

2689

)</pre></div>

2690

</div>

2691

It should be noted that for real “incrementing sequence” behavior, the

2692

built-in capabilities of the database should normally be used, which may

2693

include sequence objects or other autoincrementing capabilities. For primary

2694

key columns, SQLAlchemy will in most cases use these capabilities

2695

automatically. See the API documentation for

2696

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> including the <tt class="docutils literal">autoincrement</tt> flag, as

2697

well as the section on <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> later in this

2698

chapter for background on standard primary key generation techniques.

2699

To illustrate onupdate, we assign the Python <tt class="docutils literal">datetime</tt> function <tt class="docutils literal">now</tt> to

2700

the <tt class="docutils literal">onupdate</tt> attribute:

2701

<div class="highlight-python"><div class="highlight"><pre>import datetime

2702

2703

t = Table("mytable", meta,

2704

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

2705

2706

# define 'last_updated' to be populated with datetime.now()

2707

Column('last_updated', DateTime, onupdate=datetime.datetime.now),

2708

)</pre></div>

2709

</div>

2710

When an update statement executes and no value is passed for <tt class="docutils literal">last_updated</tt>,

2711

the <tt class="docutils literal">datetime.datetime.now()</tt> Python function is executed and its return

2712

value used as the value for <tt class="docutils literal">last_updated</tt>. Notice that we provide <tt class="docutils literal">now</tt>

2713

as the function itself without calling it (i.e. there are no parenthesis

2714

following) - SQLAlchemy will execute the function at the time the statement

2715

executes.

2716

2717

<h4>Context-Sensitive Default Functions<a class="headerlink" href="#context-sensitive-default-functions" title="Permalink to this headline">¶</a></h4>

2718

The Python functions used by <tt class="docutils literal">default</tt> and <tt class="docutils literal">onupdate</tt> may also make use of

2719

the current statement’s context in order to determine a value. The <cite>context</cite>

2720

of a statement is an internal SQLAlchemy object which contains all information

2721

about the statement being executed, including its source expression, the

2722

parameters associated with it and the cursor. The typical use case for this

2723

context with regards to default generation is to have access to the other

2724

values being inserted or updated on the row. To access the context, provide a

2725

function that accepts a single <tt class="docutils literal">context</tt> argument:

2726

<div class="highlight-python"><div class="highlight"><pre>def mydefault(context):

2727

return context.current_parameters['counter'] + 12

2728

2729

t = Table('mytable', meta,

2730

Column('counter', Integer),

2731

Column('counter_plus_twelve', Integer, default=mydefault, onupdate=mydefault)

2732

)</pre></div>

2733

</div>

2734

Above we illustrate a default function which will execute for all INSERT and

2735

UPDATE statements where a value for <tt class="docutils literal">counter_plus_twelve</tt> was otherwise not

2736

provided, and the value will be that of whatever value is present in the

2737

execution for the <tt class="docutils literal">counter</tt> column, plus the number 12.

2738

While the context object passed to the default function has many attributes,

2739

the <tt class="docutils literal">current_parameters</tt> member is a special member provided only during the

2740

execution of a default function for the purposes of deriving defaults from its

2741

existing values. For a single statement that is executing many sets of bind

2742

parameters, the user-defined function is called for each set of parameters,

2743

and <tt class="docutils literal">current_parameters</tt> will be provided with each individual parameter set

2744

for each execution.

2745

</div>

2746

</div>

2747

2748

<h3>SQL Expressions<a class="headerlink" href="#sql-expressions" title="Permalink to this headline">¶</a></h3>

2749

The “default” and “onupdate” keywords may also be passed SQL expressions,

2750

including select statements or direct function calls:

2751

<div class="highlight-python"><div class="highlight"><pre>t = Table("mytable", meta,

2752

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

2753

2754

# define 'create_date' to default to now()

2755

Column('create_date', DateTime, default=func.now()),

2756

2757

# define 'key' to pull its default from the 'keyvalues' table

2758

Column('key', String(20), default=keyvalues.select(keyvalues.c.type='type1', limit=1)),

2759

2760

# define 'last_modified' to use the current_timestamp SQL function on update

2761

Column('last_modified', DateTime, onupdate=func.utc_timestamp())

2762

)</pre></div>

2763

</div>

2764

Above, the <tt class="docutils literal">create_date</tt> column will be populated with the result of the

2765

<tt class="docutils literal">now()</tt> SQL function (which, depending on backend, compiles into <tt class="docutils literal">NOW()</tt>

2766

or <tt class="docutils literal">CURRENT_TIMESTAMP</tt> in most cases) during an INSERT statement, and the

2767

<tt class="docutils literal">key</tt> column with the result of a SELECT subquery from another table. The

2768

<tt class="docutils literal">last_modified</tt> column will be populated with the value of

2769

<tt class="docutils literal">UTC_TIMESTAMP()</tt>, a function specific to MySQL, when an UPDATE statement is

2770

emitted for this table.

2771

Note that when using <tt class="docutils literal">func</tt> functions, unlike when using Python <cite>datetime</cite>

2772

functions we do call the function, i.e. with parenthesis “()” - this is

2773

because what we want in this case is the return value of the function, which

2774

is the SQL expression construct that will be rendered into the INSERT or

2775

UPDATE statement.

2776

The above SQL functions are usually executed “inline” with the INSERT or

2777

UPDATE statement being executed, meaning, a single statement is executed which

2778

embeds the given expressions or subqueries within the VALUES or SET clause of

2779

the statement. Although in some cases, the function is “pre-executed” in a

2780

SELECT statement of its own beforehand. This happens when all of the following

2781

is true:

2782

2783

<li>the column is a primary key column</li>

2784

<li>the database dialect does not support a usable <tt class="docutils literal">cursor.lastrowid</tt> accessor

2785

(or equivalent); this currently includes PostgreSQL, Oracle, and Firebird, as

2786

well as some MySQL dialects.</li>

2787

<li>the dialect does not support the “RETURNING” clause or similar, or the

2788

<tt class="docutils literal">implicit_returning</tt> flag is set to <tt class="docutils literal">False</tt> for the dialect. Dialects

2789

which support RETURNING currently include Postgresql, Oracle, Firebird, and

2790

MS-SQL.</li>

2791

<li>the statement is a single execution, i.e. only supplies one set of

2792

parameters and doesn’t use “executemany” behavior</li>

2793

<li>the <tt class="docutils literal">inline=True</tt> flag is not set on the

2794

<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> or

2795

<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Update" title="sqlalchemy.sql.expression.Update"><tt class="xref py py-class docutils literal">Update()</tt></a> construct, and the statement has

2796

not defined an explicit <cite>returning()</cite> clause.</li>

2797

</ul>

2798

Whether or not the default generation clause “pre-executes” is not something

2799

that normally needs to be considered, unless it is being addressed for

2800

performance reasons.

2801

When the statement is executed with a single set of parameters (that is, it is

2802

not an “executemany” style execution), the returned

2803

<a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><tt class="xref py py-class docutils literal">ResultProxy</tt></a> will contain a collection

2804

accessible via <tt class="docutils literal">result.postfetch_cols()</tt> which contains a list of all

2805

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects which had an inline-executed

2806

default. Similarly, all parameters which were bound to the statement,

2807

including all Python and SQL expressions which were pre-executed, are present

2808

in the <tt class="docutils literal">last_inserted_params()</tt> or <tt class="docutils literal">last_updated_params()</tt> collections on

2809

<a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><tt class="xref py py-class docutils literal">ResultProxy</tt></a>. The <tt class="docutils literal">inserted_primary_key</tt>

2810

collection contains a list of primary key values for the row inserted (a list

2811

so that single-column and composite-column primary keys are represented in the

2812

same format).

2813

</div>

2814

2815

<h3>Server Side Defaults<a class="headerlink" href="#server-side-defaults" title="Permalink to this headline">¶</a></h3>

2816

A variant on the SQL expression default is the <tt class="docutils literal">server_default</tt>, which gets

2817

placed in the CREATE TABLE statement during a <tt class="docutils literal">create()</tt> operation:

2818

<div class="highlight-python+sql"><div class="highlight"><pre>t = Table('test', meta,

2819

Column('abc', String(20), server_default='abc'),

2820

Column('created_at', DateTime, server_default=text("sysdate"))

2821

)</pre></div>

2822

</div>

2823

A create call for the above table will produce:

2824

<div class="highlight-python"><pre>CREATE TABLE test (

2825

abc varchar(20) default 'abc',

2826

created_at datetime default sysdate

2827

)</pre>

2828

</div>

2829

The behavior of <tt class="docutils literal">server_default</tt> is similar to that of a regular SQL

2830

default; if it’s placed on a primary key column for a database which doesn’t

2831

have a way to “postfetch” the ID, and the statement is not “inlined”, the SQL

2832

expression is pre-executed; otherwise, SQLAlchemy lets the default fire off on

2833

the database side normally.

2834

</div>

2835

2836

<h3>Triggered Columns<a class="headerlink" href="#triggered-columns" title="Permalink to this headline">¶</a></h3>

2837

Columns with values set by a database trigger or other external process may be

2838

called out using <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a> as a marker:

2839

<div class="highlight-python"><div class="highlight"><pre>t = Table('test', meta,

2840

Column('abc', String(20), server_default=FetchedValue()),

2841

Column('def', String(20), server_onupdate=FetchedValue())

2842

)</pre></div>

2843

</div>

2844

2845

Changed in version 0.8.0b2,0.7.10: The <tt class="docutils literal">for_update</tt> argument on <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a> is set automatically

2846

when specified as the <tt class="docutils literal">server_onupdate</tt> argument. If using an older version,

2847

specify the onupdate above as <tt class="docutils literal">server_onupdate=FetchedValue(for_update=True)</tt>.

2848

These markers do not emit a “default” clause when the table is created,

2849

however they do set the same internal flags as a static <tt class="docutils literal">server_default</tt>

2850

clause, providing hints to higher-level tools that a “post-fetch” of these

2851

rows should be performed after an insert or update.

2852

2853

Note

2854

It’s generally not appropriate to use <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a> in

2855

conjunction with a primary key column, particularly when using the

2856

ORM or any other scenario where the <a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy.inserted_primary_key" title="sqlalchemy.engine.ResultProxy.inserted_primary_key"><tt class="xref py py-attr docutils literal">ResultProxy.inserted_primary_key</tt></a>

2857

attribute is required. This is becaue the “post-fetch” operation requires

2858

that the primary key value already be available, so that the

2859

row can be selected on its primary key.

2860

For a server-generated primary key value, all databases provide special

2861

accessors or other techniques in order to acquire the “last inserted

2862

primary key” column of a table. These mechanisms aren’t affected by the presence

2863

of <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a>. For special situations where triggers are

2864

used to generate primary key values, and the database in use does not

2865

support the <tt class="docutils literal">RETURNING</tt> clause, it may be necessary to forego the usage

2866

of the trigger and instead apply the SQL expression or function as a

2867

“pre execute” expression:

2868

<div class="highlight-python"><div class="highlight"><pre>t = Table('test', meta,

2869

Column('abc', MyType, default=func.generate_new_value(), primary_key=True)

2870

)</pre></div>

2871

</div>

2872

Where above, when <a class="reference internal" href="#sqlalchemy.schema.Table.insert" title="sqlalchemy.schema.Table.insert"><tt class="xref py py-meth docutils literal">Table.insert()</tt></a> is used,

2873

the <tt class="docutils literal">func.generate_new_value()</tt> expression will be pre-executed

2874

in the context of a scalar <tt class="docutils literal">SELECT</tt> statement, and the new value will

2875

be applied to the subsequent <tt class="docutils literal">INSERT</tt>, while at the same time being

2876

made available to the <a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy.inserted_primary_key" title="sqlalchemy.engine.ResultProxy.inserted_primary_key"><tt class="xref py py-attr docutils literal">ResultProxy.inserted_primary_key</tt></a>

2877

attribute.

2878

</div>

2879

</div>

2880

2881

<h3>Defining Sequences<a class="headerlink" href="#defining-sequences" title="Permalink to this headline">¶</a></h3>

2882

SQLAlchemy represents database sequences using the

2883

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> object, which is considered to be a

2884

special case of “column default”. It only has an effect on databases which

2885

have explicit support for sequences, which currently includes Postgresql,

2886

Oracle, and Firebird. The <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> object is

2887

otherwise ignored.

2888

The <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> may be placed on any column as a

2889

“default” generator to be used during INSERT operations, and can also be

2890

configured to fire off during UPDATE operations if desired. It is most

2891

commonly used in conjunction with a single integer primary key column:

2892

<div class="highlight-python"><div class="highlight"><pre>table = Table("cartitems", meta,

2893

Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True),

2894

Column("description", String(40)),

2895

Column("createdate", DateTime())

2896

)</pre></div>

2897

</div>

2898

Where above, the table “cartitems” is associated with a sequence named

2899

“cart_id_seq”. When INSERT statements take place for “cartitems”, and no value

2900

is passed for the “cart_id” column, the “cart_id_seq” sequence will be used to

2901

generate a value.

2902

When the <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> is associated with a table,

2903

CREATE and DROP statements issued for that table will also issue CREATE/DROP

2904

for the sequence object as well, thus “bundling” the sequence object with its

2905

parent table.

2906

The <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> object also implements special

2907

functionality to accommodate Postgresql’s SERIAL datatype. The SERIAL type in

2908

PG automatically generates a sequence that is used implicitly during inserts.

2909

This means that if a <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object defines a

2910

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> on its primary key column so that it

2911

works with Oracle and Firebird, the <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> would

2912

get in the way of the “implicit” sequence that PG would normally use. For this

2913

use case, add the flag <tt class="docutils literal">optional=True</tt> to the

2914

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> object - this indicates that the

2915

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> should only be used if the database

2916

provides no other option for generating primary key identifiers.

2917

The <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> object also has the ability to be

2918

executed standalone like a SQL expression, which has the effect of calling its

2919

“next value” function:

2920

<div class="highlight-python"><div class="highlight"><pre>seq = Sequence('some_sequence')

2921

nextid = connection.execute(seq)</pre></div>

2922

</div>

2923

</div>

2924

2925

<h3>Default Objects API<a class="headerlink" href="#default-objects-api" title="Permalink to this headline">¶</a></h3>

2926

2927

2928

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">ColumnDefault</tt><big>(</big>arg, **kwargs<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ColumnDefault" title="Permalink to this definition">¶</a></dt>

2929

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.DefaultGenerator" title="sqlalchemy.schema.DefaultGenerator"><tt class="xref py py-class docutils literal">sqlalchemy.schema.DefaultGenerator</tt></a>

2930

A plain default value on a column.

2931

This could correspond to a constant, a callable function,

2932

or a SQL clause.

2933

<a class="reference internal" href="#sqlalchemy.schema.ColumnDefault" title="sqlalchemy.schema.ColumnDefault"><tt class="xref py py-class docutils literal">ColumnDefault</tt></a> is generated automatically

2934

whenever the <tt class="docutils literal">default</tt>, <tt class="docutils literal">onupdate</tt> arguments of

2935

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> are used. A <a class="reference internal" href="#sqlalchemy.schema.ColumnDefault" title="sqlalchemy.schema.ColumnDefault"><tt class="xref py py-class docutils literal">ColumnDefault</tt></a>

2936

can be passed positionally as well.

2937

For example, the following:

2938

<div class="highlight-python"><div class="highlight"><pre>Column('foo', Integer, default=50)</pre></div>

2939

</div>

2940

Is equivalent to:

2941

<div class="highlight-python"><div class="highlight"><pre>Column('foo', Integer, ColumnDefault(50))</pre></div>

2942

</div>

2943

</dd></dl>

2944

2945

2946

2947

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DefaultClause</tt><big>(</big>arg, for_update=False, _reflected=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DefaultClause" title="Permalink to this definition">¶</a></dt>

2948

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">sqlalchemy.schema.FetchedValue</tt></a>

2949

A DDL-specified DEFAULT column value.

2950

<a class="reference internal" href="#sqlalchemy.schema.DefaultClause" title="sqlalchemy.schema.DefaultClause"><tt class="xref py py-class docutils literal">DefaultClause</tt></a> is a <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a>

2951

that also generates a “DEFAULT” clause when

2952

“CREATE TABLE” is emitted.

2953

<a class="reference internal" href="#sqlalchemy.schema.DefaultClause" title="sqlalchemy.schema.DefaultClause"><tt class="xref py py-class docutils literal">DefaultClause</tt></a> is generated automatically

2954

whenever the <tt class="docutils literal">server_default</tt>, <tt class="docutils literal">server_onupdate</tt> arguments of

2955

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> are used. A <a class="reference internal" href="#sqlalchemy.schema.DefaultClause" title="sqlalchemy.schema.DefaultClause"><tt class="xref py py-class docutils literal">DefaultClause</tt></a>

2956

can be passed positionally as well.

2957

For example, the following:

2958

<div class="highlight-python"><div class="highlight"><pre>Column('foo', Integer, server_default="50")</pre></div>

2959

</div>

2960

Is equivalent to:

2961

<div class="highlight-python"><div class="highlight"><pre>Column('foo', Integer, DefaultClause("50"))</pre></div>

2962

</div>

2963

</dd></dl>

2964

2965

2966

2967

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DefaultGenerator</tt><big>(</big>for_update=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DefaultGenerator" title="Permalink to this definition">¶</a></dt>

2968

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._NotAColumnExpr</tt>, <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>

2969

Base class for column default values.

2970

</dd></dl>

2971

2972

2973

2974

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">FetchedValue</tt><big>(</big>for_update=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.FetchedValue" title="Permalink to this definition">¶</a></dt>

2975

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._NotAColumnExpr</tt>, <a class="reference internal" href="events.html#sqlalchemy.events.SchemaEventTarget" title="sqlalchemy.events.SchemaEventTarget"><tt class="xref py py-class docutils literal">sqlalchemy.events.SchemaEventTarget</tt></a>

2976

A marker for a transparent database-side default.

2977

Use <a class="reference internal" href="#sqlalchemy.schema.FetchedValue" title="sqlalchemy.schema.FetchedValue"><tt class="xref py py-class docutils literal">FetchedValue</tt></a> when the database is configured

2978

to provide some automatic default for a column.

2979

E.g.:

2980

<div class="highlight-python"><div class="highlight"><pre>Column('foo', Integer, FetchedValue())</pre></div>

2981

</div>

2982

Would indicate that some trigger or default generator

2983

will create a new value for the <tt class="docutils literal">foo</tt> column during an

2984

INSERT.

2985

2986

See also

2987

<a class="reference internal" href="#triggered-columns">Triggered Columns</a>

2988

</div>

2989

</dd></dl>

2990

2991

2992

2993

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">PassiveDefault</tt><big>(</big>*arg, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.PassiveDefault" title="Permalink to this definition">¶</a></dt>

2994

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.DefaultClause" title="sqlalchemy.schema.DefaultClause"><tt class="xref py py-class docutils literal">sqlalchemy.schema.DefaultClause</tt></a>

2995

A DDL-specified DEFAULT column value.

2996

2997

Deprecated since version 0.6: <a class="reference internal" href="#sqlalchemy.schema.PassiveDefault" title="sqlalchemy.schema.PassiveDefault"><tt class="xref py py-class docutils literal">PassiveDefault</tt></a> is deprecated.

2998

Use <a class="reference internal" href="#sqlalchemy.schema.DefaultClause" title="sqlalchemy.schema.DefaultClause"><tt class="xref py py-class docutils literal">DefaultClause</tt></a>.

2999

</dd></dl>

3000

3001

3002

3003

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">Sequence</tt><big>(</big>name, start=None, increment=None, schema=None, optional=False, quote=None, metadata=None, quote_schema=None, for_update=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Sequence" title="Permalink to this definition">¶</a></dt>

3004

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.DefaultGenerator" title="sqlalchemy.schema.DefaultGenerator"><tt class="xref py py-class docutils literal">sqlalchemy.schema.DefaultGenerator</tt></a>

3005

Represents a named database sequence.

3006

The <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> object represents the name and configurational

3007

parameters of a database sequence. It also represents

3008

a construct that can be “executed” by a SQLAlchemy <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>

3009

or <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a>, rendering the appropriate “next value” function

3010

for the target database and returning a result.

3011

The <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> is typically associated with a primary key column:

3012

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

3013

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

3014

)</pre></div>

3015

</div>

3016

When CREATE TABLE is emitted for the above <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, if the

3017

target platform supports sequences, a CREATE SEQUENCE statement will

3018

be emitted as well. For platforms that don’t support sequences,

3019

the <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> construct is ignored.

3020

See also: <a class="reference internal" href="#sqlalchemy.schema.CreateSequence" title="sqlalchemy.schema.CreateSequence"><tt class="xref py py-class docutils literal">CreateSequence</tt></a> <a class="reference internal" href="#sqlalchemy.schema.DropSequence" title="sqlalchemy.schema.DropSequence"><tt class="xref py py-class docutils literal">DropSequence</tt></a>

3021

3022

3023

<tt class="descname">__init__</tt><big>(</big>name, start=None, increment=None, schema=None, optional=False, quote=None, metadata=None, quote_schema=None, for_update=False<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Sequence.__init__" title="Permalink to this definition">¶</a></dt>

3024

<dd>Construct a <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> object.

3025

3026

3027

3028

3029

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

3030

<li>name – The name of the sequence.</li>

3031

<li>start – the starting index of the sequence. This value is

3032

used when the CREATE SEQUENCE command is emitted to the database

3033

as the value of the “START WITH” clause. If <tt class="docutils literal">None</tt>, the

3034

clause is omitted, which on most platforms indicates a starting

3035

value of 1.</li>

3036

<li>increment – the increment value of the sequence. This

3037

value is used when the CREATE SEQUENCE command is emitted to

3038

the database as the value of the “INCREMENT BY” clause. If <tt class="docutils literal">None</tt>,

3039

the clause is omitted, which on most platforms indicates an

3040

increment of 1.</li>

3041

<li>schema – Optional schema name for the sequence, if located

3042

in a schema other than the default.</li>

3043

<li>optional – boolean value, when <tt class="docutils literal">True</tt>, indicates that this

3044

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> object only needs to be explicitly generated

3045

on backends that don’t provide another way to generate primary

3046

key identifiers. Currently, it essentially means, “don’t create

3047

this sequence on the Postgresql backend, where the SERIAL keyword

3048

creates a sequence for us automatically”.</li>

3049

<li>quote – boolean value, when <tt class="docutils literal">True</tt> or <tt class="docutils literal">False</tt>, explicitly

3050

forces quoting of the schema name on or off. When left at its

3051

default of <tt class="docutils literal">None</tt>, normal quoting rules based on casing and reserved

3052

words take place.</li>

3053

<li>metadata – optional <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object which will be

3054

associated with this <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a>. A <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a>

3055

that is associated with a <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> gains access to the

3056

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

3057

<a class="reference internal" href="#sqlalchemy.schema.Sequence.create" title="sqlalchemy.schema.Sequence.create"><tt class="xref py py-meth docutils literal">Sequence.create()</tt></a> and <a class="reference internal" href="#sqlalchemy.schema.Sequence.drop" title="sqlalchemy.schema.Sequence.drop"><tt class="xref py py-meth docutils literal">Sequence.drop()</tt></a> methods will

3058

make usage of that engine automatically.

3059

3060

Changed in version 0.7: Additionally, the appropriate CREATE SEQUENCE/

3061

DROP SEQUENCE DDL commands will be emitted corresponding to this

3062

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> when <a class="reference internal" href="#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-meth docutils literal">MetaData.create_all()</tt></a> and

3063

<a class="reference internal" href="#sqlalchemy.schema.MetaData.drop_all" title="sqlalchemy.schema.MetaData.drop_all"><tt class="xref py py-meth docutils literal">MetaData.drop_all()</tt></a> are invoked.

3064

Note that when a <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> is applied to a <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>,

3065

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

3066

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object of that column’s parent <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

3067

when that association is made. The <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> will then

3068

be subject to automatic CREATE SEQUENCE/DROP SEQUENCE corresponding

3069

to when the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object itself is created or dropped,

3070

rather than that of the <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object overall.

3071

</li>

3072

<li>for_update – Indicates this <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a>, when associated

3073

with a <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, should be invoked for UPDATE statements

3074

on that column’s table, rather than for INSERT statements, when

3075

no value is otherwise present for that column in the statement.</li>

3076

</ul>

3077

</td>

3078

</tr>

3079

</tbody>

3080

</table>

3081

</dd></dl>

3082

3083

3084

3085

<tt class="descname">create</tt><big>(</big>bind=None, checkfirst=True<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Sequence.create" title="Permalink to this definition">¶</a></dt>

3086

<dd>Creates this sequence in the database.

3087

</dd></dl>

3088

3089

3090

3091

<tt class="descname">drop</tt><big>(</big>bind=None, checkfirst=True<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Sequence.drop" title="Permalink to this definition">¶</a></dt>

3092

<dd>Drops this sequence from the database.

3093

</dd></dl>

3094

3095

3096

3097

<tt class="descname">next_value</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.schema.Sequence.next_value" title="Permalink to this definition">¶</a></dt>

3098

<dd>Return a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.functions.next_value" title="sqlalchemy.sql.functions.next_value"><tt class="xref py py-class docutils literal">next_value</tt></a> function element

3099

which will render the appropriate increment function

3100

for this <a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a> within any SQL expression.

3101

</dd></dl>

3102

3103

</dd></dl>

3104

3105

</div>

3106

</div>

3107

3108

<h2>Defining Constraints and Indexes<a class="headerlink" href="#defining-constraints-and-indexes" title="Permalink to this headline">¶</a></h2>

3109

3110

<h3>Defining Foreign Keys<a class="headerlink" href="#defining-foreign-keys" title="Permalink to this headline">¶</a></h3>

3111

A foreign key in SQL is a table-level construct that constrains one or more

3112

columns in that table to only allow values that are present in a different set

3113

of columns, typically but not always located on a different table. We call the

3114

columns which are constrained the foreign key columns and the columns which

3115

they are constrained towards the referenced columns. The referenced columns

3116

almost always define the primary key for their owning table, though there are

3117

exceptions to this. The foreign key is the “joint” that connects together

3118

pairs of rows which have a relationship with each other, and SQLAlchemy

3119

assigns very deep importance to this concept in virtually every area of its

3120

operation.

3121

In SQLAlchemy as well as in DDL, foreign key constraints can be defined as

3122

additional attributes within the table clause, or for single-column foreign

3123

keys they may optionally be specified within the definition of a single

3124

column. The single column foreign key is more common, and at the column level

3125

is specified by constructing a <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object

3126

as an argument to a <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object:

3127

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

3128

Column('pref_id', Integer, primary_key=True),

3129

Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),

3130

Column('pref_name', String(40), nullable=False),

3131

Column('pref_value', String(100))

3132

)</pre></div>

3133

</div>

3134

Above, we define a new table <tt class="docutils literal">user_preference</tt> for which each row must

3135

contain a value in the <tt class="docutils literal">user_id</tt> column that also exists in the <tt class="docutils literal">user</tt>

3136

table’s <tt class="docutils literal">user_id</tt> column.

3137

The argument to <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> is most commonly a

3138

string of the form <tablename>.<columnname>, or for a table in a remote

3139

schema or “owner” of the form <schemaname>.<tablename>.<columnname>. It may

3140

also be an actual <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object, which as we’ll

3141

see later is accessed from an existing <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

3142

object via its <tt class="docutils literal">c</tt> collection:

3143

<div class="highlight-python"><div class="highlight"><pre>ForeignKey(user.c.user_id)</pre></div>

3144

</div>

3145

The advantage to using a string is that the in-python linkage between <tt class="docutils literal">user</tt>

3146

and <tt class="docutils literal">user_preference</tt> is resolved only when first needed, so that table

3147

objects can be easily spread across multiple modules and defined in any order.

3148

Foreign keys may also be defined at the table level, using the

3149

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> object. This object can

3150

describe a single- or multi-column foreign key. A multi-column foreign key is

3151

known as a composite foreign key, and almost always references a table that

3152

has a composite primary key. Below we define a table <tt class="docutils literal">invoice</tt> which has a

3153

composite primary key:

3154

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

3155

Column('invoice_id', Integer, primary_key=True),

3156

Column('ref_num', Integer, primary_key=True),

3157

Column('description', String(60), nullable=False)

3158

)</pre></div>

3159

</div>

3160

And then a table <tt class="docutils literal">invoice_item</tt> with a composite foreign key referencing

3161

<tt class="docutils literal">invoice</tt>:

3162

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

3163

Column('item_id', Integer, primary_key=True),

3164

Column('item_name', String(60), nullable=False),

3165

Column('invoice_id', Integer, nullable=False),

3166

Column('ref_num', Integer, nullable=False),

3167

ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id', 'invoice.ref_num'])

3168

)</pre></div>

3169

</div>

3170

It’s important to note that the

3171

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> is the only way to define a

3172

composite foreign key. While we could also have placed individual

3173

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

3174

<tt class="docutils literal">invoice_item.invoice_id</tt> and <tt class="docutils literal">invoice_item.ref_num</tt> columns, SQLAlchemy

3175

would not be aware that these two values should be paired together - it would

3176

be two individual foreign key constraints instead of a single composite

3177

foreign key referencing two columns.

3178

3179

<h4>Creating/Dropping Foreign Key Constraints via ALTER<a class="headerlink" href="#creating-dropping-foreign-key-constraints-via-alter" title="Permalink to this headline">¶</a></h4>

3180

In all the above examples, the <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object

3181

causes the “REFERENCES” keyword to be added inline to a column definition

3182

within a “CREATE TABLE” statement when

3183

<a class="reference internal" href="#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal">create_all()</tt></a> is issued, and

3184

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> invokes the “CONSTRAINT”

3185

keyword inline with “CREATE TABLE”. There are some cases where this is

3186

undesireable, particularly when two tables reference each other mutually, each

3187

with a foreign key referencing the other. In such a situation at least one of

3188

the foreign key constraints must be generated after both tables have been

3189

built. To support such a scheme, <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> and

3190

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> offer the flag

3191

<tt class="docutils literal">use_alter=True</tt>. When using this flag, the constraint will be generated

3192

using a definition similar to “ALTER TABLE <tablename> ADD CONSTRAINT <name>

3193

...”. Since a name is required, the <tt class="docutils literal">name</tt> attribute must also be specified.

3194

For example:

3195

<div class="highlight-python"><div class="highlight"><pre>node = Table('node', meta,

3196

Column('node_id', Integer, primary_key=True),

3197

Column('primary_element', Integer,

3198

ForeignKey('element.element_id', use_alter=True, name='fk_node_element_id')

3199

)

3200

)

3201

3202

element = Table('element', meta,

3203

Column('element_id', Integer, primary_key=True),

3204

Column('parent_node_id', Integer),

3205

ForeignKeyConstraint(

3206

['parent_node_id'],

3207

['node.node_id'],

3208

use_alter=True,

3209

name='fk_element_parent_node_id'

3210

)

3211

)</pre></div>

3212

</div>

3213

</div>

3214

3215

<h4>ON UPDATE and ON DELETE<a class="headerlink" href="#on-update-and-on-delete" title="Permalink to this headline">¶</a></h4>

3216

Most databases support cascading of foreign key values, that is the when a

3217

parent row is updated the new value is placed in child rows, or when the

3218

parent row is deleted all corresponding child rows are set to null or deleted.

3219

In data definition language these are specified using phrases like “ON UPDATE

3220

CASCADE”, “ON DELETE CASCADE”, and “ON DELETE SET NULL”, corresponding to

3221

foreign key constraints. The phrase after “ON UPDATE” or “ON DELETE” may also

3222

other allow other phrases that are specific to the database in use. The

3223

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> and

3224

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> objects support the

3225

generation of this clause via the <tt class="docutils literal">onupdate</tt> and <tt class="docutils literal">ondelete</tt> keyword

3226

arguments. The value is any string which will be output after the appropriate

3227

“ON UPDATE” or “ON DELETE” phrase:

3228

<div class="highlight-python"><div class="highlight"><pre>child = Table('child', meta,

3229

Column('id', Integer,

3230

ForeignKey('parent.id', onupdate="CASCADE", ondelete="CASCADE"),

3231

primary_key=True

3232

)

3233

)

3234

3235

composite = Table('composite', meta,

3236

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

3237

Column('rev_id', Integer),

3238

Column('note_id', Integer),

3239

ForeignKeyConstraint(

3240

['rev_id', 'note_id'],

3241

['revisions.id', 'revisions.note_id'],

3242

onupdate="CASCADE", ondelete="SET NULL"

3243

)

3244

)</pre></div>

3245

</div>

3246

Note that these clauses are not supported on SQLite, and require <tt class="docutils literal">InnoDB</tt>

3247

tables when used with MySQL. They may also not be supported on other

3248

databases.

3249

</div>

3250

</div>

3251

3252

<h3>UNIQUE Constraint<a class="headerlink" href="#unique-constraint" title="Permalink to this headline">¶</a></h3>

3253

Unique constraints can be created anonymously on a single column using the

3254

<tt class="docutils literal">unique</tt> keyword on <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>. Explicitly named

3255

unique constraints and/or those with multiple columns are created via the

3256

<a class="reference internal" href="#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><tt class="xref py py-class docutils literal">UniqueConstraint</tt></a> table-level construct.

3257

<div class="highlight-python+sql"><div class="highlight"><pre>meta = MetaData()

3258

mytable = Table('mytable', meta,

3259

3260

# per-column anonymous unique constraint

3261

Column('col1', Integer, unique=True),

3262

3263

Column('col2', Integer),

3264

Column('col3', Integer),

3265

3266

# explicit/composite unique constraint. 'name' is optional.

3267

UniqueConstraint('col2', 'col3', name='uix_1')

3268

)</pre></div>

3269

</div>

3270

</div>

3271

3272

<h3>CHECK Constraint<a class="headerlink" href="#check-constraint" title="Permalink to this headline">¶</a></h3>

3273

Check constraints can be named or unnamed and can be created at the Column or

3274

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

3275

The text of the check constraint is passed directly through to the database,

3276

so there is limited “database independent” behavior. Column level check

3277

constraints generally should only refer to the column to which they are

3278

placed, while table level constraints can refer to any columns in the table.

3279

Note that some databases do not actively support check constraints such as

3280

MySQL.

3281

<div class="highlight-python+sql"><div class="highlight"><pre>meta = MetaData()

3282

mytable = Table('mytable', meta,

3283

3284

# per-column CHECK constraint

3285

Column('col1', Integer, CheckConstraint('col1>5')),

3286

3287

Column('col2', Integer),

3288

Column('col3', Integer),

3289

3290

# table level CHECK constraint. 'name' is optional.

3291

CheckConstraint('col2 > col3 + 5', name='check1')

3292

)

3293

3294

<a href='#' class='sql_link'>sql</a>mytable.create(engine)

3295

<div class='popup_sql'>CREATE TABLE mytable (

3296

col1 INTEGER CHECK (col1>5),

3297

col2 INTEGER,

3298

col3 INTEGER,

3299

CONSTRAINT check1 CHECK (col2 > col3 + 5)

3300

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

3301

</div>

3302

</div>

3303

3304

<h3>Setting up Constraints when using the Declarative ORM Extension<a class="headerlink" href="#setting-up-constraints-when-using-the-declarative-orm-extension" title="Permalink to this headline">¶</a></h3>

3305

The <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is the SQLAlchemy Core construct that allows one to define

3306

table metadata, which among other things can be used by the SQLAlchemy ORM

3307

as a target to map a class. The <a class="reference internal" href="../orm/extensions/declarative.html">Declarative</a>

3308

extension allows the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object to be created automatically, given

3309

the contents of the table primarily as a mapping of <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects.

3310

To apply table-level constraint objects such as <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a>

3311

to a table defined using Declarative, use the <tt class="docutils literal">__table_args__</tt> attribute,

3312

described at <a class="reference internal" href="../orm/extensions/declarative.html#declarative-table-args">Table Configuration</a>.

3313

</div>

3314

3315

<h3>Constraints API<a class="headerlink" href="#constraints-api" title="Permalink to this headline">¶</a></h3>

3316

3317

3318

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">Constraint</tt><big>(</big>name=None, deferrable=None, initially=None, _create_rule=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Constraint" title="Permalink to this definition">¶</a></dt>

3319

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>

3320

A table-level SQL constraint.

3321

</dd></dl>

3322

3323

3324

3325

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">CheckConstraint</tt><big>(</big>sqltext, name=None, deferrable=None, initially=None, table=None, _create_rule=None, _autoattach=True<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CheckConstraint" title="Permalink to this definition">¶</a></dt>

3326

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.Constraint</tt></a>

3327

A table- or column-level CHECK constraint.

3328

Can be included in the definition of a Table or Column.

3329

</dd></dl>

3330

3331

3332

3333

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">ColumnCollectionConstraint</tt><big>(</big>*columns, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ColumnCollectionConstraint" title="Permalink to this definition">¶</a></dt>

3334

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema.ColumnCollectionMixin</tt>, <a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.Constraint</tt></a>

3335

A constraint that proxies a ColumnCollection.

3336

</dd></dl>

3337

3338

3339

3340

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">ForeignKey</tt><big>(</big>column, _constraint=None, use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, schema=None, initially=None, link_to_name=False, match=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey" title="Permalink to this definition">¶</a></dt>

3341

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>

3342

Defines a dependency between two columns.

3343

<tt class="docutils literal">ForeignKey</tt> is specified as an argument to a <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object,

3344

e.g.:

3345

<div class="highlight-python"><div class="highlight"><pre>t = Table("remote_table", metadata,

3346

Column("remote_id", ForeignKey("main_table.id"))

3347

)</pre></div>

3348

</div>

3349

Note that <tt class="docutils literal">ForeignKey</tt> is only a marker object that defines

3350

a dependency between two columns. The actual constraint

3351

is in all cases represented by the <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a>

3352

object. This object will be generated automatically when

3353

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

3354

in turn is associated with a <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. Conversely,

3355

when <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> is applied to a <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

3356

<tt class="docutils literal">ForeignKey</tt> markers are automatically generated to be

3357

present on each associated <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, which are also

3358

associated with the constraint object.

3359

Note that you cannot define a “composite” foreign key constraint,

3360

that is a constraint between a grouping of multiple parent/child

3361

columns, using <tt class="docutils literal">ForeignKey</tt> objects. To define this grouping,

3362

the <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> object must be used, and applied

3363

to the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. The associated <tt class="docutils literal">ForeignKey</tt> objects

3364

are created automatically.

3365

The <tt class="docutils literal">ForeignKey</tt> objects associated with an individual

3366

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object are available in the <cite>foreign_keys</cite> collection

3367

of that column.

3368

Further examples of foreign key configuration are in

3369

<a class="reference internal" href="#metadata-foreignkeys">Defining Foreign Keys</a>.

3370

3371

3372

<tt class="descname">__init__</tt><big>(</big>column, _constraint=None, use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, schema=None, initially=None, link_to_name=False, match=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.__init__" title="Permalink to this definition">¶</a></dt>

3373

<dd>Construct a column-level FOREIGN KEY.

3374

The <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object when constructed generates a

3375

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

3376

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

3377

3378

3379

3380

3381

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

3382

<li>column – A single target column for the key relationship. A

3383

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object or a column name as a string:

3384

<tt class="docutils literal">tablename.columnkey</tt> or <tt class="docutils literal">schema.tablename.columnkey</tt>.

3385

<tt class="docutils literal">columnkey</tt> is the <tt class="docutils literal">key</tt> which has been assigned to the column

3386

(defaults to the column name itself), unless <tt class="docutils literal">link_to_name</tt> is

3387

<tt class="docutils literal">True</tt> in which case the rendered name of the column is used.

3388

3389

New in version 0.7.4: Note that if the schema name is not included, and the

3390

underlying <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> has a “schema”, that value will

3391

be used.

3392

</li>

3393

<li>name – Optional string. An in-database name for the key if

3394

<cite>constraint</cite> is not provided.</li>

3395

<li>onupdate – Optional string. If set, emit ON UPDATE <value> when

3396

issuing DDL for this constraint. Typical values include CASCADE,

3397

DELETE and RESTRICT.</li>

3398

<li>ondelete – Optional string. If set, emit ON DELETE <value> when

3399

issuing DDL for this constraint. Typical values include CASCADE,

3400

DELETE and RESTRICT.</li>

3401

<li>deferrable – Optional bool. If set, emit DEFERRABLE or NOT

3402

DEFERRABLE when issuing DDL for this constraint.</li>

3403

<li>initially – Optional string. If set, emit INITIALLY <value> when

3404

issuing DDL for this constraint.</li>

3405

<li>link_to_name – if True, the string name given in <tt class="docutils literal">column</tt> is

3406

the rendered name of the referenced column, not its locally

3407

assigned <tt class="docutils literal">key</tt>.</li>

3408

<li>use_alter – passed to the underlying

3409

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> to indicate the constraint should be

3410

generated/dropped externally from the CREATE TABLE/ DROP TABLE

3411

statement. See that classes’ constructor for details.</li>

3412

<li>match – Optional string. If set, emit MATCH <value> when issuing

3413

DDL for this constraint. Typical values include SIMPLE, PARTIAL

3414

and FULL.</li>

3415

</ul>

3416

</td>

3417

</tr>

3418

</tbody>

3419

</table>

3420

</dd></dl>

3421

3422

3423

3424

<tt class="descname">column</tt><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.column" title="Permalink to this definition">¶</a></dt>

3425

<dd>Return the target <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> referenced by this

3426

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>.

3427

If this <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> was created using a

3428

string-based target column specification, this

3429

attribute will on first access initiate a resolution

3430

process to locate the referenced remote

3431

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>. The resolution process traverses

3432

to the parent <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, and

3433

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> to proceed - if any of these aren’t

3434

yet present, an error is raised.

3435

</dd></dl>

3436

3437

3438

3439

<tt class="descname">copy</tt><big>(</big>schema=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.copy" title="Permalink to this definition">¶</a></dt>

3440

<dd>Produce a copy of this <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object.

3441

The new <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> will not be bound

3442

to any <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>.

3443

This method is usually used by the internal

3444

copy procedures of <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

3445

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

3446

3447

3448

3449

3450

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body">schema – The returned <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> will

3451

reference the original table and column name, qualified

3452

by the given string schema name.</td>

3453

</tr>

3454

</tbody>

3455

</table>

3456

</dd></dl>

3457

3458

3459

3460

<tt class="descname">get_referent</tt><big>(</big>table<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.get_referent" title="Permalink to this definition">¶</a></dt>

3461

<dd>Return the <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> in the given <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

3462

referenced by this <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>.

3463

Returns None if this <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> does not reference the given

3464

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

3465

</dd></dl>

3466

3467

3468

3469

<tt class="descname">references</tt><big>(</big>table<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.references" title="Permalink to this definition">¶</a></dt>

3470

<dd>Return True if the given <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is referenced by this

3471

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>.

3472

</dd></dl>

3473

3474

3475

3476

<tt class="descname">target_fullname</tt><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.target_fullname" title="Permalink to this definition">¶</a></dt>

3477

<dd>Return a string based ‘column specification’ for this

3478

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>.

3479

This is usually the equivalent of the string-based “tablename.colname”

3480

argument first passed to the object’s constructor.

3481

</dd></dl>

3482

3483

</dd></dl>

3484

3485

3486

3487

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">ForeignKeyConstraint</tt><big>(</big>columns, refcolumns, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, use_alter=False, link_to_name=False, match=None, table=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKeyConstraint" title="Permalink to this definition">¶</a></dt>

3488

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.Constraint</tt></a>

3489

A table-level FOREIGN KEY constraint.

3490

Defines a single column or composite FOREIGN KEY ... REFERENCES

3491

constraint. For a no-frills, single column foreign key, adding a

3492

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> to the definition of a <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> is a shorthand

3493

equivalent for an unnamed, single column <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a>.

3494

Examples of foreign key configuration are in <a class="reference internal" href="#metadata-foreignkeys">Defining Foreign Keys</a>.

3495

3496

3497

<tt class="descname">__init__</tt><big>(</big>columns, refcolumns, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, use_alter=False, link_to_name=False, match=None, table=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKeyConstraint.__init__" title="Permalink to this definition">¶</a></dt>

3498

<dd>Construct a composite-capable FOREIGN KEY.

3499

3500

3501

3502

3503

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

3504

<li>columns – A sequence of local column names. The named columns

3505

must be defined and present in the parent Table. The names should

3506

match the <tt class="docutils literal">key</tt> given to each column (defaults to the name) unless

3507

3508

<li>refcolumns – A sequence of foreign column names or Column

3509

objects. The columns must all be located within the same Table.</li>

3510

<li>name – Optional, the in-database name of the key.</li>

3511

<li>onupdate – Optional string. If set, emit ON UPDATE <value> when

3512

issuing DDL for this constraint. Typical values include CASCADE,

3513

DELETE and RESTRICT.</li>

3514

<li>ondelete – Optional string. If set, emit ON DELETE <value> when

3515

issuing DDL for this constraint. Typical values include CASCADE,

3516

DELETE and RESTRICT.</li>

3517

<li>deferrable – Optional bool. If set, emit DEFERRABLE or NOT

3518

DEFERRABLE when issuing DDL for this constraint.</li>

3519

<li>initially – Optional string. If set, emit INITIALLY <value> when

3520

issuing DDL for this constraint.</li>

3521

<li>link_to_name – if True, the string name given in <tt class="docutils literal">column</tt> is

3522

the rendered name of the referenced column, not its locally assigned

3523

3524

<li>use_alter – If True, do not emit the DDL for this constraint as

3525

part of the CREATE TABLE definition. Instead, generate it via an

3526

ALTER TABLE statement issued after the full collection of tables

3527

have been created, and drop it via an ALTER TABLE statement before

3528

the full collection of tables are dropped. This is shorthand for the

3529

usage of <a class="reference internal" href="#sqlalchemy.schema.AddConstraint" title="sqlalchemy.schema.AddConstraint"><tt class="xref py py-class docutils literal">AddConstraint</tt></a> and <a class="reference internal" href="#sqlalchemy.schema.DropConstraint" title="sqlalchemy.schema.DropConstraint"><tt class="xref py py-class docutils literal">DropConstraint</tt></a> applied

3530

as “after-create” and “before-drop” events on the MetaData object.

3531

This is normally used to generate/drop constraints on objects that

3532

are mutually dependent on each other.</li>

3533

<li>match – Optional string. If set, emit MATCH <value> when issuing

3534

DDL for this constraint. Typical values include SIMPLE, PARTIAL

3535

and FULL.</li>

3536

</ul>

3537

</td>

3538

</tr>

3539

</tbody>

3540

</table>

3541

</dd></dl>

3542

3543

</dd></dl>

3544

3545

3546

3547

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">PrimaryKeyConstraint</tt><big>(</big>*columns, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.PrimaryKeyConstraint" title="Permalink to this definition">¶</a></dt>

3548

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.ColumnCollectionConstraint" title="sqlalchemy.schema.ColumnCollectionConstraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.ColumnCollectionConstraint</tt></a>

3549

A table-level PRIMARY KEY constraint.

3550

Defines a single column or composite PRIMARY KEY constraint. For a

3551

no-frills primary key, adding <tt class="docutils literal">primary_key=True</tt> to one or more

3552

<tt class="docutils literal">Column</tt> definitions is a shorthand equivalent for an unnamed single- or

3553

multiple-column PrimaryKeyConstraint.

3554

</dd></dl>

3555

3556

3557

3558

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">UniqueConstraint</tt><big>(</big>*columns, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.UniqueConstraint" title="Permalink to this definition">¶</a></dt>

3559

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.ColumnCollectionConstraint" title="sqlalchemy.schema.ColumnCollectionConstraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.ColumnCollectionConstraint</tt></a>

3560

A table-level UNIQUE constraint.

3561

Defines a single column or composite UNIQUE constraint. For a no-frills,

3562

single column constraint, adding <tt class="docutils literal">unique=True</tt> to the <tt class="docutils literal">Column</tt>

3563

definition is a shorthand equivalent for an unnamed, single column

3564

UniqueConstraint.

3565

</dd></dl>

3566

3567

</div>

3568

3569

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

3570

Indexes can be created anonymously (using an auto-generated name <tt class="docutils literal">ix_<column

3571

label></tt>) for a single column using the inline <tt class="docutils literal">index</tt> keyword on

3572

<a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, which also modifies the usage of

3573

<tt class="docutils literal">unique</tt> to apply the uniqueness to the index itself, instead of adding a

3574

separate UNIQUE constraint. For indexes with specific names or which encompass

3575

more than one column, use the <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct,

3576

which requires a name.

3577

Below we illustrate a <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> with several

3578

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> objects associated. The DDL for “CREATE

3579

INDEX” is issued right after the create statements for the table:

3580

<div class="highlight-python+sql"><div class="highlight"><pre>meta = MetaData()

3581

mytable = Table('mytable', meta,

3582

# an indexed column, with index "ix_mytable_col1"

3583

Column('col1', Integer, index=True),

3584

3585

# a uniquely indexed column with index "ix_mytable_col2"

3586

Column('col2', Integer, index=True, unique=True),

3587

3588

Column('col3', Integer),

3589

Column('col4', Integer),

3590

3591

Column('col5', Integer),

3592

Column('col6', Integer),

3593

)

3594

3595

# place an index on col3, col4

3596

Index('idx_col34', mytable.c.col3, mytable.c.col4)

3597

3598

# place a unique index on col5, col6

3599

Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)

3600

3601

<a href='#' class='sql_link'>sql</a>mytable.create(engine)

3602

<div class='popup_sql'>CREATE TABLE mytable (

3603

col1 INTEGER,

3604

col2 INTEGER,

3605

col3 INTEGER,

3606

col4 INTEGER,

3607

col5 INTEGER,

3608

col6 INTEGER

3609

)

3610

CREATE INDEX ix_mytable_col1 ON mytable (col1)

3611

CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)

3612

CREATE UNIQUE INDEX myindex ON mytable (col5, col6)

3613

CREATE INDEX idx_col34 ON mytable (col3, col4)</div></pre></div>

3614

</div>

3615

Note in the example above, the <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct is created

3616

externally to the table which it corresponds, using <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>

3617

objects directly. <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> also supports

3618

“inline” definition inside the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, using string names to

3619

identify columns:

3620

<div class="highlight-python"><div class="highlight"><pre>meta = MetaData()

3621

mytable = Table('mytable', meta,

3622

Column('col1', Integer),

3623

3624

Column('col2', Integer),

3625

3626

Column('col3', Integer),

3627

Column('col4', Integer),

3628

3629

# place an index on col1, col2

3630

Index('idx_col12', 'col1', 'col2'),

3631

3632

# place a unique index on col3, col4

3633

Index('idx_col34', 'col3', 'col4', unique=True)

3634

)</pre></div>

3635

</div>

3636

3637

New in version 0.7: Support of “inline” definition inside the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

3638

for <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a>.

3639

The <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> object also supports its own <tt class="docutils literal">create()</tt> method:

3640

<div class="highlight-python+sql"><div class="highlight"><pre>i = Index('someindex', mytable.c.col5)

3641

<a href='#' class='sql_link'>sql</a>i.create(engine)

3642

<div class='popup_sql'>CREATE INDEX someindex ON mytable (col5)</div></pre></div>

3643

</div>

3644

3645

<h4>Functional Indexes<a class="headerlink" href="#functional-indexes" title="Permalink to this headline">¶</a></h4>

3646

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> supports SQL and function expressions, as supported by the

3647

target backend. To create an index against a column using a descending

3648

value, the <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">ColumnElement.desc()</tt></a> modifier may be used:

3649

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

3650

3651

Index('someindex', mytable.c.somecol.desc())</pre></div>

3652

</div>

3653

Or with a backend that supports functional indexes such as Postgresql,

3654

a “case insensitive” index can be created using the <tt class="docutils literal">lower()</tt> function:

3655

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import func, Index

3656

3657

Index('someindex', func.lower(mytable.c.somecol))</pre></div>

3658

</div>

3659

3660

New in version 0.8: <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> supports SQL expressions and functions

3661

as well as plain columns.

3662

</div>

3663

</div>

3664

3665

<h3>Index API<a class="headerlink" href="#index-api" title="Permalink to this headline">¶</a></h3>

3666

3667

3668

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">Index</tt><big>(</big>name, *expressions, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Index" title="Permalink to this definition">¶</a></dt>

3669

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema.ColumnCollectionMixin</tt>, <a class="reference internal" href="#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>

3670

A table-level INDEX.

3671

Defines a composite (one or more column) INDEX. For a no-frills, single

3672

column index, adding <tt class="docutils literal">index=True</tt> to the <tt class="docutils literal">Column</tt> definition is

3673

a shorthand equivalent for an unnamed, single column <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a>.

3674

3675

See also

3676

<a class="reference internal" href="#schema-indexes">Indexes</a> - General information on <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a>.

3677

<a class="reference internal" href="../dialects/postgresql.html#postgresql-indexes">Postgresql-Specific Index Options</a> - PostgreSQL-specific options available for the

3678

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct.

3679

<a class="reference internal" href="../dialects/mysql.html#mysql-indexes">MySQL Specific Index Options</a> - MySQL-specific options available for the

3680

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct.

3681

<a class="reference internal" href="../dialects/mssql.html#mssql-indexes">MSSQL-Specific Index Options</a> - MSSQL-specific options available for the

3682

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct.

3683

</div>

3684

3685

3686

<tt class="descname">__init__</tt><big>(</big>name, *expressions, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Index.__init__" title="Permalink to this definition">¶</a></dt>

3687

<dd>Construct an index object.

3688

3689

3690

3691

3692

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

3693

<li>name – The name of the index</li>

3694

<li>*expressions – Column expressions to include in the index. The expressions

3695

are normally instances of <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, but may also

3696

be arbitrary SQL expressions which ultmately refer to a

3697

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

3698

3699

New in version 0.8: <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> supports SQL expressions as

3700

well as plain columns.

3701

</li>

3702

<li>unique – Defaults to False: create a unique index.</li>

3703

<li>**kw – Other keyword arguments may be interpreted by specific dialects.</li>

3704

</ul>

3705

</td>

3706

</tr>

3707

</tbody>

3708

</table>

3709

</dd></dl>

3710

3711

3712

3713

3714

<dd>Return the connectable associated with this Index.

3715

</dd></dl>

3716

3717

3718

3719

<tt class="descname">create</tt><big>(</big>bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Index.create" title="Permalink to this definition">¶</a></dt>

3720

<dd>Issue a <tt class="docutils literal">CREATE</tt> statement for this

3721

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

3722

for connectivity.

3723

See also <a class="reference internal" href="#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-meth docutils literal">MetaData.create_all()</tt></a>.

3724

</dd></dl>

3725

3726

3727

3728

3729

<dd>Issue a <tt class="docutils literal">DROP</tt> statement for this

3730

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

3731

for connectivity.

3732

See also <a class="reference internal" href="#sqlalchemy.schema.MetaData.drop_all" title="sqlalchemy.schema.MetaData.drop_all"><tt class="xref py py-meth docutils literal">MetaData.drop_all()</tt></a>.

3733

</dd></dl>

3734

3735

</dd></dl>

3736

3737

</div>

3738

</div>

3739

3740

<h2>Customizing DDL<a class="headerlink" href="#customizing-ddl" title="Permalink to this headline">¶</a></h2>

3741

In the preceding sections we’ve discussed a variety of schema constructs

3742

including <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

3743

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

3744

<a class="reference internal" href="#sqlalchemy.schema.CheckConstraint" title="sqlalchemy.schema.CheckConstraint"><tt class="xref py py-class docutils literal">CheckConstraint</tt></a>, and

3745

<a class="reference internal" href="#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal">Sequence</tt></a>. Throughout, we’ve relied upon the

3746

<tt class="docutils literal">create()</tt> and <a class="reference internal" href="#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal">create_all()</tt></a> methods of

3747

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> and <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> in

3748

order to issue data definition language (DDL) for all constructs. When issued,

3749

a pre-determined order of operations is invoked, and DDL to create each table

3750

is created unconditionally including all constraints and other objects

3751

associated with it. For more complex scenarios where database-specific DDL is

3752

required, SQLAlchemy offers two techniques which can be used to add any DDL

3753

based on any condition, either accompanying the standard generation of tables

3754

or by itself.

3755

3756

<h3>Controlling DDL Sequences<a class="headerlink" href="#controlling-ddl-sequences" title="Permalink to this headline">¶</a></h3>

3757

The <tt class="docutils literal">sqlalchemy.schema</tt> package contains SQL expression constructs that

3758

provide DDL expressions. For example, to produce a <tt class="docutils literal">CREATE TABLE</tt> statement:

3759

<div class="highlight-python+sql"><div class="highlight"><pre>from sqlalchemy.schema import CreateTable

3760

<a href='#' class='sql_link'>sql</a>engine.execute(CreateTable(mytable))

3761

<div class='popup_sql'>CREATE TABLE mytable (

3762

col1 INTEGER,

3763

col2 INTEGER,

3764

col3 INTEGER,

3765

col4 INTEGER,

3766

col5 INTEGER,

3767

col6 INTEGER

3768

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

3769

</div>

3770

Above, the <a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><tt class="xref py py-class docutils literal">CreateTable</tt></a> construct works like any

3771

other expression construct (such as <tt class="docutils literal">select()</tt>, <tt class="docutils literal">table.insert()</tt>, etc.). A

3772

full reference of available constructs is in <a class="reference internal" href="#schema-api-ddl">DDL Expression Constructs API</a>.

3773

The DDL constructs all extend a common base class which provides the

3774

capability to be associated with an individual

3775

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> or <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>

3776

object, to be invoked upon create/drop events. Consider the example of a table

3777

which contains a CHECK constraint:

3778

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

3779

Column('user_id', Integer, primary_key=True),

3780

Column('user_name', String(40), nullable=False),

3781

CheckConstraint('length(user_name) >= 8',name="cst_user_name_length")

3782

)

3783

3784

<a href='#' class='sql_link'>sql</a>users.create(engine)

3785

<div class='popup_sql'>CREATE TABLE users (

3786

user_id SERIAL NOT NULL,

3787

user_name VARCHAR(40) NOT NULL,

3788

PRIMARY KEY (user_id),

3789

CONSTRAINT cst_user_name_length CHECK (length(user_name) >= 8)

3790

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

3791

</div>

3792

The above table contains a column “user_name” which is subject to a CHECK

3793

constraint that validates that the length of the string is at least eight

3794

characters. When a <tt class="docutils literal">create()</tt> is issued for this table, DDL for the

3795

<a class="reference internal" href="#sqlalchemy.schema.CheckConstraint" title="sqlalchemy.schema.CheckConstraint"><tt class="xref py py-class docutils literal">CheckConstraint</tt></a> will also be issued inline within

3796

the table definition.

3797

The <a class="reference internal" href="#sqlalchemy.schema.CheckConstraint" title="sqlalchemy.schema.CheckConstraint"><tt class="xref py py-class docutils literal">CheckConstraint</tt></a> construct can also be

3798

constructed externally and associated with the

3799

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> afterwards:

3800

<div class="highlight-python"><div class="highlight"><pre>constraint = CheckConstraint('length(user_name) >= 8',name="cst_user_name_length")

3801

users.append_constraint(constraint)</pre></div>

3802

</div>

3803

So far, the effect is the same. However, if we create DDL elements

3804

corresponding to the creation and removal of this constraint, and associate

3805

them with the <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> as events, these new events

3806

will take over the job of issuing DDL for the constraint. Additionally, the

3807

constraint will be added via ALTER:

3808

<div class="highlight-python+sql"><div class="highlight"><pre>from sqlalchemy import event

3809

3810

event.listen(

3811

users,

3812

"after_create",

3813

AddConstraint(constraint)

3814

)

3815

event.listen(

3816

users,

3817

"before_drop",

3818

DropConstraint(constraint)

3819

)

3820

3821

<a href='#' class='sql_link'>sql</a>users.create(engine)

3822

<div class='popup_sql'>CREATE TABLE users (

3823

user_id SERIAL NOT NULL,

3824

user_name VARCHAR(40) NOT NULL,

3825

PRIMARY KEY (user_id)

3826

)

3827

3828

ALTER TABLE users ADD CONSTRAINT cst_user_name_length CHECK (length(user_name) >= 8)</div>

3829

<a href='#' class='sql_link'>sql</a>users.drop(engine)

3830

<div class='popup_sql'>ALTER TABLE users DROP CONSTRAINT cst_user_name_length

3831

DROP TABLE user</div></pre></div>

3832

</div>

3833

The real usefulness of the above becomes clearer once we illustrate the

3834

<a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if" title="sqlalchemy.schema.DDLElement.execute_if"><tt class="xref py py-meth docutils literal">DDLElement.execute_if()</tt></a> method. This method returns a modified form of

3835

the DDL callable which will filter on criteria before responding to a

3836

received event. It accepts a parameter <tt class="docutils literal">dialect</tt>, which is the string

3837

name of a dialect or a tuple of such, which will limit the execution of the

3838

item to just those dialects. It also accepts a <tt class="docutils literal">callable_</tt> parameter which

3839

may reference a Python callable which will be invoked upon event reception,

3840

returning <tt class="docutils literal">True</tt> or <tt class="docutils literal">False</tt> indicating if the event should proceed.

3841

If our <a class="reference internal" href="#sqlalchemy.schema.CheckConstraint" title="sqlalchemy.schema.CheckConstraint"><tt class="xref py py-class docutils literal">CheckConstraint</tt></a> was only supported by

3842

Postgresql and not other databases, we could limit its usage to just that dialect:

3843

<div class="highlight-python"><div class="highlight"><pre>event.listen(

3844

users,

3845

'after_create',

3846

AddConstraint(constraint).execute_if(dialect='postgresql')

3847

)

3848

event.listen(

3849

users,

3850

'before_drop',

3851

DropConstraint(constraint).execute_if(dialect='postgresql')

3852

)</pre></div>

3853

</div>

3854

Or to any set of dialects:

3855

<div class="highlight-python"><div class="highlight"><pre>event.listen(

3856

users,

3857

"after_create",

3858

AddConstraint(constraint).execute_if(dialect=('postgresql', 'mysql'))

3859

)

3860

event.listen(

3861

users,

3862

"before_drop",

3863

DropConstraint(constraint).execute_if(dialect=('postgresql', 'mysql'))

3864

)</pre></div>

3865

</div>

3866

When using a callable, the callable is passed the ddl element, the

3867

<a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> or <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>

3868

object whose “create” or “drop” event is in progress, and the

3869

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a> object being used for the

3870

operation, as well as additional information as keyword arguments. The

3871

callable can perform checks, such as whether or not a given item already

3872

exists. Below we define <tt class="docutils literal">should_create()</tt> and <tt class="docutils literal">should_drop()</tt> callables

3873

that check for the presence of our named constraint:

3874

<div class="highlight-python+sql"><div class="highlight"><pre>def should_create(ddl, target, connection, **kw):

3875

row = connection.execute("select conname from pg_constraint where conname='%s'" % ddl.element.name).scalar()

3876

return not bool(row)

3877

3878

def should_drop(ddl, target, connection, **kw):

3879

return not should_create(ddl, target, connection, **kw)

3880

3881

event.listen(

3882

users,

3883

"after_create",

3884

AddConstraint(constraint).execute_if(callable_=should_create)

3885

)

3886

event.listen(

3887

users,

3888

"before_drop",

3889

DropConstraint(constraint).execute_if(callable_=should_drop)

3890

)

3891

3892

<a href='#' class='sql_link'>sql</a>users.create(engine)

3893

<div class='popup_sql'>CREATE TABLE users (

3894

user_id SERIAL NOT NULL,

3895

user_name VARCHAR(40) NOT NULL,

3896

PRIMARY KEY (user_id)

3897

)

3898

3899

select conname from pg_constraint where conname='cst_user_name_length'

3900

ALTER TABLE users ADD CONSTRAINT cst_user_name_length CHECK (length(user_name) >= 8)</div>

3901

<a href='#' class='sql_link'>sql</a>users.drop(engine)

3902

<div class='popup_sql'>select conname from pg_constraint where conname='cst_user_name_length'

3903

ALTER TABLE users DROP CONSTRAINT cst_user_name_length

3904

DROP TABLE user</div></pre></div>

3905

</div>

3906

</div>

3907

3908

<h3>Custom DDL<a class="headerlink" href="#custom-ddl" title="Permalink to this headline">¶</a></h3>

3909

Custom DDL phrases are most easily achieved using the

3910

<a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><tt class="xref py py-class docutils literal">DDL</tt></a> construct. This construct works like all the

3911

other DDL elements except it accepts a string which is the text to be emitted:

3912

<div class="highlight-python+sql"><div class="highlight"><pre>event.listen(

3913

metadata,

3914

"after_create",

3915

DDL("ALTER TABLE users ADD CONSTRAINT "

3916

"cst_user_name_length "

3917

" CHECK (length(user_name) >= 8)")

3918

)</pre></div>

3919

</div>

3920

A more comprehensive method of creating libraries of DDL constructs is to use

3921

custom compilation - see <a class="reference internal" href="compiler.html">Custom SQL Constructs and Compilation Extension</a> for

3922

details.

3923

</div>

3924

3925

<h3>DDL Expression Constructs API<a class="headerlink" href="#ddl-expression-constructs-api" title="Permalink to this headline">¶</a></h3>

3926

3927

3928

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DDLElement</tt><a class="headerlink" href="#sqlalchemy.schema.DDLElement" title="Permalink to this definition">¶</a></dt>

3929

<dd>Bases: <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><tt class="xref py py-class docutils literal">sqlalchemy.sql.expression.Executable</tt></a>, <tt class="xref py py-class docutils literal">sqlalchemy.schema._DDLCompiles</tt>

3930

Base class for DDL expression constructs.

3931

This class is the base for the general purpose <a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><tt class="xref py py-class docutils literal">DDL</tt></a> class,

3932

as well as the various create/drop clause constructs such as

3933

<a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><tt class="xref py py-class docutils literal">CreateTable</tt></a>, <a class="reference internal" href="#sqlalchemy.schema.DropTable" title="sqlalchemy.schema.DropTable"><tt class="xref py py-class docutils literal">DropTable</tt></a>, <a class="reference internal" href="#sqlalchemy.schema.AddConstraint" title="sqlalchemy.schema.AddConstraint"><tt class="xref py py-class docutils literal">AddConstraint</tt></a>,

3934

etc.

3935

<a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><tt class="xref py py-class docutils literal">DDLElement</tt></a> integrates closely with SQLAlchemy events,

3936

introduced in <a class="reference internal" href="event.html">Events</a>. An instance of one is

3937

itself an event receiving callable:

3938

<div class="highlight-python"><div class="highlight"><pre>event.listen(

3939

users,

3940

'after_create',

3941

AddConstraint(constraint).execute_if(dialect='postgresql')

3942

)</pre></div>

3943

</div>

3944

See also:

3945

3946

3947

<a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><tt class="xref py py-class docutils literal">DDLEvents</tt></a>

3948

<a class="reference internal" href="event.html">Events</a>

3949

<a class="reference internal" href="#schema-ddl-sequences">Controlling DDL Sequences</a>

3950

</div></blockquote>

3951

3952

3953

<tt class="descname">__call__</tt><big>(</big>target, bind, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DDLElement.__call__" title="Permalink to this definition">¶</a></dt>

3954

<dd>Execute the DDL as a ddl_listener.

3955

</dd></dl>

3956

3957

3958

3959

<tt class="descname">against</tt><big>(</big>target<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DDLElement.against" title="Permalink to this definition">¶</a></dt>

3960

<dd>Return a copy of this DDL against a specific schema item.

3961

</dd></dl>

3962

3963

3964

3965

3966

3967

3968

3969

3970

<tt class="descname">callable_</tt> = None<a class="headerlink" href="#sqlalchemy.schema.DDLElement.callable_" title="Permalink to this definition">¶</a></dt>

3971

3972

3973

3974

3975

<tt class="descname">dialect</tt> = None<a class="headerlink" href="#sqlalchemy.schema.DDLElement.dialect" title="Permalink to this definition">¶</a></dt>

3976

3977

3978

3979

3980

<tt class="descname">execute</tt><big>(</big>bind=None, target=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DDLElement.execute" title="Permalink to this definition">¶</a></dt>

3981

<dd>Execute this DDL immediately.

3982

Executes the DDL statement in isolation using the supplied

3983

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a> or

3984

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a> assigned to the <tt class="docutils literal">.bind</tt>

3985

property, if not supplied. If the DDL has a conditional <tt class="docutils literal">on</tt>

3986

criteria, it will be invoked with None as the event.

3987

3988

3989

3990

3991

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

3992

<li>bind – Optional, an <tt class="docutils literal">Engine</tt> or <tt class="docutils literal">Connection</tt>. If not supplied, a valid

3993

<a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a> must be present in the

3994

<tt class="docutils literal">.bind</tt> property.</li>

3995

<li>target – Optional, defaults to None. The target SchemaItem for the

3996

execute call. Will be passed to the <tt class="docutils literal">on</tt> callable if any,

3997

and may also provide string expansion data for the

3998

statement. See <tt class="docutils literal">execute_at</tt> for more information.</li>

3999

</ul>

4000

</td>

4001

</tr>

4002

</tbody>

4003

</table>

4004

</dd></dl>

4005

4006

4007

4008

<tt class="descname">execute_at</tt><big>(</big>event_name, target<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DDLElement.execute_at" title="Permalink to this definition">¶</a></dt>

4009

<dd>Link execution of this DDL to the DDL lifecycle of a SchemaItem.

4010

4011

Deprecated since version 0.7: See <a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><tt class="xref py py-class docutils literal">DDLEvents</tt></a>, as well as <a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if" title="sqlalchemy.schema.DDLElement.execute_if"><tt class="xref py py-meth docutils literal">DDLElement.execute_if()</tt></a>.

4012

Links this <tt class="docutils literal">DDLElement</tt> to a <tt class="docutils literal">Table</tt> or <tt class="docutils literal">MetaData</tt> instance,

4013

executing it when that schema item is created or dropped. The DDL

4014

statement will be executed using the same Connection and transactional

4015

context as the Table create/drop itself. The <tt class="docutils literal">.bind</tt> property of

4016

this statement is ignored.

4017

4018

4019

4020

4021

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

4022

<li>event – One of the events defined in the schema item’s <tt class="docutils literal">.ddl_events</tt>;

4023

e.g. ‘before-create’, ‘after-create’, ‘before-drop’ or ‘after-drop’</li>

4024

<li>target – The Table or MetaData instance for which this DDLElement will

4025

be associated with.</li>

4026

</ul>

4027

</td>

4028

</tr>

4029

</tbody>

4030

</table>

4031

A DDLElement instance can be linked to any number of schema items.

4032

<tt class="docutils literal">execute_at</tt> builds on the <tt class="docutils literal">append_ddl_listener</tt> interface of

4033

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

4034

Caveat: Creating or dropping a Table in isolation will also trigger

4035

any DDL set to <tt class="docutils literal">execute_at</tt> that Table’s MetaData. This may change

4036

in a future release.

4037

</dd></dl>

4038

4039

4040

4041

<tt class="descname">execute_if</tt><big>(</big>dialect=None, callable_=None, state=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DDLElement.execute_if" title="Permalink to this definition">¶</a></dt>

4042

<dd>Return a callable that will execute this

4043

DDLElement conditionally.

4044

Used to provide a wrapper for event listening:

4045

<div class="highlight-python"><div class="highlight"><pre>event.listen(

4046

metadata,

4047

'before_create',

4048

DDL("my_ddl").execute_if(dialect='postgresql')

4049

)</pre></div>

4050

</div>

4051

4052

4053

4054

4055

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

4056

<li>dialect – May be a string, tuple or a callable

4057

predicate. If a string, it will be compared to the name of the

4058

executing database dialect:

4059

<div class="highlight-python"><div class="highlight"><pre>DDL('something').execute_if(dialect='postgresql')</pre></div>

4060

</div>

4061

If a tuple, specifies multiple dialect names:

4062

<div class="highlight-python"><div class="highlight"><pre>DDL('something').execute_if(dialect=('postgresql', 'mysql'))</pre></div>

4063

</div>

4064

</li>

4065

<li>callable – A callable, which will be invoked with

4066

four positional arguments as well as optional keyword

4067

arguments:

4068

4069

4070

4071

4072

4073

<tr class="field-odd field"><th class="field-name">ddl:</th><td class="field-body">This DDL element.</td>

4074

</tr>

4075

<tr class="field-even field"><th class="field-name">target:</th><td class="field-body">The <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> or <a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> object which is the

4076

target of this event. May be None if the DDL is executed

4077

explicitly.</td>

4078

</tr>

4079

<tr class="field-odd field"><th class="field-name">bind:</th><td class="field-body">The <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a> being used for DDL execution</td>

4080

</tr>

4081

<tr class="field-even field"><th class="field-name">tables:</th><td class="field-body">Optional keyword argument - a list of Table objects which are to

4082

be created/ dropped within a MetaData.create_all() or drop_all()

4083

method call.</td>

4084

</tr>

4085

<tr class="field-odd field"><th class="field-name">state:</th><td class="field-body">Optional keyword argument - will be the <tt class="docutils literal">state</tt> argument

4086

passed to this function.</td>

4087

</tr>

4088

<tr class="field-even field"><th class="field-name">checkfirst:</th><td class="field-body">Keyword argument, will be True if the ‘checkfirst’ flag was

4089

set during the call to <tt class="docutils literal">create()</tt>, <tt class="docutils literal">create_all()</tt>,

4090

4091

</tr>

4092

</tbody>

4093

</table>

4094

</div></blockquote>

4095

If the callable returns a true value, the DDL statement will be

4096

executed.

4097

</li>

4098

<li>state – any value which will be passed to the <a href="#id2">callable_</a>

4099

as the <tt class="docutils literal">state</tt> keyword argument.</li>

4100

</ul>

4101

</td>

4102

</tr>

4103

</tbody>

4104

</table>

4105

See also:

4106

4107

<div><a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><tt class="xref py py-class docutils literal">DDLEvents</tt></a>

4108

<a class="reference internal" href="event.html">Events</a>

4109

</div></blockquote>

4110

</dd></dl>

4111

4112

4113

4114

4115

4116

4117

4118

4119

<tt class="descname">target</tt> = None<a class="headerlink" href="#sqlalchemy.schema.DDLElement.target" title="Permalink to this definition">¶</a></dt>

4120

4121

4122

</dd></dl>

4123

4124

4125

4126

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DDL</tt><big>(</big>statement, on=None, context=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DDL" title="Permalink to this definition">¶</a></dt>

4127

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><tt class="xref py py-class docutils literal">sqlalchemy.schema.DDLElement</tt></a>

4128

A literal DDL statement.

4129

Specifies literal SQL DDL to be executed by the database. DDL objects

4130

function as DDL event listeners, and can be subscribed to those events

4131

listed in <a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><tt class="xref py py-class docutils literal">DDLEvents</tt></a>, using either <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> or

4132

<a class="reference internal" href="#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> objects as targets. Basic templating support allows

4133

a single DDL instance to handle repetitive tasks for multiple tables.

4134

Examples:

4135

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import event, DDL

4136

4137

tbl = Table('users', metadata, Column('uid', Integer))

4138

event.listen(tbl, 'before_create', DDL('DROP TRIGGER users_trigger'))

4139

4140

spow = DDL('ALTER TABLE %(table)s SET secretpowers TRUE')

4141

event.listen(tbl, 'after_create', spow.execute_if(dialect='somedb'))

4142

4143

drop_spow = DDL('ALTER TABLE users SET secretpowers FALSE')

4144

connection.execute(drop_spow)</pre></div>

4145

</div>

4146

When operating on Table events, the following <tt class="docutils literal">statement</tt>

4147

string substitions are available:

4148

<div class="highlight-python"><pre>%(table)s - the Table name, with any required quoting applied

4149

%(schema)s - the schema name, with any required quoting applied

4150

%(fullname)s - the Table name including schema, quoted if needed</pre>

4151

</div>

4152

The DDL’s “context”, if any, will be combined with the standard

4153

substutions noted above. Keys present in the context will override

4154

the standard substitutions.

4155

4156

4157

<tt class="descname">__init__</tt><big>(</big>statement, on=None, context=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DDL.__init__" title="Permalink to this definition">¶</a></dt>

4158

<dd>Create a DDL statement.

4159

4160

4161

4162

4163

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

4164

<li>statement – A string or unicode string to be executed. Statements will be

4165

processed with Python’s string formatting operator. See the

4166

<tt class="docutils literal">context</tt> argument and the <tt class="docutils literal">execute_at</tt> method.

4167

A literal ‘%’ in a statement must be escaped as ‘%%’.

4168

SQL bind parameters are not available in DDL statements.

4169

</li>

4170

<li>on – Deprecated. See <a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if" title="sqlalchemy.schema.DDLElement.execute_if"><tt class="xref py py-meth docutils literal">DDLElement.execute_if()</tt></a>.

4171

Optional filtering criteria. May be a string, tuple or a callable

4172

predicate. If a string, it will be compared to the name of the

4173

executing database dialect:

4174

<div class="highlight-python"><div class="highlight"><pre>DDL('something', on='postgresql')</pre></div>

4175

</div>

4176

If a tuple, specifies multiple dialect names:

4177

<div class="highlight-python"><div class="highlight"><pre>DDL('something', on=('postgresql', 'mysql'))</pre></div>

4178

</div>

4179

If a callable, it will be invoked with four positional arguments

4180

as well as optional keyword arguments:

4181

4182

4183

4184

4185

4186

<tr class="field-odd field"><th class="field-name">ddl:</th><td class="field-body">This DDL element.</td>

4187

</tr>

4188

<tr class="field-even field"><th class="field-name">event:</th><td class="field-body">The name of the event that has triggered this DDL, such as

4189

‘after-create’ Will be None if the DDL is executed explicitly.</td>

4190

</tr>

4191

<tr class="field-odd field"><th class="field-name">target:</th><td class="field-body">The <tt class="docutils literal">Table</tt> or <tt class="docutils literal">MetaData</tt> object which is the target of

4192

this event. May be None if the DDL is executed explicitly.</td>

4193

</tr>

4194

<tr class="field-even field"><th class="field-name">connection:</th><td class="field-body">The <tt class="docutils literal">Connection</tt> being used for DDL execution</td>

4195

</tr>

4196

<tr class="field-odd field"><th class="field-name">tables:</th><td class="field-body">Optional keyword argument - a list of Table objects which are to

4197

be created/ dropped within a MetaData.create_all() or drop_all()

4198

method call.</td>

4199

</tr>

4200

</tbody>

4201

</table>

4202

</div></blockquote>

4203

If the callable returns a true value, the DDL statement will be

4204

executed.

4205

</li>

4206

<li>context – Optional dictionary, defaults to None. These values will be

4207

available for use in string substitutions on the DDL statement.</li>

4208

<li>bind – Optional. A <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a>, used by

4209

default when <tt class="docutils literal">execute()</tt> is invoked without a bind argument.</li>

4210

</ul>

4211

</td>

4212

</tr>

4213

</tbody>

4214

</table>

4215

See also:

4216

4217

<div><a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><tt class="xref py py-class docutils literal">DDLEvents</tt></a>

4218

<tt class="xref py py-mod docutils literal">sqlalchemy.event</tt></div></blockquote>

4219

</dd></dl>

4220

4221

</dd></dl>

4222

4223

4224

4225

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">CreateTable</tt><big>(</big>element, on=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CreateTable" title="Permalink to this definition">¶</a></dt>

4226

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4227

Represent a CREATE TABLE statement.

4228

4229

4230

<tt class="descname">__init__</tt><big>(</big>element, on=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CreateTable.__init__" title="Permalink to this definition">¶</a></dt>

4231

<dd>Create a <a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><tt class="xref py py-class docutils literal">CreateTable</tt></a> construct.

4232

4233

4234

4235

4236

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

4237

<li>element – a <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> that’s the subject

4238

of the CREATE</li>

4239

<li>on – See the description for ‘on’ in <a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><tt class="xref py py-class docutils literal">DDL</tt></a>.</li>

4240

<li>bind – See the description for ‘bind’ in <a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><tt class="xref py py-class docutils literal">DDL</tt></a>.</li>

4241

</ul>

4242

</td>

4243

</tr>

4244

</tbody>

4245

</table>

4246

</dd></dl>

4247

4248

</dd></dl>

4249

4250

4251

4252

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DropTable</tt><big>(</big>element, on=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DropTable" title="Permalink to this definition">¶</a></dt>

4253

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4254

Represent a DROP TABLE statement.

4255

</dd></dl>

4256

4257

4258

4259

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">CreateColumn</tt><big>(</big>element<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CreateColumn" title="Permalink to this definition">¶</a></dt>

4260

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._DDLCompiles</tt>

4261

Represent a <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> as rendered in a CREATE TABLE statement,

4262

via the <a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><tt class="xref py py-class docutils literal">CreateTable</tt></a> construct.

4263

This is provided to support custom column DDL within the generation

4264

of CREATE TABLE statements, by using the

4265

compiler extension documented in <a class="reference internal" href="compiler.html">Custom SQL Constructs and Compilation Extension</a>

4266

to extend <a class="reference internal" href="#sqlalchemy.schema.CreateColumn" title="sqlalchemy.schema.CreateColumn"><tt class="xref py py-class docutils literal">CreateColumn</tt></a>.

4267

Typical integration is to examine the incoming <a class="reference internal" href="#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>

4268

object, and to redirect compilation if a particular flag or condition

4269

is found:

4270

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

4271

from sqlalchemy.ext.compiler import compiles

4272

4273

@compiles(schema.CreateColumn)

4274

def compile(element, compiler, **kw):

4275

column = element.element

4276

4277

if "special" not in column.info:

4278

return compiler.visit_create_column(element, **kw)

4279

4280

text = "%s SPECIAL DIRECTIVE %s" % (

4281

column.name,

4282

compiler.type_compiler.process(column.type)

4283

)

4284

default = compiler.get_column_default_string(column)

4285

if default is not None:

4286

text += " DEFAULT " + default

4287

4288

if not column.nullable:

4289

text += " NOT NULL"

4290

4291

if column.constraints:

4292

text += " ".join(

4293

compiler.process(const)

4294

for const in column.constraints)

4295

return text</pre></div>

4296

</div>

4297

The above construct can be applied to a <a class="reference internal" href="#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> as follows:

4298

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import Table, Metadata, Column, Integer, String

4299

from sqlalchemy import schema

4300

4301

metadata = MetaData()

4302

4303

table = Table('mytable', MetaData(),

4304

Column('x', Integer, info={"special":True}, primary_key=True),

4305

Column('y', String(50)),

4306

Column('z', String(20), info={"special":True})

4307

)

4308

4309

metadata.create_all(conn)</pre></div>

4310

</div>

4311

Above, the directives we’ve added to the <a class="reference internal" href="#sqlalchemy.schema.Column.info" title="sqlalchemy.schema.Column.info"><tt class="xref py py-attr docutils literal">Column.info</tt></a> collection

4312

will be detected by our custom compilation scheme:

4313

<div class="highlight-python"><pre>CREATE TABLE mytable (

4314

x SPECIAL DIRECTIVE INTEGER NOT NULL,

4315

y VARCHAR(50),

4316

z SPECIAL DIRECTIVE VARCHAR(20),

4317

PRIMARY KEY (x)

4318

)</pre>

4319

</div>

4320

4321

New in version 0.8: The <a class="reference internal" href="#sqlalchemy.schema.CreateColumn" title="sqlalchemy.schema.CreateColumn"><tt class="xref py py-class docutils literal">CreateColumn</tt></a> construct was added

4322

to support custom column creation styles.

4323

</dd></dl>

4324

4325

4326

4327

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">CreateSequence</tt><big>(</big>element, on=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CreateSequence" title="Permalink to this definition">¶</a></dt>

4328

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4329

Represent a CREATE SEQUENCE statement.

4330

</dd></dl>

4331

4332

4333

4334

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DropSequence</tt><big>(</big>element, on=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DropSequence" title="Permalink to this definition">¶</a></dt>

4335

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4336

Represent a DROP SEQUENCE statement.

4337

</dd></dl>

4338

4339

4340

4341

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">CreateIndex</tt><big>(</big>element, on=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CreateIndex" title="Permalink to this definition">¶</a></dt>

4342

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4343

Represent a CREATE INDEX statement.

4344

</dd></dl>

4345

4346

4347

4348

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DropIndex</tt><big>(</big>element, on=None, bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DropIndex" title="Permalink to this definition">¶</a></dt>

4349

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4350

Represent a DROP INDEX statement.

4351

</dd></dl>

4352

4353

4354

4355

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">AddConstraint</tt><big>(</big>element, *args, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.AddConstraint" title="Permalink to this definition">¶</a></dt>

4356

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4357

Represent an ALTER TABLE ADD CONSTRAINT statement.

4358

</dd></dl>

4359

4360

4361

4362

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DropConstraint</tt><big>(</big>element, cascade=False, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DropConstraint" title="Permalink to this definition">¶</a></dt>

4363

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4364

Represent an ALTER TABLE DROP CONSTRAINT statement.

4365

</dd></dl>

4366

4367

4368

4369

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">CreateSchema</tt><big>(</big>name, quote=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CreateSchema" title="Permalink to this definition">¶</a></dt>

4370

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4371

Represent a CREATE SCHEMA statement.

4372

4373

New in version 0.7.4.

4374

The argument here is the string name of the schema.

4375

4376

4377

<tt class="descname">__init__</tt><big>(</big>name, quote=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CreateSchema.__init__" title="Permalink to this definition">¶</a></dt>

4378

<dd>Create a new <a class="reference internal" href="#sqlalchemy.schema.CreateSchema" title="sqlalchemy.schema.CreateSchema"><tt class="xref py py-class docutils literal">CreateSchema</tt></a> construct.

4379

</dd></dl>

4380

4381

</dd></dl>

4382

4383

4384

4385

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">DropSchema</tt><big>(</big>name, quote=None, cascade=False, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DropSchema" title="Permalink to this definition">¶</a></dt>

4386

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema._CreateDropBase</tt>

4387

Represent a DROP SCHEMA statement.

4388

The argument here is the string name of the schema.

4389

4390

New in version 0.7.4.

4391

4392

4393

<tt class="descname">__init__</tt><big>(</big>name, quote=None, cascade=False, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.DropSchema.__init__" title="Permalink to this definition">¶</a></dt>

4394

<dd>Create a new <a class="reference internal" href="#sqlalchemy.schema.DropSchema" title="sqlalchemy.schema.DropSchema"><tt class="xref py py-class docutils literal">DropSchema</tt></a> construct.

4395

</dd></dl>

4396

4397

</dd></dl>

4398

4399

</div>

169

170

<ul>

171

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

172

<li class="toctree-l1"><a class="reference internal" href="reflection.html">Reflecting Database Objects</a></li>

173

<li class="toctree-l1"><a class="reference internal" href="defaults.html">Column Insert/Update Defaults</a></li>

174

<li class="toctree-l1"><a class="reference internal" href="constraints.html">Defining Constraints and Indexes</a></li>

175

<li class="toctree-l1"><a class="reference internal" href="ddl.html">Customizing DDL</a></li>

176

</ul>

4400

177

</div>

4401

178

</div>

4402

179