Declarative base -- only add a constraint if particular db engine

[Gregg Lind]

I use declarative base for defining classes.

I have a constraint that only works in Postgres. How do I declare
that constraint "lowername_check" only if the session is going
postgres (and not to sqlite, for example).

pg_only_constraint = CheckConstraint("lowername !~
'[[:upper:]]'",name='lowername_check'),
class Data(Base):
__tablename__ = 'Data'
lowername=Column(Unicode, nullable=False)
__table_args__ = (
pg_only_constraint, {}
)

Thanks!

Gregg Lind

[Michael Bayer]

The cleanest way is to use the schema.DDL() construct which can filter
against various backends, but requires that you spell out the constraint
explicitly:

DDL("CREATE CONSTRAINT ....", on="postgres").execute_at('after-create',
Data.__table__)

Alternatively, if you want to stick with the CheckConstraint object you
can create a function "create_pg_constraints()" which is called at the
point your app calls "create_engine()", that contains all PG specific
constructs - the function would be called based on engine.dialect.name ==
"postgres".

We have a more flexible architecture in 0.6 for this sort of thing and I
think if we add an AddConstraint() construct there and also move most of
DDL()'s execute-at and "on" functionality into the base DDLElement class,
that would enable both constructs to be combined together as in
AddConstraint(CheckConstraint(...args...),
on="postgres")).execute_at('after-create', Data.__table__).



>
>
> Thanks!
>
> Gregg Lind
>
> >
>

[Gregg Lind]

As always, thank you for the complete, exhaustive answer. This
particular thing is definitely and edge case, and rather non-obvious,
so thank you for walking me through it.

Either of those are clean enough for me!

Is there are more proper / general way to describe the problem, so
google and make this answer easier to find?

Gregg

[Gregg Lind]

I used the DDL style

DDL('''ALTER TABLE data ADD CONSTRAINT lowername_check CHECK
(lowername !~ '[[\:upper\:]]')''',

on="postgres").execute_at('after-create',Data.__table__)

and now my "print_schema" method (based on
http://www.sqlalchemy.org/trac/wiki/FAQ#HowcanIgettheCREATETABLEDROPTABLEoutputasastring)
breaks (on PG only, because of the DDL), with this error:

TypeError: unsupported operand type(s) for +: '_TextClause' and 'str'

I escaped the colons in the DDL. Workarounds?

Gregg


Code:

def print_schema(T="postgres"):
''' print print_schema will print the schema in use '''
global Base
from StringIO import StringIO
buf = StringIO()
print '%s://' % T
engine = create_engine('%s://' % T, strategy='mock',
executor=lambda s, p='': buf.write(s + p))
Base.metadata.create_all(engine)
return buf.getvalue()

[Michael Bayer]

Gregg Lind wrote:
>
> I used the DDL style
>
> DDL('''ALTER TABLE data ADD CONSTRAINT lowername_check CHECK
> (lowername !~ '[[\:upper\:]]')''',
> on="postgres").execute_at('after-create',Data.__table__)
>
> and now my "print_schema" method (based on
> http://www.sqlalchemy.org/trac/wiki/FAQ#HowcanIgettheCREATETABLEDROPTABLEoutputasastring)
> breaks (on PG only, because of the DDL), with this error:
>
> TypeError: unsupported operand type(s) for +: '_TextClause' and 'str'
>
> I escaped the colons in the DDL. Workarounds?

escape them with \\: or use r'\:'

[Gregg Lind]

Alas, that doesn't seem to matter or help.

Even this statement causes the same issue. Odd. Must not be related
to the colons, alas.

DDL(r''' ''', on="postgres").execute_at('after-create',Data.__table__)

[Michael Bayer]

full stack trace plz

[Michael Bayer]

Gregg Lind wrote:
>
> Alas, that doesn't seem to matter or help.
>
> Even this statement causes the same issue. Odd. Must not be related
> to the colons, alas.
>
> DDL(r''' ''', on="postgres").execute_at('after-create',Data.__table__)

didnt realize you're printing with mock. its:

buf.write(str(s) + p)

[Gregg Lind]

You got me there! Updating the FAQ on it would fix the issue for others.

For reference:

##########################
from sqlalchemy import *
from sqlalchemy.schema import DDL
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Data(Base):
__tablename__ = 'data'
f1 = Column(Integer, nullable=False, primary_key=True)
f2 = Column(Integer, nullable=False, primary_key=True)

DDL(r''' ''', on="postgres").execute_at('after-create',Data.__table__)

def print_schema_wrong(db):

from StringIO import StringIO
buf = StringIO()

engine = create_engine('%s://' % db, strategy='mock',

executor=lambda s, p='': buf.write(s + p))

#meta = MetaData()
meta = Base.metadata
meta.create_all(engine)
print buf.getvalue()


def print_schema(db):

from StringIO import StringIO
buf = StringIO()

engine = create_engine('%s://' % db, strategy='mock',
executor=lambda s, p='': buf.write(str(s) + p))
#meta = MetaData()
meta = Base.metadata
meta.create_all(engine)
print buf.getvalue()


# fine
print_schema('sqlite')
print_schema('postgres')
print_schema_wrong('sqlite')

# will throw an error
print_schema_wrong('postgres')

######################33

[Michael Bayer]

mock is on the way out as a general use tool.