Re: [sqlalchemy] Session query on different server is not returning an object
Yeah, sounds very reasonable, my first thought was to isolate a session to each subprocess. The only reason I thought about working with detached objects is because I'm having trouble reproducing the problem, and I thought if I worked clean sessions, these problems shouldn't occur. Thanks for your time Mike. //J On Tuesday, 28 August 2012 16:25:47 UTC+2, Michael Bayer wrote: On Aug 28, 2012, at 2:54 AM, Jakob D. wrote: Shouldn't open transactions within the session close when I issue a session.remove() yes. Does an open transaction mean I cannot issue any queries at all before closing them? You can issue as many queries as you want at any time. the transaction accumulates state as these queries proceed. The isolation level of the transaction, which is usually configurable, determines how this transaction will interact with other concurrent transactions going on, to what degree concurrent data will be exposed, etc. A good overview is at: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29 The service is basically a set of subprocesses doing the same thing. After each iteration of the subprocess, the session is removed, and any exceptions will issue a rollback. OK, this is something you need to be careful with, if your program is using fork() or some variant (subprocess, multiprocessing, etc).A DBAPI connection generally will not travel along process boundaries. A Session also, when passed across a process boundary, naturally creates a second Session, since that's how child processes work. If you are calling close()/remove() only in the parent/child, you'd still have a second Session where this has not been done. Inside of a new fork(), you need to be creating a brand new engine using create_engine(), which establishes connection state local to that fork. Then you need to establish a brand new Session to go along with it. If you're using scoped_session(), making sure the registry has no Session when the fork starts, by calling scoped_session.remove(), is a good idea. Objects that are sent along the process boundary should be added to the subprocess Session using either merge() or add(). Besides remove and commit, what can I do to close open transactions? A commit should issue a flush? close() the session, or remove(), it's done. I thought I should explicitly create a new session, in case the remove fails and as you said, to make sure I'm using the session that's expected. I also thought about doing this for each query and then removing the session right after, maybe working with detached objects. I thought this way, there should be no problems with the session being in a undesirable state from a previous iteration since I'm always working on a fresh session. working with detached objects and many new sessions, such that you are leaving the objects detached as their normal state and creating sessions just for ad-hoc queries, is not a good way to approach the problem, and works against the design of SQLAlchemy as well as the database itself. When an object is detached, it pretty much shouldn't be used at all except to store it in a cache, or to place it into another Session. So if your detached object pattern is just so that you can transfer objects over subprocess boundaries, then its OK. But the parent process and child process should have just a single Session at a time (per thread, but I assume you're not using threads). When objects are in use, they should be associated with a Session. The lifespan of the Session should cover a regular series of operations against a field of objects. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Vmpab6oYo-cJ. 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] SQLAlchemy column_property
I have two models:
class Report(Base):
__tablename__ = 'report'
id = Column(Integer, primary_key=True)
class ReportPhoto(Base):
__tablename__ = 'report_photo'
id = Column(Integer, primary_key=True)
report_id = Column(Integer, ForeignKey(Report.id), nullable=False)
report = relationship(Report, uselist=False,
backref=backref('report_photo', uselist=True))
And I would like to add column to Report model which indicates is there any
records within ReportPhoto. I try to use [column_property][1] this way:
class Report(Base):
__tablename__ = 'report'
id = Column(Integer, primary_key=True)
has_photo = column_property(
select(ReportPhoto.any())
)
but get an error `NameError: name 'ReportPhoto' is not defined`. How I can
fix this issue?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/Q9sa44l7Pp8J.
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] OrderingList weird reordering after session.commit()
Many thanks Michael,
I'll read the doc more carefully in the future or after a night of
brain-off mode :)
It works now as expected.
And many thanks again to the SQLAlchemy team for such a great essential
tool.
Kind grateful regards
--
Gilles Lenfant
Le mercredi 29 août 2012 04:00:34 UTC+2, Michael Bayer a écrit :
On Aug 28, 2012, at 11:59 AM, Gilles Lenfant wrote:
Hi,
I extracted the code from a complex application and isolated the issue I'm
actually facing in a short sample here : https://gist.github.com/3499127
To summarize : I need a parent entity that has ordered children. I use
for this a
sqlalchemy.ext.orderinglist.ordering_list that is supposed to manage
transparently the position attribute of any child to keep a persistent
ordered list (line 20).
The real application logic appends or inserts the Child entities at
the appropriate position using the OrderingList canonical API.
So far so good, my app manages perfectly the children order (as in lines
57, 58) , but when commiting the changes, the last created child is moved
to the end of its parent OrderingList (lines 61-66), whatever's the
position where I inserted that child before commiting.
I don't know if I'm facicing an SQLAlchemy real issue or if I missed
something (sorry, I'm an SA noob). Googling around didn't yield valuable
information regarding this specific issue.
Any help would be really appreciated. Kind regards.
the orderinglist maintains ordering during in-Python mutations. It
doesn't have any effect when rows are loaded from the database, which is a
good thing as the database can send the rows back ordered much more
efficiently than if the ordering list had to respond to every row loaded.
So your relationship needs an order by:
children = relationship('Child', backref='parent',
collection_class=ordering_list('position'),
order_by=Child.position
)
I checked the docs, it is there though these are kind of old docs and they
spend a lot of time repeating themselves and also we didn't have the
benefit of Sphinx directives when these were written, if you read the doc
for OrderingList.__init__ (
http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/orderinglist.html#sqlalchemy.ext.orderinglist.OrderingList.__init__):
This implementation relies on the list starting in the proper order, so
be *sure* to put an order_by on your relationship. But this should be
more prominently mentioned at the top as a note box and some of the
redundancy should be chopped down here.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/1VevvaSYNnMJ.
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] __main__ error
Hello,
I'm quite new in SA and I'm having some problems with a script. After running
the script, I'm getting this error:
[__main__.Boreholes object at 0xb7a2958c, __main__.Boreholes object at
0xb7a2962c, __main__.Boreholes object at 0xb7a2966c, __main__.Boreholes
object at 0xb7a296cc, __main__.Boreholes object at 0xb7a2972c,
__main__.Boreholes object at 0xb7a2978c, __main__.Boreholes object at
0xb7a297ec, __main__.Boreholes object at 0xb7a2984c, __main__.Boreholes
object at 0xb7a298ac, __main__.Boreholes object at 0xb7a2990c,
__main__.Boreholes object at 0xb7a2996c, __main__.Boreholes object at
0xb7a299cc, __main__.Boreholes object at 0xb7a29a2c, __main__.Boreholes
object at 0xb7a29a8c, __main__.Boreholes object at 0xb7a29aec,
__main__.Boreholes object at 0xb7a29b4c, __main__.Boreholes object at
0xb7a29bac, __main__.Boreholes object at 0xb7a29c2c, __main__.Boreholes
object at 0xb7a29cac, __main__.Boreholes object at 0xb7a29d2c,
__main__.Boreholes object at 0xb7a29dac, __main__.Boreholes object at
0xb7a29e2c, __main__.Boreholes object at 0xb7a29eac, __main__.Boreholes
object at 0xb7a29f2c, __main__.Boreholes object at 0xb7a29fac,
__main__.Boreholes object at 0xb7a3504c, __main__.Boreholes object at
0xb7a350cc, __main__.Boreholes object at 0xb7a3514c, __main__.Boreholes
object at 0xb7a351cc, __main__.Boreholes object at 0xb7a3524c,
__main__.Boreholes object at 0xb7a352cc, __main__.Boreholes object at
0xb7a3534c, __main__.Boreholes object at 0xb7a353cc, __main__.Boreholes
object at 0xb7a3544c, __main__.Boreholes object at 0xb7a354cc,
__main__.Boreholes object at 0xb7a3554c, __main__.Boreholes object at
0xb7a355cc, __main__.Boreholes object at 0xb7a3564c, __main__.Boreholes
object at 0xb7a356cc, __main__.Boreholes object at 0xb7a3574c,
__main__.Boreholes object at 0xb7a357cc, __main__.Boreholes object at
0xb7a3584c, __main__.Boreholes object at 0xb7a358cc, __main__.Boreholes
object at 0xb7a3594c, __main__.Boreholes object at 0xb7a359cc,
__main__.Boreholes object at 0xb7a35a4c, __main__.Boreholes object at
0xb7a35acc, __main__.Boreholes object at 0xb7a35b4c, __main__.Boreholes
object at 0xb7a35bcc, __main__.Boreholes object at 0xb7a35c4c,
__main__.Boreholes object at 0xb7a35ccc]
the script is here:
*
# import things to be used
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import mapper, sessionmaker
# connecting to database engine
myengine = create_engine('postgresql://postgres:pass@localhost:5432/mop',
echo=False)
# MetaData: describing the database schema
mymetadata = MetaData(myengine)
# load existing tables in postgis database
boreholes = Table('boreholes_point_wgs84', mymetadata, autoload=True)
# defining empty classes to be mapped to existing tables
class Boreholes(object):
pass
# mapping empty classes to existing tables [ie. ORM]
Boreholesmapper = mapper(Boreholes, boreholes)
# session operations [finding data, adding data, modifying data and deleting
data]
Session = sessionmaker(bind=myengine)
mysession = Session()
# queries
alldata = mysession.query(Boreholes).all()
print alldata
*
I'm working with python2.4 and SA 0.7.8 in rhel5 (32-bit).
Any hint is appreciated,
Best regards,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es
necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
--
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] __main__ error
doesn't look like an error to me. It's just the result of your
print alldata
call. It depends on Postgis which attributes are available on the
Borehole instances, but you could use
print dir(alldata[0])
to get an idea about what is available.
On Wed, Aug 29, 2012 at 12:37 PM, Gery . gameji...@hotmail.com wrote:
Hello,
I'm quite new in SA and I'm having some problems with a script. After
running the script, I'm getting this error:
[__main__.Boreholes object at 0xb7a2958c, __main__.Boreholes object at
0xb7a2962c, __main__.Boreholes object at 0xb7a2966c, __main__.Boreholes
object at 0xb7a296cc, __main__.Boreholes object at 0xb7a2972c,
__main__.Boreholes object at 0xb7a2978c, __main__.Boreholes object at
0xb7a297ec, __main__.Boreholes object at 0xb7a2984c, __main__.Boreholes
object at 0xb7a298ac, __main__.Boreholes object at 0xb7a2990c,
__main__.Boreholes object at 0xb7a2996c, __main__.Boreholes object at
0xb7a299cc, __main__.Boreholes object at 0xb7a29a2c, __main__.Boreholes
object at 0xb7a29a8c, __main__.Boreholes object at 0xb7a29aec,
__main__.Boreholes object at 0xb7a29b4c, __main__.Boreholes object at
0xb7a29bac, __main__.Boreholes object at 0xb7a29c2c, __main__.Boreholes
object at 0xb7a29cac, __main__.Boreholes object at 0xb7a29d2c,
__main__.Boreholes object at 0xb7a29dac, __main__.Boreholes object at
0xb7a29e2c, __main__.Boreholes object at 0xb7a29eac, __main__.Boreholes
object at 0xb7a29f2c, __main__.Boreholes object at 0xb7a29fac,
__main__.Boreholes object at 0xb7a3504c, __main__.Boreholes object at
0xb7a350cc, __main__.Boreholes object at 0xb7a3514c, __main__.Boreholes
object at 0xb7a351cc, __main__.Boreholes object at 0xb7a3524c,
__main__.Boreholes object at 0xb7a352cc, __main__.Boreholes object at
0xb7a3534c, __main__.Boreholes object at 0xb7a353cc, __main__.Boreholes
object at 0xb7a3544c, __main__.Boreholes object at 0xb7a354cc,
__main__.Boreholes object at 0xb7a3554c, __main__.Boreholes object at
0xb7a355cc, __main__.Boreholes object at 0xb7a3564c, __main__.Boreholes
object at 0xb7a356cc, __main__.Boreholes object at 0xb7a3574c,
__main__.Boreholes object at 0xb7a357cc, __main__.Boreholes object at
0xb7a3584c, __main__.Boreholes object at 0xb7a358cc, __main__.Boreholes
object at 0xb7a3594c, __main__.Boreholes object at 0xb7a359cc,
__main__.Boreholes object at 0xb7a35a4c, __main__.Boreholes object at
0xb7a35acc, __main__.Boreholes object at 0xb7a35b4c, __main__.Boreholes
object at 0xb7a35bcc, __main__.Boreholes object at 0xb7a35c4c,
__main__.Boreholes object at 0xb7a35ccc]
the script is here:
*
# import things to be used
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import mapper, sessionmaker
# connecting to database engine
myengine = create_engine('postgresql://postgres:pass@localhost:5432/mop',
echo=False)
# MetaData: describing the database schema
mymetadata = MetaData(myengine)
# load existing tables in postgis database
boreholes = Table('boreholes_point_wgs84', mymetadata, autoload=True)
# defining empty classes to be mapped to existing tables
class Boreholes(object):
pass
# mapping empty classes to existing tables [ie. ORM]
Boreholesmapper = mapper(Boreholes, boreholes)
# session operations [finding data, adding data, modifying data and deleting
data]
Session = sessionmaker(bind=myengine)
mysession = Session()
# queries
alldata = mysession.query(Boreholes).all()
print alldata
*
I'm working with python2.4 and SA 0.7.8 in rhel5 (32-bit).
Any hint is appreciated,
Best regards,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO
es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
--
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] __main__ error
thanks Robert, using your suggestion I get:
['__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__',
'__hash__', '__init__', '__module__', '__new__', '__reduce__', '__reduce_ex__',
'__repr__', '__setattr__', '__str__', '__weakref__', '_sa_class_manager',
'_sa_instance_state', u'comments', u'core_rec_m', u'fid', u'geom', u'h_f_mwm2',
u'id', u'latitude', u'longitude', u'max_pen_m', u'source', u'station',
u'survey', u't_g_ckm1', u'type', u'w_depth_m']
so you were right, but one question, when I use:
**
# starting with ORM: declarative base class [catalog of classes mapped to
database tables relative to this base]
mybase = declarative_base(metadata=mymetadata)
# defining classes to be mapped
class Boreholes(mybase):
__tablename__ = 'boreholes_point_wgs84'
__table_args__ = {'autoload':True}
def __init__ (self, id, fid, longitude, latitude, w_depth_m, station,
type, survey, source, max_pen_m, core_rec_m, t_g_ckm1, h_f_mwm2, comments):
self.id = id
self.fid = fid
self.longitude = longitude
self.latitude = latitude
self.w_depth_m = w_depth_m
self.station = station
self.type = type
self.survey = survey
self.source = source
self.max_pen_m = max_pen_m
self.core_rec_m = core_rec_m
self.t_g_ckm1 = t_g_ckm1
self.h_f_mwm2 = h_f_mwm2
self.comments = comments
def __repr__ (self):
return Boreholes(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s) % (self.id, self.fid, self.longitude, self.latitude,
self.w_depth_m, self.station, self.type, self.survey, self.source,
self.max_pen_m, self.core_rec_m, self.t_g_ckm1, self.h_f_mwm2, self.comments)
**
whit the same print.alldata I can output all the rows, but in this way I don't
know how to make the mapper(Boreholes, boreholes) I used in the first place. Is
it possible to print all the rows with my original code?
Thanks,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es
necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:05:18 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
doesn't look like an error to me. It's just the result of your
print alldata
call. It depends on Postgis which attributes are available on the
Borehole instances, but you could use
print dir(alldata[0])
to get an idea about what is available.
On Wed, Aug 29, 2012 at 12:37 PM, Gery . gameji...@hotmail.com wrote:
Hello,
I'm quite new in SA and I'm having some problems with a script. After
running the script, I'm getting this error:
[__main__.Boreholes object at 0xb7a2958c, __main__.Boreholes object at
0xb7a2962c, __main__.Boreholes object at 0xb7a2966c, __main__.Boreholes
object at 0xb7a296cc, __main__.Boreholes object at 0xb7a2972c,
__main__.Boreholes object at 0xb7a2978c, __main__.Boreholes object at
0xb7a297ec, __main__.Boreholes object at 0xb7a2984c, __main__.Boreholes
object at 0xb7a298ac, __main__.Boreholes object at 0xb7a2990c,
__main__.Boreholes object at 0xb7a2996c, __main__.Boreholes object at
0xb7a299cc, __main__.Boreholes object at 0xb7a29a2c, __main__.Boreholes
object at 0xb7a29a8c, __main__.Boreholes object at 0xb7a29aec,
__main__.Boreholes object at 0xb7a29b4c, __main__.Boreholes object at
0xb7a29bac, __main__.Boreholes object at 0xb7a29c2c, __main__.Boreholes
object at 0xb7a29cac, __main__.Boreholes object at 0xb7a29d2c,
__main__.Boreholes object at 0xb7a29dac, __main__.Boreholes object at
0xb7a29e2c, __main__.Boreholes object at 0xb7a29eac, __main__.Boreholes
object at 0xb7a29f2c, __main__.Boreholes object at 0xb7a29fac,
__main__.Boreholes object at 0xb7a3504c, __main__.Boreholes object at
0xb7a350cc, __main__.Boreholes object at 0xb7a3514c, __main__.Boreholes
object at 0xb7a351cc, __main__.Boreholes object at 0xb7a3524c,
__main__.Boreholes object at 0xb7a352cc, __main__.Boreholes object at
0xb7a3534c, __main__.Boreholes object at 0xb7a353cc, __main__.Boreholes
object at 0xb7a3544c, __main__.Boreholes object at 0xb7a354cc,
__main__.Boreholes object at 0xb7a3554c, __main__.Boreholes object at
0xb7a355cc, __main__.Boreholes object at 0xb7a3564c, __main__.Boreholes
RE: [sqlalchemy] __main__ error
Thanks Simon, that's the way, it works well, but is it possible to output all
the tables without specifying every column?
Thanks,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es
necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
From: si...@simonking.org.uk
Date: Wed, 29 Aug 2012 12:19:42 +0100
Subject: Re: [sqlalchemy] __main__ error
To: sqlalchemy@googlegroups.com
On Wed, Aug 29, 2012 at 11:37 AM, Gery . gameji...@hotmail.com wrote:
Hello,
I'm quite new in SA and I'm having some problems with a script. After
running the script, I'm getting this error:
[__main__.Boreholes object at 0xb7a2958c, __main__.Boreholes object at
0xb7a2962c, __main__.Boreholes object at 0xb7a2966c, __main__.Boreholes
object at 0xb7a296cc, __main__.Boreholes object at 0xb7a2972c,
__main__.Boreholes object at 0xb7a2978c, __main__.Boreholes object at
0xb7a297ec, __main__.Boreholes object at 0xb7a2984c, __main__.Boreholes
object at 0xb7a298ac, __main__.Boreholes object at 0xb7a2990c,
__main__.Boreholes object at 0xb7a2996c, __main__.Boreholes object at
0xb7a299cc, __main__.Boreholes object at 0xb7a29a2c, __main__.Boreholes
object at 0xb7a29a8c, __main__.Boreholes object at 0xb7a29aec,
__main__.Boreholes object at 0xb7a29b4c, __main__.Boreholes object at
0xb7a29bac, __main__.Boreholes object at 0xb7a29c2c, __main__.Boreholes
object at 0xb7a29cac, __main__.Boreholes object at 0xb7a29d2c,
__main__.Boreholes object at 0xb7a29dac, __main__.Boreholes object at
0xb7a29e2c, __main__.Boreholes object at 0xb7a29eac, __main__.Boreholes
object at 0xb7a29f2c, __main__.Boreholes object at 0xb7a29fac,
__main__.Boreholes object at 0xb7a3504c, __main__.Boreholes object at
0xb7a350cc, __main__.Boreholes object at 0xb7a3514c, __main__.Boreholes
object at 0xb7a351cc, __main__.Boreholes object at 0xb7a3524c,
__main__.Boreholes object at 0xb7a352cc, __main__.Boreholes object at
0xb7a3534c, __main__.Boreholes object at 0xb7a353cc, __main__.Boreholes
object at 0xb7a3544c, __main__.Boreholes object at 0xb7a354cc,
__main__.Boreholes object at 0xb7a3554c, __main__.Boreholes object at
0xb7a355cc, __main__.Boreholes object at 0xb7a3564c, __main__.Boreholes
object at 0xb7a356cc, __main__.Boreholes object at 0xb7a3574c,
__main__.Boreholes object at 0xb7a357cc, __main__.Boreholes object at
0xb7a3584c, __main__.Boreholes object at 0xb7a358cc, __main__.Boreholes
object at 0xb7a3594c, __main__.Boreholes object at 0xb7a359cc,
__main__.Boreholes object at 0xb7a35a4c, __main__.Boreholes object at
0xb7a35acc, __main__.Boreholes object at 0xb7a35b4c, __main__.Boreholes
object at 0xb7a35bcc, __main__.Boreholes object at 0xb7a35c4c,
__main__.Boreholes object at 0xb7a35ccc]
the script is here:
*
# import things to be used
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import mapper, sessionmaker
# connecting to database engine
myengine = create_engine('postgresql://postgres:pass@localhost:5432/mop',
echo=False)
# MetaData: describing the database schema
mymetadata = MetaData(myengine)
# load existing tables in postgis database
boreholes = Table('boreholes_point_wgs84', mymetadata, autoload=True)
# defining empty classes to be mapped to existing tables
class Boreholes(object):
pass
# mapping empty classes to existing tables [ie. ORM]
Boreholesmapper = mapper(Boreholes, boreholes)
# session operations [finding data, adding data, modifying data and deleting
data]
Session = sessionmaker(bind=myengine)
mysession = Session()
# queries
alldata = mysession.query(Boreholes).all()
print alldata
*
I'm working with python2.4 and SA 0.7.8 in rhel5 (32-bit).
Any hint is appreciated,
Best regards,
Gery
That's not an error - that is a list of instances of the Borehole
class, which is what you get back from Query.all().
The columns in your boreholes_point_wgs84 become properties of those
instances. So for example if the table has name, latitude and
longitude columns, you would be able to change your last 2 lines to
say something like:
for borehole in mysession.query(Boreholes).all():
print borehole.name, borehole.latitude, borehole.longitude
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
Re: [sqlalchemy] __main__ error
for borehole in alldata:
for attr in sorted(filter(lambda a: not a.startswith('_'), dir(borehole))):
print attr, getattr(borehole, attr)
could do the trick
On Wed, Aug 29, 2012 at 1:24 PM, Gery . gameji...@hotmail.com wrote:
thanks Robert, using your suggestion I get:
['__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__',
'__hash__', '__init__', '__module__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__',
'_sa_class_manager', '_sa_instance_state', u'comments', u'core_rec_m',
u'fid', u'geom', u'h_f_mwm2', u'id', u'latitude', u'longitude',
u'max_pen_m', u'source', u'station', u'survey', u't_g_ckm1', u'type',
u'w_depth_m']
so you were right, but one question, when I use:
**
# starting with ORM: declarative base class [catalog of classes mapped to
database tables relative to this base]
mybase = declarative_base(metadata=mymetadata)
# defining classes to be mapped
class Boreholes(mybase):
__tablename__ = 'boreholes_point_wgs84'
__table_args__ = {'autoload':True}
def __init__ (self, id, fid, longitude, latitude, w_depth_m,
station, type, survey, source, max_pen_m, core_rec_m, t_g_ckm1, h_f_mwm2,
comments):
self.id = id
self.fid = fid
self.longitude = longitude
self.latitude = latitude
self.w_depth_m = w_depth_m
self.station = station
self.type = type
self.survey = survey
self.source = source
self.max_pen_m = max_pen_m
self.core_rec_m = core_rec_m
self.t_g_ckm1 = t_g_ckm1
self.h_f_mwm2 = h_f_mwm2
self.comments = comments
def __repr__ (self):
return Boreholes(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s) % (self.id, self.fid, self.longitude, self.latitude,
self.w_depth_m, self.station, self.type, self.survey, self.source,
self.max_pen_m, self.core_rec_m, self.t_g_ckm1, self.h_f_mwm2,
self.comments)
**
whit the same print.alldata I can output all the rows, but in this way I
don't know how to make the mapper(Boreholes, boreholes) I used in the first
place. Is it possible to print all the rows with my original code?
Thanks,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO
es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:05:18 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
doesn't look like an error to me. It's just the result of your
print alldata
call. It depends on Postgis which attributes are available on the
Borehole instances, but you could use
print dir(alldata[0])
to get an idea about what is available.
On Wed, Aug 29, 2012 at 12:37 PM, Gery . gameji...@hotmail.com wrote:
Hello,
I'm quite new in SA and I'm having some problems with a script. After
running the script, I'm getting this error:
[__main__.Boreholes object at 0xb7a2958c, __main__.Boreholes object
at
0xb7a2962c, __main__.Boreholes object at 0xb7a2966c,
__main__.Boreholes
object at 0xb7a296cc, __main__.Boreholes object at 0xb7a2972c,
__main__.Boreholes object at 0xb7a2978c, __main__.Boreholes object at
0xb7a297ec, __main__.Boreholes object at 0xb7a2984c,
__main__.Boreholes
object at 0xb7a298ac, __main__.Boreholes object at 0xb7a2990c,
__main__.Boreholes object at 0xb7a2996c, __main__.Boreholes object at
0xb7a299cc, __main__.Boreholes object at 0xb7a29a2c,
__main__.Boreholes
object at 0xb7a29a8c, __main__.Boreholes object at 0xb7a29aec,
__main__.Boreholes object at 0xb7a29b4c, __main__.Boreholes object at
0xb7a29bac, __main__.Boreholes object at 0xb7a29c2c,
__main__.Boreholes
object at 0xb7a29cac, __main__.Boreholes object at 0xb7a29d2c,
__main__.Boreholes object at 0xb7a29dac, __main__.Boreholes object at
0xb7a29e2c, __main__.Boreholes object at 0xb7a29eac,
__main__.Boreholes
object at 0xb7a29f2c, __main__.Boreholes object at 0xb7a29fac,
__main__.Boreholes object at 0xb7a3504c, __main__.Boreholes object at
0xb7a350cc, __main__.Boreholes object at 0xb7a3514c,
__main__.Boreholes
object at 0xb7a351cc, __main__.Boreholes object at 0xb7a3524c,
__main__.Boreholes object at 0xb7a352cc, __main__.Boreholes object at
RE: [sqlalchemy] __main__ error
That was really helpful Robert, it works nicely, how could I modify this code
to get geojson format? is it possible in this way or is there a better approach?
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es
necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:28:44 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
for borehole in alldata:
for attr in sorted(filter(lambda a: not a.startswith('_'),
dir(borehole))):
print attr, getattr(borehole, attr)
could do the trick
On Wed, Aug 29, 2012 at 1:24 PM, Gery . gameji...@hotmail.com wrote:
thanks Robert, using your suggestion I get:
['__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__',
'__hash__', '__init__', '__module__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__',
'_sa_class_manager', '_sa_instance_state', u'comments', u'core_rec_m',
u'fid', u'geom', u'h_f_mwm2', u'id', u'latitude', u'longitude',
u'max_pen_m', u'source', u'station', u'survey', u't_g_ckm1', u'type',
u'w_depth_m']
so you were right, but one question, when I use:
**
# starting with ORM: declarative base class [catalog of classes mapped to
database tables relative to this base]
mybase = declarative_base(metadata=mymetadata)
# defining classes to be mapped
class Boreholes(mybase):
__tablename__ = 'boreholes_point_wgs84'
__table_args__ = {'autoload':True}
def __init__ (self, id, fid, longitude, latitude, w_depth_m,
station, type, survey, source, max_pen_m, core_rec_m, t_g_ckm1, h_f_mwm2,
comments):
self.id = id
self.fid = fid
self.longitude = longitude
self.latitude = latitude
self.w_depth_m = w_depth_m
self.station = station
self.type = type
self.survey = survey
self.source = source
self.max_pen_m = max_pen_m
self.core_rec_m = core_rec_m
self.t_g_ckm1 = t_g_ckm1
self.h_f_mwm2 = h_f_mwm2
self.comments = comments
def __repr__ (self):
return Boreholes(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s) % (self.id, self.fid, self.longitude, self.latitude,
self.w_depth_m, self.station, self.type, self.survey, self.source,
self.max_pen_m, self.core_rec_m, self.t_g_ckm1, self.h_f_mwm2,
self.comments)
**
whit the same print.alldata I can output all the rows, but in this way I
don't know how to make the mapper(Boreholes, boreholes) I used in the first
place. Is it possible to print all the rows with my original code?
Thanks,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO
es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:05:18 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
doesn't look like an error to me. It's just the result of your
print alldata
call. It depends on Postgis which attributes are available on the
Borehole instances, but you could use
print dir(alldata[0])
to get an idea about what is available.
On Wed, Aug 29, 2012 at 12:37 PM, Gery . gameji...@hotmail.com wrote:
Hello,
I'm quite new in SA and I'm having some problems with a script. After
running the script, I'm getting this error:
[__main__.Boreholes object at 0xb7a2958c, __main__.Boreholes object
at
0xb7a2962c, __main__.Boreholes object at 0xb7a2966c,
__main__.Boreholes
object at 0xb7a296cc, __main__.Boreholes object at 0xb7a2972c,
__main__.Boreholes object at 0xb7a2978c, __main__.Boreholes object at
0xb7a297ec, __main__.Boreholes object at 0xb7a2984c,
__main__.Boreholes
object at 0xb7a298ac, __main__.Boreholes object at 0xb7a2990c,
__main__.Boreholes object at 0xb7a2996c, __main__.Boreholes object at
0xb7a299cc, __main__.Boreholes object at 0xb7a29a2c,
Re: [sqlalchemy] __main__ error
you could create a method on the Boreholes mapper to convert an
instance to geojson (which I assume to be some sort of dictionary):
class Boreholes(mybase):
...
def geojson(self):
return dict([(attr, getattr(self, attr, None)) for attr in
['latitude', 'longitude', .. whatever else there is in geojson ...]])
On Wed, Aug 29, 2012 at 1:34 PM, Gery . gameji...@hotmail.com wrote:
That was really helpful Robert, it works nicely, how could I modify this
code to get geojson format? is it possible in this way or is there a better
approach?
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO
es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:28:44 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
for borehole in alldata:
for attr in sorted(filter(lambda a: not a.startswith('_'),
dir(borehole))):
print attr, getattr(borehole, attr)
could do the trick
On Wed, Aug 29, 2012 at 1:24 PM, Gery . gameji...@hotmail.com wrote:
thanks Robert, using your suggestion I get:
['__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__',
'__hash__', '__init__', '__module__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__',
'_sa_class_manager', '_sa_instance_state', u'comments', u'core_rec_m',
u'fid', u'geom', u'h_f_mwm2', u'id', u'latitude', u'longitude',
u'max_pen_m', u'source', u'station', u'survey', u't_g_ckm1', u'type',
u'w_depth_m']
so you were right, but one question, when I use:
**
# starting with ORM: declarative base class [catalog of classes mapped
to
database tables relative to this base]
mybase = declarative_base(metadata=mymetadata)
# defining classes to be mapped
class Boreholes(mybase):
__tablename__ = 'boreholes_point_wgs84'
__table_args__ = {'autoload':True}
def __init__ (self, id, fid, longitude, latitude, w_depth_m,
station, type, survey, source, max_pen_m, core_rec_m, t_g_ckm1,
h_f_mwm2,
comments):
self.id = id
self.fid = fid
self.longitude = longitude
self.latitude = latitude
self.w_depth_m = w_depth_m
self.station = station
self.type = type
self.survey = survey
self.source = source
self.max_pen_m = max_pen_m
self.core_rec_m = core_rec_m
self.t_g_ckm1 = t_g_ckm1
self.h_f_mwm2 = h_f_mwm2
self.comments = comments
def __repr__ (self):
return Boreholes(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s) % (self.id, self.fid, self.longitude, self.latitude,
self.w_depth_m, self.station, self.type, self.survey, self.source,
self.max_pen_m, self.core_rec_m, self.t_g_ckm1, self.h_f_mwm2,
self.comments)
**
whit the same print.alldata I can output all the rows, but in this way I
don't know how to make the mapper(Boreholes, boreholes) I used in the
first
place. Is it possible to print all the rows with my original code?
Thanks,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si
NO
es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT
necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken
Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:05:18 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
doesn't look like an error to me. It's just the result of your
print alldata
call. It depends on Postgis which attributes are available on the
Borehole instances, but you could use
print dir(alldata[0])
to get an idea about what is available.
On Wed, Aug 29, 2012 at 12:37 PM, Gery . gameji...@hotmail.com wrote:
Hello,
I'm quite new in SA and I'm having some problems with a script. After
running the script, I'm getting this error:
[__main__.Boreholes object at 0xb7a2958c, __main__.Boreholes
object
at
0xb7a2962c, __main__.Boreholes object at 0xb7a2966c,
__main__.Boreholes
object at 0xb7a296cc, __main__.Boreholes object at 0xb7a2972c,
__main__.Boreholes object at 0xb7a2978c, __main__.Boreholes object
at
0xb7a297ec, __main__.Boreholes object at 0xb7a2984c,
__main__.Boreholes
object at 0xb7a298ac, __main__.Boreholes object at
[sqlalchemy] Multiple identical relations in a Many-To-Many-Relationship
Hello,
the requirement to have multiple identical relations in a
Many-To-Many-Relationship may sound a bit absurd, however, I'm wondering
how I could do that with SA.
In principle this should work in the way that the association table would
have multiple identical rows for such a case. A straight-forward
many-to-many-reference-example shows that SA kind of strips such doubles
out. I already played a bit with some kwargs of the relation-function but
with no success.
I'm using SqlAlchemy 0.7.8 with Sqlite 3.7.9 and Python 2.7.3 at Ubuntu
12.04.
The attached code illustrates it. Before the session is committed, the
object references the part two times, after the commit it only does it
once. Neverthless (switching echo on shows that) the relation is inserted
two times in the association table and when accessing the parts-attribute
of the object also the select/join will bring that row two times. I would
then expect to have the part object two times in the parts-list. But
its not. How could I achieve that?
Thanks in advance.
Best regards, Frank
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/l_2zwhEFX70J.
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.
#-*- coding: utf-8 -*-
from sqlalchemy import create_engine, Column, String, Integer, Table, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relation
engine = create_engine('sqlite:///:memory:', echo=False, encoding='utf-8')
Base = declarative_base()
Session = sessionmaker(bind=engine)
object_part_assoc_table = Table('Object_Part', Base.metadata,
Column('object', String(length=100, convert_unicode=True), ForeignKey('Object.id')),
Column('part', String(length=100, convert_unicode=True), ForeignKey('Part.id'))
)
class Object(Base):
__tablename__ = Object
id = Column(Integer, primary_key=True)
name = Column(String)
parts = relation(Part, secondary=object_part_assoc_table)
class Part(Base):
__tablename__ = Part
id = Column(Integer, primary_key=True)
name = Column(String)
def __repr__(self):
return self.name
Base.metadata.create_all(engine)
session = Session()
object1 = Object(id=1, name=object1); session.add(object1)
part1 = Part(id=1, name=part1); session.add(part1)
object1.parts = [part1, part1]
print object1.parts
session.commit()
print object1.parts
[sqlalchemy] Re: Multiple identical relations in a Many-To-Many-Relationship
There was a little mistake in the example: The column declaration of the association table should use Integer as type not String. Nevertheless the the example runs :) the same way. So the mistake does not influence the above described behaviour. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/C06g0sMst2gJ. 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] __main__ error
cool, thanks Robert, I'll try that and see what comes up,
Cheers,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es
necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:54:49 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
you could create a method on the Boreholes mapper to convert an
instance to geojson (which I assume to be some sort of dictionary):
class Boreholes(mybase):
...
def geojson(self):
return dict([(attr, getattr(self, attr, None)) for attr in
['latitude', 'longitude', .. whatever else there is in geojson ...]])
On Wed, Aug 29, 2012 at 1:34 PM, Gery . gameji...@hotmail.com wrote:
That was really helpful Robert, it works nicely, how could I modify this
code to get geojson format? is it possible in this way or is there a better
approach?
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO
es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:28:44 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
for borehole in alldata:
for attr in sorted(filter(lambda a: not a.startswith('_'),
dir(borehole))):
print attr, getattr(borehole, attr)
could do the trick
On Wed, Aug 29, 2012 at 1:24 PM, Gery . gameji...@hotmail.com wrote:
thanks Robert, using your suggestion I get:
['__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__',
'__hash__', '__init__', '__module__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__',
'_sa_class_manager', '_sa_instance_state', u'comments', u'core_rec_m',
u'fid', u'geom', u'h_f_mwm2', u'id', u'latitude', u'longitude',
u'max_pen_m', u'source', u'station', u'survey', u't_g_ckm1', u'type',
u'w_depth_m']
so you were right, but one question, when I use:
**
# starting with ORM: declarative base class [catalog of classes mapped
to
database tables relative to this base]
mybase = declarative_base(metadata=mymetadata)
# defining classes to be mapped
class Boreholes(mybase):
__tablename__ = 'boreholes_point_wgs84'
__table_args__ = {'autoload':True}
def __init__ (self, id, fid, longitude, latitude, w_depth_m,
station, type, survey, source, max_pen_m, core_rec_m, t_g_ckm1,
h_f_mwm2,
comments):
self.id = id
self.fid = fid
self.longitude = longitude
self.latitude = latitude
self.w_depth_m = w_depth_m
self.station = station
self.type = type
self.survey = survey
self.source = source
self.max_pen_m = max_pen_m
self.core_rec_m = core_rec_m
self.t_g_ckm1 = t_g_ckm1
self.h_f_mwm2 = h_f_mwm2
self.comments = comments
def __repr__ (self):
return Boreholes(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s) % (self.id, self.fid, self.longitude, self.latitude,
self.w_depth_m, self.station, self.type, self.survey, self.source,
self.max_pen_m, self.core_rec_m, self.t_g_ckm1, self.h_f_mwm2,
self.comments)
**
whit the same print.alldata I can output all the rows, but in this way I
don't know how to make the mapper(Boreholes, boreholes) I used in the
first
place. Is it possible to print all the rows with my original code?
Thanks,
Gery
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si
NO
es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT
necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken
Sie
NICHT, wenn es NICHT notwendig ist.
Date: Wed, 29 Aug 2012 13:05:18 +0200
Subject: Re: [sqlalchemy] __main__ error
From: xrotw...@googlemail.com
To: sqlalchemy@googlegroups.com
doesn't look like an error to me. It's just the result of your
print alldata
call. It depends on Postgis which attributes are available on the
Borehole instances, but you could use
Re: [sqlalchemy] Multiple identical relations in a Many-To-Many-Relationship
On Aug 29, 2012, at 7:56 AM, Frank Hempel wrote: Hello, the requirement to have multiple identical relations in a Many-To-Many-Relationship may sound a bit absurd, however, I'm wondering how I could do that with SA. OK, you mean, duplicate rows in the association table. In principle this should work in the way that the association table would have multiple identical rows for such a case. A straight-forward many-to-many-reference-example shows that SA kind of strips such doubles out. I already played a bit with some kwargs of the relation-function but with no success. its not something relationship() is likely going to support. relationship() considers rows in the secondary table to be unique and ideally you'd have declared both FK columns as primary keys as well. All the mechanics of relationship loading and persisting work on the assumption that instances are unique in their collections - this follows from how relational schemas function. For example, with such a schema, it is completely impossible to remove just a single element from such a collection, without removing all the dupes (what would the DELETE statement be otherwise ?) .Joined eager loading would also be impossible to implement as it relies upon deduplication of rows. To even support this function in a half-broken way would be an enormous undertaking and probably have other show-stopping issues along the way that would further reduce it's feasability. -- 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] Multiple identical relations in a Many-To-Many-Relationship
Am 29.08.2012 17:55, schrieb Michael Bayer: In principle this should work in the way that the association table would have multiple identical rows for such a case. A straight-forward many-to-many-reference-example shows that SA kind of strips such doubles out. I already played a bit with some kwargs of the relation-function but with no success. its not something relationship() is likely going to support. relationship() considers rows in the secondary table to be unique and ideally you'd have declared both FK columns as primary keys as well. All the mechanics of relationship loading and persisting work on the assumption that instances are unique in their collections - this follows from how relational schemas function. For example, with such a schema, it is completely impossible to remove just a single element from such a collection, without removing all the dupes (what would the DELETE statement be otherwise ?) .Joined eager loading would also be impossible to implement as it relies upon deduplication of rows. To even support this function in a half-broken way would be an enormous undertaking and probably have other show-stopping issues along the way that would further reduce it's feasability. yes, you are totally right. Deleting just one of the doubles would not be possible at all, hence I should go for a clean db schema. Thanks for your comment! Regards, Frank -- 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] Using Postgresql's crosstab tablefunc in SQLAlchemy
Hey Michael,
First of all, thanks for the prompt answer and the pointer to the right
direction!
I fiddled around with this, fixed and packaged it up, documented it, and
created something hopefully useful to others:
https://github.com/makmanalp/sqlalchemy-crosstab-postgresql
- I found the $$ operator which removes the need for the awkward
crosstab_param. This is useful because I have query bits and pieces that I
already compose as functions (eg: get_year = lambda col:
cast(func.date_part('year', func.to_timestamp(col)), Integer) etc) and I
don't want to rewrite those using crosstab_param again.
- I support the second form of crosstab that takes another query that
defines possible columns, which IMHO is cleaner.
- I auto-add the required order_bys
Thanks again and hope this helps in the future.
Cheers,
~mali
On Fri, Aug 24, 2012 at 3:58 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Aug 24, 2012, at 1:52 PM, Mali Akmanalp wrote:
Hi All,
I've been struggling recently to get postgres crosstabs [0] to work with
SQLAlchemy. The interesting thing about these is that they require you to
pass in a *string of a query* into the function, rather than an actual SQL
expression, like so:
select * from crosstab('select column, row, count(foo) as 'value' from
derp');
The closest thing I could come up with to this was to use the subquery
function, doing:
sq = session.query(column, row, value).subquery()
for the inner part and then placing that into the outer func.crosstab().
But of course I don't want to manually turn the subquery into a string by
putting it into quotes myself since there probably are a billion edge cases
I could miss and there probably is a better way.
this is a new (and extremely awkward, wow) kind of SQL compilation
arrangement. Two ways to do it:
1. just use a string. connection/session.execute(select * from ...).
this syntax is totally specific to Postgresql in any case. It's unlikely
you need lots of fluency here as the construct is so awkward to start with.
2. create a custom construct; see the docs at
http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html. Here is a
prototype, keeping in mind I'm not deeply familiar with this construct, I'm
just showing some techniques you can use to have the construction of it be
fairly automated. I'm assuming that bound parameters are out of the
question, so I have a function crosstab_param here - you can also use
bindparam() if you want bound values in the embedded SELECT:
from sqlalchemy.sql import ColumnElement, FromClause, column,
literal_column
from sqlalchemy.ext.compiler import compiles
SELECT *
FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
class crosstab(FromClause):
def __init__(self, name, stmt, columns):
self.name = name
self.stmt = stmt
self.columns = columns
def _populate_column_collection(self):
self._columns.update(
column(name, type=type_)
for name, type_ in self.names
)
def crosstab_param(value):
# we can't use bound parameters in crosstab?
return literal_column(''%s'' % value)
@compiles(crosstab)
def visit_element(element, compiler, **kw):
return
crosstab('%s order by 1, 2') AS %s(%s)
% (
compiler.visit_select(element.stmt),
element.name,
, .join(
%s %s % (c.name, compiler.visit_typeclause(c))
for c in element.c
)
)
from sqlalchemy import Table, Column, MetaData, Text, or_, select
m = MetaData()
ct = Table('ct', m, Column('rowid', Text),
Column('attribute', Text),
Column('value', Text))
stmt = select([ct]).where(
or_(
ct.c.attribute == crosstab_param('att2'),
ct.c.attribute == crosstab_param('att3')
)
)
print select(['*']).select_from(crosstab(ct, stmt, ct.c))
--
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.
[sqlalchemy] eager load many-to-many without generating cycles
Hey folks, I am looking for a bit of advice. I have a many-to-many relationship which goes through a middle class: class Foo(Base): id = Column(Integer, primary_key=True) class Bar(Base): id = Column(Integer, primary_key=True) class FooToBar(Base): foo_id = Column(Integer, primary_key=True, ForeignKey(foo.id)) bar_id = Column(Integer, primary_key=True, ForeignKey(bar.id)) I want to be able to eagerly-load a tree from a Foo to all its Bars, or a tree from a Bar to all its Foos, but I do not want to eagerly-load the entire graph of related Foo and Bar objects. If I naively set up the relationship, that is what happens: FooToBar.foo = relationship(Foo, backref=bars, lazy=joined) FooToBar.bar = relationship(Bar, backref=foos, lazy=joined) Is there a straightforward way to accomplish tree-like loading? (My actual code masks the existence of FooToBar with two association_proxy attributes, but I don't think that is relevant for the example.) Could I generate two different models (in place of FooToBar), each with a one-way eager-loading relationship? Or could I use join_depth on the relationship to cap how far the recursion goes? Thanks, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/tSDOeiYU4_0J. 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] eager load many-to-many without generating cycles
On Aug 29, 2012, at 4:45 PM, Christopher Lee wrote: Hey folks, I am looking for a bit of advice. I have a many-to-many relationship which goes through a middle class: class Foo(Base): id = Column(Integer, primary_key=True) class Bar(Base): id = Column(Integer, primary_key=True) class FooToBar(Base): foo_id = Column(Integer, primary_key=True, ForeignKey(foo.id)) bar_id = Column(Integer, primary_key=True, ForeignKey(bar.id)) I want to be able to eagerly-load a tree from a Foo to all its Bars, or a tree from a Bar to all its Foos, but I do not want to eagerly-load the entire graph of related Foo and Bar objects. If I naively set up the relationship, that is what happens: FooToBar.foo = relationship(Foo, backref=bars, lazy=joined) FooToBar.bar = relationship(Bar, backref=foos, lazy=joined) Is there a straightforward way to accomplish tree-like loading? (My actual code masks the existence of FooToBar with two association_proxy attributes, but I don't think that is relevant for the example.) Could I generate two different models (in place of FooToBar), each with a one-way eager-loading relationship? Or could I use join_depth on the relationship to cap how far the recursion goes? Eager loading should be guarding against any kind of endless recursion from occurring here. You should be able to set up the relationships like you have, and it will normally halt as soon as it sees the same relationship more than once in the path. join_depth would set a number of how many times its acceptable to see a particular relationship in the path. If there isn't any join_depth set, it might be defaulting to not doing it at all, I'd have to try it out to confirm. Have you tried just running it ? -- 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] eager load many-to-many without generating cycles
On Wed, Aug 29, 2012 at 3:41 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 29, 2012, at 4:45 PM, Christopher Lee wrote: Hey folks, I am looking for a bit of advice. I have a many-to-many relationship which goes through a middle class: class Foo(Base): id = Column(Integer, primary_key=True) class Bar(Base): id = Column(Integer, primary_key=True) class FooToBar(Base): foo_id = Column(Integer, primary_key=True, ForeignKey(foo.id)) bar_id = Column(Integer, primary_key=True, ForeignKey(bar.id)) I want to be able to eagerly-load a tree from a Foo to all its Bars, or a tree from a Bar to all its Foos, but I do not want to eagerly-load the entire graph of related Foo and Bar objects. If I naively set up the relationship, that is what happens: FooToBar.foo = relationship(Foo, backref=bars, lazy=joined) FooToBar.bar = relationship(Bar, backref=foos, lazy=joined) Is there a straightforward way to accomplish tree-like loading? (My actual code masks the existence of FooToBar with two association_proxy attributes, but I don't think that is relevant for the example.) Could I generate two different models (in place of FooToBar), each with a one-way eager-loading relationship? Or could I use join_depth on the relationship to cap how far the recursion goes? Eager loading should be guarding against any kind of endless recursion from occurring here. You should be able to set up the relationships like you have, and it will normally halt as soon as it sees the same relationship more than once in the path. join_depth would set a number of how many times its acceptable to see a particular relationship in the path. If there isn't any join_depth set, it might be defaulting to not doing it at all, I'd have to try it out to confirm. Have you tried just running it ? There is no endless recursion going on; each object is only loaded once. Sorry I was not specific. But, given the following object graph: Foo 1 - [Bar 1, Bar 2, Bar 3] Foo 2 - [Bar 4, Bar 5, Bar 6] Bar 1 - [Foo 1, Foo 2] When I query for Foo1, I want to eagerly load Foo1, Bar 1, Bar 2, and Bar3. I do not want to eagerly load Bar 1, Foo 2, Bar 4, Bar 5, Bar 6. Similarly, if I query for Bar 1, I want to eagerly load Foo1 and Foo2. I do not want to eagerly load Bar 2-6. Is that any clearer? -- 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] eager load many-to-many without generating cycles
On Aug 29, 2012, at 7:00 PM, Christopher Lee wrote: There is no endless recursion going on; each object is only loaded once. Sorry I was not specific. But, given the following object graph: Foo 1 - [Bar 1, Bar 2, Bar 3] Foo 2 - [Bar 4, Bar 5, Bar 6] Bar 1 - [Foo 1, Foo 2] When I query for Foo1, I want to eagerly load Foo1, Bar 1, Bar 2, and Bar3. I do not want to eagerly load Bar 1, Foo 2, Bar 4, Bar 5, Bar 6. Similarly, if I query for Bar 1, I want to eagerly load Foo1 and Foo2. I do not want to eagerly load Bar 2-6. Is that any clearer? sure, join_depth will control how deep a particular joined/subquery eagerload chain will go.Current behavior of join_depth is a simple check of how deep a particular relationship is located in an eager load chain, and this is not specific to the target relationship in contradiction to my earlier post where I was remembering incorrectly what it does. Setting it to one on both of these relationships will cause the eager chain to only go one mapper deep. This raises the issue of join_depth preventing eager loads entirely when the parent object is itself already the subject of unrelated eager load, which isn't quite what it was intended for (it was intended for recursive loops), so I wonder if a new feature might someday be warranted which allows the depth question to be answered in a more comprehensive way. -- 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.
