Importing fixtures with foreign keys and SQLAlchemy?
- by Chris Reid
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:59:48,683 INFO [sqlalchemy.engine.base.Engine.0x...9eb0] INSERT INTO books (title, author_id) VALUES (%(title)s, %(author_id)s) RETURNING books.id
15:59:48,683 INFO [sqlalchemy.engine.base.Engine.0x...9eb0] {'author_id': None, 'title': 'Dune'}
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?
