DropConstraint exception

[Gerry Reno]

Ok, I tried this:

from sqlalchemy.schema import DropConstraint
for table in metadata.tables.keys():
for con in metadata.tables[table].constraints:
if isinstance(con, PrimaryKeyConstraint):
engine.execute(DropConstraint(con))

but I'm getting an exception:
engine.execute(DropConstraint(con))
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1457, in execute
return connection.execute(statement, *multiparams, **params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1035, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1081, in _execute_ddl
compiled_ddl=ddl.compile(dialect=self.dialect),
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/
expression.py", line 1262, in compile
compiler.compile()
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 629, in compile
self.string = self.process(self.statement)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 632, in process
return obj._compiler_dispatch(self, **kwargs)
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/visitors.py",
line 47, in _compiler_dispatch
return getter(visitor)(self, **kw)
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/compiler.py",
line 1052, in visit_drop_constraint
self.preparer.format_constraint(drop.element),
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/compiler.py",
line 1393, in format_constraint
return self.quote(constraint.name, constraint.quote)
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/compiler.py",
line 1367, in quote
if self._requires_quotes(ident):
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/compiler.py",
line 1348, in _requires_quotes
lc_value = value.lower()
AttributeError: 'NoneType' object has no attribute 'lower'

What did I miss?

-Gerry

[Michael Bayer]

seems like a bug, actually. does that PrimaryKeyConstraint have a name ?

we don't usually issue a DropConstraint on a PK constraint like that.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Gerry Reno]

The PrimaryKeyConstraints were created like this:

category = Table('category', metadata,
Column('name', String (64), nullable=False ),
...
PrimaryKeyConstraint('name'),
)

-Gerry

[Michael Bayer]

PrimaryKeyConstraint(name='name')

[Gerry Reno]

What do I do then for a composite primary key?

PrimaryKeyConstraint('col1','col2')

-Gerry

[Gerry Reno]

Ok, I tried with the keyword but I get this using 0.6beta1:

PrimaryKeyConstraint(id='id'),
File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line
1391, in __init__
super(ColumnCollectionConstraint, self).__init__(**kw)
TypeError: __init__() got an unexpected keyword argument 'id'

-Gerry

[Michael Bayer]

don't know why you'd be using beta1 when we're up to beta3.....heres a fully working example:

from sqlalchemy import *
from sqlalchemy.schema import *

metadata = MetaData()

c1 = Table('category', metadata,

Column('name', String (64), nullable=False ),

PrimaryKeyConstraint('name', name='somename')
)

c2 = Table('category2', metadata,

Column('name', String (64), nullable=False ),

Column('name2', String (64), nullable=False ),
PrimaryKeyConstraint('name', 'name2', name='somename')
)

print DropConstraint(c1.constraints.pop())
print DropConstraint(c2.constraints.pop())

[Gerry Reno]

Upgraded to beta3.

So you're saying that if we want to be able to drop a constraint later
on we must create it with a name.
Alright. But I am still getting an Operational Error:

################################################
#!/usr/bin/env python

import sqlalchemy
from sqlalchemy import Table, Column, MetaData
from sqlalchemy.schema import *
from sqlalchemy.types import *

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

metadata = MetaData()

category = Table('category', metadata,
Column('name', String (64), nullable=False ),

PrimaryKeyConstraint('name', name='name'),
)

metadata.create_all(engine)

from sqlalchemy.schema import DropConstraint

engine.execute(DropConstraint(category.constraints.pop()))

################################################

Yields:
$ python /tmp/testthis.py
2010-04-15 11:39:56,367 INFO sqlalchemy.engine.base.Engine.0x...9a2c
PRAGMA table_info("category")
2010-04-15 11:39:56,368 INFO sqlalchemy.engine.base.Engine.0x...9a2c
()
2010-04-15 11:39:56,369 INFO sqlalchemy.engine.base.Engine.0x...9a2c
CREATE TABLE category (
name VARCHAR(64) NOT NULL,
CONSTRAINT name PRIMARY KEY (name)
)

2010-04-15 11:39:56,369 INFO sqlalchemy.engine.base.Engine.0x...9a2c
()
2010-04-15 11:39:56,369 INFO sqlalchemy.engine.base.Engine.0x...9a2c
COMMIT
2010-04-15 11:39:56,370 INFO sqlalchemy.engine.base.Engine.0x...9a2c
ALTER TABLE category DROP CONSTRAINT name
2010-04-15 11:39:56,375 INFO sqlalchemy.engine.base.Engine.0x...9a2c
()
2010-04-15 11:39:56,375 INFO sqlalchemy.engine.base.Engine.0x...9a2c
ROLLBACK
Traceback (most recent call last):
File "/tmp/testthis.py", line 26, in <module>
engine.execute(DropConstraint(category.constraints.pop()))
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1533, in execute

return connection.execute(statement, *multiparams, **params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",

line 1086, in execute

return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",

line 1136, in _execute_ddl
return self.__execute_context(context)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1180, in __execute_context
context.parameters[0], context=context)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1249, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1247, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters,
context=context)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/
default.py", line 266, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) near "DROP":
syntax error u'ALTER TABLE category DROP CONSTRAINT name' ()

What am I doing wrong here?

