[sqlalchemy] A record is dropped on add
Hi,
I have a problem that when I add a record to 'cellarbook' using the
following code the 'old' cellarbook entry is dropped from the db.
oldCB = session.query(db.Cellarbook).get(7)
newCB = db.Cellarbook()
# set some data from existing dbitem
newCB.cellar = oldCB.cellar
newCB.authuser = oldCB.authuser
newCB.supplier = oldCB.supplier
newCB.vintage = oldCB.vintage
newBot = db.Bottle()
newCB.vintage.bottle.append(newBot)
newCB.bottle = newBot
newCB.drinkinfo = oldCB.drinkinfo
session.add(newCB)
session.commit()
After this I have newCB.id=10 in the database but oldCB.id=7 has gone.
I guess/think that I have a relation setup incorrectly which creates
this effect, but I can not put my finger on it.
Appreciate any hint.
Werner
The relation setup for the classes involved, and I suspect
Vintage.drinkinfo/Cellarbook.drinkinfo ones:
Vintage.vintadd = sao.relationship('Vintadd', uselist=False,
cascade=all, delete, delete-orphan,
single_parent=True)
Vintage.cellarbook = sao.relationship('Cellarbook', uselist=False,
cascade=all, delete, delete-orphan,
backref='vintage')
Vintage.drinkinfo = sao.relationship('Drinkinfo', backref='vintage',
cascade=all, delete, delete-orphan,
single_parent=True)
Bottle.cellarbook = sao.relationship('Cellarbook', uselist=False,
#cascade=all, delete, delete-orphan,
backref='bottle')
Bottle.vintage = sao.relationship('Vintage', backref='bottle',
#cascade=all, delete, delete-orphan,
single_parent=True)
Cellarbook.supplier = sao.relationship('Profilec',
primaryjoin=('Cellarbook.fk_supplier_id==Profilec.id'),
viewonly=True)
Cellarbook.cellar = sao.relationship('Cellar', backref='cellarbook')
Cellarbook.authuser = sao.relationship('Authuser')
Cellarbook.drinkinfo = sao.relationship('Drinkinfo', backref='cellarbook',
single_parent=True)
Cellarbook.purchase = sao.relationship('Purchase', backref='cellarbook',
cascade=all, delete,
delete-orphan)
Purchase.cellar = sao.relationship('Cellar')
Purchase.currency = sao.relationship('Currency')
Purchase.supplier = sao.relationship('Profilec',
primaryjoin=('Purchase.fk_supplier_id==Profilec.id'),
viewonly=True)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] A record is dropped on add
On 03/10/2013 11:42, Werner wrote:
Hi,
I have a problem that when I add a record to 'cellarbook' using the
following code the 'old' cellarbook entry is dropped from the db.
oldCB = session.query(db.Cellarbook).get(7)
newCB = db.Cellarbook()
# set some data from existing dbitem
newCB.cellar = oldCB.cellar
newCB.authuser = oldCB.authuser
newCB.supplier = oldCB.supplier
newCB.vintage = oldCB.vintage
newBot = db.Bottle()
newCB.vintage.bottle.append(newBot)
newCB.bottle = newBot
newCB.drinkinfo = oldCB.drinkinfo
session.add(newCB)
session.commit()
After this I have newCB.id=10 in the database but oldCB.id=7 has gone.
I guess/think that I have a relation setup incorrectly which creates
this effect, but I can not put my finger on it.
Appreciate any hint.
Werner
The relation setup for the classes involved, and I suspect
Vintage.drinkinfo/Cellarbook.drinkinfo ones:
Vintage.vintadd = sao.relationship('Vintadd', uselist=False,
cascade=all, delete, delete-orphan,
single_parent=True)
Vintage.cellarbook = sao.relationship('Cellarbook', uselist=False,
cascade=all, delete,
delete-orphan,
backref='vintage')
Vintage.drinkinfo = sao.relationship('Drinkinfo', backref='vintage',
cascade=all, delete,
delete-orphan,
single_parent=True)
Bottle.cellarbook = sao.relationship('Cellarbook', uselist=False,
#cascade=all, delete,
delete-orphan,
backref='bottle')
Bottle.vintage = sao.relationship('Vintage', backref='bottle',
#cascade=all, delete, delete-orphan,
single_parent=True)
Cellarbook.supplier = sao.relationship('Profilec',
primaryjoin=('Cellarbook.fk_supplier_id==Profilec.id'),
viewonly=True)
Cellarbook.cellar = sao.relationship('Cellar', backref='cellarbook')
Cellarbook.authuser = sao.relationship('Authuser')
Cellarbook.drinkinfo = sao.relationship('Drinkinfo',
backref='cellarbook',
single_parent=True)
Cellarbook.purchase = sao.relationship('Purchase', backref='cellarbook',
cascade=all, delete,
delete-orphan)
Purchase.cellar = sao.relationship('Cellar')
Purchase.currency = sao.relationship('Currency')
Purchase.supplier = sao.relationship('Profilec',
primaryjoin=('Purchase.fk_supplier_id==Profilec.id'),
viewonly=True)
If I create a new 'vintage' record then it works (see code below), but
that is another use case and I really would like to support both of them.
Werner
newCB = db.Cellarbook()
# set some data from existing dbitem
newCB.cellar = oldCB.cellar
newCB.authuser = oldCB.authuser
newCB.supplier = oldCB.supplier
newCB.drinkinfo = oldCB.drinkinfo
## use existing vintage
#newCB.vintage = oldCB.vintage
# create a new vintage
dbVintage = db.Vintage()
dbVintage.vintage = 2025
dbVintAdd = db.Vintadd()
dbVintage.vintadd = dbVintAdd
dbVintage.drinkinfo = newCB.drinkinfo
newCB.vintage = dbVintage
# create a bottle
newBot = db.Bottle()
newCB.vintage.bottle.append(newBot)
newCB.bottle = newBot
session.add(newCB)
session.flush()
session.commit()
--
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] using SQLA in distributed, concurrent, or asynchronous contexts?
Hi folks, I just got a new position and am hoping I can advocate for using SQLAlchemy there. I will be doing some backend web service stuff that interacts with a db, but likely in some form of distributed architecture. It's all going to be on AWS. I believe there will be some service implementation that either needs to be highly performant or highly concurrent or some combination of both. IE there may be lots of clients hitting the service and the service has to interact with third party payment services which might be slow, and we don't want the server choking just waiting on the third party responses. Up till now I haven't had to do stuff in this domain. Can anyone tell me what I might want to look into as far as ways of handling concurrency or non-blocking services that will play fair with SQLAlchemy? I read that trying to get SA working on Twisted is not a good plan. Would love any pointers on what to read up on, use, etc. Right now all my experience is on Pylons/Pyramid and has not had to use any fancy messaging, deferred processing, or concurreny handling so I think I have a lot reading ahead of me. Thanks! Iain -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] using WITH query AS, cte() with postgresql
Sorry for this follow-up, but I'm really at a loss as to what to check next…
What should I do if after I insert a new Appl row, the query we've worked on
in this thread is *not* finding it, i.e. it's not being returned in the result
set? I'm really surprised and thinking I must be missing something really basic?
I've deleted the lastname, firstname indexes and re-created them, but the newly
created row is still *not* being returned!
Here's the sql insert from the SA log:
2013-10-03 12:26:37,638 INFO [sqlalchemy.engine.base.Engine][Dummy-3] BEGIN
(implicit)
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3] INSERT
INTO appl (refid, appldate, lastname, firstname, cityid, cityid2) VALUES
(nextval('appl_refid_seq'), %(appldate)s, %(lastname)s, %(firstname)s,
%(cityid)s, %(cityid2)s) RETURNING appl.id
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3]
{'cityid2': None, 'cityid': None, 'firstname': u'test3', 'lastname': u'test3',
'appldate': u'10/3/2013'}
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Col
('id',)
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Row
(14574,)
2013-10-03 12:26:37,645 INFO [sqlalchemy.engine.base.Engine][Dummy-3] COMMIT
What's also really odd, is if I manually run the sql in psql, it works and
returns the new row:
WITH distinct_query AS (
SELECT DISTINCT ON (refid) id, refid, lastname, firstname, appldate
FROM appl WHERE lastname ILIKE 'test%'
GROUP BY refid, id, lastname, firstname, appldate ORDER BY refid, appldate
DESC
)
SELECT * from distinct_query ORDER BY lastname, firstname;
Another odd thing is in my application, I have another search feature, and it's
using an SA query that's not using the cte/distinct query below, and it works!
Sorry to have to ask this, but I'm stuck and need to get this working.
Thanks again for all the help!
On Oct 1, 2013, at 1:22 PM, Nathan Mailg nathanma...@gmail.com wrote:
Thanks Mike, that works!
I really appreciate you taking the time to show me how to get this working.
Do you have an amazon wish list or something like that somewhere?
On Sep 30, 2013, at 2:09 PM, Michael Bayer mike...@zzzcomputing.com wrote:
qlast, qfirst = 'a', 'b'
d = DBSession.query(Appl).\
distinct(Appl.refid).\
filter(Appl.lastname == qlast).\
filter(Appl.firstname == qfirst).\
group_by(Appl).\
order_by(Appl.refid)
d = d.cte('distinct_query')
q = DBSession.query(Appl).select_entity_from(d).\
join(Appl.city).\
order_by(d.c.lastname, d.c.firstname)
for row in q:
print row.refid, row.firstname, row.lastname, row.city.name
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] How to find the local column name given a ForeignKey object?
Hello there, I'm wondering if there is an easy way of finding the local column name given a ForeignKey. For example, the snippet below just gives me the remote column name: for fkey in meta.tables['mytable'].foreign_keys: print fkey.column I understand I could iterate over all the columns of the local table to find which has a foreign key, but I imagine there is a more direct way. Thanks! :) g. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Self-Referential scalar query as column_property [SOLVED]
On Tue, 2013-10-01 at 20:53 -0400, Michael Bayer wrote: is it that you want the column property declared inside the class declaration? you can use __declare_last__ as one option, here is that: class Project(Base): ... @classmethod def __declare_last__(cls): p_alias = cls.__table__.alias() cls.project_count = column_property( select([func.count(p_alias.c.object_id)]).\ where(p_alias.c.parent_id == cls.object_id). as_scalar() ) Yes, this is exactly what I was looking for; otherwise the definition of the model definition [it is a complex model] gets sort of smeared. This works perfectly. -- Adam Tauno Williams mailto:awill...@whitemice.org GPG D95ED383 Systems Administrator, Python Developer, LPI / NCLA -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] using WITH query AS, cte() with postgresql
are you using serializable isolation ? that would prevent an in-progress
transaction from seeing this new row committed elsewhere.
other than that, the SELECT in the log will show you what rows it's selecting
and using echo='debug' will show the rows it finds on the way back.
On Oct 3, 2013, at 2:35 PM, Nathan Mailg nathanma...@gmail.com wrote:
Sorry for this follow-up, but I'm really at a loss as to what to check next…
What should I do if after I insert a new Appl row, the query we've worked
on in this thread is *not* finding it, i.e. it's not being returned in the
result set? I'm really surprised and thinking I must be missing something
really basic?
I've deleted the lastname, firstname indexes and re-created them, but the
newly created row is still *not* being returned!
Here's the sql insert from the SA log:
2013-10-03 12:26:37,638 INFO [sqlalchemy.engine.base.Engine][Dummy-3] BEGIN
(implicit)
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3] INSERT
INTO appl (refid, appldate, lastname, firstname, cityid, cityid2) VALUES
(nextval('appl_refid_seq'), %(appldate)s, %(lastname)s, %(firstname)s,
%(cityid)s, %(cityid2)s) RETURNING appl.id
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3]
{'cityid2': None, 'cityid': None, 'firstname': u'test3', 'lastname':
u'test3', 'appldate': u'10/3/2013'}
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Col
('id',)
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Row
(14574,)
2013-10-03 12:26:37,645 INFO [sqlalchemy.engine.base.Engine][Dummy-3] COMMIT
What's also really odd, is if I manually run the sql in psql, it works and
returns the new row:
WITH distinct_query AS (
SELECT DISTINCT ON (refid) id, refid, lastname, firstname, appldate
FROM appl WHERE lastname ILIKE 'test%'
GROUP BY refid, id, lastname, firstname, appldate ORDER BY refid, appldate
DESC
)
SELECT * from distinct_query ORDER BY lastname, firstname;
Another odd thing is in my application, I have another search feature, and
it's using an SA query that's not using the cte/distinct query below, and it
works!
Sorry to have to ask this, but I'm stuck and need to get this working.
Thanks again for all the help!
On Oct 1, 2013, at 1:22 PM, Nathan Mailg nathanma...@gmail.com wrote:
Thanks Mike, that works!
I really appreciate you taking the time to show me how to get this working.
Do you have an amazon wish list or something like that somewhere?
On Sep 30, 2013, at 2:09 PM, Michael Bayer mike...@zzzcomputing.com wrote:
qlast, qfirst = 'a', 'b'
d = DBSession.query(Appl).\
distinct(Appl.refid).\
filter(Appl.lastname == qlast).\
filter(Appl.firstname == qfirst).\
group_by(Appl).\
order_by(Appl.refid)
d = d.cte('distinct_query')
q = DBSession.query(Appl).select_entity_from(d).\
join(Appl.city).\
order_by(d.c.lastname, d.c.firstname)
for row in q:
print row.refid, row.firstname, row.lastname, row.city.name
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] using SQLA in distributed, concurrent, or asynchronous contexts?
well there's always gevent. It might even be possible to get twisted and gevent to work together. So far I've managed to stick with multiprocessing for any kind of parallelism and it's done fine, but I'm not scaling up to hundreds or thousands of simultaneous things going on. On Oct 3, 2013, at 2:23 PM, Iain Duncan iainduncanli...@gmail.com wrote: Hi folks, I just got a new position and am hoping I can advocate for using SQLAlchemy there. I will be doing some backend web service stuff that interacts with a db, but likely in some form of distributed architecture. It's all going to be on AWS. I believe there will be some service implementation that either needs to be highly performant or highly concurrent or some combination of both. IE there may be lots of clients hitting the service and the service has to interact with third party payment services which might be slow, and we don't want the server choking just waiting on the third party responses. Up till now I haven't had to do stuff in this domain. Can anyone tell me what I might want to look into as far as ways of handling concurrency or non-blocking services that will play fair with SQLAlchemy? I read that trying to get SA working on Twisted is not a good plan. Would love any pointers on what to read up on, use, etc. Right now all my experience is on Pylons/Pyramid and has not had to use any fancy messaging, deferred processing, or concurreny handling so I think I have a lot reading ahead of me. Thanks! Iain -- 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. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] How to find the local column name given a ForeignKey object?
ForeignKey has .column as the column it refers to, and .parent as the column that is constrained to that .column. On Oct 3, 2013, at 3:18 PM, tiadobatima gbara...@gmail.com wrote: Hello there, I'm wondering if there is an easy way of finding the local column name given a ForeignKey. For example, the snippet below just gives me the remote column name: for fkey in meta.tables['mytable'].foreign_keys: print fkey.column I understand I could iterate over all the columns of the local table to find which has a foreign key, but I imagine there is a more direct way. Thanks! :) g. -- 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. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] A record is dropped on add
On Oct 3, 2013, at 5:42 AM, Werner werner.bru...@sfr.fr wrote:
Hi,
I have a problem that when I add a record to 'cellarbook' using the following
code the 'old' cellarbook entry is dropped from the db.
oldCB = session.query(db.Cellarbook).get(7)
newCB = db.Cellarbook()
# set some data from existing dbitem
newCB.cellar = oldCB.cellar
newCB.authuser = oldCB.authuser
newCB.supplier = oldCB.supplier
newCB.vintage = oldCB.vintage
newBot = db.Bottle()
newCB.vintage.bottle.append(newBot)
newCB.bottle = newBot
newCB.drinkinfo = oldCB.drinkinfo
session.add(newCB)
session.commit()
After this I have newCB.id=10 in the database but oldCB.id=7 has gone.
I guess/think that I have a relation setup incorrectly which creates this
effect, but I can not put my finger on it.
Appreciate any hint.
Werner
The relation setup for the classes involved, and I suspect
Vintage.drinkinfo/Cellarbook.drinkinfo ones:
Vintage.vintadd = sao.relationship('Vintadd', uselist=False,
cascade=all, delete, delete-orphan,
single_parent=True)
Vintage.cellarbook = sao.relationship('Cellarbook', uselist=False,
cascade=all, delete, delete-orphan,
backref='vintage')
my suspicion is newCB.vintage = oldCB.vintage means Vintage.cellarbook is set
to newCB, oldCB is no longer associated with Vintage.cellarbook due to
uselist=False, then delete-orphan deletes it.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] How to find the local column name given a ForeignKey object?
Great! Exactly what I was looking for :) Thank you very much! g. On Thursday, 3 October 2013 14:02:58 UTC-7, Michael Bayer wrote: ForeignKey has .column as the column it refers to, and .parent as the column that is constrained to that .column. On Oct 3, 2013, at 3:18 PM, tiadobatima gbar...@gmail.com javascript: wrote: Hello there, I'm wondering if there is an easy way of finding the local column name given a ForeignKey. For example, the snippet below just gives me the remote column name: for fkey in meta.tables['mytable'].foreign_keys: print fkey.column I understand I could iterate over all the columns of the local table to find which has a foreign key, but I imagine there is a more direct way. Thanks! :) g. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] using SQLA in distributed, concurrent, or asynchronous contexts?
On Thu, Oct 3, 2013 at 6:02 PM, Michael Bayer mike...@zzzcomputing.com wrote: well there's always gevent. It might even be possible to get twisted and gevent to work together. So far I've managed to stick with multiprocessing for any kind of parallelism and it's done fine, but I'm not scaling up to hundreds or thousands of simultaneous things going on. On Oct 3, 2013, at 2:23 PM, Iain Duncan iainduncanli...@gmail.com wrote: Hi folks, I just got a new position and am hoping I can advocate for using SQLAlchemy there. I will be doing some backend web service stuff that interacts with a db, but likely in some form of distributed architecture. It's all going to be on AWS. I believe there will be some service implementation that either needs to be highly performant or highly concurrent or some combination of both. IE there may be lots of clients hitting the service and the service has to interact with third party payment services which might be slow, and we don't want the server choking just waiting on the third party responses. Up till now I haven't had to do stuff in this domain. Can anyone tell me what I might want to look into as far as ways of handling concurrency or non-blocking services that will play fair with SQLAlchemy? I read that trying to get SA working on Twisted is not a good plan. It depends a lot on the workloads. I've tried gevent with pure SQLA apps, and it works quite dandy. However, as soon as you start adding the stuff all applications are made of (libraries), you risk breakage more and more. I've found out the hard way. As in segfaults. I think most relatively high-level pure-python libs should do fine. Mixing multiprocessing with gevent, doesn't do fine. Trow in other reactors (zmq on threads in my case), and things also break pretty badly. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: using SQLA in distributed, concurrent, or asynchronous contexts?
So the No SqlAlchemy with Twisted approach is dated. I've been on the twisted list for a while, and recently asked it -- as i have a bit of stuff for the same project running in twisted as-well-as pyramid and-also celery. The current consensus is that it's a general no-go/bad-idea if you're needing to persist the session; but you're going to run into that problem with any technology Check out the responses in this thread: July - http://twistedmatrix.com/pipermail/twisted-python/2013-July/027203.html August http://twistedmatrix.com/pipermail/twisted-python/2013-August/027333.html I resurrected it in August, and thats where some people started chiming in with ways SqlAlchemy can play nice. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: using SQLA in distributed, concurrent, or asynchronous contexts?
Also, a decent strategy to use is this: a) Your client does a lot of business logic in Pyramid. When you need to bill you hit an internal API or create a celery task b) You render a waiting page, and have ajax, or refresh, check to see if the internal API , or celery, is done processing c) when the internal api is done processing, you show the thank you screen. getting my SqlAlchemy model to work on Celery was a bit of a nightmare. i can try and dig up exactly what I did. IIRC, the problem was the Celery didn't seem to have an 'on initialization' routine, so I had to do some janky shit call my sqlalchemy initialization stuff. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] using WITH query AS, cte() with postgresql
I don't know why, but removing the join(Appl.city) call in q fixed it.
Below is the SA output of just the join section, as the full, generated query
output is very long:
Not working, returning only 1 row of 4 expected rows:
q = DBSession.query(Appl).\
select_entity_from(d).\
join(Appl.city).\
order_by(d.c.lastname, d.c.firstname)
FROM distinct_query JOIN city ON city.id = distinct_query.cityid LEFT OUTER
JOIN city AS city_1 ON city_1.id = distinct_query.cityid LEFT OUTER JOIN city
AS city_2 ON city_2.id = distinct_query.cityid2 LEFT OUTER JOIN race AS race_1
ON race_1.id = distinct_query.raceid LEFT OUTER JOIN marital AS marital_1 ON
marital_1.id = distinct_query.maritalid LEFT OUTER JOIN applreferrer AS
applreferrer_1 ON distinct_query.id = applreferrer_1.applid LEFT OUTER JOIN
agency AS agency_1 ON agency_1.id = applreferrer_1.agencyid LEFT OUTER JOIN
applsponsor AS applsponsor_1 ON distinct_query.id = applsponsor_1.applid LEFT
OUTER JOIN applbenef AS applbenef_1 ON distinct_query.id = applbenef_1.applid
LEFT OUTER JOIN race AS race_2 ON race_2.id = applbenef_1.raceid ORDER BY
distinct_query.lastname, distinct_query.firstname, distinct_query.middlename,
distinct_query.maidenname, applreferrer_1.id, applsponsor_1.id, applbenef_1.id
Working, returning 4 of 4 expected rows:
q = DBSession.query(Appl).\
select_entity_from(d).\
order_by(d.c.lastname, d.c.firstname)
FROM distinct_query LEFT OUTER JOIN city AS city_1 ON city_1.id =
distinct_query.cityid LEFT OUTER JOIN city AS city_2 ON city_2.id =
distinct_query.cityid2 LEFT OUTER JOIN race AS race_1 ON race_1.id =
distinct_query.raceid LEFT OUTER JOIN marital AS marital_1 ON marital_1.id =
distinct_query.maritalid LEFT OUTER JOIN applreferrer AS applreferrer_1 ON
distinct_query.id = applreferrer_1.applid LEFT OUTER JOIN agency AS agency_1 ON
agency_1.id = applreferrer_1.agencyid LEFT OUTER JOIN applsponsor AS
applsponsor_1 ON distinct_query.id = applsponsor_1.applid LEFT OUTER JOIN
applbenef AS applbenef_1 ON distinct_query.id = applbenef_1.applid LEFT OUTER
JOIN race AS race_2 ON race_2.id = applbenef_1.raceid ORDER BY
distinct_query.lastname, distinct_query.firstname, distinct_query.middlename,
distinct_query.maidenname, applreferrer_1.id, applsponsor_1.id, applbenef_1.id
Thanks Mike!
On Oct 3, 2013, at 4:59 PM, Michael Bayer mike...@zzzcomputing.com wrote:
are you using serializable isolation ? that would prevent an in-progress
transaction from seeing this new row committed elsewhere.
other than that, the SELECT in the log will show you what rows it's selecting
and using echo='debug' will show the rows it finds on the way back.
On Oct 3, 2013, at 2:35 PM, Nathan Mailg nathanma...@gmail.com wrote:
Sorry for this follow-up, but I'm really at a loss as to what to check next…
What should I do if after I insert a new Appl row, the query we've worked
on in this thread is *not* finding it, i.e. it's not being returned in the
result set? I'm really surprised and thinking I must be missing something
really basic?
I've deleted the lastname, firstname indexes and re-created them, but the
newly created row is still *not* being returned!
Here's the sql insert from the SA log:
2013-10-03 12:26:37,638 INFO [sqlalchemy.engine.base.Engine][Dummy-3] BEGIN
(implicit)
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3]
INSERT INTO appl (refid, appldate, lastname, firstname, cityid, cityid2)
VALUES (nextval('appl_refid_seq'), %(appldate)s, %(lastname)s,
%(firstname)s, %(cityid)s, %(cityid2)s) RETURNING appl.id
2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3]
{'cityid2': None, 'cityid': None, 'firstname': u'test3', 'lastname':
u'test3', 'appldate': u'10/3/2013'}
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Col
('id',)
2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Row
(14574,)
2013-10-03 12:26:37,645 INFO [sqlalchemy.engine.base.Engine][Dummy-3] COMMIT
What's also really odd, is if I manually run the sql in psql, it works and
returns the new row:
WITH distinct_query AS (
SELECT DISTINCT ON (refid) id, refid, lastname, firstname, appldate
FROM appl WHERE lastname ILIKE 'test%'
GROUP BY refid, id, lastname, firstname, appldate ORDER BY refid, appldate
DESC
)
SELECT * from distinct_query ORDER BY lastname, firstname;
Another odd thing is in my application, I have another search feature, and
it's using an SA query that's not using the cte/distinct query below, and it
works!
Sorry to have to ask this, but I'm stuck and need to get this working.
Thanks again for all the help!
On Oct 1, 2013, at 1:22 PM, Nathan Mailg nathanma...@gmail.com wrote:
Thanks Mike, that works!
I really appreciate you taking the time to show me how to get this working.
Do you have an amazon wish list or something like that somewhere?
On Sep 30, 2013,
