from sqlalchemy.ext import baked
from sqlalchemy import bindparam

bakery = baked.bakery()

def search_for_user(session, username, email=None):

    baked_query = bakery(lambda session: session.query(User))
    baked_query += lambda q: q.filter(User.name == bindparam('username'))

    baked_query += lambda q: q.order_by(User.id)

    if email:
        baked_query += lambda q: q.filter(User.email == bindparam('email'))

    result = baked_query(session).params(username=username, email=email).all()

    return result

class HasFooBar(object):
    foobar = Column(Integer)

    @declared_attr
    def foobar_prop(cls):
        return column_property('foobar: ' + cls.foobar)

class SomeClass(HasFooBar, Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)

class HasFooBar(object):
    @declared_attr
    def foobar(cls):
        return Column(Integer)

    @declared_attr
    def foobar_prop(cls):
        return column_property('foobar: ' + cls.foobar)

class SomeClass(HasFooBar, Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)

class HasIdMixin(object):
    @declared_attr.cascading
    def id(cls):
        if has_inherited_table(cls):
            return Column(ForeignKey('myclass.id'), primary_key=True)
        else:
            return Column(Integer, primary_key=True)

class MyClass(HasIdMixin, Base):
    __tablename__ = 'myclass'
    # ...

class MySubClass(MyClass):
    ""
    # ...

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import (declarative_base, declared_attr,
    AbstractConcreteBase)

Base = declarative_base()

class Something(Base):
    __tablename__ = u'something'
    id = Column(Integer, primary_key=True)


class Abstract(AbstractConcreteBase, Base):
    id = Column(Integer, primary_key=True)

    @declared_attr
    def something_id(cls):
        return Column(ForeignKey(Something.id))

    @declared_attr
    def something(cls):
        return relationship(Something)


class Concrete(Abstract):
    __tablename__ = u'cca'
    __mapper_args__ = {'polymorphic_identity': 'cca', 'concrete': True}

import time
from sqlalchemy import Integer, Column, create_engine, Table
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Foo(Base):
    __table__ = Table(
        'foo', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('a', Integer(), nullable=False),
        Column('b', Integer(), nullable=False),
        Column('c', Integer(), nullable=False),
    )

engine = create_engine(
    'mysql+mysqldb://scott:tiger@localhost/test', echo=True)

sess = Session(engine)

now = time.time()

# avoid using all() so that we don't have the overhead of building
# a large list of full objects in memory
for obj in sess.query(Foo).yield_per(100).limit(1000000):
    pass

print("Total time: %d" % (time.time() - now))

rows = sess.query(Foo.a, Foo.b, Foo.c).all()

-----------------
size=10 num=10000                 # few rows, lots of queries
namedtuple: 3.60302400589         # namedtuple falls over
keyedtuple: 0.255059957504        # KeyedTuple very fast
lw keyed tuple: 0.582715034485    # lw keyed trails right on KeyedTuple
-----------------
size=100 num=1000                 # <--- sweet spot
namedtuple: 0.365247011185
keyedtuple: 0.24896979332
lw keyed tuple: 0.0889317989349   # lw keyed blows both away!
-----------------
size=10000 num=100
namedtuple: 0.572599887848
keyedtuple: 2.54251694679
lw keyed tuple: 0.613876104355
-----------------
size=1000000 num=10               # few queries, lots of rows
namedtuple: 5.79669594765         # namedtuple very fast
keyedtuple: 28.856498003          # KeyedTuple falls over
lw keyed tuple: 6.74346804619     # lw keyed trails right on namedtuple

# reported by heapy, summation of SQLAlchemy objects +
# associated dicts + weakref-related objects with core of Nova imported:

    Before: total count 26477 total bytes 7975712
    After: total count 18181 total bytes 4236456

# reported for the Python module space overall with the
# core of Nova imported:

    Before: Partition of a set of 355558 objects. Total size = 61661760 bytes.
    After: Partition of a set of 346034 objects. Total size = 57808016 bytes.

