Creating tables in correct order

[Richie Ward]

I am trying to run this query:
CREATE TABLE dependenciesbinary (
id INTEGER NOT NULL AUTO_INCREMENT,
dependency_mn VARCHAR(128),
name VARCHAR(128),
operatingsystem VARCHAR(128),
architecture VARCHAR(128),
PRIMARY KEY (id),
FOREIGN KEY(dependency_mn) REFERENCES dependencies (modulename)
)ENGINE=InnoDB CHARSET=utf8

But create_all() is not creating the table "dependencies" before
"dependenciesbinary" which causes MySQL to error due to the missing
table.

Is there some way I can change the order of the create statements to
fix this?

I am using Declarative if that helps.

[Gunnlaugur Briem]

Strictly that's not a query, it's a table definition. Do you mean that
you are creating a declarative model class corresponding to this table
definition? Posting your code would help more.

You must define both tables on the same metadata instance (in
declarative, that's typically done by having both model classes extend
the same declarative_base instance). If you call create_all on that
metadata instance, it does respect the dependency order. (Unless
you've found a bug, which is unlikely for code that's as central and
heavily used as this.)

See http://www.sqlalchemy.org/docs/orm/extensions/declarative.html ---
follow the beginning of that, and make sure both of your model classes
extend the same Base instance.

Regards,

- Gulli

[Richie Ward]

I think I am using the same declarative_base instance but it is still
making the tables in the wrong order.

My model is at:
http://bazaar.launchpad.net/~richies/hypernucleus-server/PylonsPortExperimental/files/head%3A/hypernucleusserver/model/

And here is what happens when I run: paster setup-app development.ini
http://pastebin.com/VuW7UK3B

The problem now appears at games.py which is very similar to the
dependencies.py model.
A declarative_base instance is created in meta.py and then all the
models import the instance from that.

The problem is also exacerbated by InnoDB which keeps referential
integrity.
I wanted to move to InnoDB for this very reason.

[Richie Ward]

I have put my problem in a small program for easy understanding.
Run this with MySQL.
from sqlalchemy import create_engine, __version__
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
print "SQLAlchemy Version:", __version__
engine = create_engine('mysql://username:password@localhost/testdb',
echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
metadata = Base.metadata

class LotsOfNames(Base):
__tablename__ = 'lotsofnames'
__table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset':
'utf8'}

id = Column(Integer, primary_key=True)
name = Column(String(255), ForeignKey('users.name'))

class User(Base):
__tablename__ = 'users'
__table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset':
'utf8'}

id = Column(Integer, primary_key=True)
name = Column(String(255), primary_key=True)
fullname = Column(String(255))
password = Column(String(255))
morenames = relationship(LotsOfNames,
backref=backref('lotsofnames',
order_by=LotsOfNames.id))

User.__table__.drop(bind=engine, checkfirst=True)
LotsOfNames.__table__.drop(bind=engine, checkfirst=True)
metadata.create_all(engine)

On Nov 5, 10:16 am, Richie Ward <rich...@gmail.com> wrote:
> I think I am using the same declarative_base instance but it is still
> making the tables in the wrong order.
>

> My model is at:http://bazaar.launchpad.net/~richies/hypernucleus-server/PylonsPortEx...
>
> And here is what happens when I run: paster setup-app development.inihttp://pastebin.com/VuW7UK3B

[Gunnlaugur Briem]

Hi,

that example code works for me in PostgreSQL, after adding unique=True
on the name attribute of User, and reversing the order of the drop
calls. I don't have a MySQL to try against. Did you get the exact same
error from it when running against MySQL?

As for your actual app:

1. the log output suggests that some of your tables already exist: the
ROLLBACKs are presumably to clear the does-not-exist error condition.
There's no ROLLBACK after DESCRIBE `games` and DESCRIBE
`dependencies`, so those tables probably exist already, and maybe they
don't match the schema of your current declarative setup. create_all
does not modify existing tables, so creating a new table with a
foreign key against them may fail with a mismatch. That might be what
the "errno 150" from MySQL means.

2. that decoupled configuration may be right, but not dead-obviously
so :) ... to confirm, you could check that all the tables you expect
the metadata to know about are really there, and have the proper
foreign keys, just before calling create_all. Something like:

import pprint
pprint.pprint(dict(
(tn, [
(c.name, c.foreign_keys)
for c in t.c
if c.foreign_keys
])
for tn, t in metadata.tables.items()
))

Regards,

- Gulli

[Andrew Bogaard]

Richie, I realize this thread is long long dead, but do you have any recollection of how you fixed this?

Thanks,

Andrew

[Simon King]

SQLAlchemy will create tables in the correct order as long as you have
told it about the dependencies (using the ForeignKey construct). If
this isn't working for you, could you show us your code?

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b4cc20b6-2468-4b30-9835-27be53c00f07%40googlegroups.com.