Importing fixtures with foreign keys and SQLAlchemy?

Question

I've been experimenting with using fixture to load test data sets into my Pylons / PostgreSQL app. This works great except that it fails to properly create foreign keys if they reference an auto increment id field.

My fixture looks like this:

class AuthorsData(DataSet):
    class frank_herbert:
         first_name = "Frank"
         last_name = "Herbert"

class BooksData(DataSet):
    class dune:
        title = "Dune"
        author_id = AuthorsData.frank_herbert.ref('id')

And the model:

t_authors = sa.Table("authors", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("first_name", sa.types.String(100)),
    sa.Column("last_name", sa.types.String(100)),
    )

t_books = sa.Table("books", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("title", sa.types.String(100)),
    sa.Column("author_id", sa.types.Integer, sa.ForeignKey('authors.id')) 
    )

When running "paster setup-app development.ini", SQLAlchemey reports the FK value as "None" so it's obviously not finding it:

15:24:27,284 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] 
CREATE TABLE authors (
    id SERIAL NOT NULL, 
    first_name VARCHAR(100), 
    last_name VARCHAR(100), 
    PRIMARY KEY (id)
)

15:24:27,284 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] {}
15:24:27,291 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] COMMIT
15:24:27,292 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] 
CREATE TABLE books (
    id SERIAL NOT NULL, 
    title VARCHAR(100), 
    author_id INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY(author_id) REFERENCES authors (id)
)

15:24:27,293 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] {}
15:24:27,300 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] COMMIT
15:24:27,301 INFO  [blog.websetup] Inserting initial data
15:24:27,302 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] BEGIN
15:24:27,309 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] INSERT INTO authors (first_name, last_name) VALUES (%(first_name)s, %(last_name)s) RETURNING authors.id
15:24:27,309 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] {'first_name': 'Frank', 'last_name': 'Herbert'}
15:24:27,320 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] INSERT INTO books (title, author_id) VALUES (%(title)s, %(author_id)s) RETURNING books.id
15:24:27,320 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] {'author_id': None, 'title': 'Dune'}
15:24:27,322 INFO  [sqlalchemy.engine.base.Engine.0x...9f50] COMMIT
15:24:27,323 INFO  [blog.websetup] Done

The fixture docs actually warn that this might be a problem:

"However, in some cases you may need to reference an attribute that does not have a value until it is loaded, like a serial ID column. (Note that this is not supported by the SQLAlchemy data layer when using sessions.)"

http://farmdev.com/projects/fixture/using-dataset.html#referencing-foreign-dataset-classes

Does this mean that this is just not supported with SQLAlchemy? Or is it possible to load the data without using SA "sessions"? How are other people handling this issue?

Answer 1

Referencing not yet inserted rows is not supported by SQL expression layer in SQLAlchemy because it's meaningless: all queries are executed explicitly. ORM layer does support references to new objects, it even uses unit of work pattern to organize insertion order properly. To solve this problem fixture uses ref() method. But note, that in SQL expression layer you (fixture) execute statements, so SQLAlchemy has no chance to reorder them. So the author_id is not available if you try to store book before the author it refers to. Are you sure your fixtures are organised (passed to data() method) in proper order?

Answer 2

Can't you say author = AuthorsData.frank_herbert instead of specifying author_id directly?

Other than that, perhaps calling meta.Session.flush() after inserting your AuthorsData would force the id attribute to get the value? That's what I use in my tests when I need the ID to be assigned (I don't use fixture).