-Gerry

[Michael Bayer]

On Apr 15, 2010, at 11:46 AM, Gerry Reno wrote:

> Upgraded to beta3.
>
> So you're saying that if we want to be able to drop a constraint later
> on we must create it with a name.
> Alright. But I am still getting an Operational Error:
>
> ################################################
> #!/usr/bin/env python
>
> import sqlalchemy
> from sqlalchemy import Table, Column, MetaData
> from sqlalchemy.schema import *
> from sqlalchemy.types import *
>
> from sqlalchemy import create_engine
> engine = create_engine('sqlite:///:memory:', echo=True)
>
> metadata = MetaData()
>
> category = Table('category', metadata,
> Column('name', String (64), nullable=False ),
> PrimaryKeyConstraint('name', name='name'),
> )
>
> metadata.create_all(engine)
>
> from sqlalchemy.schema import DropConstraint
>
> engine.execute(DropConstraint(category.constraints.pop()))
>

> cursor.execute(statement, parameters)
> sqlalchemy.exc.OperationalError: (OperationalError) near "DROP":
> syntax error u'ALTER TABLE category DROP CONSTRAINT name' ()
>
> What am I doing wrong here?

SQLite doesn't support ALTER TABLE.

[Gerry Reno]

Yep. Works with postgresql:
$ python /tmp/testthis.py
2010-04-15 12:40:03,032 INFO sqlalchemy.engine.base.Engine.0x...240c
select version()
2010-04-15 12:40:03,032 INFO sqlalchemy.engine.base.Engine.0x...240c
{}
2010-04-15 12:40:03,053 INFO sqlalchemy.engine.base.Engine.0x...240c
select current_schema()
2010-04-15 12:40:03,053 INFO sqlalchemy.engine.base.Engine.0x...240c
{}
2010-04-15 12:40:03,119 INFO sqlalchemy.engine.base.Engine.0x...240c
select relname from pg_class c join pg_namespace n on
n.oid=c.relnamespace where n.nspname=current_schema() and
lower(relname)=%(name)s
2010-04-15 12:40:03,119 INFO sqlalchemy.engine.base.Engine.0x...240c
{'name': u'category'}
2010-04-15 12:40:03,211 INFO sqlalchemy.engine.base.Engine.0x...240c

CREATE TABLE category (
name VARCHAR(64) NOT NULL,
CONSTRAINT name PRIMARY KEY (name)
)

2010-04-15 12:40:03,211 INFO sqlalchemy.engine.base.Engine.0x...240c
{}
2010-04-15 12:40:03,630 INFO sqlalchemy.engine.base.Engine.0x...240c
COMMIT
2010-04-15 12:40:03,633 INFO sqlalchemy.engine.base.Engine.0x...240c

ALTER TABLE category DROP CONSTRAINT name

2010-04-15 12:40:03,633 INFO sqlalchemy.engine.base.Engine.0x...240c
{}
2010-04-15 12:40:03,676 INFO sqlalchemy.engine.base.Engine.0x...240c
COMMIT

-Gerry

[Gerry Reno]

Except if I reflect the tables in existing db where constraints are
all named and then try:

from sqlalchemy.schema import DropConstraint
for table in metadata.tables.keys():
    for con in metadata.tables[table].constraints:
        if isinstance(con, PrimaryKeyConstraint):
            engine.execute(DropConstraint(con))

I get this exception again:
engine.execute(DropConstraint(con))

File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1533, in execute
return connection.execute(statement, *multiparams, **params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1086, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",

line 1133, in _execute_ddl
compiled_ddl=ddl.compile(dialect=self.dialect),
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/
expression.py", line 1257, in compile
compiler.compile()
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 663, in compile

self.string = self.process(self.statement)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",

line 676, in process

return obj._compiler_dispatch(self, **kwargs)
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/visitors.py",
line 47, in _compiler_dispatch
return getter(visitor)(self, **kw)
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/compiler.py",

line 1209, in visit_drop_constraint
self.preparer.format_constraint(drop.element),
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/compiler.py",
line 1550, in format_constraint

return self.quote(constraint.name, constraint.quote)
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/compiler.py",

line 1524, in quote

if self._requires_quotes(ident):
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/compiler.py",

line 1505, in _requires_quotes

lc_value = value.lower()
AttributeError: 'NoneType' object has no attribute 'lower'

It does not look like the reflection is applying the names on the
constraints.

-Gerry

[Michael Bayer]

On Apr 15, 2010, at 3:21 PM, Gerry Reno wrote:

> Except if I reflect the tables in existing db where constraints are
> all named and then try:
>
> from sqlalchemy.schema import DropConstraint
> for table in metadata.tables.keys():
> for con in metadata.tables[table].constraints:
> if isinstance(con, PrimaryKeyConstraint):
> engine.execute(DropConstraint(con))
>

> line 1505, in _requires_quotes
> lc_value = value.lower()
> AttributeError: 'NoneType' object has no attribute 'lower'
>
> It does not look like the reflection is applying the names on the
> constraints.

maybe not for primary key constraints. Feel free to file a ticket in trac, though I don't have any time to get to this myself right now.

[Gerry Reno]

Ticket: http://www.sqlalchemy.org/trac/ticket/1769