class SomeObject(Base):
    # ...

    @hybrid_property
    def some_prop(self):
        return self.value + 5


inspect(SomeObject).all_orm_descriptors.some_prop.info['foo'] = 'bar'

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))


A.b = column_property(
        select([func.max(B.id)]).where(B.a_id == A.id).correlate(A)
    )

print(sess.query(A, a1).order_by(a1.b))

SELECT a.id AS a_id, (SELECT max(b.id) AS max_1 FROM b
WHERE b.a_id = a.id) AS anon_1, a_1.id AS a_1_id,
(SELECT max(b.id) AS max_2
FROM b WHERE b.a_id = a_1.id) AS anon_2
FROM a, a AS a_1 ORDER BY anon_1

SELECT a.id AS a_id, (SELECT max(b.id) AS max_1
FROM b WHERE b.a_id = a.id) AS anon_1, a_1.id AS a_1_id,
(SELECT max(b.id) AS max_2
FROM b WHERE b.a_id = a_1.id) AS anon_2
FROM a, a AS a_1 ORDER BY anon_2

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    type = Column(String)

    __mapper_args__ = {'polymorphic_on': type, 'with_polymorphic': '*'}

SELECT a.id AS a_id, a.type AS a_type, (SELECT max(b.id) AS max_1
FROM b WHERE b.a_id = a.id) AS anon_1
FROM a ORDER BY (SELECT max(b.id) AS max_2
FROM b WHERE b.a_id = a.id)

SELECT a.id AS a_id, a.type AS a_type, (SELECT max(b.id) AS max_1
FROM b WHERE b.a_id = a.id) AS anon_1
FROM a ORDER BY anon_1

sel = select([table]).limit(bindparam('mylimit')).offset(bindparam('myoffset'))

>>> result = connection.execute(stmt)
>>> result.fetchone()
(1, 'x')
>>> result.fetchone()
None  # indicates no more rows
>>> result.fetchone()
exception: ResourceClosedError

>>> result = connection.execute(stmt)
>>> result.fetchone()
(1, 'x')
>>> result.fetchone()
None  # indicates no more rows
>>> result.fetchone()
None  # still None
>>> result.fetchall()
[]
>>> result.close()
>>> result.fetchone()
exception: ResourceClosedError  # *now* it raises

metadata = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"}
)

foo = Table('foo', metadata,
    Column('value', Integer),
)

CheckConstraint(foo.c.value > 5)

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value CHECK (value > 5)
)

from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint

m = MetaData()

t = Table('t', m,
    Column('a', Integer),
    Column('b', Integer)
)

uq = UniqueConstraint(t.c.a, t.c.b)  # will auto-attach to Table

assert uq in t.constraints

from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint

m = MetaData()

a = Column('a', Integer)
b = Column('b', Integer)

uq = UniqueConstraint(a, b)

t = Table('t', m, a, b)

assert uq in t.constraints  # constraint auto-attached

from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint

m = MetaData()

a = Column('a', Integer)
b = Column('b', Integer)

uq = UniqueConstraint(a, 'b')

t = Table('t', m, a, b)

# constraint *not* auto-attached, as we do not have tracking
# to locate when a name 'b' becomes available on the table
assert uq not in t.constraints

from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint

m = MetaData()

a = Column('a', Integer)
b = Column('b', Integer)


t = Table('t', m, a, b)

uq = UniqueConstraint(a, 'b')

# constraint auto-attached normally as in older versions
assert uq in t.constraints

from sqlalchemy import Table, Column, MetaData, Integer, select, func

m = MetaData()

t = Table(
    't', m,
    Column('x', Integer),
    Column('y', Integer, default=func.somefunction()))

stmt = select([t.c.x])
print(t.insert().from_select(['x'], stmt))

INSERT INTO t (x, y) SELECT t.x, somefunction() AS somefunction_1
FROM t

