[sqlalchemy] hexagonal architecture: isolate domain model from DB
Hi everyone Looking for some grey beard opinion. I recently came across Alistair Cockburn's "Hexagonal architecture", aka. "ports and adapters". http://blog.8thlight.com/uncle-bob/2012/08/13/the-clean-architecture.html The names are dubious but the basic idea is not new: separate the domain model (business model, app logic, etc) from orthogonal concerns such as the DB. It seems to be a good idea in theory. It obviously would make testing very easy. But how does it work in practice? SQL alchemy is great in that it supports this, to a degree, with the old-style class-table-mapper method. We could then not use a declarative hierarchy but instead use a late-stage mapping function to dynamically build the tables from the domain model. Would declarative be amenable to this late-stage process instead of early binding in metaclasses? But the resulting mapped classes are then bound to the database layer and there I suspect the abstraction starts to leak e.g. autoloading relationships, event hooks, etc. Does anyone have any experience trying to go all the way like this? What are the pitfalls, and are they surmountable? Is it a worthy goal or a fool's errand? -- 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: many-to-one relationship with intermediate table & non equijoin
Thanks for the quick response! After much fiddling I got it working using alias(), foreign() and corresponding_column(). It seems to get the right results. Is this the simplest, right approach? joined = Enrolment.__table__.join(RosterLine, Enrolment.group_id==RosterLine.group_id).alias() cc = joined.corresponding_column secmapper = mapper(Enrolment, joined, non_primary=True) # ... enrolment = relationship(secmapper, primaryjoin=lambda:( (Timesheet.line_id == foreign(cc(RosterLine.line_id))) & (Timesheet.person_id == cc(Enrolment.person_id)) & (Timesheet.date >= cc(Enrolment.enrol_date)) & ((Timesheet.date < cc(Enrolment.next_date)) | (cc(Enrolment.next_date) == None)) ), uselist=False, viewonly=True) On Thursday, 15 August 2013 04:30:12 UTC+10, avdd wrote: > > Hello all > > Tried for hours to figure out the various relationship() options with no > luck. > > Consider: > > > class Enrolment(base): > __tablename__ = 'enrolment' > person_id = Column(String, primary_key=True) > group_id= Column(String, primary_key=True) > enrol_date = Column(Date, primary_key=True) > level_id= Column(String, nullable=False) > next_date = Column(Date) > > def __repr__(self): > return 'Enrol(%s, %s, %s, %s)' % (self.person_id, > self.enrol_date, > self.group_id, > self.level_id) > > class RosterLine(base): > __tablename__ = 'roster_line' > line_id = Column(String, primary_key=True) > group_id= Column(String, nullable=False) > > > class Timesheet(base): > __tablename__ = 'timesheet' > id = Column(Integer, primary_key=True) > person_id = Column(String, nullable=False) > line_id = Column(String, nullable=False) > date= Column(Date, nullable=False) > > enrolment = relationship(Enrolment, > primaryjoin=lambda:( > (Timesheet.person_id == > foreign(Enrolment.person_id)) > & (Timesheet.date >= Enrolment.enrol_date) > & ((Timesheet.date < Enrolment.next_date) > | (Enrolment.next_date == None)) > #& (Timesheet.line_id == > RosterLine.line_id) > #& (RosterLine.group_id == > Enrolment.group_id) > ), > # uselist=False, > viewonly=True) > > The relationship as it stands works correctly but I can't figure out the > magic words to introduce the intermediate join to RosterLine. > > The relationship should issue SQL like: > > > select E.* > fromroster_line L, > enrolment E > where L.line_id = 'work' > and L.group_id = E.group_id > and E.person_id = 'bob' > and E.enrol_date <= '2012-03-04' > and (E.next_date > '2012-03-04' > or E.next_date is null) > > > > Eternally grateful for any help. > > 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/groups/opt_out.
[sqlalchemy] many-to-one relationship with intermediate table & non equijoin
Hello all
Tried for hours to figure out the various relationship() options with no
luck.
Consider:
class Enrolment(base):
__tablename__ = 'enrolment'
person_id = Column(String, primary_key=True)
group_id= Column(String, primary_key=True)
enrol_date = Column(Date, primary_key=True)
level_id= Column(String, nullable=False)
next_date = Column(Date)
def __repr__(self):
return 'Enrol(%s, %s, %s, %s)' % (self.person_id,
self.enrol_date,
self.group_id,
self.level_id)
class RosterLine(base):
__tablename__ = 'roster_line'
line_id = Column(String, primary_key=True)
group_id= Column(String, nullable=False)
class Timesheet(base):
__tablename__ = 'timesheet'
id = Column(Integer, primary_key=True)
person_id = Column(String, nullable=False)
line_id = Column(String, nullable=False)
date= Column(Date, nullable=False)
enrolment = relationship(Enrolment,
primaryjoin=lambda:(
(Timesheet.person_id ==
foreign(Enrolment.person_id))
& (Timesheet.date >= Enrolment.enrol_date)
& ((Timesheet.date < Enrolment.next_date)
| (Enrolment.next_date == None))
#& (Timesheet.line_id ==
RosterLine.line_id)
#& (RosterLine.group_id ==
Enrolment.group_id)
),
# uselist=False,
viewonly=True)
The relationship as it stands works correctly but I can't figure out the
magic words to introduce the intermediate join to RosterLine.
The relationship should issue SQL like:
select E.*
fromroster_line L,
enrolment E
where L.line_id = 'work'
and L.group_id = E.group_id
and E.person_id = 'bob'
and E.enrol_date <= '2012-03-04'
and (E.next_date > '2012-03-04'
or E.next_date is null)
Eternally grateful for any help.
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/groups/opt_out.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from datetime import date
base = declarative_base()
class Enrolment(base):
__tablename__ = 'enrolment'
person_id = Column(String, primary_key=True)
group_id= Column(String, primary_key=True)
enrol_date = Column(Date, primary_key=True)
level_id= Column(String, nullable=False)
next_date = Column(Date)
def __repr__(self):
return 'Enrol(%s, %s, %s, %s)' % (self.person_id,
self.enrol_date,
self.group_id,
self.level_id)
class RosterLine(base):
__tablename__ = 'roster_line'
line_id = Column(String, primary_key=True)
group_id= Column(String, nullable=False)
class Timesheet(base):
__tablename__ = 'timesheet'
id = Column(Integer, primary_key=True)
person_id = Column(String, nullable=False)
line_id = Column(String, nullable=False)
date= Column(Date, nullable=False)
enrolment = relationship(Enrolment,
primaryjoin=lambda:(
(Timesheet.person_id == foreign(Enrolment.person_id))
& (Timesheet.date >= Enrolment.enrol_date)
& ((Timesheet.date < Enrolment.next_date)
| (Enrolment.next_date == None))
#& (Timesheet.line_id == RosterLine.line_id)
#& (RosterLine.group_id == Enrolment.group_id)
),
#uselist=False,
viewonly=True)
e = create_engine('sqlite://', echo=True)
base.metadata.create_all(e)
db = Session(e)
db.add(RosterLine(line_id='work', group_id='staff'))
db.add(RosterLine(line_id='etc', group_id='manager'))
db.add(Enrolment(person_id='bob',
group_id='staff',
level_id='normal',
enrol_date=date(2010,1,1),
next_date=date(2011,1,1)))
db.add(Enrolment(person_id='bob',
group_id='staff',
level_id='better',
enrol_date=date(2011,1,1)))
db.add(Enrolment
[sqlalchemy] bug in dogpile advanced example?
"two through twelve" actually shows 25 .. 40 -- 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: event interface
On Dec 31, 3:37 am, Michael Bayer wrote: > "retval" is not fantastic, though it is a known term used by pdb for > instance. "returns" as a boolean sounds like its suggesting the function > may or may not return. A non-abbrevated name would be "has_return_value". I'd say "use_" rather than "has_"; every (returning) function has a return value! But I figured that a function that doesn't return is pointless in this context so "returns" could only reasonably be interpreted to mean "use the return value", as in "returns <...>" "result" can be a synonym for "return value" also. a. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: event interface
By "hackish" I refer to the tacking on a prefix to simulate a
namespace, and similarly conflating the python identifier namespace
with the events. I have the same problem with frameworks that use
method names as URL components.
I would prefer the event names to look as you have done with the ORM
cascade symbols, e.g.
@register('before-frobnoff')
def on_before_frobnoff(): pass
@listen('before-frobnoff')
def my_frobnoff_listener()
(Of course I meant s/property/decorator/ in OP)
Javascript, DOM level 1 uses "on", but DOM level 2 uses unadorned
names:
http://www.w3.org/TR/DOM-Level-2-Events/events.html#Events-MouseEvent
As an aside, hybrid.py defines property_ (mangled) where the docs
refer to the unmangled. What's the reason for the mangling of names
that shadow builtins? Lint complaints? I find it similarly annoying
seeing type_ and such everywhere. (I wish python had a standard top-
level namespace!)
On Dec 31, 3:37 am, Michael Bayer wrote:
> On Dec 30, 2010, at 8:35 AM, avdd wrote:
>
> > Hi there
>
> > Just wondering, before 0.7 is released and the API is baked, is it
> > necessary to have all event names start with "on_"? It seems
> > redundant and hackish. (Use a property?)
>
> What's hackish ? Are you referring to the internals of event.py which look
> for "on_" in a few places ? That's not the rationale for the presence of
> "on_", though it is convenient.
>
> The rationale for "on_" is so that event names are placed into their own
> namespace, disambiguating them from other methods that are present on the
> parent Events class which deal with the mechanics of registering and
> de-registering events.
>
> Prefixing event names with "on" is a very common practice - Hibernate's event
> system, which I consulted before developing sqlalchemy.event, uses the same
> scheme:
>
>
> http://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/event...
>
> http://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/event...
>
> Javascript as we know uses "on":
>
> http://www.w3.org/TR/html4/interact/scripts.html#h-18.2.3
>
> wxpython:
>
> http://wiki.wxpython.org/Getting%20Started#Event_handling
>
> so this decision wasn't made in a vacuum and naming all events on_XYZ feels
> very natural to me. I'd welcome anyone else's thoughts on the matter.
>
>
>
> > Also, "retval" seems a prominent api symbol, it seems a shame to have
> > such a strained abbreviation. "returns" ?
>
> "retval" is not fantastic, though it is a known term used by pdb for
> instance. "returns" as a boolean sounds like its suggesting the function
> may or may not return. A non-abbrevated name would be "has_return_value".
>
>
>
> > a.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] event interface
Hi there Just wondering, before 0.7 is released and the API is baked, is it necessary to have all event names start with "on_"? It seems redundant and hackish. (Use a property?) Also, "retval" seems a prominent api symbol, it seems a shame to have such a strained abbreviation. "returns" ? a. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] optimistic concurrency and relationships
I rely heavily on the version_id_col feature and I would like to be able to either explicitly increment the version, or have the version incremented when a relationship changes. The issue here is that a change in a relationship is a semantic change to the parent record and should conflict with other changes to the record. For example consider a timesheet record with a set of allowances and an approved flag. An administrator may approve a timesheet based on the given allowances, but without extra locking there is a race condition where the allowances could be updated between the administrator viewing the timesheet and approving it. Doable? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: sqlalchemy and desktop apps
Hi Joel, Although my application is deployed on the web it was written with the goal of being redeployable as a desktop app, and to this end I have been able to abstract away the web details so that I can code my application very similarly to a desktop app. The relevance here is that all database objects are pickled and stashed in the session while the client interacts with it via AJAX messages, upon which the server unpickles the object, updates it and repickles it. This works very well, even on large trees of objects and relationships. My next step here is to stash the draft objects into the database rather than the session so that the user would be able to work on a draft over a long period before committing the changes. SQLAlchemy's optimistic concurrency control is vital here. I intend to open source my framework as soon as I have time to get it into a packagable state, but I'd be happy to share, especially if someone is able to work on a GUI shell. On Jul 24, 11:21 am, Joel Mohler wrote: > Hello, > > I'm a happy user of sqlalchemy on small personal projects of my own > (http://bitbucket.org/jbmohler/pyhaccis one of them which is not > ready for use yet). At my day job we are in the process of evaluating > platforms for a rewrite of our small business accounting system. We > expect this rewrite to have 200k-300k LOC and sqlalchemy stands up > well in many ways to some of the C# alternatives we are considering. > The notion of writing the entire project in python is quite daunting > to management who is new to opensource. > > I'm wondering if anyone would be kind enough to give an example of a > large desktop app written in sqlalchemy (even better if it's using > PyQt for the gui). We're pondering the viability of such a project in > python. In particular, we do a fair bit of document logic client side > with several hundred line invoice documents which we wish to edit as a > whole locally and send back to the server on an explicit user save > command. This is something which I wouldn't expect to typically be > needful in a web application. > > I can certainly find examples of large websites on python via django, > but desktop business applications are a bit harder to come by. I > believe that eric4 is a good example but I was hoping for a largish > project involving sqlalchemy as well. > > Thanks, > Joel -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: negative implications of using multiple declarative Base classes
I'm glad you brought this up. It seems to me that the the declarative instrumentation keys classes by their unqualified class name, precluding using the same class name for different declarative subclasses (ie, in different modules). On Jul 9, 12:01 pm, Randy Syring wrote: > I have been, naively it seems, using multiple declarative Base classes > in my webapp. They all share the same metadata object. > > I have found one negative ramification of this, which is that string > references (like what can be used in relation()) won't find the object > if they are not using the same Base. Are there others? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] changing polymorphic class
Is there a reason for preventing updates to the polymorphic_on column? I tried removing that branch (mapper.py:1628) and the mapper tests all pass. (although there are problems with other tests that are unaffected by this change) a. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] More trouble with pickle and relations
Finally managed to create a minimal example to reproduce the pickling
bug I've been having for ages:
import pickle
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
engine = sql.create_engine("sqlite:///:memory:")
Model = declarative_base()
class Child1(Model):
__tablename__ = 'child1'
parent_id = sql.Column(sql.String,
sql.ForeignKey('parent.id'),
primary_key=True)
class Child2(Model):
__tablename__ = 'child2'
parent_id = sql.Column(sql.String,
sql.ForeignKey('parent.id'),
primary_key=True)
class Parent(Model):
__tablename__ = 'parent'
id = sql.Column(sql.String, primary_key=True)
children1 = orm.relation(Child1)
children2 = orm.relation(Child2)
class Screen:
def __init__(self, obj, parent=None):
self.obj = obj
self.parent = parent
obj = Parent()
screen1 = Screen(obj)
screen1.errors = [obj.children1, obj.children2]
screen2 = Screen(Child2(), screen1)
pickle.loads(pickle.dumps(screen2))
produces:
Traceback (most recent call last):
File "pickleprob.py", line 40, in
pickle.loads(pickle.dumps(screen2))
File "/usr/lib/python2.6/pickle.py", line 1374, in loads
return Unpickler(file).load()
File "/usr/lib/python2.6/pickle.py", line 858, in load
dispatch[key](self)
File "/usr/lib/python2.6/pickle.py", line 1217, in load_build
setstate(state)
File "/home/avdd/work/careflight/src/intranet.ops/carenet/src/
sqlalchemy.6/lib/sqlalchemy/orm/collections.py", line 618, in
__setstate__
self.attr = getattr(d['owner_state'].obj().__class__,
d['key']).impl
AttributeError: type object 'NoneType' has no attribute 'children2'
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: need 0.6_beta2-compat declarative meta
In a metaclass's __init__, the attributes have already been placed on
the class, so mutating the attributes dict has no effect.
Try setting the id attribute directly:
self.id = PrimaryKey(...)
On Mar 27, 6:04 pm, Daniel Robbins wrote:
> Hi All,
>
> In 0.6_beta2, the following code is not properly adding a primary key Column
> via DeclarativeMeta which calls my PrimaryKey() function:
>
> def PrimaryKey(seqprefix):
> return Column(Integer, Sequence(seqprefix, optional=True),
> primary_key=True)
>
> class ClassDefaults(DeclarativeMeta):
> def __init__(cls,classname, bases, dict_):
> seqprefix = getattr(cls,'__tablename__',None)
> dict_['id'] = PrimaryKey(seqprefix=seqprefix)
> return DeclarativeMeta.__init__(cls, classname, bases, dict_)
>
> Base = declarative_base(metaclass=ClassDefaults)
>
> class Location(Base):
> __tablename__ = 'location'
> parent_id = Column(Integer, ForeignKey('location.id'))
> parent = relation('Location', backref=backref('children'),
> remote_side='location.c.id')
> name = UniqueString(25)
> desc = Column(String(80))
>
> SQLAlchemy 0.6_beta2 complains on table initialization:
>
> File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py", line
> 444, in _configure_pks
> "key columns for mapped table '%s'" % (self,
> self.mapped_table.description))
> sqlalchemy.exc.ArgumentError: Mapper Mapper|Location|location could not
> assemble any primary key columns for mapped table 'location'
>
> This worked under 0.6_beta1 (and likely earlier versions of SQLAlchemy).
>
> Can someone send me some code similar to above that works with 0.6_beta2, or
> is this a bug in beta2?
>
> Thanks,
>
> Daniel
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: unable to understand this error
Hi anusha,
The error is unrelated to your Login query because sqlalchemy compiles
the mappers as late as possible. It might help if you put somewhere
in your application code, after you have imported all your entities,
the lines:
from sqlalchemy import orm
orm.compile_mappers()
to catch these sorts of errors early.
Now the specific problem you see is that the ORM uses the declared
ForeignKeys to determine how to join tables for a relation, but on
your Detail table you have two foreign keys back to account and the
ORM doesn't know which one to use.
On Feb 16, 9:49 pm, anusha kadambala
wrote:
> hello all,
>
> My tables are giving following error at the time of querying. The tables got
> created properly but when i query on some table it is showing error on other
> tables.I didnt understand whats the issue here. I am querying on login table
> it is giving error on debitnote details tables.
>
> Tables:
> **
> class Login(Base):
> __tablename__ = 'login'
> usercode = Column(Integer,primary_key=True)
> username = Column(Text)
> userpassword = Column(Text)
> userrole = Column(Text)
>
> def __init__(self,username,userpassword,userrole):
> self.username = username
> self.userpassword = userpassword
> self.userrole = userrole
>
> login_table = Login.__table__
>
> class Account(Base):
> __tablename__ = "account"
> accountcode = Column(Integer, primary_key = True)
> groupcode = Column(Integer, ForeignKey("groups.groupcode"), nullable =
> False)
> groups = relation(Groups, backref = backref("Account", order_by =
> accountcode))
> accountname = Column(Text, nullable = False)
> basedon = Column(Text)
> accountdesc = Column(Text)
> openingbalance = Column(Numeric(13,2))
> openingdate = Column(TIMESTAMP)
> initialbalance = Column(Numeric(13,2))
>
> def
> __init__(self,groupcode,groups,accountname,basedon,accountdesc,openingbalance,openingdate,initialbalance):
> self.groupcode = groupcode
> self.groups = groups
> self.accountname = accountname
> self.basedon = basedon
> self.accountdesc = accountdesc
> self.openingbalance = openingbalance
> self.openingdate = openingdate
> self.initialbalance = initialbalance
>
> account_table = Account.__table__
>
> class DebitnoteMaster(Base):
> __tablename__ = "debitnotemaster"
> vouchercode = Column(String(40), primary_key = True)
> sbillno = Column(String(40))
> voucherdate = Column(TIMESTAMP, nullable = False)
> reffdate = Column(TIMESTAMP)
> booktype = Column(Text)
> chequeno = Column(Text)
> bankname = Column(Text)
> debitnarration = Column(Text, nullable = False)
>
> def
> __init__(self,vouchercode,sbillno,voucherdate,reffdate,booktype,chequeno,bankname,debitnarration):
> self.vouchercode = vouchercode
> self.sbillno = sbillno
> self.voucherdate = voucherdate
> self.reffdate = reffdate
> self.booktype = booktype
> self.chequeno = chequeno
> self.bankname = bankname
> self.debitnarration = debitnarration
>
> debitnotemaster_table = DebitnoteMaster.__table__
>
> class DebitnoteDetails(Base):
> __tablename__ = "debitnotedetails"
> dndtcode = Column(Integer, primary_key = True)
> vouchercode = Column(String(40),
> ForeignKey("debitnotemaster.vouchercode"))
> debitnotemaster = relation(DebitnoteMaster, backref =
> backref("DebitnoteDetails", order_by = dndtcode))
> craccountcode = Column(Integer, ForeignKey("account.accountcode"),
> nullable = False)
> account = relation(Account, backref = backref("DebitnoteDetails",
> order_by = dndtcode))
> draccountcode = Column(Integer, ForeignKey("account.accountcode"),
> nullable = False)
> account = relation(Account, backref = backref("DebitnoteDetails",
> order_by = dndtcode))
> amount = Column(Numeric(13,2), nullable = False)
>
> def __init__(self,vouchercode,craccountcode,draccountcode,amount):
> self.vouchercode = vouchercode
> self.craccountcode = craccountcode
> self.draccountcode = draccountcode
> self.amount = amount
>
> debitnotedetails_table = DebitnoteDetails.__table__
>
>
>
> Error:
> *
>
> Traceback (most recent call last):
> File "/usr/lib/python2.6/dist-packages/twisted/web/server.py", line 150,
> in process
> self.render(resrc)
> File "/usr/lib/python2.6/dist-packages/twisted/web/server.py", line 157,
> in render
> body = resrc.render(self)
> File "/usr/lib/python2.6/dist-packages/twisted/web/resource.py", line 190,
> in render
> return m(request)
> File "/usr/lib/python2.6/dist-packages/twisted/web/xmlrpc.py", line 118,
> in render_POST
> defer.maybeDeferred(function, *args).addErrback(
> --- ---
> File "/usr/lib/python2.6/dist-packages/twisted/internet/defer.py", line
> 106, in maybeD
[sqlalchemy] Re: pickling errors
Well after a day of tracking this down, I found that has something to do with reference cycles. Not sure what exactly, but making an attribute non-persistent solved the problem. And nothing to do with SQLAlchemy. Has anyone else noticed that pickle is not the most robust of protocols? There seems to be differences between pypickle and cpickle, and various protocol versions. And the original error was only intermittent (even in a single thread, so not concurrency- related). As an aside, when using pypickle, protocol=2, I get this error with ordering_list: pickle:1182 load_append list.append(value) sqlalchemy.orm.collections:918 append fn(self, item) sqlalchemy.ext.orderinglist:234 append self._order_entity(len(self) - 1, entity, self.reorder_on_append) AttributeError: 'OrderingList' object has no attribute 'reorder_on_append' On Feb 14, 5:48 am, Michael Bayer wrote: > On Feb 13, 2010, at 11:03 AM, avdd wrote: > > > > > I'm getting some strange errors with unpickling. I've tried all > > combinations of pickle/cPickle and protocol in (0,1,2) and still > > getting this apparent random error: > > > sqlalchemy.orm.collections:622 __setstate__ > > self.attr = getattr(d['owner_state'].obj().__class__, > > d['key']).impl > > AttributeError: type object 'NoneType' has no attribute 'flights' > > > When I inspect this frame, I see that: > > >>>> d['owner_state'].obj > > > object at 0xa1b5acc>> > > > So it seems that __setstate__ is not being called for the > > InstanceState object. > > pickling of instances (using 0.5.8 or 0.6beta) is widely tested and used > successfully in great volume, so you'd have to provide more specifics. note > that pickle formats may not be entirely compatible coming from an older > version of 0.5 (like pre 0.5.5) and 0.6. > > > > > Any ideas? > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] pickling errors
I'm getting some strange errors with unpickling. I've tried all combinations of pickle/cPickle and protocol in (0,1,2) and still getting this apparent random error: sqlalchemy.orm.collections:622 __setstate__ self.attr = getattr(d['owner_state'].obj().__class__, d['key']).impl AttributeError: type object 'NoneType' has no attribute 'flights' When I inspect this frame, I see that: >>> d['owner_state'].obj > So it seems that __setstate__ is not being called for the InstanceState object. Any ideas? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Confusion over postgresql drivers
Bruce Momjiam takes a swipe at the python postgresql drivers: http://archives.postgresql.org/pgsql-hackers/2010-02/msg00351.php Confined as the above discussion is to the ghetto of a mailing list, perhaps someone knowledgeable here can respond publicly? a. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: session.add() vs session.merge() and delete child
On Feb 2, 2:52 am, "Michael Bayer" wrote: > the behavior you first illustrated, that of merge() and add() not acting > the same regarding pending changes, was a behavior that was somewhat in > the realm of a bug. I mentioned the other day it was fixed in r6711. Well no, not in 0.5 (r6712). -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: session.add() vs session.merge() and delete child
So I get around this by essentially doing: # called on every request def refresh_model(context, obj): context.get_db().add(obj) def store_model(context, obj): db = object_session(obj) if db: db.expunge(obj) obj = db.merge(obj) db.flush() return obj Which seems to be working well so far. Mike, I understand you are busy and all, but I was hoping to learn what you thought of the usage I mentioned: 1. create or load object, don't flush, and detach 2. reattach object, modify, don't flush and detach 3. repeat 2 as necessary 4. when ready, reattach object and commit -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: passive updates and concurrent mod error
This seems to be only a problem for FKs that are also PKs, i.e. if you remove primary_key from C.p above, the example works as expected. I'm just wondering, am I going to save myself a lot of pain if I stick to surrogate, immutable primary keys? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] passive updates and concurrent mod error
Something a bit more normal now.
Combining optimistic concurrency control with cascading PK updates is
problematic, if you load the child relation, the session issues
unecessary updates for the children, resulting in
ConcurrentModificationError
"""
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
__metaclass__ = type
engine = sql.create_engine("postgresql:///avdd")
metadata = sql.MetaData(bind=engine)
db = orm.create_session(bind=engine, autocommit=False)
T = declarative_base(metadata=metadata)
class P(T):
__tablename__ = 'p'
id = sql.Column(sql.String, primary_key=True)
version = sql.Column(sql.Integer, nullable=False, default=1)
cc = orm.relation('C', backref='parent', passive_updates=True)
__mapper_args__ = {'version_id_col': version}
class C(T):
__tablename__ = 'c'
i = sql.Column(sql.String, primary_key=True)
p = sql.Column(sql.String,
sql.ForeignKey('p.id', onupdate='cascade',
ondelete='cascade'),
primary_key=True)
version = sql.Column(sql.Integer, nullable=False, default=1)
__mapper_args__ = {'version_id_col': version}
metadata.create_all()
P.__table__.delete().execute()
with db.transaction:
p = P(id='P1', cc=[C(i='C.1'), C(i='C.2')])
db.add(p)
db.expunge_all()
p = db.query(P).first()
with db.transaction:
p.id = 'P2'
# ok, no ConcModError
db.expunge_all()
p = db.query(P).first()
with db.transaction:
p.id = 'P3'
p.cc
# issues spurious updates, throws ConcModError
"""
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: session.add() vs session.merge() and delete child
On Jan 31, 4:33 am, Michael Bayer wrote:
> this example is too compliated for me to understand without great effort,
> perhaps someone else has the time to follow it more closely - it appears to
> be creating and closing many new sessions and add()ing objects between them -
> an unusual series of events.
My use case is this: I am writing a framework that allows you to
create and update objects over a series of requests, stashing the
object in the session between requests. I undersood this to be one of
the supported use-cases of SQLAlchemy.
So the general sequence of events is:
1. create or load object, don't flush, and detach
2. reattach object, modify, don't flush and detach
3. repeat 2 as necessary
4. when ready, reattach object and commit
I had previously used merge(), but that causes too much database
chatter and slows things down considerably. dont_load doesn't support
"dirty" objects. So I saw that add() can reattach a detached instance
to a session, and it all appears to work as expected (namely changing
attributes and adding/changing child relations) *except* child
deletions.
I hope that is clearer.
Actually, I just tried it against 0.6 trunk and this script works. It
doesn't work in 0.5 (r6712)
Sorry about clagging inline, google groups doesn't have an attachment
facility.
"""
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
engine = sql.create_engine("sqlite:///:memory:")
metadata = sql.MetaData(bind=engine)
DB = orm.sessionmaker(bind=engine)
T = declarative_base(metadata=metadata)
class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
info = sql.Column(sql.String)
cc = orm.relation('C',
backref='a',
cascade='all,delete-orphan')
def __repr__(self):
return " >> a: %s cc=%s" % (self.info, len(self.cc))
class C(T):
__tablename__ = 'c'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
metadata.create_all()
A.__table__.delete().execute()
def get():
return DB().query(A).first()
def change_detached(a, s, i):
# attach instance to a session to operate on it
# then detach it again to store it in a session
# change and attribute, delete one child and add another
# so len(cc) is unchanged
db = DB()
db.add(a)
a.info = s
del a.cc[-1]
a.cc.append(C(i=i))
db.close()
def store(a, merge=False):
db = DB()
if merge:
db.merge(a)
else:
db.add(a)
db.commit()
db.close()
# create and store instance
store(A(id=1, info='blah', cc=[C(i=1), C(i=2)]))
assert len(get().cc) == 2 # ok
# get and detach instance
a = get()
orm.object_session(a).close()
# change 1
change_detached(a, 'change-one', 3)
# store, reattaching with merge()
store(a, True)
assert len(get().cc) == 2 # ok
# get and detach instance
a = get()
orm.object_session(a).close()
# change 2
change_detached(a, 'change-two', 4)
# store, reattaching with add()
store(a, False)
assert len(get().cc) == 2 # fails!
"""
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] session.add() vs session.merge() and delete child
I'm using session.add() to refresh my objects while working on them,
because I don't want to merge them with the persistent state. But it
appears deletes aren't carrying across to child relations:
$ cat listdelete.py; python listdelete.py
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
echo = 0
engine = sql.create_engine("sqlite:///:memory:", echo=bool(echo))
metadata = sql.MetaData(bind=engine)
DB = orm.sessionmaker(bind=engine)
T = declarative_base(metadata=metadata)
class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
info = sql.Column(sql.String)
cc = orm.relation('C',
backref='a',
cascade='all,delete-orphan')
def __repr__(self):
return " >> a: %s cc=%s" % (self.info, len(self.cc))
class C(T):
__tablename__ = 'c'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
def get():
return DB().query(A).first()
def change(a, s, i):
orm.object_session(a).close()
db = DB()
db.add(a)
a.info = s
del a.cc[-1]
a.cc.append(C(i=i))
db.close()
metadata.create_all()
A.__table__.delete().execute()
db = DB()
a = A(id=1, info='blah', cc=[C(i=1), C(i=2)])
db.add(a)
db.commit()
db.close()
print get()
# merge and flush
a = get()
change(a, 'change one', 3)
db = DB()
db.merge(a)
db.commit()
db.close()
print get()
# add and flush
a = get()
change(a, 'change two', 4)
db = DB()
db.add(a)
db.commit()
db.close()
print get()
>> a: blah cc=2
>> a: change one cc=2
>> a: change two cc=3
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: orderinglist and delete-orphan cascade
On Jan 29, 3:52 am, "Michael Bayer" wrote: > you > need to delete the object individually and flush before altering the > collection. Thanks for the advice, but I can't do that because I'm working with objects (generically) in the detached state and committing later. I'll find another way of maintaining the order. a. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] orderinglist and delete-orphan cascade
Is this a bug or am I doing something wrong?
$ cat testordlist.py ; python testordlist.py
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.ext.declarative import declarative_base
engine = sql.create_engine("sqlite:///:memory:")
metadata = sql.MetaData(bind=engine)
db = orm.create_session(bind=engine)
T = declarative_base(metadata=metadata)
class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
uc = orm.relation('UC', order_by=('i',), cascade='all,delete-
orphan')
oc = orm.relation('OC', order_by=('i',), cascade='all,delete-
orphan',
collection_class=ordering_list('i'))
class UC(T):
__tablename__ = 'uc'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
class OC(T):
__tablename__ = 'oc'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
metadata.create_all()
a = A(id=1)
a.oc = [OC(), OC()]
a.uc = [UC(i=1), UC(i=2)]
with db.begin():
db.add(a)
with db.begin():
del db.query(A).first().uc[0]
with db.begin():
del db.query(A).first().oc[0]
Traceback (most recent call last):
File "testordlist.py", line 40, in
del db.query(A).first().oc[0]
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 449, in __exit__
self.commit()
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 378, in commit
self._prepare_impl()
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 362, in _prepare_impl
self.session.flush()
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 1354, in flush
self._flush(objects)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
session.py", line 1432, in _flush
flush_context.execute()
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
unitofwork.py", line 261, in execute
UOWExecutor().execute(self, tasks)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
unitofwork.py", line 753, in execute
self.execute_save_steps(trans, task)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
unitofwork.py", line 768, in execute_save_steps
self.save_objects(trans, task)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
unitofwork.py", line 759, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/mapper.py",
line 1417, in _save_obj
c = connection.execute(statement.values(value_params), params)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 835, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 885, in _execute_clauseelement
return self.__execute_context(context)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 907, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 961, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/engine/
base.py", line 942, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.IntegrityError: (IntegrityError) columns a_id, i are
not unique u'UPDATE oc SET i=? WHERE oc.a_id = ? AND oc.i = ?' [0, 1,
1]
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Column property vs. Python (class) property for calculated columns
Here's my reworking of the example for time types:
"""
from datetime import datetime, timedelta, date
from sqlalchemy import MetaData, Table, Column, DateTime, Date,
Interval
from sqlalchemy.orm import mapper, create_session
metadata = MetaData('postgresql:///avdd')
interval_table1 = Table('period1', metadata,
Column('start', DateTime, primary_key=True),
Column('end', DateTime, primary_key=True))
interval_table2 = Table('period2', metadata,
Column('start', DateTime, primary_key=True),
Column('length', Interval, primary_key=True))
metadata.create_all()
class BasePeriod(object):
@hybrid
def contains(self, instant):
return (self.start <= instant) & (instant < self.end)
@hybrid
def intersects(self, other):
return (self.start < other.end) & (self.end > other.start)
def __repr__(self):
return "%s(%s..%s)" % (self.__class__.__name__, self.start,
self.end)
class Period1(BasePeriod):
length = hybrid_property(lambda s: s.end - s.start)
def __init__(self, start, end):
self.start = start
self.end = end
mapper(Period1, interval_table1)
class Period2(BasePeriod):
end = hybrid_property(lambda s: s.start + s.length)
def __init__(self, start, length):
self.start = start
self.length = length
mapper(Period2, interval_table2)
session = create_session()
intervals = [timedelta(seconds=1),
timedelta(1),
timedelta(366)]
instants = [datetime.now(),
datetime(2000, 1, 2, 3, 4, 5),
datetime(1987, 6, 5, 4, 3, 2, 1)]
session.begin()
for i in instants:
for j in intervals:
session.add(Period1(i, i + j))
session.add(Period2(i, j))
session.commit()
session.expunge_all()
for ptype in (Period1, Period2):
q = session.query(ptype)
for p in q.filter(ptype.length < timedelta(10)):
print p, p.length
for p in q.filter(ptype.end < date.today()):
print p, p.length
now = datetime.now()
for p in q.filter(ptype.contains(now)):
print p, p.contains(now)
other = Period2(datetime.now(), timedelta(7))
for p in q.filter(ptype.intersects(other)).order_by(ptype.length):
print p, p.intersects(other)
"""
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Column property vs. Python (class) property for calculated columns
The descriptor works by returning an SQL Expression when called on a class argument: Interval.length -> SQL Expression Interval.contains(arg) -> SQLExpression and operates normally on instances, ie, just runs the function. On Jan 15, 11:41 pm, bojanb wrote: > Thanks Mike. I must admit I don't understand why that code works, but > it does. I guess that's the "Alchemy" in "SQLAlchemy" :-) > > However, I have issues with the difference in NULL value semantics > between Python and SQL. Ie. if a calculated column is defined via a > column_property as price*amount, then the result will be NULL if any > of the values is NULL. However, in Python, None*something throws a > TypeError, so the hybrid_property getter function needs to be filled > with lots of IFs. When called as class properties, the descriptors always generate SQL expressions as above. When called as instance properties, it just calls your function and you can do what you like with None values, e.g.: @hybrid_property def length(self): return self.thing is not None and self.thing or None > Also, this solution can't be used for date calculations, as timedelta > objects are needed. So I guess I will stick with a mix of Python The example works in postgresql with timestamps and intervals/ timedeltas. ;-) a. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: array column as primary key
Thanks! Works great.
On Jan 14, 2:27 am, "Michael Bayer" wrote:
> avdd wrote:
> > I want to map a table with a postgresql array as a primary key.
> > PostgreSQL supports it, and everything works until the session wants
> > to use the list returned from the query as an instance key. How can
> > I intercept the row returned to wrap it in a tuple? I can't figure
> > out translate_row!
>
> id skip translate_row and instead use TypeDecorator around PGArray.
> you'd override process_result_value to return tuple(value).
>
>
>
> > """
> > from sqlalchemy.orm import sessionmaker
> > from sqlalchemy import Column, Integer, create_engine
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.dialects.postgresql.base import ARRAY
>
> > engine = create_engine('postgresql:///avdd')
> > DB = sessionmaker(bind=engine)
>
> > class A(declarative_base()):
> > __tablename__ = 'a'
> > ids = Column(ARRAY(Integer()), primary_key=True)
>
> > A.__table__.delete(bind=engine).execute()
>
> > a = A()
> > a.ids = (1,2,3)
>
> > db = DB()
> > db.add(a)
> > db.commit()
>
> > del a, db
>
> > db = DB()
> > print db.query(A).all()
> > """
>
> > Traceback (most recent call last):
> > File "testarraypk.py", line 25, in
> > print db.query(A).all()
> > File "lib/sqlalchemy/orm/query.py", line 1217, in all
> > return list(self)
> > File "lib/sqlalchemy/orm/query.py", line 1376, in instances
> > rows = [process[0](row, None) for row in fetch]
> > File "lib/sqlalchemy/orm/mapper.py", line 1681, in _instance
> > instance = session_identity_map.get(identitykey)
> > File "lib/sqlalchemy/orm/identity.py", line 145, in get
> > state = dict.get(self, key, default)
> > TypeError: unhashable type: 'list'
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group at
> >http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] array column as primary key
I want to map a table with a postgresql array as a primary key.
PostgreSQL supports it, and everything works until the session wants
to use the list returned from the query as an instance key. How can
I intercept the row returned to wrap it in a tuple? I can't figure
out translate_row!
"""
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql.base import ARRAY
engine = create_engine('postgresql:///avdd')
DB = sessionmaker(bind=engine)
class A(declarative_base()):
__tablename__ = 'a'
ids = Column(ARRAY(Integer()), primary_key=True)
A.__table__.delete(bind=engine).execute()
a = A()
a.ids = (1,2,3)
db = DB()
db.add(a)
db.commit()
del a, db
db = DB()
print db.query(A).all()
"""
Traceback (most recent call last):
File "testarraypk.py", line 25, in
print db.query(A).all()
File "lib/sqlalchemy/orm/query.py", line 1217, in all
return list(self)
File "lib/sqlalchemy/orm/query.py", line 1376, in instances
rows = [process[0](row, None) for row in fetch]
File "lib/sqlalchemy/orm/mapper.py", line 1681, in _instance
instance = session_identity_map.get(identitykey)
File "lib/sqlalchemy/orm/identity.py", line 145, in get
state = dict.get(self, key, default)
TypeError: unhashable type: 'list'
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: lazy instrumented attributes and pickle
Thanks, Mike. I was calling compile_mappers before importing the modules. Whoops! On Dec 18, 1:10 pm, avdd wrote: > On Dec 18, 12:58 pm, avdd wrote: > > > # testlazy.py > > No, I'm wrong. Investigating further... -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: lazy instrumented attributes and pickle
On Dec 18, 12:58 pm, avdd wrote: > # testlazy.py No, I'm wrong. Investigating further... -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: lazy instrumented attributes and pickle
I
On Dec 18, 3:04 am, "Michael Bayer" wrote:
> in addition to the compile_mappers() step, all of your mapper() calls (or
> declarative classes) need to have been imported into the application
> before any unpickling occurs. The error you see below is still
> symptomatic of non-compiled mappers.
I don't think that's right:
"""
# testlazy.py
import pickle
import sqlalchemy as sql
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.orderinglist import ordering_list
m = sql.MetaData()
base = declarative_base(metadata=m)
class A(base):
__tablename__ = 'a'
id = sql.Column(sql.Integer(), primary_key=True)
bs = orm.relation('B', collection_class=ordering_list('index'),
backref='b')
class B(base):
__tablename__ = 'b'
a_id = sql.Column(sql.Integer(), sql.ForeignKey('a.id'),
primary_key=True)
index = sql.Column(sql.Integer(), primary_key=True)
orm.compile_mappers()
e = sql.create_engine('sqlite:///:memory:')
m.create_all(bind=e)
del e
a = A()
a.bs[:] = [B()]
a_pickle = pickle.dumps(a)
del a
orm.clear_mappers()
orm.compile_mappers()
e = sql.create_engine('sqlite:///:memory:')
db = orm.sessionmaker(bind=e)()
a = db.merge(pickle.loads(a_pickle))
"""
$ python testlazy.py
Traceback (most recent call last):
File "testlazy.py", line 36, in
a = db.merge(pickle.loads(a_pickle))
File "/usr/lib/python2.6/pickle.py", line 1374, in loads
return Unpickler(file).load()
File "/usr/lib/python2.6/pickle.py", line 858, in load
dispatch[key](self)
File "/usr/lib/python2.6/pickle.py", line 1217, in load_build
setstate(state)
File "/home/avdd/tmp/src/sqlalchemy.5/lib/sqlalchemy/orm/
collections.py", line 612, in __setstate__
self.attr = getattr(d['owner_state'].obj().__class__, d
['key']).impl
AttributeError: type object 'A' has no attribute 'bs'
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: base classes that have nothing to do with table inheritence
You could inject the attributes in a metaclass: def common_columns(): return dict(id = Column(Integer, primary_key=True), foo = Column(String)) Base = None class mymeta(DeclarativeMeta): def __init__(self, name, bases, attrs): if Base is not None: # a real sub class attrs.update(common_columns()) DeclarativeMeta.__init__(self, name, bases, attrs) Base = declarative_base(metaclass=mymeta) But note that the declarative system has a counter so the column definitions are ordered correctly for create statements. I don't know if this would adversely affect that. On Dec 17, 9:30 pm, Chris Withers wrote: > Hi All, > > So, say you have some common methods and field definitions that you want > to share across a bunch of mapper classes. My python head says that > these should all go in a base class, say, for example: > > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.schema import Column > from sqlalchemy.types import Integer, String, DateTime > > Base = declarative_base() > > class TheBase(Base): > > id = Column(Integer, primary_key=True) > ref = Column(Integer, nullable=False, index=True) > valid_from = Column(DateTime(), nullable=False, index=True) > valid_to = Column(DateTime(), index=True) > entered_by = Column(String(255), nullable=False, index=True) > deleted_by = Column(String(255), index=True) > > def some_func(self, x,y): > ... > > But, this results in: > > sqlalchemy.exc.InvalidRequestError: Class does not > have a __table__ or __tablename__ specified and does not inherit from an > existing table-mapped class. > > How should I create a class like this? This isn't about table > inheritance or the like and I'm *sure* I was told an easy solution for > this specific use case before, but I can't find it for the life of me now... > > 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: lazy instrumented attributes and pickle
On further investigation I see that this is actually per-instance lazy, so no app-level hook will solve it. So somehow __setstate__ needs to re-initialise the attribute. I do actually merge() the object back into the new session, and that is where the error first occurs. My web handling machinery masked the original traceback, which is this: bondi.web:500 invoke_controller self.context.thaw(request) bondi.app:93 thaw self.screen.thaw() bondi.view:153 thaw self.model = getmeta(self.model).refresh_model(self.context, self.model) bondi.model:309 refresh_model return self.do_refresh_model(context, model) bondi.model:477 do_refresh_model return self.getdb(context).merge(obj) sqlalchemy.orm.session:1162 merge return self._merge(instance, dont_load=dont_load, _recursive=_recursive) sqlalchemy.orm.session:1182 _merge key = mapper._identity_key_from_state(state) sqlalchemy.orm.mapper:1086 _identity_key_from_state return self.identity_key_from_primary_key (self._primary_key_from_state(state)) sqlalchemy.orm.mapper:1097 _primary_key_from_state return [self._get_state_attr_by_column(state, column) for column in self.primary_key] sqlalchemy.orm.mapper: _get_state_attr_by_column return self._get_col_to_prop(column).getattr(state, column) sqlalchemy.orm.properties:99 getattr return state.get_impl(self.key).get(state, state.dict) AttributeError: 'NoneType' object has no attribute 'get' On Dec 17, 6:46 pm, avdd wrote: > I use pickle to serialise unsaved objects in a user session. Normally > this works fine, except that for development I use an auto-reloading > server, and pickling some objects is hitting a case where some lazy > attribute isn't fully compiled. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] lazy instrumented attributes and pickle
I use pickle to serialise unsaved objects in a user session. Normally
this works fine, except that for development I use an auto-reloading
server, and pickling some objects is hitting a case where some lazy
attribute isn't fully compiled.
...
File '/home/avdd/work/careflight/src/intranet.ops2/carenet/lib/
python2.5/site-packages/sqlalchemy/orm/collections.py', line 607 in
__getstate__
return {'key': self.attr.key,
AttributeError: 'NoneType' object has no attribute 'key'
This is when using an ordering_list.
I would have assumed that calling orm.compile_mappers() is enough to
prevent this problem, but that is not so.
Is there some hook that I can call when my application is fully
initialised to ensure that all attributes are fully instrumented and
avoid this pickling problem? Or can I just do a sweep of all my
mapper attributes at startup?
While the problem is nothing more than an inconvenience for me, I
intend to make heavy use of pickled objects for draft object storage
and don't want my users losing data across system restarts.
a.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: merge, cascade and uselist=False
On Dec 14, 12:35 pm, Michael Bayer wrote: > confirmed. this is fixed in r6553 trunk/0.6 / r6554 0.5 branch. Thanks Mike, you're a legend! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] merge, cascade and uselist=False
Hi
I'm trying to merge objects across sessions and I'm seeing some odd
behavour with a one-one child relation:
"""
import sqlalchemy as sql
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
engine = sql.create_engine('sqlite:///:memory:')
metadata = sql.MetaData(bind=engine)
DB = orm.sessionmaker(bind=engine, autoflush=False)
class _base(object):
def __repr__(o):
return "<%s 0x%x id=%r>" % (type(o).__name__, id(o), o.id)
base = declarative_base(metadata=metadata, cls=_base)
class Parent(base):
__tablename__ = 'a'
id = sql.Column(sql.Integer(), nullable=False, primary_key=True)
child = orm.relation("Child1", uselist=False, cascade="all,delete-
orphan")
children = orm.relation("Child2", uselist=True,
cascade="all,delete-orphan")
class Child1(base):
__tablename__ = 'b'
id = sql.Column(sql.Integer(), nullable=False, primary_key=True)
p_id = sql.Column(sql.Integer(), sql.ForeignKey("a.id"))
class Child2(base):
__tablename__ = 'c'
id = sql.Column(sql.Integer(), nullable=False, primary_key=True)
p_id = sql.Column(sql.Integer(), sql.ForeignKey("a.id"))
metadata.create_all()
db = DB()
c1 = Child1()
c2 = Child2()
p = Parent(id=1, child=c1, children=[c2])
db.add(p)
db.commit()
db.close()
db1 = DB()
p1 = db1.query(Parent).first()
print p1, p1.child, p1.children
print
p1.child = None
p1.children[:] = []
db1.close()
db2 = DB()
p2 = db2.merge(p1)
print p2, p2.child, p2.children
print
"""
I get this output:
[]
[]
that is, when uselist=False, setting the attribute to None does not
persist across the merge.
Thanks,
a.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: a renaming proposal
On Jul 27, 9:45 am, jason kirtland <[EMAIL PROTECTED]> wrote: > This is the last opportunity > for terminology changes for a while, so I offer this up for discussion. Does anyone else think "orm.relation" is wrong? Perhaps "relationship" if you must have a noun, or "relates_to", etc, but "relation" could cement the popular misunderstanding of "relational database". a. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
