Re: [sqlalchemy] Migration from 0.5 to 0.9, legacy code and InvalidRequestError: This transaction is inactive
Am Dienstag, 9. September 2014 18:12:26 UTC+2 schrieb Jonathan Vanasco: What do you see if you drop SqlAlchemy's logging to DEBUG? I think I had a similar problem a long time ago, migrating from 0.5 to 0.8. In my case, the issue was with the `Session` factory -- i was not properly creating/deleting `session` objects and they got recycled. So an error raised on one web page request , invalidated the transaction on a completely different web page request. i think there was a slight API change, couple with me doing things the absolute wrong way in 0.5. the only thing I can think of, is tossing in a few `log.debug()` lines to trace your session objects and ensure that you're not accidentally recycling anything. i'd also make sure your current session/sessionmaker setup is in line with mike's current recommendations (which are in both the docs and faq) Thanks for your answers! I actually do have set the log level to DEBUG; sadly I don't get any more useful output. I have read the sessionmaker docs and the faq and have played with all properties when creating my sessions, but to no avail. I suspect my error is similar to yours, though; can you per chance remember what API change triggered your problem? Again, thanks for the answer, I'll go and debug the session lifetime thoroughly. -- 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] Migration from 0.5 to 0.9, legacy code and InvalidRequestError: This transaction is inactive
unfortunately I can't recall... I encountered this a long time ago (2010 or so), so I think it was a migration from .5 to .6x what i do remember, is that once I figured out how wrong i was about setting up the session... i was amazed that anything worked. I looked at the .5x and .6x changelogs but couldn't find anything (http://docs.sqlalchemy.org/en/rel_0_7/changelog/index.html) another thing i can suggest is this -- try installing a few versions and pinpoint what works/doesn't. it might be easier to find the culprit in the changelog for the first non-working release. -- 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.
[sqlalchemy] SQLAlchemy connection errors
I'm expiriencing some strange bugs which seem to be caused by connections used by Sqlalchemy, which i can't pin down exactly.. i was hoping someone has a clue whats going on here. We're working on a Pyramid (version 1.5b1) webapplication and use Sqlalchemy (version 0.9.6) for all our database connectivity. Sometimes we get errors related to the db connection or session, most of the time this would be a cursor already closed or This Connection is closed error, but we get other related exceptions too: (OperationalError) connection pointer is NULL (InterfaceError) cursor already closed Parent instance ... is not bound to a Session, and no contextual session is established; lazy load operation of attribute '...' cannot proceed A conflicting state is already present in the identity map for key (class '...', (1001L,)) This Connection is closed (original cause: ResourceClosedError: This Connection is closed) (InterfaceError) cursor already closed Parent instance ... is not bound to a Session; lazy load operation of attribute '...' cannot proceed Parent instance ... is not bound to a Session, and no contextual session is established; lazy load operation of attribute '...' cannot proceed 'NoneType' object has no attribute 'twophase' (OperationalError) connection pointer is NULL This session is in 'prepared' state; no further There is no silver bullet to reproduce them, only by refreshing many times they are bound to happen one at some point. So i made a script using multi-mechanize to spam different urls concurrently and see where and when it happens. It appears the url triggered doesn't really matter, the errors happen when there are concurrent requests that span a longer time (and other requests get served in between). This seems to indicate there is some kind of threading problem; that either the session or connection is shared among different threads. After googling for these issues I found a lot of topics, most of them tell to use scoped sessions, but the thing is we do use them already: db_session = scoped_session(sessionmaker(extension=ZopeTransactionExtension(), autocommit=False, autoflush=False)) db_meta = MetaData() - We have a BaseModel for all our orm objects: BaseModel = declarative_base(cls=BaseModelObj, metaclass=BaseMeta, metadata=db_meta) - We use the pyramid_tm tween to handle transactions during the request - We hook db_session.remove() to the pyramid NewResponse event (which is fired after everything has run). I also tried putting it in a seperate tween running after pyramid_tm or even not doing it at all, none of these seem to have effect, so the response event seemed like the most clean place to put it. - We create the engine in our main entrypoint of our pyramid project and use a NullPool and leave connection pooling to pgbouncer. We also configure the session and the bind for our BaseModel here: engine = engine_from_config(config.registry.settings, 'sqlalchemy.', poolclass=NullPool) db_session.configure(bind=engine, query_cls=FilterQuery) BaseModel.metadata.bind = engine config.add_subscriber(cleanup_db_session, NewResponse) return config.make_wsgi_app() - In our app we access all db operation using: from project.db import db_session ... db_session.query(MyModel).filter(...) db_session.execute(...) - We use psycopg2==2.5.2 to handle the connection to postgres with pgbouncer in between - I made sure no references to db_session or connections are saved anywhere (which could result in other threads reusing them) I also tried the spamming test using different webservers, using waitress and cogen i got the errors very easily, using wsgiref we unsurprisingly have no errors (which is singlethreaded). Using uwsgi and gunicorn (4 workers, gevent) i didn't get any errors. Given the differences in the webserver used, I thought it either has to do with some webservers handling requests in threads and some using new processes (maybe a forking problem)? But to complicate matters even more, when time went on and i did some new tests, the problem had gone away in waitress but now happened with gunicorn (when using gevent)! I have no clue on how to go debugging this... Finally, to test what happens to the connection, i attached an attribute to the connection at the start of the cursor execute and tried to read the attribute out at the end of the execute: @event.listens_for(Engine, before_cursor_execute) def _before_cursor_execute(conn, cursor, stmt, params, context, execmany): conn.pdtb_start_timer = time.time() @event.listens_for(Engine, after_cursor_execute) def _after_cursor_execute(conn, cursor, stmt, params, context, execmany): print conn.pdtb_start_timer Surprisingly this sometimes raised an exception: 'Connection' object has no attribute 'pdtb_start_timer' Which struck me as very strange.. I found one discussion about something similar: https://groups.google.com/d/msg/sqlalchemy/GQZSjHAGkWM/rDflJvuyWnEJ And tried adding
[sqlalchemy] Re: SQLAlchemy connection errors
1. I had a concern a few months ago when doing an audit of code. I ended up not having an issue, but this link might be of help: https://groups.google.com/d/topic/sqlalchemy/Z7tMCB_IK1M/discussion 2. I use `add_finished_callback`, which runs unconditionally at the end of every request. request.add_finished_callback(cleanup_db_session) Actually, I think this may be caused or exacerbated by your use of `NewRespsone`. I think that event can be triggered multiple times during a single request, and if you generate multiple response objects, only the first one would have an active connection. -- 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.
[sqlalchemy] Re: Choosing pattern for vertical table like mapping.
So I am trying to do the following:
class SimpleModel(object):
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
class GroupEntityAttributes(SimpleModel, db.Model):
__table_args__ = {'extend_existing': True}
__tablename__ = 'group_entity'
#Table Columns
id = db.Column(db.Integer, primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('groups.id'), nullable=
False)
entity_id = db.Column(db.Integer, db.ForeignKey('entities.id'), nullable
=False)
key = db.Column(db.String)
value = db.Column(db.Text)
#Relationships
entity = db.relationship('Entity', lazy='joined')
group = db.relationship('Group')
class GroupEntityAttributesPoly(GroupEntityAttributes):
__mapper_args__ = {'polymorphic_on': GroupEntityAttributes.key}
class GroupEntity(GroupEntityAttributesPoly):
__mapper_args__ = {'polymorphic_identity': '_has_connection'}
others = db.relationship(
'GroupEntityAttributes',
primaryjoin=and_(
remote(foreign(GroupEntityAttributes.group_id)) ==
GroupEntityAttributes.group_id,
remote(foreign(GroupEntityAttributes.entity_id)) ==
GroupEntityAttributes.entity_id,
)
)
When I try to load the model GroupEntity I get an error:
ArgumentError: Can't determine relationship direction for relationship '
GroupEntity.others' - foreign key columns within the join condition are
present in both the parent and the child's mapped tables. Ensure that only
those columns referring to a parent column are marked as foreign, either
via the foreign() annotation or via the foreign_keys argument.
I am not sure how to overcome this error. Would appreciate any help.
Best,
Anton.
On Tuesday, September 9, 2014 1:10:40 PM UTC-7, Anton wrote:
Trying to use with multiple table mapping.
On Tuesday, September 9, 2014 1:03:25 PM UTC-7, Anton wrote:
Hi,
I need some help choosing the right pattern for the models I have. I have
the following tables:
Entity (INT id, VARCHAR name)
Group (INT id, VARCHAT name)
GroupEntityAttributes(INT entity_id, INT group_id, VARCHAR key, VARCHAR
name)
Entity and Group models are pretty straight forward. But
GroupEntityAttributes represents Entity within some group with custom
attributes added, so it resembles this pattern (
https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master
), the only difference is that there is an additional foreign key.
I wonder if there is any approach I can use to adapt vertical pattern for
my needs without modifying tables.
Best,
Anton.
--
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] Choosing pattern for vertical table like mapping.
On Sep 10, 2014, at 5:11 PM, Anton anschat...@gmail.com wrote:
So I am trying to do the following:
class SimpleModel(object):
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
class GroupEntityAttributes(SimpleModel, db.Model):
__table_args__ = {'extend_existing': True}
__tablename__ = 'group_entity'
#Table Columns
id = db.Column(db.Integer, primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('groups.id'),
nullable=False)
entity_id = db.Column(db.Integer, db.ForeignKey('entities.id'),
nullable=False)
key = db.Column(db.String)
value = db.Column(db.Text)
#Relationships
entity = db.relationship('Entity', lazy='joined')
group = db.relationship('Group')
class GroupEntityAttributesPoly(GroupEntityAttributes):
__mapper_args__ = {'polymorphic_on': GroupEntityAttributes.key}
class GroupEntity(GroupEntityAttributesPoly):
__mapper_args__ = {'polymorphic_identity': '_has_connection'}
others = db.relationship(
'GroupEntityAttributes',
primaryjoin=and_(
remote(foreign(GroupEntityAttributes.group_id)) ==
GroupEntityAttributes.group_id,
remote(foreign(GroupEntityAttributes.entity_id)) ==
GroupEntityAttributes.entity_id,
)
)
What's illustrated here doesn't really make sense. If
GroupEntityAttributes.group_id referred to groups.id, then a relationship
extending from GroupEntity would need to refer to whatever class is mapped to
the entities table, which I don't see here. It looks like you're taking
foreign key columns on the group_entity table and somehow pointing it to
itself in that primaryjoin which doesn't make sense. GroupEntity would need
to be mapped to either entities or groups in order to refer to
GroupEntityAttributes using that pattern and this sort of looks like the
association object pattern, see
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
for an example of what that looks like.
Other things that you probably don't want:
1. polymorphic_on on a subclass, that should always go on the mapped class at
the bottom of the hierarchy, see
http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#joined-table-inheritance
for basic examples
2. extend_existing, this is usually only relevant when you're using reflection,
otherwise it probably means you're setting things up incorrectly
When I try to load the model GroupEntity I get an error:
ArgumentError: Can't determine relationship direction for relationship
'GroupEntity.others' - foreign key columns within the join condition are
present in both the parent and the child's mapped tables. Ensure that only
those columns referring to a parent column are marked as foreign, either via
the foreign() annotation or via the foreign_keys argument.
I am not sure how to overcome this error. Would appreciate any help.
Best,
Anton.
On Tuesday, September 9, 2014 1:10:40 PM UTC-7, Anton wrote:
Trying to use with multiple table mapping.
On Tuesday, September 9, 2014 1:03:25 PM UTC-7, Anton wrote:
Hi,
I need some help choosing the right pattern for the models I have. I have the
following tables:
Entity (INT id, VARCHAR name)
Group (INT id, VARCHAT name)
GroupEntityAttributes(INT entity_id, INT group_id, VARCHAR key, VARCHAR name)
Entity and Group models are pretty straight forward. But
GroupEntityAttributes represents Entity within some group with custom
attributes added, so it resembles this pattern (
https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master
), the only difference is that there is an additional foreign key.
I wonder if there is any approach I can use to adapt vertical pattern for my
needs without modifying tables.
Best,
Anton.
--
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] Choosing pattern for vertical table like mapping.
Hi Mike,
I have two models that I did not provide in the example: Entity and Group.
Every group can assign some customer attributes to an entity, this is
reflected in GroupEntityAttributes.
Let's say GroupEntityAttributes(group_id=1, entity_id=1, key=color,
value=red) would mean that Group 1 assigned a customer attribute color
with a value red to
the entity with id 1. So when I want to load an entity for a group I want
to load the entity object plus all the custom attributes assigned to the
entity but the group.
Also I need some mechanism mark an entity as loadable for the group, for
this I am using a record like
GroupEntityAttributes(group_id=1, entity_id=1, key=_has_connection,
value=Null), so *key* *_*has_connection** has a special meaning. If it
presents, then the entity should be loaded for the group with all other
custom attributes, otherwise no. For this purpose I created a model
GroupEntity.
So this way GroupEntity.query.filter_by(group_id=1).all() will give a list
with all entities loadable fir the Group 1. Every item of the list will
have an association to Group object and Entity object, I want *others*
property to represent a list of customer attributes, that I would like to
implement as described here
(https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master).
For this I need to have a self referencing relationship to
GroupEntityAttributes,
which appears to be self-referencing because two models use the same table.
SimpleModel is just a mixin that I use in every model I create to make sure
that they all have a unique id.
Thanks,
Anton.
On Wednesday, September 10, 2014 3:00:39 PM UTC-7, Michael Bayer wrote:
On Sep 10, 2014, at 5:11 PM, Anton ansch...@gmail.com javascript:
wrote:
So I am trying to do the following:
class SimpleModel(object):
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
class GroupEntityAttributes(SimpleModel, db.Model):
__table_args__ = {'extend_existing': True}
__tablename__ = 'group_entity'
#Table Columns
id = db.Column(db.Integer, primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('groups.id'), nullable=
False)
entity_id = db.Column(db.Integer, db.ForeignKey('entities.id'),
nullable=False)
key = db.Column(db.String)
value = db.Column(db.Text)
#Relationships
entity = db.relationship('Entity', lazy='joined')
group = db.relationship('Group')
class GroupEntityAttributesPoly(GroupEntityAttributes):
__mapper_args__ = {'polymorphic_on': GroupEntityAttributes.key}
class GroupEntity(GroupEntityAttributesPoly):
__mapper_args__ = {'polymorphic_identity': '_has_connection'}
others = db.relationship(
'GroupEntityAttributes',
primaryjoin=and_(
remote(foreign(GroupEntityAttributes.group_id)) ==
GroupEntityAttributes.group_id,
remote(foreign(GroupEntityAttributes.entity_id)) ==
GroupEntityAttributes.entity_id,
)
)
What’s illustrated here doesn’t really make sense. If
GroupEntityAttributes.group_id referred to “groups.id”, then a
relationship extending from GroupEntity would need to refer to whatever
class is mapped to the “entities” table, which I don’t see here. It looks
like you’re taking foreign key columns on the “group_entity” table and
somehow pointing it to itself in that primaryjoin which doesn’t make sense.
GroupEntity would need to be mapped to either “entities” or “groups” in
order to refer to GroupEntityAttributes using that pattern and this sort of
looks like the association object pattern, see
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
for an example of what that looks like.
Other things that you probably don’t want:
1. polymorphic_on on a subclass, that should always go on the mapped class
at the bottom of the hierarchy, see
http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#joined-table-inheritance
for basic examples
2. extend_existing, this is usually only relevant when you’re using
reflection, otherwise it probably means you’re setting things up
incorrectly
When I try to load the model GroupEntity I get an error:
ArgumentError: Can't determine relationship direction for relationship '
GroupEntity.others' - foreign key columns within the join condition are
present in both the parent and the child's mapped tables. Ensure that only
those columns referring to a parent column are marked as foreign, either
via the foreign() annotation or via the foreign_keys argument.
I am not sure how to overcome this error. Would appreciate any help.
Best,
Anton.
On Tuesday, September 9, 2014 1:10:40 PM UTC-7, Anton wrote:
Trying to use with multiple table mapping.
On Tuesday, September 9, 2014 1:03:25 PM UTC-7, Anton wrote:
Hi,
I need some help
[sqlalchemy] does anyone have a trick for printing parenthesis on complex queries?
SqlAlchemy's formatting is sometimes getting in the way while debugging complex queries Does anyone have a trick to explicitly print parenthesis in queries? That would make some of these queries easier to follow and/or regex for example: sqlalchemy generates stuff like this: x OR y AND z but we want to see: x OR (y AND z) -- 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] Choosing pattern for vertical table like mapping.
hi Anton -
if you can show me correct table designs, e.g. as simple CREATE TABLE
statements, that definitely do the thing you need, I can show you how to map to
it. So far it's not clear if you're looking for schema design via SQLAlchemy's
API.
- mike
On Sep 10, 2014, at 6:36 PM, Anton anschat...@gmail.com wrote:
Hi Mike,
I have two models that I did not provide in the example: Entity and Group.
Every group can assign some customer attributes to an entity, this is
reflected in GroupEntityAttributes.
Let's say GroupEntityAttributes(group_id=1, entity_id=1, key=color,
value=red) would mean that Group 1 assigned a customer attribute color
with a value red to
the entity with id 1. So when I want to load an entity for a group I want to
load the entity object plus all the custom attributes assigned to the entity
but the group.
Also I need some mechanism mark an entity as loadable for the group, for this
I am using a record like
GroupEntityAttributes(group_id=1, entity_id=1, key=_has_connection,
value=Null), so key _has_connection has a special meaning. If it presents,
then the entity should be loaded for the group with all other custom
attributes, otherwise no. For this purpose I created a model GroupEntity.
So this way GroupEntity.query.filter_by(group_id=1).all() will give a list
with all entities loadable fir the Group 1. Every item of the list will have
an association to Group object and Entity object, I want others property to
represent a list of customer attributes, that I would like to implement as
described here
(https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master).
For this I need to have a self referencing relationship to
GroupEntityAttributes, which appears to be self-referencing because two
models use the same table.
SimpleModel is just a mixin that I use in every model I create to make sure
that they all have a unique id.
Thanks,
Anton.
On Wednesday, September 10, 2014 3:00:39 PM UTC-7, Michael Bayer wrote:
On Sep 10, 2014, at 5:11 PM, Anton ansch...@gmail.com wrote:
So I am trying to do the following:
class SimpleModel(object):
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
class GroupEntityAttributes(SimpleModel, db.Model):
__table_args__ = {'extend_existing': True}
__tablename__ = 'group_entity'
#Table Columns
id = db.Column(db.Integer, primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('groups.id'),
nullable=False)
entity_id = db.Column(db.Integer, db.ForeignKey('entities.id'),
nullable=False)
key = db.Column(db.String)
value = db.Column(db.Text)
#Relationships
entity = db.relationship('Entity', lazy='joined')
group = db.relationship('Group')
class GroupEntityAttributesPoly(GroupEntityAttributes):
__mapper_args__ = {'polymorphic_on': GroupEntityAttributes.key}
class GroupEntity(GroupEntityAttributesPoly):
__mapper_args__ = {'polymorphic_identity': '_has_connection'}
others = db.relationship(
'GroupEntityAttributes',
primaryjoin=and_(
remote(foreign(GroupEntityAttributes.group_id)) ==
GroupEntityAttributes.group_id,
remote(foreign(GroupEntityAttributes.entity_id)) ==
GroupEntityAttributes.entity_id,
)
)
What's illustrated here doesn't really make sense. If
GroupEntityAttributes.group_id referred to groups.id, then a relationship
extending from GroupEntity would need to refer to whatever class is mapped to
the entities table, which I don't see here. It looks like you're taking
foreign key columns on the group_entity table and somehow pointing it to
itself in that primaryjoin which doesn't make sense. GroupEntity would need
to be mapped to either entities or groups in order to refer to
GroupEntityAttributes using that pattern and this sort of looks like the
association object pattern, see
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
for an example of what that looks like.
Other things that you probably don't want:
1. polymorphic_on on a subclass, that should always go on the mapped class at
the bottom of the hierarchy, see
http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#joined-table-inheritance
for basic examples
2. extend_existing, this is usually only relevant when you're using
reflection, otherwise it probably means you're setting things up incorrectly
When I try to load the model GroupEntity I get an error:
ArgumentError: Can't determine relationship direction for relationship
'GroupEntity.others' - foreign key columns within the join condition are
present in both the parent and the child's mapped tables. Ensure that only
those columns referring to a parent column are marked as foreign, either via
the
Re: [sqlalchemy] does anyone have a trick for printing parenthesis on complex queries?
might be tricky, there's self_group() and Grouping(), you can probably use the latter explicitly. On Sep 10, 2014, at 6:46 PM, Jonathan Vanasco jonat...@findmeon.com wrote: SqlAlchemy's formatting is sometimes getting in the way while debugging complex queries Does anyone have a trick to explicitly print parenthesis in queries? That would make some of these queries easier to follow and/or regex for example: sqlalchemy generates stuff like this: x OR y AND z but we want to see: x OR (y AND z) -- 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] Choosing pattern for vertical table like mapping.
Mike, the tables are already there, I am trying to create models mapped on
the tables so it is easier to work with.
Tables design looks like:
CREATE TABLE group (
id integer NOT NULL,
name character varying
);
CREATE TABLE entity (
id integer NOT NULL,
name character varying
);
CREATE TABLE group_entity (
id integer NOT NULL,
entity_id integer,
key character varying,
value text,
group_id integer NOT NULL
);
Best,
Anton.
On Wednesday, September 10, 2014 4:51:54 PM UTC-7, Michael Bayer wrote:
hi Anton -
if you can show me correct table designs, e.g. as simple CREATE TABLE
statements, that definitely do the thing you need, I can show you how to
map to it. So far it’s not clear if you’re looking for schema design via
SQLAlchemy’s API.
- mike
On Sep 10, 2014, at 6:36 PM, Anton ansch...@gmail.com javascript:
wrote:
Hi Mike,
I have two models that I did not provide in the example: Entity and Group.
Every group can assign some customer attributes to an entity, this is
reflected in GroupEntityAttributes.
Let's say GroupEntityAttributes(group_id=1, entity_id=1, key=color,
value=red) would mean that Group 1 assigned a customer attribute color
with a value red to
the entity with id 1. So when I want to load an entity for a group I want
to load the entity object plus all the custom attributes assigned to the
entity but the group.
Also I need some mechanism mark an entity as loadable for the group, for
this I am using a record like
GroupEntityAttributes(group_id=1, entity_id=1, key=_has_connection,
value=Null), so *key* *_*has_connection** has a special meaning. If it
presents, then the entity should be loaded for the group with all other
custom attributes, otherwise no. For this purpose I created a model
GroupEntity.
So this way GroupEntity.query.filter_by(group_id=1).all() will give a list
with all entities loadable fir the Group 1. Every item of the list will
have an association to Group object and Entity object, I want *others*
property to represent a list of customer attributes, that I would like to
implement as described here (
https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master).
For this I need to have a self referencing relationship to
GroupEntityAttributes,
which appears to be self-referencing because two models use the same table.
SimpleModel is just a mixin that I use in every model I create to make
sure that they all have a unique id.
Thanks,
Anton.
On Wednesday, September 10, 2014 3:00:39 PM UTC-7, Michael Bayer wrote:
On Sep 10, 2014, at 5:11 PM, Anton ansch...@gmail.com wrote:
So I am trying to do the following:
class SimpleModel(object):
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
class GroupEntityAttributes(SimpleModel, db.Model):
__table_args__ = {'extend_existing': True}
__tablename__ = 'group_entity'
#Table Columns
id = db.Column(db.Integer, primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('groups.id'), nullable
=False)
entity_id = db.Column(db.Integer, db.ForeignKey('entities.id'),
nullable=False)
key = db.Column(db.String)
value = db.Column(db.Text)
#Relationships
entity = db.relationship('Entity', lazy='joined')
group = db.relationship('Group')
class GroupEntityAttributesPoly(GroupEntityAttributes):
__mapper_args__ = {'polymorphic_on': GroupEntityAttributes.key}
class GroupEntity(GroupEntityAttributesPoly):
__mapper_args__ = {'polymorphic_identity': '_has_connection'}
others = db.relationship(
'GroupEntityAttributes',
primaryjoin=and_(
remote(foreign(GroupEntityAttributes.group_id)) ==
GroupEntityAttributes.group_id,
remote(foreign(GroupEntityAttributes.entity_id)) ==
GroupEntityAttributes.entity_id,
)
)
What’s illustrated here doesn’t really make sense. If
GroupEntityAttributes.group_id referred to “groups.id”, then a
relationship extending from GroupEntity would need to refer to whatever
class is mapped to the “entities” table, which I don’t see here. It looks
like you’re taking foreign key columns on the “group_entity” table and
somehow pointing it to itself in that primaryjoin which doesn’t make sense.
GroupEntity would need to be mapped to either “entities” or “groups” in
order to refer to GroupEntityAttributes using that pattern and this sort of
looks like the association object pattern, see
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
for an example of what that looks like.
Other things that you probably don’t want:
1. polymorphic_on on a subclass, that should always go on the mapped
class at the bottom of the hierarchy, see
Re: [sqlalchemy] Choosing pattern for vertical table like mapping.
I already have models for group, entity and group_entity. Now I am trying
to build a model that would represent an entity for a group with all the
custom attributes.
On Wednesday, September 10, 2014 5:06:36 PM UTC-7, Anton wrote:
Mike, the tables are already there, I am trying to create models mapped on
the tables so it is easier to work with.
Tables design looks like:
CREATE TABLE group (
id integer NOT NULL,
name character varying
);
CREATE TABLE entity (
id integer NOT NULL,
name character varying
);
CREATE TABLE group_entity (
id integer NOT NULL,
entity_id integer,
key character varying,
value text,
group_id integer NOT NULL
);
Best,
Anton.
On Wednesday, September 10, 2014 4:51:54 PM UTC-7, Michael Bayer wrote:
hi Anton -
if you can show me correct table designs, e.g. as simple CREATE TABLE
statements, that definitely do the thing you need, I can show you how to
map to it. So far it’s not clear if you’re looking for schema design via
SQLAlchemy’s API.
- mike
On Sep 10, 2014, at 6:36 PM, Anton ansch...@gmail.com wrote:
Hi Mike,
I have two models that I did not provide in the example: Entity and Group.
Every group can assign some customer attributes to an entity, this is
reflected in GroupEntityAttributes.
Let's say GroupEntityAttributes(group_id=1, entity_id=1, key=color,
value=red) would mean that Group 1 assigned a customer attribute color
with a value red to
the entity with id 1. So when I want to load an entity for a group I want
to load the entity object plus all the custom attributes assigned to the
entity but the group.
Also I need some mechanism mark an entity as loadable for the group, for
this I am using a record like
GroupEntityAttributes(group_id=1, entity_id=1, key=_has_connection,
value=Null), so *key* *_*has_connection** has a special meaning. If it
presents, then the entity should be loaded for the group with all other
custom attributes, otherwise no. For this purpose I created a model
GroupEntity.
So this way GroupEntity.query.filter_by(group_id=1).all() will give a
list with all entities loadable fir the Group 1. Every item of the list
will have an association to Group object and Entity object, I want
*others* property to represent a list of customer attributes, that I
would like to implement as described here (
https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master).
For this I need to have a self referencing relationship to
GroupEntityAttributes,
which appears to be self-referencing because two models use the same table.
SimpleModel is just a mixin that I use in every model I create to make
sure that they all have a unique id.
Thanks,
Anton.
On Wednesday, September 10, 2014 3:00:39 PM UTC-7, Michael Bayer wrote:
On Sep 10, 2014, at 5:11 PM, Anton ansch...@gmail.com wrote:
So I am trying to do the following:
class SimpleModel(object):
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
class GroupEntityAttributes(SimpleModel, db.Model):
__table_args__ = {'extend_existing': True}
__tablename__ = 'group_entity'
#Table Columns
id = db.Column(db.Integer, primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('groups.id'),
nullable=False)
entity_id = db.Column(db.Integer, db.ForeignKey('entities.id'),
nullable=False)
key = db.Column(db.String)
value = db.Column(db.Text)
#Relationships
entity = db.relationship('Entity', lazy='joined')
group = db.relationship('Group')
class GroupEntityAttributesPoly(GroupEntityAttributes):
__mapper_args__ = {'polymorphic_on': GroupEntityAttributes.key}
class GroupEntity(GroupEntityAttributesPoly):
__mapper_args__ = {'polymorphic_identity': '_has_connection'}
others = db.relationship(
'GroupEntityAttributes',
primaryjoin=and_(
remote(foreign(GroupEntityAttributes.group_id)) ==
GroupEntityAttributes.group_id,
remote(foreign(GroupEntityAttributes.entity_id)) ==
GroupEntityAttributes.entity_id,
)
)
What’s illustrated here doesn’t really make sense. If
GroupEntityAttributes.group_id referred to “groups.id”, then a
relationship extending from GroupEntity would need to refer to whatever
class is mapped to the “entities” table, which I don’t see here. It looks
like you’re taking foreign key columns on the “group_entity” table and
somehow pointing it to itself in that primaryjoin which doesn’t make sense.
GroupEntity would need to be mapped to either “entities” or “groups” in
order to refer to GroupEntityAttributes using that pattern and this sort of
looks like the association object pattern, see
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
for an example of what that looks like.
Re: [sqlalchemy] Choosing pattern for vertical table like mapping.
OK, just three tables, then just three mapped classes, that's it. The rest of
the stuff you're trying to do has to be done using standard Python techniques;
classes that implement special __getitem__() / __setitem__(), etc. Here's a
start, does just the get side so far:
from sqlalchemy import Integer, ForeignKey, create_engine, String, Column
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Group(Base):
__tablename__ = 'group'
id = Column(Integer, primary_key=True)
name = Column(String)
class GroupGetter(object):
def __init__(self, entity):
self.entity = entity
def __getitem__(self, key):
# TODO: probably want to cache this
return GroupItemGetter(self, key)
class GroupItemGetter(object):
def __init__(self, groupgetter, groupname):
self.entity = groupgetter.entity
self.groupname = groupname
self.data = dict(
(group_entity.key, group_entity.value)
for group_entity in self.entity._group_entities
if group_entity.group.name == self.groupname
)
def __getitem__(self, key):
return self.data[key]
def __setitem__(self, key, value):
raise NotImplementedError(an exercise)
class Entity(Base):
__tablename__ = 'entity'
id = Column(Integer, primary_key=True)
name = Column(String)
_group_entities = relationship(GroupEntity)
@property
def groups(self):
# TODO: probably want to cache this
return GroupGetter(self)
class GroupEntity(Base):
__tablename__ = 'group_entity'
id = Column(Integer, primary_key=True)
entity_id = Column(ForeignKey('entity.id'))
group_id = Column(ForeignKey('group.id'))
key = Column(String)
value = Column(String)
group = relationship(Group)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
ent = Entity(name=some customer)
g1, g2 = Group(name='g1'), Group(name='g2')
# setter side is raw so far
ent._group_entities.append(GroupEntity(group=g1, key='color', value='red'))
ent._group_entities.append(GroupEntity(group=g2, key='color', value='polkadot'))
sess.add(ent)
sess.commit()
# but here's the getter side
print ent.groups['g1']['color']
On Sep 10, 2014, at 8:08 PM, Anton anschat...@gmail.com wrote:
I already have models for group, entity and group_entity. Now I am trying to
build a model that would represent an entity for a group with all the custom
attributes.
On Wednesday, September 10, 2014 5:06:36 PM UTC-7, Anton wrote:
Mike, the tables are already there, I am trying to create models mapped on
the tables so it is easier to work with.
Tables design looks like:
CREATE TABLE group (
id integer NOT NULL,
name character varying
);
CREATE TABLE entity (
id integer NOT NULL,
name character varying
);
CREATE TABLE group_entity (
id integer NOT NULL,
entity_id integer,
key character varying,
value text,
group_id integer NOT NULL
);
Best,
Anton.
On Wednesday, September 10, 2014 4:51:54 PM UTC-7, Michael Bayer wrote:
hi Anton -
if you can show me correct table designs, e.g. as simple CREATE TABLE
statements, that definitely do the thing you need, I can show you how to map
to it. So far it's not clear if you're looking for schema design via
SQLAlchemy's API.
- mike
On Sep 10, 2014, at 6:36 PM, Anton ansch...@gmail.com wrote:
Hi Mike,
I have two models that I did not provide in the example: Entity and Group.
Every group can assign some customer attributes to an entity, this is
reflected in GroupEntityAttributes.
Let's say GroupEntityAttributes(group_id=1, entity_id=1, key=color,
value=red) would mean that Group 1 assigned a customer attribute color
with a value red to
the entity with id 1. So when I want to load an entity for a group I want to
load the entity object plus all the custom attributes assigned to the entity
but the group.
Also I need some mechanism mark an entity as loadable for the group, for
this I am using a record like
GroupEntityAttributes(group_id=1, entity_id=1, key=_has_connection,
value=Null), so key _has_connection has a special meaning. If it presents,
then the entity should be loaded for the group with all other custom
attributes, otherwise no. For this purpose I created a model GroupEntity.
So this way GroupEntity.query.filter_by(group_id=1).all() will give a list
with all entities loadable fir the Group 1. Every item of the list will have
an association to Group object and Entity object, I want others property to
represent a list of customer attributes, that I would like to implement as
described here
(https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master).
For this I need to have a self referencing
Re: [sqlalchemy] too many SQL variables in in_
Hi, if I create many or request with a little number of id (list populated with 10 values), the script return to me this message: Expression tree is too large (maximum depth 1000) So, It's possibile there is a limit for sqlite? This is a big problem for using sqlalchemy/sqlite. I cannot linking many selection because I want to order the query, so I suppose I must to do a single select query. It's correct? Best regards and thanks a lot Luca -- 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.