from sqlalchemy import Table, Column, MetaData, Text
from sqlalchemy.schema import CreateTable
from sqlalchemy.dialects.postgresql import ARRAY, array
from sqlalchemy.dialects import postgresql

metadata = MetaData()

tbl = Table("derp", metadata,
    Column("arr", ARRAY(Text),
                server_default=array(["foo", "bar", "baz"])),
)

print(CreateTable(tbl).compile(dialect=postgresql.dialect()))

CREATE TABLE derp (
    arr TEXT[] DEFAULT ARRAY['foo', 'bar', 'baz']
)

from sqlalchemy import create_engine, Unicode, select, cast
import random
import warnings

e = create_engine("sqlite://")

# Use the "once" filter (which is also the default for Python
# warnings).  Exactly ten of these warnings will
# be emitted; beyond that, the Python warnings registry will accumulate
# new values as dupes of one of the ten existing.
warnings.filterwarnings("once")

for i in range(1000):
    e.execute(select([cast(
        ('foo_%d' % random.randint(0, 1000000)).encode('ascii'), Unicode)]))

/path/lib/sqlalchemy/sql/sqltypes.py:186: SAWarning: Unicode type received
  non-unicode bind param value 'foo_4852'. (this warning may be
  suppressed after 10 occurrences)

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column('user_name', String(50))

session.query(User).update({'user_name': 'moonbeam'})

session.query(User).update({'name': 'moonbeam'})

session.query(User).update({User.name: 'moonbeam'})

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column('user_name', String(50))

    @hybrid_property
    def fullname(self):
        return self.name

session.query(User).update({'fullname': 'moonbeam'})

session.query(Address).filter(Address.user == User(id=None))

SELECT address.id AS address_id, address.user_id AS address_user_id,
address.email_address AS address_email_address
FROM address WHERE ? = address.user_id
(None,)

SELECT address.id AS address_id, address.user_id AS address_user_id,
address.email_address AS address_email_address
FROM address WHERE address.user_id IS NULL

SAWarning: Got None for value of column user.id; this is unsupported
for a relationship comparison and will not currently produce an
IS comparison (but may in a future release)

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))
    a = relationship("A")

s = Session(autoflush=False)
a1 = A(id=7)
s.add(a1)
s.commit()

a1.id = 10

s.query(B).filter(B.a == a1)

SELECT b.id AS b_id, b.a_id AS b_a_id
FROM b
WHERE ? = b.a_id
(10,)

s.query(B).filter(B.a != a1)

SELECT b.id AS b_id, b.a_id AS b_a_id
FROM b
WHERE b.a_id != ? OR b.a_id IS NULL
(7,)

SELECT b.id, b.a_id
FROM b
WHERE b.a_id != :a_id_1 OR b.a_id IS NULL
{u'a_id_1': symbol('NEVER_SET')}

>>> obj = Foo()

>>> obj.someattr
None

>>> inspect(obj).attrs.someattr.history
History(added=(), unchanged=[None], deleted=())   # 0.9 and below

>>> obj = Foo()
>>> obj.someattr = None
>>> inspect(obj).attrs.someattr.history
History(added=[None], unchanged=(), deleted=())  # all versions

class A(Base):
    __tablename__ = 'table_a'

    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'table_b'

    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('table_a.id'))
    a = relationship(A)

a1 = A(id=1)
a2 = A(id=2)
session.add_all([a1, a2])
session.flush()

b1 = B()
b1.a = a1   # we expect a_id to be '1'; takes precedence in 0.9 and 1.0

b2 = B()
b2.a = None  # we expect a_id to be None; takes precedence only in 1.0

b1.a_id = 2
b2.a_id = 2

session.add_all([b1, b2])
session.commit()

assert b1.a is a1  # passes in both 0.9 and 1.0
assert b2.a is None  # passes in 1.0, in 0.9 it's a2

u1 = sess.query(User).first()
sess.delete(u1)

sess.flush()

assert u1 not in sess
assert inspect(u1).session is sess  # this is normal before commit

