==================
SQLAlchemy Dialect
==================

    >>> connection = engine.connect()

Using the connection to execute a select statement::

    >>> result = connection.execute('select name from locations')
    >>> result.rowcount
    13

    >>> result.first()
    (u'Algol',)

Using the ORM to query the locations::

    >>> locations = session.query(Location)
    >>> [l.name for l in locations if l is not None][:5]
    [u'Algol', u'Folfanga', u'Aldebaran', u'Argabuthon', u'Bartledan']

With limit and offset::

    >>> locations = session.query(Location).offset(1).limit(4)
    >>> [l.name for l in locations if l is not None]
    [u'Folfanga', u'Aldebaran', u'Argabuthon', u'Bartledan']

With filter::

    >>> location = session.query(Location).filter_by(name='Algol').one()
    >>> location.name
    u'Algol'

Order by::

    >>> locations = session.query(Location).order_by(sa.desc(Location.name))
    >>> locations = locations.limit(2)
    >>> [l.name for l in locations]
    [u'Outer Eastern Rim', u'North West Ripple']


Insert a new location::

    >>> location = Location()
    >>> location.name = 'Earth'
    >>> location.kind = 'Planet'

    >>> session.add(location)
    >>> session.flush()

Currently refresh option is missing, therefore sleep for now::

    >>> from time import sleep
    >>> sleep(1)

Inserted location is available::

    >>> location = session.query(Location).filter_by(name='Earth').one()
    >>> location.name
    u'Earth'

Retrieve the location from the database::

    >>> session.refresh(location)
    >>> location.name
    u'Earth'

Date should have been set at the insert due to default value via python method::

    >>> from datetime import datetime
    >>> now = datetime.utcnow()
    >>> dt = location.date

    >>> dt.year == now.year
    True

    >>> dt.month == now.month
    True

    >>> dt.day == now.day
    True

    >>> (now - location.datetime).seconds < 4
    True

the location also has a date and datetime property which both are nullable and
aren't set when the row is inserted as there is no default method::

    >>> location.nullable_datetime is None
    True

    >>> location.nullable_date is None
    True

The datetime and date can be set using a update statement::

    >>> location.nullable_date = datetime.today()
    >>> location.nullable_datetime = datetime.utcnow()
    >>> session.flush()
    >>> sleep(1.1)  # wait for index refresh

Reload the object from the db::

    >>> session.refresh(location)

And verify that the date and datetime was persisted::

    >>> location.nullable_datetime is not None
    True

    >>> location.nullable_date is not None
    True

Update one Location via raw sql::

    >>> result = connection.execute("update locations set name='Mother Earth' where name='Earth'")
    >>> result.rowcount
    1

Update multiple Locations::

    >>> for x in range(10):
    ...     loc = Location()
    ...     loc.name = 'Ort %d' % x
    ...     loc.kind = 'Update'
    ...     session.add(loc)
    ...     session.flush()

    >>> sleep(1)  # give crate some time to settle

    >>> result = connection.execute("update locations set name='Kein Ort, nirgends.' where kind='Update'")
    >>> result.rowcount
    10

Check that number of affected documents of update without ``where-clause`` matches number of all
documents in the table::

    >>> result = connection.execute(u"update locations set name='Überall'")
    >>> result.rowcount == connection.execute("select * from locations limit 100").rowcount
    True
