title: Importing fixtures with foreign keys and SQLAlchemy?
body: <p>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.</p>
  
  <p>My fixture looks like this:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>And the model:</p>
  
  <pre><code>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')) 
      )
  </code></pre>
  
  <p>When running "paster setup-app development.ini", SQLAlchemey reports the FK value as "None" so it's obviously not finding it:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The fixture docs actually warn that this might be a problem:</p>
  
  <p>"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.)"</p>
  
  <p><a href="http://farmdev.com/projects/fixture/using-dataset.html#referencing-foreign-dataset-classes" rel="nofollow noreferrer">http://farmdev.com/projects/fixture/using-dataset.html#referencing-foreign-dataset-classes</a></p>
  
  <p>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?</p>