Demonstration of a possible SQLAlchemy bug

sqla_bug_test.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""Demonstrate a possible bug in SQLAlchemy.

Setting the `echo` argument of sqlalchemy.create_engine() crashes some versions
of SQLAlchemy. Tested with Python 2.7.6. See results for SQLAlchemy versions
below.

Setting ENGINE_ECHO to True or False (depending on your SQLAlchemy version)
makes the script crash with this error:

    Traceback (most recent call last):
      File "./sqla_bug_test_a.py", line 149, in <module>
        main()
      File "./sqla_bug_test_a.py", line 126, in main
        q = get_photos_with_taxa(session, metadata)
      File "./sqla_bug_test_a.py", line 140, in get_photos_with_taxa
        join(Photo.taxa_collection).join(Taxon.ranks).\
    AttributeError: type object 'photos' has no attribute 'taxa_collection'

Importing of certain modules also affects this. In some cases, the script
also crashes when `logging` is imported before `sqlalchemy` is imported. This
also happens with some other modules (e.g. argparse, hashlib).


Behavior with different SQLAlchemy versions:

0.9.1
Crashes with ECHO = False
  and `logging` imported before `sqlalchemy`

0.9.2
Crashes with ECHO = True
  and `logging` imported before `sqlalchemy`

0.9.3
Crashes with ECHO = False
  and `logging` imported before `sqlalchemy`

0.9.4
Crashes with ECHO = True

OR

Crashes with ECHO = False
  and `logging` imported before `sqlalchemy`

0.9.5
Crashes with ECHO = False

0.9.6
Crashes with ECHO = False

0.9.7
Crashes with ECHO = True

0.9.8
Crashes with ECHO = True

I later found out that the above results are not always the case. Different
databases give different behaviour. Also some days the result is different,
even with the same database.


Design of the database:

CREATE TABLE photos
(
   id INTEGER,
   md5sum VARCHAR NOT NULL,
   path VARCHAR,
   title VARCHAR,
   description VARCHAR,

   PRIMARY KEY (id),
   UNIQUE (md5sum),
   UNIQUE (path)
);

CREATE TABLE ranks
(
   id INTEGER,
   name VARCHAR NOT NULL,

   PRIMARY KEY (id),
   UNIQUE (name)
);

CREATE TABLE taxa
(
   id INTEGER,
   rank_id INTEGER NOT NULL,
   name VARCHAR NOT NULL,
   description VARCHAR,

   PRIMARY KEY (id),
   UNIQUE (rank_id, name),
   FOREIGN KEY (rank_id) REFERENCES ranks (id) ON DELETE RESTRICT
);

CREATE TABLE photos_taxa
(
   photo_id INTEGER NOT NULL,
   taxon_id INTEGER NOT NULL,

   PRIMARY KEY (photo_id, taxon_id),
   FOREIGN KEY (photo_id) REFERENCES photos (id) ON DELETE CASCADE,
   FOREIGN KEY (taxon_id) REFERENCES taxa (id) ON DELETE RESTRICT
);
"""

# Comment or uncomment this line. Moving this line below the sqlalchemy
# imports also affects whether the script crashes.
import logging

import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.automap import automap_base

DB_PATH = 'sqla_bug_test.db'

# Set to True or False.
ENGINE_ECHO = False

def main():
    engine = sqlalchemy.create_engine('sqlite:///{0}'.format(DB_PATH),
        echo=ENGINE_ECHO)
    Session = sessionmaker(bind=engine)
    session = Session()
    metadata = sqlalchemy.MetaData()
    metadata.reflect(bind=engine)

    q = get_photos_with_taxa(session, metadata)

    for photo, genus in q:
        print photo.id, genus

    session.close()

def get_photos_with_taxa(session, metadata):
    Base = automap_base(metadata=metadata)
    Base.prepare()

    Photo = Base.classes.photos
    Taxon = Base.classes.taxa
    Rank = Base.classes.ranks

    stmt = session.query(Photo.id, Taxon.name.label('genus')).\
        join(Photo.taxa_collection).join(Taxon.ranks).\
        filter(Rank.name == 'genus').subquery()

    q = session.query(Photo, 'genus').\
        join(stmt, stmt.c.id == Photo.id)

    return q

if __name__ == "__main__":
    main()

Test SQLite database: http://we.tl/IDj6WFT5KZ

SQLite test database with unrelated tables removed: http://we.tl/9TJGs2LD11