[sqlalchemy] postgresql EXCLUDE constraint
Hello, I am using this PostgreSQL exclusion constraint: CONSTRAINT only_one_valid EXCLUDE USING (synthetic_id WITH =,COALESCE(obsoleteby,'') WITH =), How can I represent this using SQLAlchemy Table metadata so that I can create the table from the metadata? Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgresql EXCLUDE constraint
custom DDL is most directly via the DDL() element: http://www.sqlalchemy.org/docs/core/schema.html#sqlalchemy.schema.DDL or if you want a more general use EXCLUDE construct that accepts arguments and SQL expressions you can subclass DDLElement and build a compilation function: http://www.sqlalchemy.org/docs/core/compiler.html DDL and DDLElement both support execution via table and metadata create/drop events. On Sep 23, 2010, at 6:22 PM, A.M. wrote: Hello, I am using this PostgreSQL exclusion constraint: CONSTRAINT only_one_valid EXCLUDE USING (synthetic_id WITH =,COALESCE(obsoleteby,'') WITH =), How can I represent this using SQLAlchemy Table metadata so that I can create the table from the metadata? Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Eager Load of Polymorphic Children Causes Table Scan
Hi everyone,
I'm trying to do a query where I joinedload with a polymorphic child table.
The child table is using a joined table inheritance scheme. I'm using
with_polymorphic = '*' to accomplish the eager loading of polymorphic types.
The query that SQLAlchemy generates results in a sub select that returns all
the child rows. Here's the setup:
class Parent(Model):
__tablename__ = parent_table
id = Column(Integer, primary_key=True)
class ChildBase(Model):
__tablename__ = child_base_table
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey(%s.id % Parent.__tablename__))
parent = sqlalchemy.orm.relation(Parent, backref='children')
child_type = Column(Integer)
__mapper_args__ = {'polymorphic_on': child_type, 'with_polymorphic':
'*'}
class Child1(ChildBase):
__tablename__ = child1_table
__mapper_args__ = {'polymorphic_identity': 1 }
child_id = Column(Integer, ForeignKey(%s.id %
ChildBase.__tablename__), primary_key=True)
child1_data = Column(Integer)
class Child2(ChildBase):
__tablename__ = child2_table
__mapper_args__ = {'polymorphic_identity': 2 }
child_id = Column(Integer, ForeignKey(%s.id %
ChildBase.__tablename__), primary_key=True)
child2_data = Column(Integer)
Here's the query that I'm doing:
session.query(models.Parent).filter_by(id=99).options(sqlalchemy.orm.joinedload_all('children'))
Here's the resulting SQL query:
SELECT parent_table.id AS parent_table_id,
anon_1.child_base_table_id AS anon_1_child_base_table_id,
anon_1.child_base_table_parent_id AS
anon_1_child_base_table_parent_id,
anon_1.child_base_table_child_type AS
anon_1_child_base_table_child_type,
anon_1.child1_table_child_id AS anon_1_child1_table_child_id,
anon_1.child1_table_child1_dataAS
anon_1_child1_table_child1_data,
anon_1.child2_table_child_id AS anon_1_child2_table_child_id,
anon_1.child2_table_child2_dataAS anon_1_child2_table_child2_data
FROM parent_table
LEFT OUTER JOIN (SELECT
child_base_table.id AS
child_base_table_id,
child_base_table.parent_id AS
child_base_table_parent_id,
child_base_table.child_type
ASchild_base_table_child_type,
child1_table.child_id
ASchild1_table_child_id,
child1_table.child1_data
ASchild1_table_child1_data,
child2_table.child_id
ASchild2_table_child_id,
child2_table.child2_data
ASchild2_table_child2_data
FROM child_base_table
LEFT OUTER JOIN child1_table
ON child_base_table.id =
child1_table.child_id
LEFT OUTER JOIN child2_table
ON child_base_table.id =
child2_table.child_id)
AS
anon_1
ON parent_table.id = anon_1.child_base_table_parent_id
WHERE parent_table.id = 99
Note that the inner select has no WHERE clause which results in selecting
all of the child rows. Without the with_polymorphic option, SQLAlchemy
doesn't produce the inner select. Using subquery_load causes the same
problem. Is there a way to prevent this from happening and eager load the
polymorphic tables?
Thanks!
Anthony
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Eager Load of Polymorphic Children Causes Table Scan
On Sep 23, 2010, at 8:18 PM, Anthony Tran wrote: Hi everyone, I'm trying to do a query where I joinedload with a polymorphic child table. The child table is using a joined table inheritance scheme. I'm using with_polymorphic = '*' to accomplish the eager loading of polymorphic types. The query that SQLAlchemy generates results in a sub select that returns all the child rows. Here's the setup: Here's the resulting SQL query: SELECT parent_table.id AS parent_table_id, anon_1.child_base_table_id AS anon_1_child_base_table_id, anon_1.child_base_table_parent_id AS anon_1_child_base_table_parent_id, anon_1.child_base_table_child_type AS anon_1_child_base_table_child_type, anon_1.child1_table_child_id AS anon_1_child1_table_child_id, anon_1.child1_table_child1_dataAS anon_1_child1_table_child1_data, anon_1.child2_table_child_id AS anon_1_child2_table_child_id, anon_1.child2_table_child2_dataAS anon_1_child2_table_child2_data FROM parent_table LEFT OUTER JOIN (SELECT child_base_table.id AS child_base_table_id, child_base_table.parent_id AS child_base_table_parent_id, child_base_table.child_type AS child_base_table_child_type, child1_table.child_id AS child1_table_child_id, child1_table.child1_dataAS child1_table_child1_data, child2_table.child_id AS child2_table_child_id, child2_table.child2_dataAS child2_table_child2_data FROM child_base_table LEFT OUTER JOIN child1_table ON child_base_table.id = child1_table.child_id LEFT OUTER JOIN child2_table ON child_base_table.id = child2_table.child_id) AS anon_1 ON parent_table.id = anon_1.child_base_table_parent_id WHERE parent_table.id = 99 Note that the inner select has no WHERE clause which results in selecting all of the child rows. Without the with_polymorphic option, SQLAlchemy doesn't produce the inner select. Using subquery_load causes the same problem. Is there a way to prevent this from happening and eager load the polymorphic tables? So, interesting terminology you used above - a sub select that returns all the child rows. The SELECT that is the right hand target of the LEFT OUTER JOIN doesn't actually return any rows to your application - the outer one does. The subquery only represents the set of all child rows. In relational theory, there is no difference between these two statements : select * from parent join child on parent.id = child.parent_id select * from parent join (select * from child) as c1 on parent.id=c1.parent_id SQLAlchemy's normal behavior makes great usage of this natural effect. The set of all rows in 'child' is the same as the set of all rows in the set of all rows in 'child'. A good SQL optimizer, like that of Postgresql or Oracle, knows this, and in fact with PG my experience is that it's extremely difficult to get it to come up with a different execution plan by moving subqueries out into joins and such - it almost always figures out the common relational structure underneath (however, if you're on PG, I can't say for sure without trying how well the above query would optimize). SQLAlchemy's eager loader treats all join targets as the same thing - only the kind of selectable which comprises the join target changes. Now, that's the theory part. The practical part is, if you're using MySQL, a query like the above is going to hit you hard and mercilessly, as their optimizer is the worst I've ever seen. So my advice here depends strongly on which backend you're using. To be blunt, if you're using MySQL, I would not be using joined table inheritance for anything critical, at all, period. MySQL completely sucks at joins and it always will.SQLAlchemy is heavily oriented around relational theory, whereas MySQL is the database where they didn't think foreign keys were really that important until ten years into the project. If you're using Postgresql, I would be checking what EXPLAIN tells you - it may or may not make any difference if a WHERE clause were stuck inside the subquery. I will say about PG that it works much better on inner joins than outer ones, though that's hard to use when you're trying to scan out on polymorphic tables like the above. So lets say you're on MySQL or PG, you can't change your schema, you've observed that yes, the planner really is performing more poorly because the subquery isn't pre-limited (I really think thats
[sqlalchemy] Is sqlalchemy.types.Enum supposed to work with SQLite?
I noticed a few versions ago that the Enum type has been added. Are there any notes regarding its compatibility? I'm getting errors with SQLite on OSX: sqlalchemy.exc.OperationalError: (OperationalError) no such column: 'in', 'out' u\nCREATE TABLE graph (\n\trevision_id INTEGER, \n \ttime_created DATETIME NOT NULL, \n\ttime_updated DATETIME, \n \tgraph_source_id INTEGER NOT NULL, \n\tdirection VARCHAR(2) NOT NULL, \n\tremote_id VARCHAR(64) NOT NULL, \n\tPRIMARY KEY (graph_source_id, revision_id, direction, remote_id), \n\tFOREIGN KEY(graph_source_id) REFERENCES graph_source (id), \n\tCHECK (direction IN (['in', 'out'])) \n)\n\n () Respective schema: class Graph(BaseModel): __tablename__ = 'graph' revision_id = Column(types.Integer, primary_key=True) time_created = Column(types.DateTime, default=datetime.now, nullable=False) time_updated = Column(types.DateTime, default=datetime.now, onupdate=datetime.now) graph_source_id = Column(types.Integer, ForeignKey(GraphSource.id), nullable=False) graph_source = orm.relationship(GraphSource) direction = Column(types.Enum(['in','out']), nullable=False) remote_id = Column(types.String(64), nullable=False, primary_key=True) Versions: SQLAlchemy 0.6.4 SQLite 3.6.23.1 python-sqlite 2.4.1 Python 2.6.5 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Is sqlalchemy.types.Enum supposed to work with SQLite?
lose the [] around the 'in', 'out' On Sep 23, 10:26 pm, Andrey Petrov sha...@gmail.com wrote: I noticed a few versions ago that the Enum type has been added. Are there any notes regarding its compatibility? I'm getting errors with SQLite on OSX: sqlalchemy.exc.OperationalError: (OperationalError) no such column: 'in', 'out' u\nCREATE TABLE graph (\n\trevision_id INTEGER, \n \ttime_created DATETIME NOT NULL, \n\ttime_updated DATETIME, \n \tgraph_source_id INTEGER NOT NULL, \n\tdirection VARCHAR(2) NOT NULL, \n\tremote_id VARCHAR(64) NOT NULL, \n\tPRIMARY KEY (graph_source_id, revision_id, direction, remote_id), \n\tFOREIGN KEY(graph_source_id) REFERENCES graph_source (id), \n\tCHECK (direction IN (['in', 'out'])) \n)\n\n () Respective schema: class Graph(BaseModel): __tablename__ = 'graph' revision_id = Column(types.Integer, primary_key=True) time_created = Column(types.DateTime, default=datetime.now, nullable=False) time_updated = Column(types.DateTime, default=datetime.now, onupdate=datetime.now) graph_source_id = Column(types.Integer, ForeignKey(GraphSource.id), nullable=False) graph_source = orm.relationship(GraphSource) direction = Column(types.Enum(['in','out']), nullable=False) remote_id = Column(types.String(64), nullable=False, primary_key=True) Versions: SQLAlchemy 0.6.4 SQLite 3.6.23.1 python-sqlite 2.4.1 Python 2.6.5 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Is sqlalchemy.types.Enum supposed to work with SQLite?
Oh drat, I swear I tried that. That worked, thanks zzzeek! 3 - shazow On Sep 23, 11:43 pm, Michael Bayer mike...@zzzcomputing.com wrote: lose the [] around the 'in', 'out' On Sep 23, 10:26 pm, Andrey Petrov sha...@gmail.com wrote: I noticed a few versions ago that the Enum type has been added. Are there any notes regarding its compatibility? I'm getting errors with SQLite on OSX: sqlalchemy.exc.OperationalError: (OperationalError) no such column: 'in', 'out' u\nCREATE TABLE graph (\n\trevision_id INTEGER, \n \ttime_created DATETIME NOT NULL, \n\ttime_updated DATETIME, \n \tgraph_source_id INTEGER NOT NULL, \n\tdirection VARCHAR(2) NOT NULL, \n\tremote_id VARCHAR(64) NOT NULL, \n\tPRIMARY KEY (graph_source_id, revision_id, direction, remote_id), \n\tFOREIGN KEY(graph_source_id) REFERENCES graph_source (id), \n\tCHECK (direction IN (['in', 'out'])) \n)\n\n () Respective schema: class Graph(BaseModel): __tablename__ = 'graph' revision_id = Column(types.Integer, primary_key=True) time_created = Column(types.DateTime, default=datetime.now, nullable=False) time_updated = Column(types.DateTime, default=datetime.now, onupdate=datetime.now) graph_source_id = Column(types.Integer, ForeignKey(GraphSource.id), nullable=False) graph_source = orm.relationship(GraphSource) direction = Column(types.Enum(['in','out']), nullable=False) remote_id = Column(types.String(64), nullable=False, primary_key=True) Versions: SQLAlchemy 0.6.4 SQLite 3.6.23.1 python-sqlite 2.4.1 Python 2.6.5 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
