On Tue, Sep 08, 2009 at 13:05 -0400, Michael Bayer wrote: > Alternatively, just start using SQLalchemy 0.6 (its trunk so far): > > from sqlalchemy.schema import DropConstraint > for cons in table.constraints: > if isinstance(con, PrimaryKeyConstraint): > engine.execute(DropConstraint(con))
As i am using current trunk i tried to implement this approach.
Unfortunately this fails.
My goal is to be able to create and drop *Constraints whenever
i want to. I need this because i am writing a tool that does bulk
imports of data and the constraint checks are a severe performance
penalty.
Inspired by the now discovered AddConstraint/DropConstraint classes you
pointed me at I implemented constraint creation like:
--- snip ---
metadata = MetaData()
tbl = Table('foo', metadata,
Column('id', Integer)
...
)
...
pkey_constraints = [
PrimaryKeyConstraint(tbl.c.id, inline_ddl=False),
...
]
engine = create_engine(postgresql+...)
tbl.create(bind=engine)
...
engine.execute(AddConstraint(pkey_constraint_for_this_table))
--- snip ---
The assumption that creating a PrimaryKeyConstraint with
inline_ddl=False will prevent SA to generate DDL for this constraint
seems to be false as the generated SQL looks like:
--- snip ---
CREATE TABLE foo (
id SERIAL NOT NULL,
...
PRIMARY KEY (id)
)
--- snip ---
So the the attempt to manually create the PrimaryKeyConstraint fails
with the error "multiple primary keys for table ..."!
Questions:
* Why is that?
* Is inline_ddl not honoured for PrimaryKeyConstraints, which *is* a
subclass of Constraint?
* Is this caused by the "table._set_primary_key(self)" call
in PrimaryKeyConstraints._set_parent() ?
* How can i programmatically create primary key constraints?
I can't drop these constraints as well. Even if i accept that my tables
are created with primary key definitions the recipe you showed me does
not work. It fails with:
--- snip ---
...
/sqlalchemy/sql/compiler.pyc in _requires_quotes(self, value)
1306 def _requires_quotes(self, value):
1307 """Return True if the given identifier requires quoting."""
-> 1308 lc_value = value.lower()
1309 return (lc_value in self.reserved_words
1310 or self.illegal_initial_characters.match(value[0])
AttributeError: 'NoneType' object has no attribute 'lower'
--- snip ---
Is sqlalchemy-migrate the only way to handle this right now? I created
the primary key constraints by specifying the *columns* as strings
before and used a dictionary to differentiate between primary key
constraint column definitions for various tables? Is this advisable? Is
there a better way to achieve this?
with kind regards and thanks for this great tool!
Wolodja Wentland
signature.asc
Description: Digital signature
