Import fixtures with foreign keys and SQLAlchemy?

Question

I experimented using fixture to load test datasets into a Pylons / PostgreSQL application. This works fine, except that it cannot correctly create foreign keys if they refer to the auto increment id field.

My device is as follows:

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 obviously does not find 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 documentation actually warns that this could be a problem:

" , , , ( , SQLAlchemy .)"

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

, SQLAlchemy? SA? ?

Answer 1

SQL SQLAlchemy, : . ORM , . ref(). , SQL (fixture) , SQLAlchemy . , author_id , , . , ( data()) ?

Answer 2

author = AuthorsData.frank_herbert author_id?

, , meta.Session.flush() AuthorsData id ? , , , ( ).

Answer 3

I solve this problem by defining orm.relationas follows:

class BooksData(DataSet):
    class dune:
        title = "Dune"
        author = AuthorsData.frank_herbert


mapper(Book, books, properties={
   'author': relation(Author)
 })