Re: [sqlalchemy] How to properly declare a postgresql partial index?
Russ russandheat...@gmail.com wrote:
I should have also indicated that the addition of sqlalchemy.sql.text fixes
the small mixin example. The little script below works, but I don't know if
it is a sketchy hack, or a safe long term solution:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.sql import text as sql_text
Base = declarative_base()
class A_TableDef(object):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
track_type = Column(SmallInteger, nullable = False)
@declared_attr
def __table_args__(cls):
return (Index(idx_track_type2, track_type,
postgresql_where = sql_text(track_type != 0)),
)
class A_Model(Base, A_TableDef):
pass
e = create_engine(postgresql://postgres:postgrespw@localhost:5433/edms,
echo =True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
with mixins, this will work as is in latest master, see
http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#improvements-to-declarative-mixins-declared-attr-and-related-features.
In 0.9, the declared_attr here is called sooner than we’d like, though this
particular example works if we just give the column a name (more complex
things will still not work very well with the mixins here though):
class A_TableDef(object):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
track_type = Column('track_type', SmallInteger, nullable = False)
@declared_attr
def __table_args__(cls):
return (Index(idx_track_type, track_type,
postgresql_where=(cls.track_type != 0)),
)
The version with text() is perfectly fine as postgresql_where isn’t significant
anywhere except in the DDL.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to properly declare a postgresql partial index?
I should have also indicated that the addition of sqlalchemy.sql.text fixes the small mixin example. The little script below works, but I don't know if it is a sketchy hack, or a safe long term solution: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base, declared_attr from sqlalchemy.sql import text as sql_text Base = declarative_base() class A_TableDef(object): __tablename__ = 'a' id = Column(Integer, primary_key=True) track_type = Column(SmallInteger, nullable = False) @declared_attr def __table_args__(cls): return (Index(idx_track_type2, track_type, postgresql_where = sql_text(track_type != 0)), ) class A_Model(Base, A_TableDef): pass e = create_engine(postgresql://postgres:postgrespw@localhost:5433/edms, echo =True) Base.metadata.drop_all(e) Base.metadata.create_all(e) -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to properly declare a postgresql partial index?
Russ russandheat...@gmail.com wrote:
What is the proper way to declare a postgresql partial index when using the
@declared_attr decorator?
these two concepts aren’t really connected
This form gives me Cannot compile Column object until its 'name' is
assigned:
track_type = Column(SmallInteger, nullable = False)
@declared_attr
def __table_args__(cls):
return (Index(idx_track_type, track_type,
postgresql_where = (cls.track_type != 0)),
)
and this form gives me AttributeError: 'str' object has no attribute
'_compiler_dispatch’:
please give me stack traces. or at least versions. works for me. Here’s
0.9:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
track_type = Column(SmallInteger, nullable = False)
@declared_attr
def __table_args__(cls):
return (Index(idx_track_type, track_type,
postgresql_where = (cls.track_type != 0)),
)
e = create_engine(postgresql://scott:tiger@localhost/test, echo =True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
CREATE TABLE a (
id SERIAL NOT NULL,
track_type SMALLINT NOT NULL,
PRIMARY KEY (id)
)
2015-02-02 17:56:12,610 INFO sqlalchemy.engine.base.Engine {}
2015-02-02 17:56:12,612 INFO sqlalchemy.engine.base.Engine COMMIT
2015-02-02 17:56:12,613 INFO sqlalchemy.engine.base.Engine CREATE INDEX
idx_track_type ON a (track_type) WHERE track_type != 0
2015-02-02 17:56:12,613 INFO sqlalchemy.engine.base.Engine {}
2015-02-02 17:56:12,614 INFO sqlalchemy.engine.base.Engine COMMIT
track_type = Column(SmallInteger, nullable = False)
@declared_attr
def __table_args__(cls):
return (Index(idx_track_type, track_type,
postgresql_where = track_type != 0),
)
From [this post][1] I learned about the use of sqlalchemy.sql.text, so this
is now working for me:
from sqlalchemy.sql import text as sql_text
# snip
@declared_attr
def __table_args__(cls):
return (Index(idx_track_type, track_type,
postgresql_where = sql_text(track_type != 0)),
)
That post also indicated there may be a bug here, but that was almost 2 years
ago. Is there a better way to do it now? More importantly, will the working
code above continue to work in the future?
[1]: http://goo.gl/Fmgynh
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to properly declare a postgresql partial index?
What is the proper way to declare a postgresql partial index when using the @declared_attr decorator? these two concepts aren’t really connected Sorry -- I described that poorly, then. However, I only see the problem (in v0.9.8) when I am using @declared_attr as in the case of a mixin. Your test script works for me, but not when I tweak it to have mixin behaviour. Try this version: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base, declared_attr Base = declarative_base() class A_TableDef(object): __tablename__ = 'a' id = Column(Integer, primary_key=True) track_type = Column(SmallInteger, nullable = False) @declared_attr def __table_args__(cls): return (Index(idx_track_type, track_type, postgresql_where = (cls.track_type != 0)), ) class A_Model(Base, A_TableDef): pass e = create_engine(postgresql://scott:tiger@localhost/test, echo =True) Base.metadata.drop_all(e) Base.metadata.create_all(e) -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to properly declare a postgresql partial index?
Thanks. The name addition seems tidier to me so I switched to that for the
moment.
On a somewhat unrelated note, I love the what's new in 1.0 docs you
linked. I had not checked them out yet. The Performance section is
particularly awesome and well written. Aspects of it bring me back to
putting together my profiling talk [1] from a while ago (optimizing
SQLAlchemy inserts was a perfect vehicle for the talk). I'll have to
update that thing now with the fancy new bulk operations... they look quite
convenient for decent gain with little pain. Nice!
Russ
[1]: https://speakerdeck.com/rwarren/a-brief-intro-to-profiling-in-python
On Monday, February 2, 2015 at 7:55:03 PM UTC-5, Michael Bayer wrote:
Russ russand...@gmail.com javascript: wrote:
I should have also indicated that the addition of sqlalchemy.sql.text
fixes the small mixin example. The little script below works, but I don't
know if it is a sketchy hack, or a safe long term solution:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.sql import text as sql_text
Base = declarative_base()
class A_TableDef(object):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
track_type = Column(SmallInteger, nullable = False)
@declared_attr
def __table_args__(cls):
return (Index(idx_track_type2, track_type,
postgresql_where = sql_text(track_type != 0)),
)
class A_Model(Base, A_TableDef):
pass
e =
create_engine(postgresql://postgres:postgrespw@localhost:5433/edms, echo
=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
with mixins, this will work as is in latest master, see
http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#improvements-to-declarative-mixins-declared-attr-and-related-features.
In 0.9, the declared_attr here is called sooner than we’d like, though
this
particular example works if we just give the column a name (more complex
things will still not work very well with the mixins here though):
class A_TableDef(object):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
track_type = Column('track_type', SmallInteger, nullable = False)
@declared_attr
def __table_args__(cls):
return (Index(idx_track_type, track_type,
postgresql_where=(cls.track_type != 0)),
)
The version with text() is perfectly fine as postgresql_where isn’t
significant anywhere except in the DDL.
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
