Re: [sqlalchemy] single table hierarchy: disable polymorphism and load all as base class
Hi!
I've finally made some progress :)
The following code works for the most of my needs:
Dependency and JobModel define the columns in the DB.
AbstractJob is the common ancestor in the hierarchy. It also defines the
common API.
Job is the polymorphic version, and HelloJob and ByeJob are subclasses
defined in external code that may not be available at runtime.
GenericJob is the non-polimorphic version. It allows to optionally select
entities when you do not want polymorphism.
Base = declarative_base()
class Dependency(Base):
__tablename__ = 'dependency'
parent_id = Column('parent_id', Integer, nullable=False)
child_id = Column('child_id', Integer, nullable=False)
__table_args__ = (
# Primary key
PrimaryKeyConstraint('parent_id', 'child_id'),
# Foreign keys
ForeignKeyConstraint(['parent_id'], ['job.id'],
onupdate='CASCADE', ondelete='CASCADE'),
ForeignKeyConstraint(['child_id'], ['job.id'],
onupdate='CASCADE', ondelete='CASCADE'),
)
class JobModel(object):
__tablename__ = 'job'
__table_args__ = (
# Primary key
PrimaryKeyConstraint('id'),
# Do not let subclasses redefine the model
{'keep_existing' : True}
)
id = Column('id', Integer, nullable=False)
name = Column('name', String(50), nullable=False)
type = Column('type', String(20), nullable=False)
class AbstractJob(Base, JobModel):
__abstract__ = True
def do_something(self):
pass
class Job(AbstractJob):
__mapper_args__ = {
'polymorphic_on' : AbstractJob.type,
'polymorphic_identity': None,
}
parents = relationship('Job',
back_populates = 'children', secondary = 'dependency',
primaryjoin = 'Dependency.child_id == Job.id',
secondaryjoin= 'Job.id == Dependency.parent_id')
children = relationship('Job',
back_populates = 'parents', secondary = 'dependency',
primaryjoin = 'Dependency.parent_id == Job.id',
secondaryjoin= 'Job.id == Dependency.child_id')
class HelloJob(Job):
__mapper_args__ = {
'polymorphic_identity':'hello'
}
def do_something(self):
print Hello!
class ByeJob(Job):
__mapper_args__ = {
'polymorphic_identity':'bye'
}
def do_something(self):
print Bye!
class GenericJob(AbstractJob):
parents = relationship('GenericJob',
back_populates = 'children', secondary = 'dependency',
primaryjoin = 'Dependency.child_id == GenericJob.id',
secondaryjoin= 'GenericJob.id == Dependency.parent_id')
children = relationship('GenericJob',
back_populates = 'parents', secondary = 'dependency',
primaryjoin = 'Dependency.parent_id == GenericJob.id',
secondaryjoin= 'GenericJob.id == Dependency.child_id')
But the are still a few rough edges :/
- I cannot change the name of the attributes in JobModel. If I change
the name attribute to _name, it fails with:
ArgumentError: When configuring property '_name' on
Mapper|GenericJob|job, column 'name' is not represented in the
mapper's table. Use the `column_property()` function to force this
column to be mapped as a read-only attribute.This error dissapears
when commenting either Job or GenericJob.
- GenericJob definition must go AFTER Job definition, or it fails with:
InvalidRequestError: Could not map polymorphic_on column 'type' to
the mapped table - polymorphic loads will not function properly
I'm near the solution :P
Thanks!
Pau.
2014-03-24 11:23 GMT+01:00 Pau Tallada tall...@pic.es:
No, polymorphic_on=None does not have any effect :(
I'll keep trying :P
Thanks!
Pau.
2014-03-17 18:56 GMT+01:00 Michael Bayer mike...@zzzcomputing.com:
er, guessing, polymorphic_on=None also for that class? not sure of the
mapper’s tolerance.
On Mar 17, 2014, at 12:10 PM, Pau Tallada tall...@pic.es wrote:
Hi, I tried this, but then it selects WHERE table.type IN (NULL) :(
class NonPolymorphicClass(MyBaseClass):
__mapper_args__ = {
'concrete' : True
}
I'll try some more things, like overriding the __new__ method on the base
class, to create subclasses if they are available, and instances of the
base class if not.
Thank you very much anyway :)
Pau.
2014-03-17 15:44 GMT+01:00 Michael Bayer mike...@zzzcomputing.com:
maybe make a subclass of the polymorphic base, and just put __concrete__
= True in the mapper_args.skip the non primary part.
On Mar 17, 2014, at 8:45 AM, Pau Tallada tall...@pic.es wrote:
Hi,
Can it be done in declarative?
I've tried several ways, but I cannot find a working one :/
Basically, I think I need to redefine some self-referential
relationships, as they link against the polymorphic class.
Meta = declarative_meta()
class NonPolymorphicClass(MyBaseClass):
__mapper_args__ = {
'non_primary' : True
}
== ArgumentError:
Re: [sqlalchemy] What is the rationale of having to manually set up a relationship between two tables?
I would say that the extension is intended to replace SqlSoup in the
future, but may not be ready for that quite yet.
Simon
On Tue, Mar 25, 2014 at 12:39 AM, Bao Niu niuba...@gmail.com wrote:
Compare to SQLsoup, is this extension more recommended?
On Mon, Mar 24, 2014 at 3:32 AM, Simon King si...@simonking.org.uk wrote:
On Mon, Mar 24, 2014 at 6:48 AM, Bao Niu niuba...@gmail.com wrote:
Suppose we have two tables in an existing database, user and
address.
There is a one-to-many relationships between these two tables with a
foreign
key user.id==address_user_id.
Now we *reflect* this schema directly from the database:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(E)
Class User(Base):
__tablename__ = 'user'
__table_args__ = {'autoload': True}
addresses = sqlalchemy.orm.relationship(Address, backref=user)
Class Address(Base):
__tablename__ = 'address'
__table_args__ = {'autoload': True}
To me, it is a mystery why the almighty SA can autoload everything, but
not
an obvious relationship (in red ink). What benefit is it for the user to
manually define such a simple and unambiguous relationship? Can't it be
automatically done, i.e., leaving out the red part?
Will this feature likely be included in future versions, say, sqlalchemy
1.0?
You might be interested in the experimental automap extension:
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html
Hope that helps,
Simon
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/_q1tBBfTh0w/unsubscribe.
To unsubscribe from this group and all its topics, 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.
--
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] joinedload()
Ah, so, it turns out to be more subtle than I first thought. It took me
quite a while to narrow it down to an easily reproducible case. To trigger
the behavior you need to be: joinedloading() along a backref, and also I
think it matters that I am joining back onto the same table and returning a
bunch of objects. Quite a corner case I think.
This code demonstrates the behavior - it issues a second query in the for
loop for the 'A' which has no child.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
join_table = Table('parent_child', Base.metadata,
Column('id_a', ForeignKey('a.id')),
Column('id_b', ForeignKey('a.id')))
parent = relationship(A,
secondary=join_table,
primaryjoin = (id == join_table.c.id_a),
secondaryjoin = (id == join_table.c.id_b),
uselist=False,
backref=backref(child, uselist=False))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
a2 = A(parent=a1)
sess.add(a1)
sess.add(a2)
sess.commit()
sess.close()
results = sess.query(A).options(joinedload(child)).all()
print
for a in results:
print a.child is None
On Mon, Mar 24, 2014 at 8:34 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Mar 24, 2014, at 2:09 PM, Philip Scott safetyfirstp...@gmail.com
wrote:
Is this a bug, or perhaps some expected side effect of the joined load?
seemed like something that might be possible but the scalar loader is
initializing the attribute to None to start with, here's a simple test that
doesn't show your behavior, so see if you can just modify this one to show
what you are seeing. note we only need to see that 'bs' is in a1.__dict__
to prevent a lazyload.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B, secondary=Table('atob', Base.metadata,
Column('aid', ForeignKey('a.id')),
Column('bid', ForeignKey('b.id'))
),
uselist=False)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(A())
sess.commit()
sess.close()
a1 = sess.query(A).options(joinedload(bs)).first()
assert 'bs' in a1.__dict__
assert a1.__dict__['bs'] is None
assert a1.bs is None
--
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] joinedload()
On Mar 25, 2014, at 7:48 AM, Philip Scott safetyfirstp...@gmail.com wrote:
Ah, so, it turns out to be more subtle than I first thought. It took me quite
a while to narrow it down to an easily reproducible case. To trigger the
behavior you need to be: joinedloading() along a backref, and also I think it
matters that I am joining back onto the same table and returning a bunch of
objects. Quite a corner case I think.
This code demonstrates the behavior - it issues a second query in the for
loop for the 'A' which has no child.
this is very difficult to resolve and it may have been something I've said was
unfixable in the past. The a2 object here is loaded in two different
contexts, one is as the joined loaded child of a1, the other as a first class
result. Because when the query orders by a1, a2, we hit a1 first, a2 is
necessarily loaded as the child of a1. The joined loading only goes one level
deep, that is, it doesn't load the children of children, unless you told it to
by saying joinedload(child).joinedload(child). So a2.child's loader is
declared as not loaded. then on the next row it comes in as a first class
result, but the a2 object we get there is only an identity map lookup - this
object is already loaded.
Adjusting this behavior would require the loading logic figure out mid-results
that the context for a particular object is changing. pretty complicated.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
join_table = Table('parent_child', Base.metadata,
Column('id_a', ForeignKey('a.id')),
Column('id_b', ForeignKey('a.id')))
parent = relationship(A,
secondary=join_table,
primaryjoin = (id == join_table.c.id_a),
secondaryjoin = (id == join_table.c.id_b),
uselist=False,
backref=backref(child, uselist=False))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
a2 = A(parent=a1)
sess.add(a1)
sess.add(a2)
sess.commit()
sess.close()
results = sess.query(A).options(joinedload(child)).all()
print
for a in results:
print a.child is None
On Mon, Mar 24, 2014 at 8:34 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Mar 24, 2014, at 2:09 PM, Philip Scott safetyfirstp...@gmail.com wrote:
Is this a bug, or perhaps some expected side effect of the joined load?
seemed like something that might be possible but the scalar loader is
initializing the attribute to None to start with, here's a simple test that
doesn't show your behavior, so see if you can just modify this one to show
what you are seeing. note we only need to see that 'bs' is in a1.__dict__ to
prevent a lazyload.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B, secondary=Table('atob', Base.metadata,
Column('aid', ForeignKey('a.id')),
Column('bid', ForeignKey('b.id'))
),
uselist=False)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(A())
sess.commit()
sess.close()
a1 = sess.query(A).options(joinedload(bs)).first()
assert 'bs' in a1.__dict__
assert a1.__dict__['bs'] is None
assert a1.bs is None
--
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.
--
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
Re: [sqlalchemy] joinedload()
I understand, I had a feeling it would be something like that. Don't worry,
I can work around it by using a subqueryload() instead (which I guess fixes
it by changing the order things are loaded?)
Thank you very much for taking the time to answer; still loving SQLAlchemy!
On Tue, Mar 25, 2014 at 1:06 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Mar 25, 2014, at 7:48 AM, Philip Scott safetyfirstp...@gmail.com
wrote:
Ah, so, it turns out to be more subtle than I first thought. It took me
quite a while to narrow it down to an easily reproducible case. To trigger
the behavior you need to be: joinedloading() along a backref, and also I
think it matters that I am joining back onto the same table and returning a
bunch of objects. Quite a corner case I think.
This code demonstrates the behavior - it issues a second query in the for
loop for the 'A' which has no child.
this is very difficult to resolve and it may have been something I've said
was unfixable in the past. The a2 object here is loaded in two different
contexts, one is as the joined loaded child of a1, the other as a first
class result. Because when the query orders by a1, a2, we hit a1 first,
a2 is necessarily loaded as the child of a1. The joined loading only goes
one level deep, that is, it doesn't load the children of children, unless
you told it to by saying joinedload(child).joinedload(child). So
a2.child's loader is declared as not loaded. then on the next row it
comes in as a first class result, but the a2 object we get there is only an
identity map lookup - this object is already loaded.
Adjusting this behavior would require the loading logic figure out
mid-results that the context for a particular object is changing. pretty
complicated.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
join_table = Table('parent_child', Base.metadata,
Column('id_a', ForeignKey('a.id')),
Column('id_b', ForeignKey('a.id')))
parent = relationship(A,
secondary=join_table,
primaryjoin = (id == join_table.c.id_a),
secondaryjoin = (id == join_table.c.id_b),
uselist=False,
backref=backref(child, uselist=False))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
a2 = A(parent=a1)
sess.add(a1)
sess.add(a2)
sess.commit()
sess.close()
results = sess.query(A).options(joinedload(child)).all()
print
for a in results:
print a.child is None
On Mon, Mar 24, 2014 at 8:34 PM, Michael Bayer
mike...@zzzcomputing.comwrote:
On Mar 24, 2014, at 2:09 PM, Philip Scott safetyfirstp...@gmail.com
wrote:
Is this a bug, or perhaps some expected side effect of the joined load?
seemed like something that might be possible but the scalar loader is
initializing the attribute to None to start with, here's a simple test that
doesn't show your behavior, so see if you can just modify this one to show
what you are seeing. note we only need to see that 'bs' is in a1.__dict__
to prevent a lazyload.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B, secondary=Table('atob', Base.metadata,
Column('aid', ForeignKey('a.id')),
Column('bid', ForeignKey('b.id'))
),
uselist=False)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(A())
sess.commit()
sess.close()
a1 = sess.query(A).options(joinedload(bs)).first()
assert 'bs' in a1.__dict__
assert a1.__dict__['bs'] is None
assert a1.bs is None
--
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
[sqlalchemy] Best practices for query_property and scoped_session?
Hi, I have some models that use query_property in combinations with subclassed Query objects. I think this is very neat and allows you to define some common filters that can be used for your ORM object. Now this works very well with the currently configured session, but run into limitations if I want to query different databases with the same model in the same application. For that you can ofcourse use: my_session.query(MyObject).filter() but that way I will lose my defined query_property methods like: MyObject.query.my_filter_extended_via_query_property() Is there a way I can still use my defined sessions in combination with query_property ? Or is the use of query_property not recommended? Regards, Yun -- 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] join aliased
OK, thank you On Saturday, March 22, 2014 9:03:01 PM UTC+1, Michael Bayer wrote: On Mar 22, 2014, at 9:16 AM, lars van gemerden la...@rational-it.comjavascript: wrote: query = session.query(Email) query = query.join(user) #or query = query.join(user, aliased = True) query = query.add_columns(Email.email, User.name) the add_columns() method does not have the clause adaptation behavior of filter(), so it does not take into account the fact that “aliased=True” was called when the User entity was first pulled in. so this pulls in the User entity twice, once from User.name, and another because of the aliased(User) brought in by the join. solution is not to use aliased=True (a feature I would never have added if it were today), use ua = aliased(User); q.join(ua, “user”); query.add_columns(ua.name). -- 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] a relationship question
Hi all,
Simple question, but couldn't find it in the docs:
- How can i retrieve the target class in a relationship in the ORM? Say i
have a sqla class with a relationship(User, primaryjoin = ...), how can i
retrieve the User class from the relationship ('relationship' is a
descriptor, right?)
Cheers, Lars
--
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] joinedload()
On Mar 25, 2014, at 12:16 PM, Philip Scott safetyfirstp...@gmail.com wrote:
I understand, I had a feeling it would be something like that. Don't worry, I
can work around it by using a subqueryload() instead (which I guess fixes it
by changing the order things are loaded?)
I'd have to look into it, I'm not sure why offhand.
Thank you very much for taking the time to answer; still loving SQLAlchemy!
On Tue, Mar 25, 2014 at 1:06 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Mar 25, 2014, at 7:48 AM, Philip Scott safetyfirstp...@gmail.com wrote:
Ah, so, it turns out to be more subtle than I first thought. It took me
quite a while to narrow it down to an easily reproducible case. To trigger
the behavior you need to be: joinedloading() along a backref, and also I
think it matters that I am joining back onto the same table and returning a
bunch of objects. Quite a corner case I think.
This code demonstrates the behavior - it issues a second query in the for
loop for the 'A' which has no child.
this is very difficult to resolve and it may have been something I've said
was unfixable in the past. The a2 object here is loaded in two different
contexts, one is as the joined loaded child of a1, the other as a first class
result. Because when the query orders by a1, a2, we hit a1 first, a2 is
necessarily loaded as the child of a1. The joined loading only goes one
level deep, that is, it doesn't load the children of children, unless you
told it to by saying joinedload(child).joinedload(child). So a2.child's
loader is declared as not loaded. then on the next row it comes in as a
first class result, but the a2 object we get there is only an identity map
lookup - this object is already loaded.
Adjusting this behavior would require the loading logic figure out
mid-results that the context for a particular object is changing. pretty
complicated.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
join_table = Table('parent_child', Base.metadata,
Column('id_a', ForeignKey('a.id')),
Column('id_b', ForeignKey('a.id')))
parent = relationship(A,
secondary=join_table,
primaryjoin = (id == join_table.c.id_a),
secondaryjoin = (id == join_table.c.id_b),
uselist=False,
backref=backref(child, uselist=False))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
a2 = A(parent=a1)
sess.add(a1)
sess.add(a2)
sess.commit()
sess.close()
results = sess.query(A).options(joinedload(child)).all()
print
for a in results:
print a.child is None
On Mon, Mar 24, 2014 at 8:34 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Mar 24, 2014, at 2:09 PM, Philip Scott safetyfirstp...@gmail.com wrote:
Is this a bug, or perhaps some expected side effect of the joined load?
seemed like something that might be possible but the scalar loader is
initializing the attribute to None to start with, here's a simple test that
doesn't show your behavior, so see if you can just modify this one to show
what you are seeing. note we only need to see that 'bs' is in a1.__dict__
to prevent a lazyload.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B, secondary=Table('atob', Base.metadata,
Column('aid', ForeignKey('a.id')),
Column('bid', ForeignKey('b.id'))
),
uselist=False)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(A())
sess.commit()
sess.close()
a1 = sess.query(A).options(joinedload(bs)).first()
assert 'bs' in a1.__dict__
assert a1.__dict__['bs'] is None
assert a1.bs is None
--
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] Re: Bulk Inserts and Unique Constraints
I wasn't going to bother, but I had a look at doing this just out of curiosity, and these were the results: executemany(): Inserting 424 entries: 0.3362s Inserting 20,000 segments: 14.01s COPY: Inserting 425 entries: 0.04s Inserting 20,000 segments: 0.3s So a pretty massive boost. Thanks :) On Monday, 24 March 2014 23:30:32 UTC+8, Jonathan Vanasco wrote: Since you're using Postgres... have you considered using python to generate a COPY file ? Sqlalchemy doesn't seem to support it natively... maybe via 'text', but your underlying psycopg2 driver does. it's way way way faster. i've found it significantly faster than dropping fkeys and using prepared statements. -- 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] a relationship question
you can get this with MyClass.user.property.mapper.class_
On Mar 25, 2014, at 12:50 PM, lars van gemerden l...@rational-it.com wrote:
Hi all,
Simple question, but couldn't find it in the docs:
- How can i retrieve the target class in a relationship in the ORM? Say i
have a sqla class with a relationship(User, primaryjoin = ...), how can i
retrieve the User class from the relationship ('relationship' is a
descriptor, right?)
Cheers, Lars
--
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.
[sqlalchemy] Trying to map a big arbitrary query to a class
Hello, I have what seems like a simple problem, but the only solution I
have come up with so far isn't very good. I have a large complex query that
is hand optimized that is built from aggregates and other data from
different mapped tables. I can create a query for it by doing
Session.query([column names]).from_statement(query).params({}) and that
works just fine, but rather than each row being a KeyedTuple, I would like
each row to be mapped to an object of a class, so that I can create some
properties and methods on the object
When I tried
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#mapping-a-class-against-arbitrary-selects
I get an error about not having a primary key defined
I did get this to kind of work with Bundles, but it looks ugly and you have
to dereference the bundle
Here's a gist of the code:
https://gist.github.com/subssn21/0f7300203aa39536ff5d
What's the right way to handle this?
--
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] What is the rationale of having to manually set up a relationship between two tables?
It really helps. I've upgraded to 0.9.3 and it's great!
On Mar 25, 2014 3:35 AM, Simon King si...@simonking.org.uk wrote:
I would say that the extension is intended to replace SqlSoup in the
future, but may not be ready for that quite yet.
Simon
On Tue, Mar 25, 2014 at 12:39 AM, Bao Niu niuba...@gmail.com wrote:
Compare to SQLsoup, is this extension more recommended?
On Mon, Mar 24, 2014 at 3:32 AM, Simon King si...@simonking.org.uk
wrote:
On Mon, Mar 24, 2014 at 6:48 AM, Bao Niu niuba...@gmail.com wrote:
Suppose we have two tables in an existing database, user and
address.
There is a one-to-many relationships between these two tables with a
foreign
key user.id==address_user_id.
Now we *reflect* this schema directly from the database:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(E)
Class User(Base):
__tablename__ = 'user'
__table_args__ = {'autoload': True}
addresses = sqlalchemy.orm.relationship(Address,
backref=user)
Class Address(Base):
__tablename__ = 'address'
__table_args__ = {'autoload': True}
To me, it is a mystery why the almighty SA can autoload everything,
but
not
an obvious relationship (in red ink). What benefit is it for the user
to
manually define such a simple and unambiguous relationship? Can't it
be
automatically done, i.e., leaving out the red part?
Will this feature likely be included in future versions, say,
sqlalchemy
1.0?
You might be interested in the experimental automap extension:
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html
Hope that helps,
Simon
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/_q1tBBfTh0w/unsubscribe.
To unsubscribe from this group and all its topics, 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.
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/_q1tBBfTh0w/unsubscribe.
To unsubscribe from this group and all its topics, 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.
[sqlalchemy] session and thread confusion
I've been reading through the SQLAlchemy docs for a while now and am still fairly confused about using SQLAlchemy with threaded processes. Hopefully my specific question helps me understand things a little more... I'm using the Pyramid framework with SQLAlchemy. The default scaffold for this setup has the following line: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) I understand this essentially sets things up so in my web application I can just do `DBSession.query(...)` and it automatically manages sessions for me based on each web request. There's also some sort of interaction with this and the `pyramid_tm` transaction manager. This is all fine for my regular web application. My problem comes up when I'm trying to create a portion of my application which spawns several threads to listen for other tcp connections and then save results in the database. Right now that portion of the application maintains a 20-thread pool to handle incoming connections. I'm passing the DBSession into the function that spawns the threads and then it's used as a global variable similar to how the rest of the web application uses it. I wanted to find out if this is the proper way of handling this. I think since the sessions are thread-local there isn't any session sharing across threads. However, I'm not sure how the scoped_session() works in these long-running threads. I'm a little concerned that it's opening a transaction and then sitting on it without ever committing... I'm really not sure. Should I change it so my threads instead use something like: with transaction.manager: DBSession.add(SomeORMObject(id=4)) or: with DBSession.begin(): DBSession.add(SomeORMObject(id=4)) Will that commit the transaction and then release the session connection at the end of the context? -- 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] session and thread confusion
On 25 Mar 2014, at 19:05, Tim Tisdall tisd...@gmail.com wrote: I've been reading through the SQLAlchemy docs for a while now and am still fairly confused about using SQLAlchemy with threaded processes. Hopefully my specific question helps me understand things a little more... I'm using the Pyramid framework with SQLAlchemy. The default scaffold for this setup has the following line: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) I understand this essentially sets things up so in my web application I can just do `DBSession.query(...)` and it automatically manages sessions for me based on each web request. There's also some sort of interaction with this and the `pyramid_tm` transaction manager. This is all fine for my regular web application. My problem comes up when I'm trying to create a portion of my application which spawns several threads to listen for other tcp connections and then save results in the database. Right now that portion of the application maintains a 20-thread pool to handle incoming connections. I'm passing the DBSession into the function that spawns the threads and then it's used as a global variable similar to how the rest of the web application uses it. I wanted to find out if this is the proper way of handling this. I think since the sessions are thread-local there isn't any session sharing across threads. However, I'm not sure how the scoped_session() works in these long-running threads. I'm a little concerned that it's opening a transaction and then sitting on it without ever committing... I'm really not sure. Should I change it so my threads instead use something like: with transaction.manager: DBSession.add(SomeORMObject(id=4)) or: with DBSession.begin(): DBSession.add(SomeORMObject(id=4)) Will that commit the transaction and then release the session connection at the end of the context? In your pyramid app, there are a few different packages interacting. First is the transaction package, which provides an abstract idea of a transaction (not necessarily a database transaction) which can be committed or rolled back. Other packages register to be part of the transaction. Second, the zope.sqlalchemy package is the bridge between SQLAlchemy and the transaction. By using the ZopeTransactionExtension in your DBSession, you are ensuring that the transaction package will manage SQLAlchemy transactions. Finally, the pyramid_tm package is the bridge between pyramid and the transaction package. It ensures that a transaction is started at the beginning of a request and committed or rolled back at the end of the request. The important part of pyramid_tm is pretty short and is probably worth looking at: https://github.com/Pylons/pyramid_tm/blob/master/pyramid_tm/__init__.py#L55 To use SQLAlchemy transactions in your thread pool, you can either use the SQLAlchemy APIs directly, or you can use the ones from the transaction package. If you are using anything else that integrates with the transaction package (eg. pyramid_mailer), you'll probably want the second option. Otherwise, it probably doesn't matter much. In your thread pool, you'll want to make sure that each piece of work starts a new transaction, and either commits or rolls back the transaction at the end, then finally closes it. Closing it will return its connection back to the connection pool. It is recommended to keep the session mechanics separate from the main body of your code. So you should probably try to structure your thread pool such that it sets up the session, calls a separate function to do the actual work, then commits or rolls back the transaction as appropriate. This is described at: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it The SA APIs you need are basically Session.begin, Session.commit, Session.rollback and Session.close. The transaction ones are transaction.manager.begin, transaction.manager.commit and transaction.manager.abort. (Note that zope.sqlalchemy will close the session after a commit or abort) Hope that helps, Simon -- 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: session and thread confusion
After putting more logging on it seems that `with transaction.manager` contexts are committing and returning the connection to the connection pool as I'd hoped. However, I began looking into this more because I got a MySQL server has gone away error despite having sqlalchemy.pool_recycle = 14400 set (which I believe is 4hrs and the default is 8hrs on mysql to expire a connection). I read some posts saying I needed to `session.close()` to release the session, but it seems ilke the connections are being returned to the pool. How can I avoid those errors? -- 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] session and thread confusion
Thanks, that does clear up some of the components for me. On Tuesday, 25 March 2014 16:05:26 UTC-4, Simon King wrote: On 25 Mar 2014, at 19:05, Tim Tisdall tis...@gmail.com javascript: wrote: I've been reading through the SQLAlchemy docs for a while now and am still fairly confused about using SQLAlchemy with threaded processes. Hopefully my specific question helps me understand things a little more... I'm using the Pyramid framework with SQLAlchemy. The default scaffold for this setup has the following line: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) I understand this essentially sets things up so in my web application I can just do `DBSession.query(...)` and it automatically manages sessions for me based on each web request. There's also some sort of interaction with this and the `pyramid_tm` transaction manager. This is all fine for my regular web application. My problem comes up when I'm trying to create a portion of my application which spawns several threads to listen for other tcp connections and then save results in the database. Right now that portion of the application maintains a 20-thread pool to handle incoming connections. I'm passing the DBSession into the function that spawns the threads and then it's used as a global variable similar to how the rest of the web application uses it. I wanted to find out if this is the proper way of handling this. I think since the sessions are thread-local there isn't any session sharing across threads. However, I'm not sure how the scoped_session() works in these long-running threads. I'm a little concerned that it's opening a transaction and then sitting on it without ever committing... I'm really not sure. Should I change it so my threads instead use something like: with transaction.manager: DBSession.add(SomeORMObject(id=4)) or: with DBSession.begin(): DBSession.add(SomeORMObject(id=4)) Will that commit the transaction and then release the session connection at the end of the context? In your pyramid app, there are a few different packages interacting. First is the “transaction” package, which provides an abstract idea of a “transaction” (not necessarily a database transaction) which can be committed or rolled back. Other packages register to be part of the transaction. Second, the zope.sqlalchemy package is the bridge between SQLAlchemy and the transaction. By using the ZopeTransactionExtension in your DBSession, you are ensuring that the transaction package will manage SQLAlchemy transactions. Finally, the pyramid_tm package is the bridge between pyramid and the transaction package. It ensures that a transaction is started at the beginning of a request and committed or rolled back at the end of the request. The important part of pyramid_tm is pretty short and is probably worth looking at: https://github.com/Pylons/pyramid_tm/blob/master/pyramid_tm/__init__.py#L55 To use SQLAlchemy transactions in your thread pool, you can either use the SQLAlchemy APIs directly, or you can use the ones from the transaction package. If you are using anything else that integrates with the transaction package (eg. pyramid_mailer), you’ll probably want the second option. Otherwise, it probably doesn’t matter much. In your thread pool, you’ll want to make sure that each piece of work starts a new transaction, and either commits or rolls back the transaction at the end, then finally closes it. Closing it will return its connection back to the connection pool. It is recommended to keep the session mechanics separate from the main body of your code. So you should probably try to structure your thread pool such that it sets up the session, calls a separate function to do the actual work, then commits or rolls back the transaction as appropriate. This is described at: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it The SA APIs you need are basically Session.begin, Session.commit, Session.rollback and Session.close. The transaction ones are transaction.manager.begin, transaction.manager.commit and transaction.manager.abort. (Note that zope.sqlalchemy will close the session after a commit or abort) Hope that helps, Simon -- 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] Trying to map a big arbitrary query to a class
On Mar 25, 2014, at 2:43 PM, Michael Robellard m...@robellard.com wrote:
Hello, I have what seems like a simple problem, but the only solution I have
come up with so far isn't very good. I have a large complex query that is
hand optimized that is built from aggregates and other data from different
mapped tables. I can create a query for it by doing Session.query([column
names]).from_statement(query).params({}) and that works just fine, but rather
than each row being a KeyedTuple, I would like each row to be mapped to an
object of a class, so that I can create some properties and methods on the
object
When I tried
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#mapping-a-class-against-arbitrary-selects
I get an error about not having a primary key defined
I did get this to kind of work with Bundles, but it looks ugly and you have
to dereference the bundle
Here's a gist of the code:
https://gist.github.com/subssn21/0f7300203aa39536ff5d
What's the right way to handle this?
the query you have there is entirely fixed. You can map to this if you wanted
(using mapper() with text()) but as this is read only and has no malleability
at all, you might as well just map a dictionary to it:
class ShiftPunchData(object):
def some_method(self):
pass
@classmethod
def load_data(cls, session):
for row in session.execute( your big query goes here):
s = ShiftPunchData()
s.__dict__.update(row)
yield s
you'd get the same effect. of course theres no relationships or eager loading
or anything like that but you're working with an enormous fixed query so none
of that applies anyway.
now if OTOH you want to build a flexible mapping based on that general query
idea, you'd want to express most or all of it in terms of a SQL expression,
either a core expression or ORM. It depends on what you want to be able to do
with it.
--
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] Definition for scalar relationship?
In the Doc for Automap, the Overriding Naming Schemes part, it distinguishes the concept of scalar relationship and collection relationship. For collection relationship it is easy to understand and think of an example. However, what would a *scalar relationship* look like? Can't imagine a use case for this. An example would be very much appreciated. Thanks. -- 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: Definition for scalar relationship?
collection = one to many / many to many. scalar relationship = one-to-one or many-to-one relationship. when defining a relationship, you pass in 'uselist=False' to create define the relationship as scalar -- 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.