sess.expunge(u1)

assert u1 not in sess
assert inspect(u1).session is None  # would fail

q = sess.query(Object).options(lazyload('*')).yield_per(100)

q = sess.query(Object).enable_eagerloads(False).yield_per(100)

q = sess.query(Object).options(
    lazyload('*'), joinedload("some_manytoone")).yield_per(100)

from sqlalchemy import Integer, Column, String, ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship("B")

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

print(s.query(A).join(A.bs).join(A.bs))

SELECT a.id AS a_id
FROM a JOIN b ON a.id = b.a_id

s.query(A).join(A.bs).\
    filter(B.foo == 'bar').\
    reset_joinpoint().join(A.bs, B.cs).filter(C.bar == 'bat')

SAWarning: Pathed join target A.bs has already been joined to; skipping

print(s.query(A).join(B, B.a_id == A.id).join(B, B.a_id == A.id))

SELECT a.id AS a_id
FROM a JOIN b ON b.a_id = a.id JOIN b AS b_1 ON b_1.a_id = a.id

SELECT a.id AS a_id
FROM a JOIN b ON b.a_id = a.id JOIN b ON b.a_id = a.id

from sqlalchemy import Integer, Column, String, ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    type = Column(String)

    __mapper_args__ = {'polymorphic_on': type, 'polymorphic_identity': 'a'}


class ASub1(A):
    __mapper_args__ = {'polymorphic_identity': 'asub1'}


class ASub2(A):
    __mapper_args__ = {'polymorphic_identity': 'asub2'}


class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)

    a_id = Column(Integer, ForeignKey("a.id"))

    a = relationship("A", primaryjoin="B.a_id == A.id", backref='b')

s = Session()

print(s.query(ASub1).join(B, ASub1.b).join(ASub2, B.a))

print(s.query(ASub1).join(B, ASub1.b).join(ASub2, ASub2.id == B.a_id))

SELECT a.id AS a_id, a.type AS a_type
FROM a JOIN b ON b.a_id = a.id JOIN a ON b.a_id = a.id AND a.type IN (:type_1)
WHERE a.type IN (:type_2)

SELECT a.id AS a_id, a.type AS a_type
FROM a JOIN b ON b.a_id = a.id JOIN a AS a_1
ON a_1.id = b.a_id AND a_1.type IN (:type_1)
WHERE a_1.type IN (:type_2)

asub2_alias = aliased(ASub2)

print(s.query(ASub1).join(B, ASub1.b).join(asub2_alias, B.a.of_type(asub2_alias)))

from sqlalchemy.orm import Bundle

class DictBundle(Bundle):
    def create_row_processor(self, query, procs, labels):
        """Override create_row_processor to return values as dictionaries"""
        def proc(row, result):
            return dict(
                        zip(labels, (proc(row, result) for proc in procs))
                    )
        return proc

from sqlalchemy.orm import Bundle

class DictBundle(Bundle):
    def create_row_processor(self, query, procs, labels):
        """Override create_row_processor to return values as dictionaries"""
        def proc(row):
            return dict(
                        zip(labels, (proc(row) for proc in procs))
                    )
        return proc

query(User).options(
    joinedload("orders", innerjoin=False).joinedload("items", innerjoin=True))

FROM users LEFT OUTER JOIN (orders JOIN items ON <onclause>) ON <onclause>

query(User).options(
    joinedload("orders", innerjoin=False).joinedload("items", innerjoin="unnested"))

FROM users LEFT OUTER JOIN orders ON <onclause> LEFT OUTER JOIN items ON <onclause>

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    b = relationship("B", uselist=False)


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

s = Session()
print(s.query(A).options(joinedload(A.b)).limit(5))

SELECT anon_1.a_id AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id
FROM (SELECT a.id AS a_id
FROM a LIMIT :param_1) AS anon_1
LEFT OUTER JOIN b AS b_1 ON anon_1.a_id = b_1.a_id

