[sqlalchemy] Re: keyword-only arguments in entity constructor confuse mapper
The patch worked on 0.7.0 and i don't get warning from Python (3.2), so it seem to have addressed the issue correctly. (Well i don't get same error at least, once i finish with unittests i can either confirm or deny lack of side effects) Thanks! On Jul 29, 6:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 29, 2011, at 3:45 AM, Phazorx wrote: Most of my entities accept various combinations of parameters and it makes sense for my to use keyword-only pattern of constructors: class Person(Root_Entity): def __init__(self, session, *, first_name, last_name): class Address(Root_Entity): def __init__(self, session, *, street, building, unit=None, zip=None, office=None, city=My City, region=None, country=My Country): however, in this case i get following from python while SQLA figures out relationships: ValueError: Function has keyword-only arguments or annotations, use getfullargspec() API which can support them full traceback:http://dpaste.com/hold/581307/ Everything is peachy as soon as i get rid of *, in constructor obviously... but what can i do to preserve such constructors and still be able to use SQLA? So you're using some Python syntax I've never seen before, let's check (checking...OK its new in Python 3, does not appear to be in the language tutorial either, just in the PEP) and in the first case we'd have to use getfullargspec() in that case when Py3 is in use, however we'd also have to interpret the extended tuple returned by getfullargspec() correctly when we establish instrumentation. If the following patch works, then we could commit once a test is written, though looking at it I'm not optimistic that some significant extra work might be needed to do this correctly. Until then this is an unsupported use case. Ticket #2237 is addedhttp://www.sqlalchemy.org/trac/ticket/2237. diff -r 87a1dc569235 lib/sqlalchemy/util/compat.py --- a/lib/sqlalchemy/util/compat.py Thu Jul 28 11:53:18 2011 -0400 +++ b/lib/sqlalchemy/util/compat.py Fri Jul 29 10:35:23 2011 -0400 @@ -90,6 +90,11 @@ from urlparse import parse_qsl if py3k: + from inspect import getfullargspec as inspect_getfullargspec +else: + from inspect import getargspec as inspect_getfullargspec + +if py3k: # they're bringing it back in 3.2. brilliant ! def callable(fn): return hasattr(fn, '__call__') diff -r 87a1dc569235 lib/sqlalchemy/util/langhelpers.py --- a/lib/sqlalchemy/util/langhelpers.py Thu Jul 28 11:53:18 2011 -0400 +++ b/lib/sqlalchemy/util/langhelpers.py Fri Jul 29 10:35:23 2011 -0400 @@ -15,7 +15,7 @@ import sys import types import warnings -from compat import update_wrapper, set_types, threading +from compat import update_wrapper, set_types, threading, inspect_getfullargspec from sqlalchemy import exc def _unique_symbols(used, *bases): @@ -149,7 +149,7 @@ 'apply_pos': '(self, a, b, c, **d)'} - spec = callable(fn) and inspect.getargspec(fn) or fn + spec = callable(fn) and inspect_getfullargspec(fn) or fn args = inspect.formatargspec(*spec) if spec[0]: self_arg = spec[0][0] -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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.
RE: [sqlalchemy] data driven schema in sqlalchemy
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of espresso maker Sent: 05 August 2011 06:19 To: sqlalchemy Subject: [sqlalchemy] data driven schema in sqlalchemy Hi there, I have a data driven database schema that I am trying to implement in sqlalchemy. Here's how the tables look like: user user_id | | user_properties property_id | property_name | property_description user_properties_data user_id | property_id | property_value What I would like to do eventually is if I have u = User() , u. [some_propery_name] return the property_value if it exist for that user. Any suggestions on how to implement this? There's an example of something like this in the SQLAlchemy repository: http://www.sqlalchemy.org/docs/orm/examples.html#vertical-attribute-mapp ing http://hg.sqlalchemy.org/sqlalchemy/file/3e75f284f253/examples/vertical I don't think it's exactly what you've described, but hopefully it's a starting point. Hope that helps, Simon -- 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.
Re: [sqlalchemy] Need for ImplicitForeignKeyConstraint
yeah wow I just saw that.Can you use table.add_constraint(fk) instead of _set_parent() ? On Aug 5, 2011, at 1:34 AM, Fayaz Yusuf Khan wrote: So I had been working on this tiny project now and then. And here's the poc. http://paste.pound-python.org/show/10578/ I think I'm somewhat misusing the _set_parent() here though. On Sunday, July 24, 2011 06:52:45 PM Michael Bayer wrote: On Jul 24, 2011, at 8:39 AM, Fayaz Yusuf Khan wrote: The problem with using different mixins is that you lose out on a lot of code reusability. In my case, I have a 'user' column that appears in almost all table declarations. To have a separate mixin class for each joint-table inheritance would destroy the purpose of having a mixin altogether. In your example you can simply use CMixin and TMixin separately instead of inheriting them from one another, then apply CMixin and TMixin directly to C individually.That makes more sense here since for every class X which you want to have user, you'd apply CMixin explicitly. The more I look at this the more it seems completely correct to me. Mixins and declarative do a lot , and sticking to Python's regular rules for inheritance is what makes them great. Perhaps, there should be a shorthand for implicitly creating columns along with foreign key constraints? So something like ImplicitForeignKeyConstraint( ['user', 'timestamp'], ['Timeline.user', 'Timeline.timestamp'], primary_key=True) should lead to the creation of Column('user', String, primary_key=True), Column('timestamp',Integer, autoincrement=False, primary_key=True), ForeignKeyConstraint( ['user', 'timestamp'], ['Timeline.user', 'Timeline.timestamp']) Not something for core but certainly something you could provide yourself (use append_column()). SQLA's APIs try to remain explicit about things leaving implicit helper layers as an external task (hence relationship + ForeignKey, as opposed to the all in one demo I did at http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/ , etc) -- Fayaz Yusuf Khan Cloud developer and designer Dexetra SS, Kochi, India fayaz.yusuf.khan_AT_gmail_DOT_com fayaz_AT_dexetra_DOT_com +91-9746-830-823 -- 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.
Re: [sqlalchemy] Re: keyword-only arguments in entity constructor confuse mapper
that's actually interesting, does the constructor of your mapped object still obey the same contract that the *, x, y syntax describes ? or can you suddenly pass first_name, last_name positionally as well ? On Aug 5, 2011, at 2:20 AM, Phazorx wrote: The patch worked on 0.7.0 and i don't get warning from Python (3.2), so it seem to have addressed the issue correctly. (Well i don't get same error at least, once i finish with unittests i can either confirm or deny lack of side effects) Thanks! On Jul 29, 6:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 29, 2011, at 3:45 AM, Phazorx wrote: Most of my entities accept various combinations of parameters and it makes sense for my to use keyword-only pattern of constructors: class Person(Root_Entity): def __init__(self, session, *, first_name, last_name): class Address(Root_Entity): def __init__(self, session, *, street, building, unit=None, zip=None, office=None, city=My City, region=None, country=My Country): however, in this case i get following from python while SQLA figures out relationships: ValueError: Function has keyword-only arguments or annotations, use getfullargspec() API which can support them full traceback:http://dpaste.com/hold/581307/ Everything is peachy as soon as i get rid of *, in constructor obviously... but what can i do to preserve such constructors and still be able to use SQLA? So you're using some Python syntax I've never seen before, let's check (checking...OK its new in Python 3, does not appear to be in the language tutorial either, just in the PEP) and in the first case we'd have to use getfullargspec() in that case when Py3 is in use, however we'd also have to interpret the extended tuple returned by getfullargspec() correctly when we establish instrumentation. If the following patch works, then we could commit once a test is written, though looking at it I'm not optimistic that some significant extra work might be needed to do this correctly. Until then this is an unsupported use case. Ticket #2237 is addedhttp://www.sqlalchemy.org/trac/ticket/2237. diff -r 87a1dc569235 lib/sqlalchemy/util/compat.py --- a/lib/sqlalchemy/util/compat.py Thu Jul 28 11:53:18 2011 -0400 +++ b/lib/sqlalchemy/util/compat.py Fri Jul 29 10:35:23 2011 -0400 @@ -90,6 +90,11 @@ from urlparse import parse_qsl if py3k: +from inspect import getfullargspec as inspect_getfullargspec +else: +from inspect import getargspec as inspect_getfullargspec + +if py3k: # they're bringing it back in 3.2. brilliant ! def callable(fn): return hasattr(fn, '__call__') diff -r 87a1dc569235 lib/sqlalchemy/util/langhelpers.py --- a/lib/sqlalchemy/util/langhelpers.pyThu Jul 28 11:53:18 2011 -0400 +++ b/lib/sqlalchemy/util/langhelpers.pyFri Jul 29 10:35:23 2011 -0400 @@ -15,7 +15,7 @@ import sys import types import warnings -from compat import update_wrapper, set_types, threading +from compat import update_wrapper, set_types, threading, inspect_getfullargspec from sqlalchemy import exc def _unique_symbols(used, *bases): @@ -149,7 +149,7 @@ 'apply_pos': '(self, a, b, c, **d)'} -spec = callable(fn) and inspect.getargspec(fn) or fn +spec = callable(fn) and inspect_getfullargspec(fn) or fn args = inspect.formatargspec(*spec) if spec[0]: self_arg = spec[0][0] -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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. -- 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.
Re: [sqlalchemy] One to many, but only load one
On Aug 4, 2011, at 7:22 PM, Mark Erbaugh wrote: Thanks, Could you explain how to do contains_eager with an explicit query(). I tried putting a query inside a call to contains_eager, but get an error: ArgumentError: mapper option expects string key or list of attributes So I think both approaches have advantages, the one here is nice because it will work with any query. A more efficient query doesn't rely upon the correlated subquery, and instead joins to a grouping, allowing all the max(date) rows to be found at once: subq = s.query(B.a_id, func.max(B.date).label('date')).group_by(B.a_id).subquery() for obj in s.query(A).join(A.bs).\ join(subq, A.bs).\ filter(subq.c.date==B.date).options(contains_eager(A.bs)): print obj.bs the SQL here is: SELECT b.id AS b_id, b.a_id AS b_a_id, b.date AS b_date, a.id AS a_id FROM a JOIN b ON a.id = b.a_id JOIN (SELECT b.a_id AS a_id, max(b.date) AS date FROM b GROUP BY b.a_id) AS anon_1 ON a.id = anon_1.a_id WHERE anon_1.date = b.date let's see mongodb do that ! :) I'm keeping a running track of these examples at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/RelationshipToLatest as this use is something that has come up a lot before. Mark On Aug 4, 2011, at 6:39 PM, Michael Bayer wrote: awkardly and inefficiently from a SQL perspective. contains_eager() with an explicit query() would produce better result from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() import datetime class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) bs = relationship(B) class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) a_id = Column(Integer, ForeignKey('a.id')) date = Column(Date) A.latest_b = relationship(B, primaryjoin=and_( A.id==B.a_id, B.date==select([func.max(B.date)]).where(B.a_id==A.id).correlate(A.__table__) ) ) e = create_engine('sqlite://', echo=True) Base.metadata.create_all(e) s = Session(e) s.add_all([ A(bs=[ B(date=datetime.date(2011, 10, 5)), B(date=datetime.date(2011, 8, 4)), B(date=datetime.date(2011, 9, 17)), ]), A(bs=[ B(date=datetime.date(2011, 10, 5)), B(date=datetime.date(2011, 8, 4)), B(date=datetime.date(2011, 9, 17)), ]), ]) s.commit() for obj in s.query(A).options(joinedload(A.latest_b)): print obj.latest_b On Aug 4, 2011, at 5:55 PM, Mark Erbaugh wrote: Table A has a one to many relationship with Table B. There may be zero or more rows in B for each row in A. I would like to have a query that retrieves all the rows in table A joined with the first related row in table B (if one exists). In this case, each row in table B has a DATE field and I want to retrieve the row with the latest date. Is this possible using joinedload? Thanks, Mark -- 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. -- 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. -- 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. -- 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.
Re: [sqlalchemy] Re: Cascade Deletes
Il 04/08/11 21.27, Aviv Giladi ha scritto: Hey, Tried adding cascade to Rating's backref call like so: subrating = relationship(SubRating, backref=backref(rating, cascade=all, delete-orphan uselist=False)) This unfortunately doesn't work - when I delete a Rating, the according Subratings are NOT removed. What am I doing wrong? (Testing with SQLite) Are you sure about the position of 'cascade' keyword? I think the right way to do that could be: subrating = relationship(SubRating, cascade=all, delete-orphan, backref=backref(rating, uselist=False)) Regards, Stefano. -- 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] Default values
In a declaratively created table, is there an automatic way to get a new instance of the class object to be populated with values specified in a 'default' clause? i.e. class MyTable(Base): __tablename__ = 'table' name = Column(String, default='new name') ... newRow = MyTable() is there a way to have newRow.name automatically have the value 'new name' before it is committed to the database? The best I've been able to come up with so far is to use a 'CONSTANT' in the default clause and use that same CONSTANT to initialize the field in the class' __init__, but this doesn't seem very DRY. Or, maybe is this the wrong question? Maybe I'm trying to do things the wrong way. I'm trying to use mostly the same code add a new row or edit an existing row. If the user is adding a record, I create a new instance of the class and use the add/edit screen to edit the data. If the user is editing an existing row, I retrieve the row, then use the add/edit screen with it. Thanks, Mark -- 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] Declarative Field Type 'Alias'
In my application, some tables have several fields that need to have the same type and default value, i.e.: field1 = Column(Integer, default=2) field2 = Column(Integer, default=2) ... Is there some way to refactor the Common(Integer, default=2), short of creating a custom column type? I could see the possibility that in a future version of the application, I would want to globally change the column type or default value for all these fields at once. So far, I've come up with creating a function that returns the column. def common_field(): return Column(Integer, default=2) field1 = common_field() field2 = common_field() Is there a better way? Mark -- 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.
Re: [sqlalchemy] Default values
Il 05/08/11 19.29, Mark Erbaugh ha scritto: In a declaratively created table, is there an automatic way to get a new instance of the class object to be populated with values specified in a 'default' clause? i.e. class MyTable(Base): __tablename__ = 'table' name = Column(String, default='new name') ... newRow = MyTable() is there a way to have newRow.name automatically have the value 'new name' before it is committed to the database? The best I've been able to come up with so far is to use a 'CONSTANT' in the default clause and use that same CONSTANT to initialize the field in the class' __init__, but this doesn't seem very DRY. Or, maybe is this the wrong question? Maybe I'm trying to do things the wrong way. I'm trying to use mostly the same code add a new row or edit an existing row. If the user is adding a record, I create a new instance of the class and use the add/edit screen to edit the data. If the user is editing an existing row, I retrieve the row, then use the add/edit screen with it. Thanks, Mark Hi Mark, to fill with defaults you can do: newRow = MyTable() session.add(newRow) session.flush() print newRow.name 'print newRow.name' will display 'new name' To use the same code for create/update I suggest you to use session.merge: http://www.sqlalchemy.org/docs/orm/session.html#merging Regards, Stefano. -- 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.
Re: [sqlalchemy] Default values
On Aug 5, 2011, at 2:00 PM, Stefano Fontanelli wrote: Il 05/08/11 19.29, Mark Erbaugh ha scritto: In a declaratively created table, is there an automatic way to get a new instance of the class object to be populated with values specified in a 'default' clause? i.e. class MyTable(Base): __tablename__ = 'table' name = Column(String, default='new name') ... newRow = MyTable() is there a way to have newRow.name automatically have the value 'new name' before it is committed to the database? The best I've been able to come up with so far is to use a 'CONSTANT' in the default clause and use that same CONSTANT to initialize the field in the class' __init__, but this doesn't seem very DRY. Or, maybe is this the wrong question? Maybe I'm trying to do things the wrong way. I'm trying to use mostly the same code add a new row or edit an existing row. If the user is adding a record, I create a new instance of the class and use the add/edit screen to edit the data. If the user is editing an existing row, I retrieve the row, then use the add/edit screen with it. Thanks, Mark Hi Mark, to fill with defaults you can do: newRow = MyTable() session.add(newRow) session.flush() print newRow.name 'print newRow.name' will display 'new name' To use the same code for create/update I suggest you to use session.merge: http://www.sqlalchemy.org/docs/orm/session.html#merging Stefano, Thanks for the reply. The problem I see with this approach is that I think it actually commits the new row to the database. In the app, it's possible that the user could decide to cancel before inserting the new row. Of course, I could back out the addition, but it seems like it would be better to not insert in the first place. Mark -- 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: Cascade Deletes
Hey Stefano, I tried that, but when I did, this is the error I got while inserting a new rating: InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'SELECT subratings.id AS subratings_id \nFROM subratings \nWHERE subratings.id = ?' (symbol 'NEVER_SET,) On Aug 5, 9:46 am, Stefano Fontanelli s.fontane...@asidev.com wrote: Il 04/08/11 21.27, Aviv Giladi ha scritto: Hey, Tried adding cascade to Rating's backref call like so: subrating = relationship(SubRating, backref=backref(rating, cascade=all, delete-orphan uselist=False)) This unfortunately doesn't work - when I delete a Rating, the according Subratings are NOT removed. What am I doing wrong? (Testing with SQLite) Are you sure about the position of 'cascade' keyword? I think the right way to do that could be: subrating = relationship(SubRating, cascade=all, delete-orphan, backref=backref(rating, uselist=False)) Regards, Stefano. -- 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.
Re: [sqlalchemy] misleading docs on logging
On 03/08/2011 01:01, Michael Bayer wrote: import logging logging.getLogger('sqlalchemy').setLevel(logging.INFO) logging.getLogger('sqlalchemy.pool').setLevel(logging.DEBUG) logging.getLogger('sqlalchemy.engine').setLevel(logging.WARNING) logging.getLogger('sqlalchemy.mapper').setLevel(logging.WARNING) ...to get just the pool logging, and then, with 0.6 at least, you find that things like checking connections in and out of the pool don't appear to be logged. Am I missing something here? Not sure if you're missing anything but I certainly am. sqlalchemy.pool is the only logger involved with pool logging and I'm having a hard time imagining how you've arrived at your result ! Indeed, heat of the moment stuff. I could reproduce at the time, but can't now... Apologies for the noise... Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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.
Re: [sqlalchemy] Re: Cascade Deletes
Il 05/08/11 20.38, Aviv Giladi ha scritto: Hey Stefano, I tried that, but when I did, this is the error I got while inserting a new rating: InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'SELECT subratings.id AS subratings_id \nFROM subratings \nWHERE subratings.id = ?' (symbol 'NEVER_SET,) I need the whole code to help you :) I think it is not related with cascade set. Regards, Stefano. -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web: www.asidev.com Skype: stefanofontanelli -- 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.
Re: [sqlalchemy] Default values
Il 05/08/11 20.33, Mark Erbaugh ha scritto: On Aug 5, 2011, at 2:00 PM, Stefano Fontanelli wrote: Il 05/08/11 19.29, Mark Erbaugh ha scritto: In a declaratively created table, is there an automatic way to get a new instance of the class object to be populated with values specified in a 'default' clause? i.e. class MyTable(Base): __tablename__ = 'table' name = Column(String, default='new name') ... newRow = MyTable() is there a way to have newRow.name automatically have the value 'new name' before it is committed to the database? The best I've been able to come up with so far is to use a 'CONSTANT' in the default clause and use that same CONSTANT to initialize the field in the class' __init__, but this doesn't seem very DRY. Or, maybe is this the wrong question? Maybe I'm trying to do things the wrong way. I'm trying to use mostly the same code add a new row or edit an existing row. If the user is adding a record, I create a new instance of the class and use the add/edit screen to edit the data. If the user is editing an existing row, I retrieve the row, then use the add/edit screen with it. Thanks, Mark Hi Mark, to fill with defaults you can do: newRow = MyTable() session.add(newRow) session.flush() print newRow.name 'print newRow.name' will display 'new name' To use the same code for create/update I suggest you to use session.merge: http://www.sqlalchemy.org/docs/orm/session.html#merging Stefano, Thanks for the reply. The problem I see with this approach is that I think it actually commits the new row to the database. In the app, it's possible that the user could decide to cancel before inserting the new row. Of course, I could back out the addition, but it seems like it would be better to not insert in the first place. I understand, but why can you not use session.rollback and session.commit? -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web: www.asidev.com Skype: stefanofontanelli -- 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.
Re: [sqlalchemy] Declarative Field Type 'Alias'
On Aug 5, 2011, at 1:36 PM, Mark Erbaugh wrote: In my application, some tables have several fields that need to have the same type and default value, i.e.: field1 = Column(Integer, default=2) field2 = Column(Integer, default=2) ... Is there some way to refactor the Common(Integer, default=2), short of creating a custom column type? I could see the possibility that in a future version of the application, I would want to globally change the column type or default value for all these fields at once. So far, I've come up with creating a function that returns the column. def common_field(): return Column(Integer, default=2) field1 = common_field() field2 = common_field() Is there a better way? What's the issue with using a function to generate a Column of a certain pre-determined configuration (what are functions in a procedural language for if not this) ? FTR I use functions to generate prefab Column objects all the time and they are also intrinsic to the example application I've created for the SQLAlchemy book project (which is on a somewhat indefinite schedule at the moment, unless someone wants to help write) . If the issue is that these tables need to have a certain series of completely fixed columns, i.e. same names and everything, here are a series of approaches for that depending on what you're doing. 1. Regular declarative ? Use declarative mixins. class MyMixin(object): updated_at = Column(DateTime, onupdate=datetime.utcnow) 2. if I am using Table metadata directly (i.e. with declarative, __table__ = Table()), I'd typically use a function around Table: def standard_table(*args, **kw): return Table(*(args + [_standard_table_cols()]), **kw) 3. For all tables, use events: @event.listens_for(Table, after_parent_attach) def _table_standard_cols(table, metadata): table.append_column(Column(DateTime, onupdate=datetime.utcnow)) 4. Certain classes of tables...there's probably a nice way to combine the table events with a subset of table classes. (tries...success !) from sqlalchemy import * from sqlalchemy import event from sqlalchemy.schema import CreateTable import datetime class TableWithUTC(Table): pass @event.listens_for(TableWithUTC, after_parent_attach) def _add_col(table, metadata): table.append_column(Column('updated_at', DateTime, onupdate=datetime.datetime.utcnow)) m = MetaData() t1 = Table('t1', m, Column('x', Integer)) t2 = TableWithUTC('t2', m, Column('x', Integer)) t3 = TableWithUTC('t3', m, Column('x', Integer)) t4 = Table('t4', m, Column('x', Integer)) assert t2.c.updated_at is not None assert 'updated_at' not in t4.c for name in 't1', 't2', 't3', 't4': print CreateTable(m.tables[name]) -- 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.
Re: [sqlalchemy] Declarative Field Type 'Alias'
On Aug 5, 2011, at 3:51 PM, Michael Bayer wrote: On Aug 5, 2011, at 1:36 PM, Mark Erbaugh wrote: In my application, some tables have several fields that need to have the same type and default value, i.e.: field1 = Column(Integer, default=2) field2 = Column(Integer, default=2) ... Is there some way to refactor the Common(Integer, default=2), short of creating a custom column type? I could see the possibility that in a future version of the application, I would want to globally change the column type or default value for all these fields at once. So far, I've come up with creating a function that returns the column. def common_field(): return Column(Integer, default=2) field1 = common_field() field2 = common_field() Is there a better way? What's the issue with using a function to generate a Column of a certain pre-determined configuration (what are functions in a procedural language for if not this) ? No issue at all. I just wanted to make sure I was doing it 'the right way'. I just noticed that in several places, SA will let you pass in a class or an instance of a class and figures out what to do with it. I thought that something like that might be working here. Actually, there is a small issue with using a function: Where should the function live? Obviously for some schema, this field type is used in multiple tables and belongs in a global namespace, but for others (as in my application), the field type is unique to an individual table. It would be nice if the function could live in the class's namespace. This is more of a Python issue than a SA issue, but I had trouble getting this to work. I did, but the code seems a little awkard to me sigh. In addition to the requirements already, I also wanted toe default value to be a class level 'constant'. The problem, as I see it, is that since the class definition isn't complete, it's namespace isn't avaialble. Since the default value 'constant' is a class data member, it would make sense if the function were a @classmethod, but I couldn't get python to accept: class Table(Base): ... DEFAULT = 2 @classmethod def CustomColumn(cls): return Column(Integer, default=DEFAULT) ... field1 = CustomColumn() Python complained 'classmethod object is not callable' on the last line above. Next I tried changing that line to: field1 = Table.CustomColumn() Now Python complained 'Table' is not defined If I leave the @classmethod decroator off, I couldn't figure out how to reference the class level data DEFAULT. Python complained on the return Column(... line ' global name DEFAULT is not defined. What I finally ended up with that works is: class Table(Base): ... DEFAULT = 2 def CustomColumn(default=DEFAULT): return Column(Integer, default=default) ... field1 = CustomColumn() Mark -- 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: Cascade Deletes
Hi Stefano, Thanks! The code is just like this: subrating_subratingproperty_association = Table('subrating_subratingproperty_association', Base.metadata, Column('subrating_id', Integer, ForeignKey('subratings.id')), Column('subrating_property_id', Integer, ForeignKey('subrating_properties.id'))) class SubRatingProperty(Base): __tablename__ = 'subrating_properties' id = Column(Integer, primary_key=True) name = Column(Unicode(32), unique=True) subratings = relationship(SubRating, secondary=subrating_subratingproperty_association, backref=subrating_properties) class SubRating(Base): __tablename__ = 'subratings' id = Column(Integer, primary_key=True) name = Column(Unicode(32), unique=True) class Rating(Base): __tablename__ = 'ratings' id = Column(Integer, primary_key=True) name = Column(Unicode(32), unique=True) subrating_id = Column(Integer, ForeignKey('subratings.id')) subrating = relationship(SubRating, backref=backref(rating, cascade=all, delete-orphan, uselist=False)) I create and add a Rating and Subrating (both end up in the DB no problem). Then, I call session.delete(rating_obj) and commit it. I look at the DB, and the Rating is gone, but the SubRating is still there. The DB shows that the Rating has the correct Subrating's ID.. On Aug 5, 11:45 am, Stefano Fontanelli s.fontane...@asidev.com wrote: Il 05/08/11 20.38, Aviv Giladi ha scritto: Hey Stefano, I tried that, but when I did, this is the error I got while inserting a new rating: InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'SELECT subratings.id AS subratings_id \nFROM subratings \nWHERE subratings.id = ?' (symbol 'NEVER_SET,) I need the whole code to help you :) I think it is not related with cascade set. Regards, Stefano. -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web:www.asidev.com Skype: stefanofontanelli -- 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.