Re: [sqlalchemy] Belonging to a session vs being in the session
On Thursday, 24 July 2014 21:56:11 UTC+3, Michael Bayer wrote: expunge() is a bug: https://bitbucket.org/zzzeek/sqlalchemy/issue/3139/expunge-after-delete-fails it’s supposed to raise (hence can’t fix this til 1.0). so ignore expunge. if you were to emit session.rollback(), you’d find the DELETE is rolled back and you’d again get True, True. The Session still knows about this object within the transaction’s “ready to roll back” state. The object isn’t permanently not part of the Session until the commit(): session.commit() log(commit) output: commit: (False, False) Thanks, good to know that expunge() is a bug. As for my other questions: - is session.commit() the only way to dissociate the object from the session? - I guess a (False, True) combination is not ever possible, correct? -- 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] One-off listeners
On Monday, 25 November 2013 17:27:02 UTC+2, Michael Bayer wrote:
On Nov 25, 2013, at 9:00 AM, George Sakkis george...@gmail.comjavascript:
wrote:
Hi all,
is there a feature or pattern for adding a listener that is to be
executed (at most) once? For example, say you want to send an email when
user is created and the session is committed. If event.listen() supported a
once boolean parameter, this could be expressed as:
def email_user(email_address, message):
...
...
if valid_data:
new_user = User(**valid_data)
db.session.add(new_user)
# send an email to new_user after committing
event.listen(db.session, after_commit,
once=True, # hypothetical parameter
lambda session: email_user(new_user.email,
welcome
{}!.format(new_user.namehttp://www.google.com/url?q=http%3A%2F%2Fnew_user.namesa=Dsntz=1usg=AFQjCNHUYRuAKfHoBRBgPDAOZm4sTiKzOg))
there are certain events such as pool.first_connect that use a “once” type
of system, but that is linked to the type of event.
I haven’t tried this but you should be able to call event.remove()
(sqlalchemy 0.9) within the handler:
def go(session):
email_user(…)
event.remove(session, “after_commit”, go)
event.listen(session, “after_commit”, go)
Nice, this should do the trick, thanks. Any workaround for 0.7.x-0.8.x ?
George
--
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/groups/opt_out.
[sqlalchemy] One-off listeners
Hi all,
is there a feature or pattern for adding a listener that is to be executed
(at most) once? For example, say you want to send an email when user is
created and the session is committed. If event.listen() supported a once
boolean parameter, this could be expressed as:
def email_user(email_address, message):
...
...
if valid_data:
new_user = User(**valid_data)
db.session.add(new_user)
# send an email to new_user after committing
event.listen(db.session, after_commit,
once=True, # hypothetical parameter
lambda session: email_user(new_user.email, welcome
{}!.format(new_user.name))
Thanks,
George
--
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/groups/opt_out.
Re: [sqlalchemy] Code organization with declarative models
On Tuesday, August 13, 2013 12:59:57 AM UTC+3, Ams Fwd wrote: On 08/12/2013 02:50 PM, George Sakkis wrote: Hello everyone, this is more of a code architecture and design question but I'm wondering what the best practices are regarding declarative models. On the one extreme, models are pretty barebone, with little more than the columns, relationships and possibly a few declared attributes and properties (python and/or hybrid). On the other extreme, models are much heavier, encapsulating pretty much the business logic of the application in methods (and classmethods or staticmethods for querying the database). Between these two extremes are models with some common or important business logic kept inside the class and the rest defined elsewhere (where this elsewhere might be the controllers or the resource layer or the Data Access Objects or whatever the nomenclature happens to be). So where should the line be drawn between what belongs in a declarative class and what not? For example, I suspect that models should be completely decoupled from the Session; any Session-related code (for querying/updating/deleting objects) should not live inside the declarative class. Still I haven't seen this being mentioned explicitly in the docs and can't put my finger on it. Any insight would be appreciated. Thanks, George -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. My 2cents: Usually I keep the models absolutely barebones (as you suggested). As far as I am concerned they are not aware of any 'business' logic and only deal with CRUD operations and perhaps complex CRUD if the need arises. I usually have another abstraction which is generally a factory that delegates db tasks to the model and deals with all business logicky stuff, sessions, complex relationships etc. (a bit like Django's managers but not quite as coupled to the model i.e. the model does not know about it). This has worked quite well for me in the past and although it is a bit more work is quite flexible. HTH AM Yes, this helps and it's close to my experience as well. One thing though - even if only dealing with CRUD operations (especially complex) in the model, it's not barebones any more. For starters you need a reference to a (typically global) Session object. Then you have to decide what, say, a Model.create() method should do: does it only initialize and return a new transient object or does it also add it to the session? Or maybe it should call flush() or commit() on top of that? I've been actually trying to dig myself out of a similar hole lately where, to make things worse, the create logic often lives in Model.__init__. In addition to simply initializing a particular object, it may also hit the db to fetch other objects that are needed, instantiate a bunch of new children objects, call flush() and/or commit(), insert a log entry row in another table and more.. So although viewed from the outside it's just CRUD, it has all sorts of business logic and assumptions bundled with it. Regards, George -- 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/groups/opt_out.
[sqlalchemy] Code organization with declarative models
Hello everyone, this is more of a code architecture and design question but I'm wondering what the best practices are regarding declarative models. On the one extreme, models are pretty barebone, with little more than the columns, relationships and possibly a few declared attributes and properties (python and/or hybrid). On the other extreme, models are much heavier, encapsulating pretty much the business logic of the application in methods (and classmethods or staticmethods for querying the database). Between these two extremes are models with some common or important business logic kept inside the class and the rest defined elsewhere (where this elsewhere might be the controllers or the resource layer or the Data Access Objects or whatever the nomenclature happens to be). So where should the line be drawn between what belongs in a declarative class and what not? For example, I suspect that models should be completely decoupled from the Session; any Session-related code (for querying/updating/deleting objects) should not live inside the declarative class. Still I haven't seen this being mentioned explicitly in the docs and can't put my finger on it. Any insight would be appreciated. Thanks, George -- 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/groups/opt_out.
Re: [sqlalchemy] order_by hybrid property fails when specifed as string in a relationship
On Wednesday, June 19, 2013 9:47:08 PM UTC+1, Michael Bayer wrote: On Jun 19, 2013, at 4:19 PM, George Sakkis george...@gmail.comjavascript: wrote: It seems that hybrid properties are not allowed to be specified as strings for the order_by parameter of a relationship; attempting it fails with InvalidRequestError: Class ... does not have a mapped column named '...'. Is this a known limitation or a bug? Sample test case below. It's kind of a missing feature; here's a patch to make that work which will be for 0.8: http://www.sqlalchemy.org/trac/ticket/2761 Awesome, thanks for the instant response! George -- 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/groups/opt_out.
[sqlalchemy] order_by hybrid property fails when specifed as string in a relationship
It seems that hybrid properties are not allowed to be specified as strings
for the order_by parameter of a relationship; attempting it fails with
InvalidRequestError: Class ... does not have a mapped column named
'...'. Is this a known limitation or a bug? Sample test case below.
Thanks,
George
#
from sqlalchemy import Column, Integer, String, ForeignKey, case
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import relationship
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
firstname = Column(String(50))
lastname = Column(String(50))
game_id = Column(Integer, ForeignKey('game.id'))
@hybrid_property
def fullname(self):
if self.firstname is not None:
return self.firstname + + self.lastname
else:
return self.lastname
@fullname.expression
def fullname(cls):
return case([
(cls.firstname != None, cls.firstname + + cls.lastname),
], else_=cls.lastname)
class Game(Base):
__tablename__ = 'game'
id = Column(Integer, primary_key=True)
name = Column(String(50))
if 0: # this works
users = relationship(User, order_by=User.fullname)
else: # this fails
users = relationship(User, order_by=User.fullname)
if __name__ == '__main__':
game = Game(name=tetris)
--
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/groups/opt_out.
[sqlalchemy] Re: Session identity map and PyPy
On Jan 30, 3:54 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 30, 2013, at 5:43 AM, George Sakkis wrote: Hello, I am in the process of migrating to PyPy and have a handful of unit test failing due to the different garbage collector and SQLAlchemy's usage of weak references in the Session identity map. Most failures are probably safe to ignore and all of them are fixed after manually calling gc.collect() somewhere in the test. this is true but FTR we do have a pypy suite up on jenkins.sqlalchemy.org, we don't run it often because it takes hours, but we did go through lots of effort to ensure that all the tests pass, and those that just really need cPython's GC are skipped. Unless you're referring to your own tests. Yes I meant my tests, not the SQLAlchemy test suite; sorry for the confusion. One specific failure though looks more worrying: a Session.rollback() call in the test's tearDown() method raises InvalidRequestError: Can't attach instance `X`; another instance with key (class `X`, (8L, 1L)) is already present in this session. Before digging deeper, any idea on why it is triggered here and not in any other test? it would be impossible to say without specific details and examples. Generally there is probably some logic that relies upon an X(8, 1) being garbage collected from the Session (note the identity map is weak referencing) so that an add() later on can succeed. An add() inside of a tearDown() is kind of strange, though, the stack trace would tell you how it's getting there. Here's the relevant stacktrace: Traceback (most recent call last): File /home/gsakkis/pdo/src/hoist/test/__init__.py, line 55, in tearDown Session.rollback() File /home/gsakkis/pdo/.virtualenv_pypy/site-packages/sqlalchemy/ orm/scoping.py, line 114, in do return getattr(self.registry(), name)(*args, **kwargs) File /home/gsakkis/pdo/.virtualenv_pypy/site-packages/sqlalchemy/ orm/session.py, line 673, in rollback self.transaction.rollback() File /home/gsakkis/pdo/.virtualenv_pypy/site-packages/sqlalchemy/ orm/session.py, line 387, in rollback transaction._rollback_impl() File /home/gsakkis/pdo/.virtualenv_pypy/site-packages/sqlalchemy/ orm/session.py, line 418, in _rollback_impl self._restore_snapshot() File /home/gsakkis/pdo/.virtualenv_pypy/site-packages/sqlalchemy/ orm/session.py, line 275, in _restore_snapshot self.session._update_impl(s) File /home/gsakkis/pdo/.virtualenv_pypy/site-packages/sqlalchemy/ orm/session.py, line 1621, in _update_impl self._attach(state) File /home/gsakkis/pdo/.virtualenv_pypy/site-packages/sqlalchemy/ orm/session.py, line 1649, in _attach % (mapperutil.state_str(state), state.key)) InvalidRequestError: Can't attach instance MyModel at 0x87fcc60; another instance with key (class 'my_package.MyModel', (8L, 1L)) is already present in this session. MyModel is a declarative_base() subclass. By the way the exception is not raised consistently, sometimes the test passes. Is there another/better way to prevent it other than calling gc.collect() before Session.rollback() (and perhaps other Session operations)? I'd make sure the tests use a brand new Session for every test that's clean. That should be the case; setUp() calls Session.begin() and tearDown() calls: try: Session.rollback() finally: Session.remove() The Session is in autocommit mode. More generally, are there any other known issues and caveats of running SQLAlchemy on PyPY? Our own test suite has a lot of performance issues with Pypy, not sure if due to memory or JIT crunching or what, I reported it to their bug tracker at the request of their developers. Do you happen to run coverage along with tests (--with-xcoverage)? If yes that must be the reason; at least in our case removing the coverage reduced the overhead from 10x the time of CPython down to ~15%. Thanks, George -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Session identity map and PyPy
Hello, I am in the process of migrating to PyPy and have a handful of unit test failing due to the different garbage collector and SQLAlchemy's usage of weak references in the Session identity map. Most failures are probably safe to ignore and all of them are fixed after manually calling gc.collect() somewhere in the test. One specific failure though looks more worrying: a Session.rollback() call in the test's tearDown() method raises InvalidRequestError: Can't attach instance `X`; another instance with key (class `X`, (8L, 1L)) is already present in this session. Before digging deeper, any idea on why it is triggered here and not in any other test? Is there another/better way to prevent it other than calling gc.collect() before Session.rollback() (and perhaps other Session operations)? More generally, are there any other known issues and caveats of running SQLAlchemy on PyPY? Thanks for any tip, George -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Non-deterministic insertion order
On Oct 2, 10:39 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 2, 2012, at 4:29 PM, George Sakkis wrote: I had the impression that the Session’s unit of work figures out automatically the potential object dependencies and makes sure that the insertion order is consistent to a topological sort but apparently I misunderstood. I postedhttps://gist.github.com/3822855as an example. In the first case (object_lists = [authors, books]), first a bunch of Authors is committed and then a bunch of Books. As expected this always succeeds. The next two cases though where authors and books are commited at once may or may not succeed. Why it doesn't use the topological sort of the tables and why it is not deterministic? on a quick glance I see no usage of relationship(). The topological sort is based on the relationship() paths built between mappers, not the foreign keys. Ah, I see. Out of curiosity, why it's not consistent across runs (i.e. always fail)? Thanks for the super prompt reply! George -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] bindparam ignores required=True
Hi, I found an older thread about using a bindparam with required=True (http://groups.google.com/group/sqlalchemy/tree/browse_frm/thread/ cc0cafb0241c51da/a5fdd0aeefecd75c) but the example doesn't work for me on 0.7.4, it still passes None instead of raising an exception. Am I missing something or is it a regression? Thanks, George -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Query - column names
Thanks, didn't know that, though in this case I want the keys in the same column order but keys() doesn't preserve it. George On Jun 3, 8:59 pm, Michael Bayer mike...@zzzcomputing.com wrote: each row has a keys() attribute if that helps... On Jun 3, 2009, at 8:49 PM, George Sakkis wrote: Is there a (public) API for getting the column names of a given Query instance and other similar introspection needs ? I didn't find anything related in the docs but after digging in the code I came up with col_names = [e._result_label for e in q._entities] but I'm not sure how stable and robust this is. George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query - column names
On Jun 4, 5:09 am, King Simon-NFHD78 simon.k...@motorola.com wrote: George Sakkis wrote: Is there a (public) API for getting the column names of a given Query instance and other similar introspection needs ? I didn't find anything related in the docs but after digging in the code I came up with col_names = [e._result_label for e in q._entities] but I'm not sure how stable and robust this is. George Query instances have a 'statement' property that returns the underlying SELECT object. You can then inspect it's 'columns' attribute: columns = list(query.statement.columns) for column in columns: print column.name Would that do what you want? Awesome, that's much better; thanks! George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Query - column names
Is there a (public) API for getting the column names of a given Query instance and other similar introspection needs ? I didn't find anything related in the docs but after digging in the code I came up with col_names = [e._result_label for e in q._entities] but I'm not sure how stable and robust this is. George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Relation spanning multiple tables
On Jun 2, 7:00 am, Mike Conley mconl...@gmail.com wrote:
This looks like an association proxy. Have you tried that?
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html
I don't see how this is related. From a quick read, it seems that's a
simplified way to handle many-to-many relations by hiding the
association object and it requires a regular relation to be defined
anyway. In my example there are two independent one-to-many
relationships and the intermediate table (User) is not just an
association object, it's a standalone object.
George
On Mon, Jun 1, 2009 at 11:46 PM, George Sakkis george.sak...@gmail.comwrote:
I can't for the life of me figure out how to specify a relation
spanning 3 tables. I think I've tried all combinations of
primaryjoin, secondaryjoin, viewonly, foreign_keys, remote_dest and
all that jazz, to no avail so far. If this is possible at all, there
should be a sample usage at the docs or at least a recipe on the wiki
since apparently it's not obvious at all. Below are some of my failed
attempts; any help will be very much appreciated.
George
#
from sqlalchemy.orm import relation, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String,
ForeignKey, and_
TEST = 5
def main():
c = Company()
u = User()
a = Address()
c.employees.append(u)
u.addresses.append(a)
session = sessionmaker()()
session.add(c)
session.flush()
print a.company
Base = declarative_base(bind=create_engine('sqlite:///:memory:',
echo=True))
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key=True)
name = Column(String(50))
employees = relation('User')
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(50))
company_id = Column(Integer, ForeignKey(Company.id))
addresses = relation('Address')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email = Column(String(50))
user_id = Column(Integer, ForeignKey(User.id))
# 1st attempt: just a primary join. Fails with
# sqlalchemy.exc.ArgumentError: Could not locate any equated,
locally
# mapped column pairs for primaryjoin condition 'addresses.user_id
= users.id
# AND users.company_id = company.id' on relation Address.company.
For more
# relaxed rules on join conditions, the relation may be marked as
viewonly=True.
if TEST == 1:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id))
# 2nd attempt: add viewonly=True. Fails with
# sqlalchemy.exc.ArgumentError: Could not determine relation
direction for
# primaryjoin condition 'addresses.user_id = users.id AND
users.company_id =
# company.id', on relation Address.company. Specify the
'foreign_keys'
# argument to indicate which columns on the relation are foreign.
if TEST == 2:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True)
# 3rd attempt: add foreign_keys=[user_id]. Fails with
#sqlalchemy.exc.ArgumentError: Remote column 'users.id' is not
part of
# mapping Mapper|Company|company. Specify remote_side argument to
indicate
# which column lazy join condition should bind.
if TEST == 3:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True,
foreign_keys=[user_id])
# 4th attempt: add remote_side=[Company.id]. Fails with
# sqlalchemy.orm.exc.UnmappedColumnError: No column
users.company_id is
# configured on mapper Mapper|Address|addresses...
if TEST == 4:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True,
foreign_keys=[user_id],
remote_side=[Company.id])
# 5th attempt: try secondary table/join. Fails with:
# sqlalchemy.orm.exc.UnmappedColumnError: Column 'users.id' is not
available,
# due to conflicting property
'id':sqlalchemy.orm.properties.ColumnProperty
# object at 0x8f73bac
if TEST == 5:
company = relation(Company, User.__table__,
primaryjoin=user_id==User.id,
secondaryjoin=User.company_id==Company.id)
# give up :/
if __name__ == '__main__':
Base.metadata.create_all()
main()
--~--~-~--~~~---~--~~
You received
[sqlalchemy] Specifying Foreign Keys not enough for join
I'm trying to use the primaryjoin/foreign_keys parameters (http:// www.sqlalchemy.org/docs/05/mappers.html#specifying-foreign-keys) to specify a relation between tables that don't have explicit foreign keys but apparently it's not enough for join() to figure it out: from sqlalchemy.orm import join, relation from sqlalchemy import Column, Integer, String from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email = Column(String(50)) user_id = Column(Integer) user = relation(User, primaryjoin=User.id==user_id, foreign_keys=user_id) # this works print join(User, Address, Address.user) users JOIN addresses ON users.id = addresses.user_id # this doesn't print join(User, Address) Traceback (most recent call last): ... sqlalchemy.exc.ArgumentError: Can't find any foreign key relationships between 'users' and 'addresses' Why doesn't join() figure out the join condition, and is there a way to make it work without having to pass it explicitly every time ? George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Specifying Foreign Keys not enough for join
On Jun 1, 3:34 pm, Michael Bayer mike...@zzzcomputing.com wrote: George Sakkis wrote: I'm trying to use the primaryjoin/foreign_keys parameters (http:// www.sqlalchemy.org/docs/05/mappers.html#specifying-foreign-keys) to specify a relation between tables that don't have explicit foreign keys but apparently it's not enough for join() to figure it out: from sqlalchemy.orm import join, relation from sqlalchemy import Column, Integer, String from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email = Column(String(50)) user_id = Column(Integer) user = relation(User, primaryjoin=User.id==user_id, foreign_keys=user_id) # this works print join(User, Address, Address.user) users JOIN addresses ON users.id = addresses.user_id # this doesn't print join(User, Address) Traceback (most recent call last): ... sqlalchemy.exc.ArgumentError: Can't find any foreign key relationships between 'users' and 'addresses' Why doesn't join() figure out the join condition, join() looks for foreign keys between the two tables at the schema level. you haven't defined that here (use ForeignKey, and then you can lose the foreign_keys parameter too). This was just an example, in the actual case the table schema is reflected so there's no explicit ForeignKey, that was the whole point of messing with primaryjoin/foreign_keys in the first place. So is there another way (or even workaround) to avoid passing explicitly the condition ? George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Relation spanning multiple tables
I can't for the life of me figure out how to specify a relation
spanning 3 tables. I think I've tried all combinations of
primaryjoin, secondaryjoin, viewonly, foreign_keys, remote_dest and
all that jazz, to no avail so far. If this is possible at all, there
should be a sample usage at the docs or at least a recipe on the wiki
since apparently it's not obvious at all. Below are some of my failed
attempts; any help will be very much appreciated.
George
#
from sqlalchemy.orm import relation, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String,
ForeignKey, and_
TEST = 5
def main():
c = Company()
u = User()
a = Address()
c.employees.append(u)
u.addresses.append(a)
session = sessionmaker()()
session.add(c)
session.flush()
print a.company
Base = declarative_base(bind=create_engine('sqlite:///:memory:',
echo=True))
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key=True)
name = Column(String(50))
employees = relation('User')
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(50))
company_id = Column(Integer, ForeignKey(Company.id))
addresses = relation('Address')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email = Column(String(50))
user_id = Column(Integer, ForeignKey(User.id))
# 1st attempt: just a primary join. Fails with
# sqlalchemy.exc.ArgumentError: Could not locate any equated,
locally
# mapped column pairs for primaryjoin condition 'addresses.user_id
= users.id
# AND users.company_id = company.id' on relation Address.company.
For more
# relaxed rules on join conditions, the relation may be marked as
viewonly=True.
if TEST == 1:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id))
# 2nd attempt: add viewonly=True. Fails with
# sqlalchemy.exc.ArgumentError: Could not determine relation
direction for
# primaryjoin condition 'addresses.user_id = users.id AND
users.company_id =
# company.id', on relation Address.company. Specify the
'foreign_keys'
# argument to indicate which columns on the relation are foreign.
if TEST == 2:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True)
# 3rd attempt: add foreign_keys=[user_id]. Fails with
#sqlalchemy.exc.ArgumentError: Remote column 'users.id' is not
part of
# mapping Mapper|Company|company. Specify remote_side argument to
indicate
# which column lazy join condition should bind.
if TEST == 3:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True,
foreign_keys=[user_id])
# 4th attempt: add remote_side=[Company.id]. Fails with
# sqlalchemy.orm.exc.UnmappedColumnError: No column
users.company_id is
# configured on mapper Mapper|Address|addresses...
if TEST == 4:
company = relation(Company,
primaryjoin=and_(user_id==User.id,
User.company_id==Company.id),
viewonly=True,
foreign_keys=[user_id],
remote_side=[Company.id])
# 5th attempt: try secondary table/join. Fails with:
# sqlalchemy.orm.exc.UnmappedColumnError: Column 'users.id' is not
available,
# due to conflicting property
'id':sqlalchemy.orm.properties.ColumnProperty
# object at 0x8f73bac
if TEST == 5:
company = relation(Company, User.__table__,
primaryjoin=user_id==User.id,
secondaryjoin=User.company_id==Company.id)
# give up :/
if __name__ == '__main__':
Base.metadata.create_all()
main()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Bulk insert on related tables
I've been trying to speed up a bulk insert process that currently seems too slow. I've read the past threads about how to replace the orm/session based inserts with table.insert().execute(*valuedicts) but in my case the objects are related (via 1-to-many relations if it makes a difference). In order to insert a child instance, the parent instance(s) has/ve to be inserted first so that the auto-generated ids can be assigned as foreign keys to the child. I guess that's what session.commit() does behind the scene anyway, so does it make sense to replicate this complexity (e.g. the topological sorting implied by the child-parent relationships) outside the orm ? Is there a faster way ? Or maybe it's already fast enough and I have unrealistic expectations ? Currently it inserts ~300K total rows (spread across 3 tables - 25K / 110K / 165K) in little less than half an hour, ~170 rows/second. That's in a PostgreSQL database running with synchronous_commit turned off and all foreign and unique constraints dropped during the bulk load. George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: FlushError (unsaved, pending instance and is an orphan)
On Apr 6, 2:52 am, svilen [EMAIL PROTECTED] wrote:
what's your mappers? many2one/one2many relations etc?
I expected that I can create a parent-child link either from parent
to child (by appending to ranker.results) or from child to parent
(as above), but apparently the latter doesn't seem to work. Is this
the case or something else is wrong ?
George
It's one2many from Ranker to Result.
class Ranker(Entity):
has_many('results', of_kind='Result', cascade='all, delete-
orphan')
# (...) other fields
class Result(Entity):
belongs_to('ranker', of_kind='Ranker',
column_kwargs=dict(nullable=False))
# (...) other fields
George
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] FlushError (unsaved, pending instance and is an orphan)
I'm using SA (with Elixir on top) and I have a parent Entity Ranker that has many children Results; that is, for a Ranker instance rk, rk.results gives its children and for a Result rs, rs.ranker gives its parent. When I add new children by providing the parent to the constructor of the child (rather than appending the child to rk.results) and then try to flush, I get: FlushError: instance Result at (...) is an unsaved, pending instance and is an orphan (is not attached to any parent 'Ranker' instance via that classes' 'results' attribute) Here's a (very) stripped-down version of what I'm doing: ranker = Ranker(...) for score in scores: Result(ranker=ranker, score=score, **kwds).save() session.flush() I expected that I can create a parent-child link either from parent to child (by appending to ranker.results) or from child to parent (as above), but apparently the latter doesn't seem to work. Is this the case or something else is wrong ? George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Stored procedures
Is there a way to call a stored procedure from sqlalchemy and access the returned result set ? If it makes a difference, I'm specifically interested in MySQL stored procedures. What I want to do is use this result set as part of another query, but MySQL doesn't currently allow treating a stored procedure as a (temporary) table, e.g. the following doesn't work: Select y from (call my_proc(1,2)) where x3; If I can capture the result set of my_proc with sqlalchemy, I can express the outer query in python and bypass MySQL's lack of syntactic support for this. Otherwise I'll probably rewrite my_proc in sqlalchemy, which may not be that bad after all, but I'd rather avoid this if possible. Thanks, George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy -~--~~~~--~~--~--~---
[sqlalchemy] Re: Stored procedures
Michael Bayer wrote: the func keyword is used for stored procedures. in the latest trunk, you can also create table-like elements out of funcs to support multi-column stored procedures, and you can create the SQL corresponding to the patterns you describe. That's pretty cool, too bad I can't use it for MySQL. Nevermind, I rewrote the stored proc using sqlalchemy and numarray to do the heavy number crunching and I'm happy with the result. Not only is the code shorter, but it's at least as fast as before ! George --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy -~--~~~~--~~--~--~---