SELECT a.id AS a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id
FROM a LEFT OUTER JOIN b AS b_1 ON a.id = b_1.a_id
LIMIT :param_1

class Widget(Base):
    __table__ = 'widget_table'

class FooWidget(Widget):
    pass

sess.query(FooWidget).from_self().all()

SELECT
    anon_1.widgets_id AS anon_1_widgets_id,
    anon_1.widgets_type AS anon_1_widgets_type
FROM (SELECT widgets.id AS widgets_id, widgets.type AS widgets_type,
FROM widgets) AS anon_1
WHERE anon_1.widgets_type IN (?)

SELECT
    anon_1.widgets_id AS anon_1_widgets_id,
    anon_1.widgets_type AS anon_1_widgets_type
FROM (SELECT widgets.id AS widgets_id, widgets.type AS widgets_type,
FROM widgets
WHERE widgets.type IN (?)) AS anon_1

sess.query(FooWidget.id).count()

SELECT count(*) AS count_1
FROM (SELECT widgets.id AS widgets_id
FROM widgets
WHERE widgets.type IN (?)) AS anon_1

class Widget(Base):
    __tablename__ = 'widget'
    id = Column(Integer, primary_key=True)
    type = Column(String)
    related_id = Column(ForeignKey('related.id'))
    related = relationship("Related", backref="widget")
    __mapper_args__ = {'polymorphic_on': type}


class FooWidget(Widget):
    __mapper_args__ = {'polymorphic_identity': 'foo'}


class Related(Base):
    __tablename__ = 'related'
    id = Column(Integer, primary_key=True)

s.query(Related).join(FooWidget, Related.widget).all()

SELECT related.id AS related_id
FROM related JOIN widget ON related.id = widget.related_id AND widget.type IN (:type_1)

# ON clause will now render as
# related.id = widget.related_id AND widget.type IN (:type_1)
s.query(Related).join(FooWidget, FooWidget.related_id == Related.id).all()

# ON clause will now render as
# related.id = widget.related_id AND widget.type IN (:type_1)
s.query(Related).join(FooWidget).all()

# the argument sent to where() is a full SQL expression
stmt = select([sometable]).where("somecolumn = 'value'")

# This is a normal Core expression with a string argument -
# we aren't talking about this!!
stmt = select([sometable]).where(sometable.c.somecolumn == 'value')

stmt = select(["a", "b"]).where("a = b").select_from("sometable")

SAWarning: Textual column expression 'a' should be explicitly declared
with text('a'), or use column('a') for more specificity
(this warning may be suppressed after 10 occurrences)

SAWarning: Textual column expression 'b' should be explicitly declared
with text('b'), or use column('b') for more specificity
(this warning may be suppressed after 10 occurrences)

SAWarning: Textual SQL expression 'a = b' should be explicitly declared
as text('a = b') (this warning may be suppressed after 10 occurrences)

SAWarning: Textual SQL FROM expression 'sometable' should be explicitly
declared as text('sometable'), or use table('sometable') for more
specificity (this warning may be suppressed after 10 occurrences)

import warnings
warnings.simplefilter("error")   # all warnings raise an exception

from sqlalchemy import select, text
stmt = select([
        text("a"),
        text("b")
    ]).where(text("a = b")).select_from(text("sometable"))

from sqlalchemy import select, text, column, table

stmt = select([column("a"), column("b")]).\
    where(text("a = b")).select_from(table("sometable"))

import itertools

counter = itertools.count(1)
t = Table(
    'my_table', metadata,
    Column('id', Integer, default=lambda: next(counter)),
    Column('data', String)
)

conn.execute(t.insert().values([
    {"data": "d1"},
    {"data": "d2"},
    {"data": "d3"},
]))

INSERT INTO my_table (id, data) VALUES (?, ?), (?, ?), (?, ?)
(1, 'd1', 2, 'd2', 3, 'd3')

