[sqlalchemy] Exists/Correlate newbie question
I've got a SQL query I am trying to convert over into sqlalchemy,
however I just can't suss out the correlate and exists part of
sqlalchemy. My query is:
SELECT m_date,sensor_id,m_value,d_report_hour
FROM multi_obs mo
WHERE
m_date = '2011-03-23T00:00:00' and m_date '2011-03-23T24:00:00'
AND
sensor_id = 4644 AND
EXISTS (SELECT d_report_hour
FROM multi_obs
WHERE
m_date = '2011-03-23T00:00:00' AND m_date
'2011-03-23T24:00:00' AND
sensor_id=518 and
mo.d_report_hour=d_report_hour)
ORDER BY sensor_id ASC, d_report_hour ASC;
I've got my multi_obs table already in an object in sqlalchemy. I am
not sure how to get the mo correlation working inside the EXISTS
subquery. I cobbled up this:
matchDatesQ = qaqc.db.session.query(multi_obs).\
filter(multi_obs.m_date = beginDate).\
filter(multi_obs.m_date endDate).\
filter(multi_obs.sensor_id == nnSensorId).\
filter(multi_obs.d_report_hour == mo.d_report_hour).\
filter(multi_obs.d_top_of_hour == 1).\
correlate(multi_obs).\
subquery()
recs = qaqc.db.session.query(multi_obs).\
correlate('mo').\
filter(multi_obs.m_date = beginDate).\
filter(multi_obs.m_date endDate).\
filter(multi_obs.sensor_id == sensorId).\
filter(multi_obs.d_top_of_hour == 1).\
filter(exists(matchDatesQ)).\
order_by(multi_obs.m_date.asc()).all()
however, I get errors in the matchesDateQ query about the mo not
being defined. I understand why that is, I just am not sure how to go
about getting this to work.
Any tips/hints are greatly appreciated.
Dan
--
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] Exists/Correlate newbie question
On Mar 24, 2011, at 2:19 PM, dan wrote:
I've got a SQL query I am trying to convert over into sqlalchemy,
however I just can't suss out the correlate and exists part of
sqlalchemy. My query is:
SELECT m_date,sensor_id,m_value,d_report_hour
FROM multi_obs mo
WHERE
m_date = '2011-03-23T00:00:00' and m_date '2011-03-23T24:00:00'
AND
sensor_id = 4644 AND
EXISTS (SELECT d_report_hour
FROM multi_obs
WHERE
m_date = '2011-03-23T00:00:00' AND m_date
'2011-03-23T24:00:00' AND
sensor_id=518 and
mo.d_report_hour=d_report_hour)
ORDER BY sensor_id ASC, d_report_hour ASC;
I've got my multi_obs table already in an object in sqlalchemy. I am
not sure how to get the mo correlation working inside the EXISTS
subquery. I cobbled up this:
matchDatesQ = qaqc.db.session.query(multi_obs).\
filter(multi_obs.m_date = beginDate).\
filter(multi_obs.m_date endDate).\
filter(multi_obs.sensor_id == nnSensorId).\
filter(multi_obs.d_report_hour == mo.d_report_hour).\
filter(multi_obs.d_top_of_hour == 1).\
correlate(multi_obs).\
subquery()
recs = qaqc.db.session.query(multi_obs).\
correlate('mo').\
filter(multi_obs.m_date = beginDate).\
filter(multi_obs.m_date endDate).\
filter(multi_obs.sensor_id == sensorId).\
filter(multi_obs.d_top_of_hour == 1).\
filter(exists(matchDatesQ)).\
order_by(multi_obs.m_date.asc()).all()
however, I get errors in the matchesDateQ query about the mo not
being defined. I understand why that is, I just am not sure how to go
about getting this to work.
so you want to reference multi_obs in two ways, one is aliased. For that you
use the aliased() construct:
mo = aliased(multi_obs)
that's where you get 'mo' from - you then construct the outer query using the
'mo' object, i.e. mo.m_date = beginDate etc.. The inner query then can
reference mo. Its all replacing string identifiers in the SQL with Python
identities (i.e. objects) in your program.The above shouldn't need any
explicit calls to correlate as it is automatic for basic scenarios (it also
generally is given a selectable object, not a string).
--
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] Stale association proxy
Thanks, Michael!
That seems to have fixed it.
Cody
On Wed, Mar 23, 2011 at 1:41 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Mar 23, 2011, at 11:28 AM, argentp...@gmail.com wrote:
Hello everyone,
I am currently working on a Pylons project, and keep on receiving the
following error from SQLAlchemy:
InvalidRequestError: stale association proxy, parent object has gone
out of scope
this is because the parent is being garbage collected while the association
proxy is still doing its work:
# will not work with immediate gc
for item in session.query(Order).first().items:
print item
the solution is to do it like this:
o1 = session.query(Order).first()
for item in o1.items:
print item
example is attached.
Here is the code for the model:
class AccountInfo(Base):
__tablename__ = 'account_info'
userid = schema.Column(types.Integer,
schema.ForeignKey(users.id), nullable=False, primary_key=True)
enabled = schema.Column(types.Integer, default=1)
username = schema.Column(types.Unicode(25), nullable=False,unique=True)
password = schema.Column(types.Unicode(255), default=u'password')
#leveled_privs = orm.relation(PrivilegeLevel,
backref=account_info,
collection_class=attribute_mapped_collection('priv_id'))
# Below creates a relationship between AccountInfo and
PrivilegeLevel placed into a dict sorted by priv_id
perms = orm.relation(Privilege, secondary=privileges_assoc)#,
backref=account_info)
privileges = association_proxy(perms,name)
def __init__(self,info):
for k,v in info:
setattr(self, k, v)
privileges is the source of the problem. Whenever I try to use it in
the site, I get the previously mentioned error. I've taken a look at
the documentation on the site, and I believe that I am using it
correctly.
When I searched google, I wasn't able to find anything useful. I did
find something about removing stale cached attribute instances here:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08914.html
But I couldn't find any attributes on my instances that seemed similar
to what that user was speaking about.
Here's an example of how I'm using it, if that helps:
edit.perms in myAccountInfoInstance.privileges
True
In the Python shell, it works occasionally. In a request, not at all
Is anyone able to help me?
Cody
--
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.
--
.-.
/v\ L I N U X
// \\ Phear the Penguin
/( )\
^^-^^
--
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] cannot import name cx_oracle
Hi there,
Im new to sqlalchemy, be kind and gentle :)
Using sqlalchemy 0.6.6 and the last stable cx_Oracle.pyd on my windows
pc.
OracleXE 10g is installed locally.
Processing the following code, gives me the error
ImportError: cannot import name cx_Oracle
import sqlalchemy
sqlalchemy.create_engine('oracle://hr:hr@XE')
connection = oracle_db.connect()
result = connection.execute(select employee_id,first_name,last_name
from Employees)
for row in result:
print (row)
connection.close()
This sound to be launched by the following code within cx_oracle
def dbapi(cls):
from . import cx_Oracle
return cx_Oracle
Tried to fix without success.
Calling similar code from cx_Oracle is running fine.
Can someone point me on the right direction ?
Regards,
--
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] cannot import name cx_oracle
On Mar 26, 2011, at 6:54 PM, tanc wrote:
Hi there,
Im new to sqlalchemy, be kind and gentle :)
Using sqlalchemy 0.6.6 and the last stable cx_Oracle.pyd on my windows
pc.
OracleXE 10g is installed locally.
Processing the following code, gives me the error
ImportError: cannot import name cx_Oracle
import sqlalchemy
sqlalchemy.create_engine('oracle://hr:hr@XE')
connection = oracle_db.connect()
result = connection.execute(select employee_id,first_name,last_name
from Employees)
for row in result:
print (row)
connection.close()
This sound to be launched by the following code within cx_oracle
def dbapi(cls):
from . import cx_Oracle
return cx_Oracle
Tried to fix without success.
Calling similar code from cx_Oracle is running fine.
Can someone point me on the right direction ?
Regards,
One aspect may be that the SQLAlchemy Oracle dialect has never been tested with
Python 3 and likely has a lot of issues. I know you're using Python 3 due to
the relative import in the code snippet you're attaching (a very important
detail).
I'd strongly advise you stick with Python 2 until library maintainers have
finished getting their act together. We're mostly getting much closer, but its
still kind of early for end users.
--
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.