Incorrect number of bindings supplied. The current statement uses 6,
and there are 4 supplied.
[SQL: u'INSERT INTO my_table (id, data) VALUES (?, ?), (?, ?), (?, ?)']
[parameters: (1, 'd1', 'd2', 'd3')]

INSERT INTO my_table (id, data) VALUES (:id, :data_0), (:id, :data_1), (:id, :data_2)
{u'data_2': 'd3', u'data_1': 'd2', u'data_0': 'd1', 'id': 1}

t = Table(
    'my_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', String, server_default='some default')
)

conn.execute(t.insert().values([
    {"data": "d1"},
    {"data": "d2"},
    {},
]))

sqlalchemy.exc.CompileError: INSERT value for column my_table.data is
explicitly rendered as a boundparameter in the VALUES clause; a
Python-side value or SQL expression is required

INSERT INTO my_table (data) VALUES (:data_0), (:data_1), (:data_0)
{u'data_1': 'd2', u'data_0': 'd1'}

my_table = Table('my_table', metadata, autoload_with=some_engine)

select([null(), null()])

table = Table('sometable', metadata,
    Column('some_enum', ENUM('a', 'b', 'c', name='myenum'))
)

table.create(engine)  # will emit CREATE TYPE and CREATE TABLE
table.drop(engine)  # will emit DROP TABLE and DROP TYPE - new for 1.0

my_enum = ENUM('a', 'b', 'c', name='myenum', metadata=metadata)

table = Table('sometable', metadata,
    Column('some_enum', my_enum)
)

# will fail: ENUM 'my_enum' does not exist
table.create(engine)

# will check for enum and emit CREATE TYPE
table.create(engine, checkfirst=True)

table.drop(engine)  # will emit DROP TABLE, *not* DROP TYPE

metadata.drop_all(engine) # will emit DROP TYPE

metadata.create_all(engine) # will emit CREATE TYPE

from sqlalchemy import inspect, create_engine

engine = create_engine("postgresql+psycopg2://host/dbname")
insp = inspect(engine)
print(insp.get_enums())

from sqlalchemy import *

metadata = MetaData()
user_tmp = Table(
    "user_tmp", metadata,
    Column("id", INT, primary_key=True),
    Column('name', VARCHAR(50)),
    prefixes=['TEMPORARY']
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
with e.begin() as conn:
    user_tmp.create(conn, checkfirst=True)

    # checkfirst will succeed
    user_tmp.create(conn, checkfirst=True)

from sqlalchemy import *

metadata = MetaData()
user_tmp = Table(
    "user_tmp", metadata,
    Column("id", INT, primary_key=True),
    Column('name', VARCHAR(50)),
    prefixes=['TEMPORARY']
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
with e.begin() as conn:
    user_tmp.create(conn, checkfirst=True)

    m2 = MetaData()
    user = Table(
        "user_tmp", m2,
        Column("id", INT, primary_key=True),
        Column('name', VARCHAR(50)),
    )

    # in 0.9, *will create* the new table, overwriting the old one.
    # in 1.0, *will not create* the new table
    user.create(conn, checkfirst=True)

func.count(1).filter(True)

>>> connection.execute(
...    select([
...        matchtable.c.title.match('Agile Ruby Programming').label('ruby'),
...        matchtable.c.title.match('Dive Python').label('python'),
...        matchtable.c.title
...    ]).order_by(matchtable.c.id)
... )
[
    (2.0, 0.0, 'Agile Web Development with Ruby On Rails'),
    (0.0, 2.0, 'Dive Into Python'),
    (2.0, 0.0, "Programming Matz's Ruby"),
    (0.0, 0.0, 'The Definitive Guide to Django'),
    (0.0, 1.0, 'Python in a Nutshell')
]

engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")

included_parts = select([
    part.c.sub_part, part.c.part, part.c.quantity
]).where(part.c.part == "p1").\
    cte(name="included_parts", recursive=True).\
    suffix_with(
        "search depth first by part set ord1",
        "cycle part set y_cycle to 1 default 0", dialect='oracle')