[sqlalchemy] Session.mapper deprecation
Hi all, I've been trying to work around the Session.mapper deprecation warning in Elixir by providing the same functionality within Elixir. The question is whether _ScopedExt is considered deprecated too ? I guess it is but want to make sure to not duplicate code needlessly... -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Orm slow to update why?
Christian Démolis wrote:
Thx for your answer.
MakeReleased is a method of com object windows agent (self.agent =
DispatchWithEvents('CosmoAgent.clsCCAgent', Evenement))
It takes 0 second to execute as we can see in the execute print
yes I realized later there were two blocks of timer calls. The inclusion
of that code made the example harder to read.
In particular using session.merge() with an object will issue a SELECT
first to locate the current row. this is likely the cause of the
slowness in this specific case since you seem to have a slow network
(profiling would reveal this too). Passing dont_load=True to merge()
will skip the SELECT step and trust that the state you are passing it is
the state that is within the database.
Orm does additional steps
is it possible to force him update only one attribute of the object?
you may say:
session.query(MyObject).filter(some_criterion).update({'attrname':somenewvalue},
False)
which will issue an UPDATE statement matching the criterion. this is the
fastest way by far using the ORM only.
Changing the False to evaluate or expire will also update or expire
the state of your ORM instance - but that will add some overhead.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Session.mapper deprecation
Gaetan de Menten wrote: Hi all, I've been trying to work around the Session.mapper deprecation warning in Elixir by providing the same functionality within Elixir. The question is whether _ScopedExt is considered deprecated too ? I guess it is but want to make sure to not duplicate code needlessly... Yeah that would be part of it. I was going to advise that you make your extension call the original init first, *then* add to the session, since most issues are related to the current ordering. This is still doable, but if you are using the init_instance events we have, it seems like those are hardwired to trigger the event before the old initializer runs. hmm. Although, the reverse ordering may have just as many issues and even the same ones, as the object can be added to the session via cascades and similar. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Session.mapper deprecation
On Wed, Sep 30, 2009 at 16:57, Michael Bayer mike...@zzzcomputing.com wrote: Gaetan de Menten wrote: I've been trying to work around the Session.mapper deprecation warning in Elixir by providing the same functionality within Elixir. The question is whether _ScopedExt is considered deprecated too ? I guess it is but want to make sure to not duplicate code needlessly... Yeah that would be part of it. I was going to advise that you make your extension call the original init first, *then* add to the session, This is what I've come up with anyway... since most issues are related to the current ordering. FWIW, I indeed had errors in our test suite when trying to add the instance to the session before doing the original init. These were all inheritance tests IIRC. The add to the session afterwards does not seem to produce any obvious errors. I can only hope there are no subtle ones either... This is still doable, but if you are using the init_instance events we have, it seems like those are hardwired to trigger the event before the old initializer runs. hmm. Although, the reverse ordering may have just as many issues and even the same ones, as the object can be added to the session via cascades and similar. Thanks for the insight, -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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] padding of CHAR fields, inconsistent where clause; Oracle example
Hello all:
Consider table:
CREATE TABLE PRICE_SOURCES (
ID decimal(22) PRIMARY KEY NOT NULL,
DESCRIPTION char(100) NOT NULL
)
and this screen dump from ipython session:
In [28]: import cx_Oracle
In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= 'EJV').fetchall()
Out[32]:
[(149,
'EJV
')]
Note that the result has padding in the column description as it is
of type CHAR(100), with is fixed length.
Now, let's do the same in SqlAlchemy.
In [33]: soup = SqlSoup('oracle://myuser:myp...@mydb')
In [34]: soup.price_sources.filter_by(description='EJV').all()
Out[34]: []
As you can see it returns zero rows. Only after I pad the parameter
for where clause will I get my row back:
In [35]: soup.price_sources.filter_by
(desciption='EJV
').all()
Out[35]: [MappedPrice_sources(pricesource=Decimal
('149'),desciption='EJV
')]
The same behavior happens even if I use ORM and regardless whether the
column metadata is defined with String, OracleString or CHAR.
I can understand the query result being padded as it is consistent
with behavior everywhere but the where clasues must not. It is a big
problem if you try to use session.merge() because you have to pad
contents of object fields otherwise the ORM will insert a duplicate.
Please help. I hope I am doing something wrong or missing something.
Regards,
Victor
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
On Sep 30, 2:07 pm, volx victor.o...@gmail.com wrote:
Hello all:
Consider table:
CREATE TABLE PRICE_SOURCES (
ID decimal(22) PRIMARY KEY NOT NULL,
DESCRIPTION char(100) NOT NULL
)
and this screen dump from ipython session:
In [28]: import cx_Oracle
In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= 'EJV').fetchall()
Out[32]:
[(149,
'EJV
')]
Note that the result has padding in the column description as it is
of type CHAR(100), with is fixed length.
Now, let's do the same in SqlAlchemy.
In [33]: soup = SqlSoup('oracle://myuser:myp...@mydb')
In [34]: soup.price_sources.filter_by(description='EJV').all()
Out[34]: []
As you can see it returns zero rows. Only after I pad the parameter
for where clause will I get my row back:
In [35]: soup.price_sources.filter_by
(desciption='EJV
').all()
Out[35]: [MappedPrice_sources(pricesource=Decimal
('149'),desciption='EJV
')]
The same behavior happens even if I use ORM and regardless whether the
column metadata is defined with String, OracleString or CHAR.
I can understand the query result being padded as it is consistent
with behavior everywhere but the where clasues must not. It is a big
problem if you try to use session.merge() because you have to pad
contents of object fields otherwise the ORM will insert a duplicate.
Please help. I hope I am doing something wrong or missing something.
that's a little strange. have you tried:
In [28]: import cx_Oracle
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= :someparam, dict(someparam='EJV')).fetchall()
?
since the usage of bind parameters is how it actually runs. If same
behavior there then it's a cx_oracle behavior.you can verify this
by turning on SQL echoing. As a workaround you can build yourself a
TypeDecorator that elongates/truncates incoming strings though that
seems a little inconvenient.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
On Sep 29, 6:14 pm, Michael Bayer mike...@zzzcomputing.com wrote: Andrew wrote: This is very confusing; I have an ORM generated SQL statement that is joining on a specific id. However, when I run it, for some reason, the specific id (that was joined on) is occasionally None! However, when I run the generated SQL copied from the server's debug log in SQLDeveloper, I get all the IDs correctly. Sure, I expected as much, but since the query is complex, I needed some time to anonymize it :) The query's ORM is as follows: query = meta.Session.query( ss.c.session_sid, au, s, iyo, iy, i, hds )\ .filter( ss.c.start_date func.to_date(start_date, '-MM- DD') )\ .filter( au.c.user_sid == ss.c.user_sid )\ .filter( au.c.handle == handle )\ .filter( s.c.user_sid == ss.c.user_sid )\ .filter( iyo.c.year_sid == s.c.staff_sid)\ .filter( iy.c.year_sid == iyo.c.year_sid)\ .filter( iy.c.is_current == 1 )\ .filter( i.c.sid == iy.c.sid)\ .filter( ssd.session_sid(+) = ss.session_sid)\ .filter( hds.session_sid(+) = ss.session_sid)\ .order_by( desc(ss.c.start_date) )\ .distinct() So when I run this and just do a simple loop through the data, ss.session_sid is appearing as None about 5% of the time. However, when I copy and paste the generated SQL from the server logs, it is able to select all of the session_sid's And to answer your question, as you can see, yes, there *is* an outer join on the column that is not being displayed. Is there a problem with the join? Using the outerjoin() function isn't an option because this query has been specifically optimized for Oracle--and rewriting it with the joins adds almost 200% more time to the query. As always, your help is always greatly appreeciated! Andrew --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
I have just now and it looks that this post probably belongs on
cx_Oracle mailing list.
In [47]: cursor.execute(select * from price_sources where desciption
= :someparam, dict(someparam='EJV')).fetchall()
Out[47]: []
In [49]: cursor.execute(select * from price_sources where desciption
= :someparam, dict
(someparam='EJV
')).fetchall()
Out[49]:
[(149,
'EJV
')]
On Sep 30, 2:18 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 30, 2:07 pm, volx victor.o...@gmail.com wrote:
Hello all:
Consider table:
CREATE TABLE PRICE_SOURCES (
ID decimal(22) PRIMARY KEY NOT NULL,
DESCRIPTION char(100) NOT NULL
)
and this screen dump from ipython session:
In [28]: import cx_Oracle
In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= 'EJV').fetchall()
Out[32]:
[(149,
'EJV
')]
Note that the result has padding in the column description as it is
of type CHAR(100), with is fixed length.
Now, let's do the same in SqlAlchemy.
In [33]: soup = SqlSoup('oracle://myuser:myp...@mydb')
In [34]: soup.price_sources.filter_by(description='EJV').all()
Out[34]: []
As you can see it returns zero rows. Only after I pad the parameter
for where clause will I get my row back:
In [35]: soup.price_sources.filter_by
(desciption='EJV
').all()
Out[35]: [MappedPrice_sources(pricesource=Decimal
('149'),desciption='EJV
')]
The same behavior happens even if I use ORM and regardless whether the
column metadata is defined with String, OracleString or CHAR.
I can understand the query result being padded as it is consistent
with behavior everywhere but the where clasues must not. It is a big
problem if you try to use session.merge() because you have to pad
contents of object fields otherwise the ORM will insert a duplicate.
Please help. I hope I am doing something wrong or missing something.
that's a little strange. have you tried:
In [28]: import cx_Oracle
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= :someparam, dict(someparam='EJV')).fetchall()
?
since the usage of bind parameters is how it actually runs. If same
behavior there then it's a cx_oracle behavior. you can verify this
by turning on SQL echoing. As a workaround you can build yourself a
TypeDecorator that elongates/truncates incoming strings though that
seems a little inconvenient.
--~--~-~--~~~---~--~~
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] Aggregation funnctions as mapped column_properties
Hi,
I'm trying to get aggregation functions to work as column_properties,
but I'm having a hard time figuring it out. The example below doesnt
work.. maybe someone can help?
ratings = Table('ratings', metadata,
Column('rating_key', Integer, primary_key=True),
Column('user_id', Integer, primary_key=True),
Column('rating', SmallInteger, default=0)
)
class Rating(object): pass
mapper(Rating,ratings,
properties = { 'count' : column_property(
select([func.count
(ratings.c.id)],ratings.c.id==ratings.c.id)),
'average': column_property(
select([func.avg
(ratings.c.rating)],ratings.c.id==ratings.c.id))
}
)
Test data:
sql.insert(ratings).execute([{'rating_key':1,'user_id':1,'rating':5},
{'rating_key':1,'user_id':2,'rating':4},
{'rating_key': 2,'user_id':1,'rating':1},
{'rating_key':3,'user_id':1,'rating':1},
{'rating_key':3,'user_id':2,'rating':1},
{'rating_key':3,'user_id':3,'rating':4},])
r = s.query(Rating).filter_by(rating_key=1,user_id=1).first()
desired result:
r.rating_key = 1
r_user_id = 1
r.rating = 5
r.average = 4.5 #averate all users for rating_key 1
r.count = 2 #rating count (users) for rating_key 1
thanks Bj
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
cx_Oracle actually has thread on that a topic at
http://sourceforge.net/mailarchive/message.php?msg_id=47BED8B8.3983.00E5.0%40uwinnipeg.ca
It boils down to having to specify a type for input parameter. Is that
something I could plug-in as a decorator or would that need to be
hacked on SqlAlchemy level?
Here's how:
In [70]: query = select * from price_sources where description
= :someparam
In [71]: cursor.setinputsizes(someparam=cx_Oracle.FIXED_CHAR)
Out[71]: {'someparam': cx_Oracle.FIXED_CHAR with value None}
In [72]: cursor.execute(query, dict(someparam='EJV')).fetchall()
Out[72]:
[(149,
'EJV
')]
Any help would be appreciated.
On Sep 30, 2:36 pm, volx victor.o...@gmail.com wrote:
I have just now and it looks that this post probably belongs on
cx_Oracle mailing list.
In [47]: cursor.execute(select * from price_sources where desciption
= :someparam, dict(someparam='EJV')).fetchall()
Out[47]: []
In [49]: cursor.execute(select * from price_sources where desciption
= :someparam, dict
(someparam='EJV
')).fetchall()
Out[49]:
[(149,
'EJV
')]
On Sep 30, 2:18 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 30, 2:07 pm, volx victor.o...@gmail.com wrote:
Hello all:
Consider table:
CREATE TABLE PRICE_SOURCES (
ID decimal(22) PRIMARY KEY NOT NULL,
DESCRIPTION char(100) NOT NULL
)
and this screen dump from ipython session:
In [28]: import cx_Oracle
In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= 'EJV').fetchall()
Out[32]:
[(149,
'EJV
')]
Note that the result has padding in the column description as it is
of type CHAR(100), with is fixed length.
Now, let's do the same in SqlAlchemy.
In [33]: soup = SqlSoup('oracle://myuser:myp...@mydb')
In [34]: soup.price_sources.filter_by(description='EJV').all()
Out[34]: []
As you can see it returns zero rows. Only after I pad the parameter
for where clause will I get my row back:
In [35]: soup.price_sources.filter_by
(desciption='EJV
').all()
Out[35]: [MappedPrice_sources(pricesource=Decimal
('149'),desciption='EJV
')]
The same behavior happens even if I use ORM and regardless whether the
column metadata is defined with String, OracleString or CHAR.
I can understand the query result being padded as it is consistent
with behavior everywhere but the where clasues must not. It is a big
problem if you try to use session.merge() because you have to pad
contents of object fields otherwise the ORM will insert a duplicate.
Please help. I hope I am doing something wrong or missing something.
that's a little strange. have you tried:
In [28]: import cx_Oracle
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= :someparam, dict(someparam='EJV')).fetchall()
?
since the usage of bind parameters is how it actually runs. If same
behavior there then it's a cx_oracle behavior. you can verify this
by turning on SQL echoing. As a workaround you can build yourself a
TypeDecorator that elongates/truncates incoming strings though that
seems a little inconvenient.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
I missed one important statement - prepare must be called on the
query:
query = select * from price_sources where description = :someparam
cursor.prepare(query)
cursor.setinputsizes(dict(someparam=cx_Oracle.FIXED_CHAR))
cursor.execute(query, dict(someparam='EJV')).fetchall()
On Sep 30, 3:07 pm, volx victor.o...@gmail.com wrote:
cx_Oracle actually has thread on that a topic
athttp://sourceforge.net/mailarchive/message.php?msg_id=47BED8B8.3983.0...
It boils down to having to specify a type for input parameter. Is that
something I could plug-in as a decorator or would that need to be
hacked on SqlAlchemy level?
Here's how:
In [70]: query = select * from price_sources where description
= :someparam
In [71]: cursor.setinputsizes(someparam=cx_Oracle.FIXED_CHAR)
Out[71]: {'someparam': cx_Oracle.FIXED_CHAR with value None}
In [72]: cursor.execute(query, dict(someparam='EJV')).fetchall()
Out[72]:
[(149,
'EJV
')]
Any help would be appreciated.
On Sep 30, 2:36 pm, volx victor.o...@gmail.com wrote:
I have just now and it looks that this post probably belongs on
cx_Oracle mailing list.
In [47]: cursor.execute(select * from price_sources where desciption
= :someparam, dict(someparam='EJV')).fetchall()
Out[47]: []
In [49]: cursor.execute(select * from price_sources where desciption
= :someparam, dict
(someparam='EJV
')).fetchall()
Out[49]:
[(149,
'EJV
')]
On Sep 30, 2:18 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 30, 2:07 pm, volx victor.o...@gmail.com wrote:
Hello all:
Consider table:
CREATE TABLE PRICE_SOURCES (
ID decimal(22) PRIMARY KEY NOT NULL,
DESCRIPTION char(100) NOT NULL
)
and this screen dump from ipython session:
In [28]: import cx_Oracle
In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= 'EJV').fetchall()
Out[32]:
[(149,
'EJV
')]
Note that the result has padding in the column description as it is
of type CHAR(100), with is fixed length.
Now, let's do the same in SqlAlchemy.
In [33]: soup = SqlSoup('oracle://myuser:myp...@mydb')
In [34]: soup.price_sources.filter_by(description='EJV').all()
Out[34]: []
As you can see it returns zero rows. Only after I pad the parameter
for where clause will I get my row back:
In [35]: soup.price_sources.filter_by
(desciption='EJV
').all()
Out[35]: [MappedPrice_sources(pricesource=Decimal
('149'),desciption='EJV
')]
The same behavior happens even if I use ORM and regardless whether the
column metadata is defined with String, OracleString or CHAR.
I can understand the query result being padded as it is consistent
with behavior everywhere but the where clasues must not. It is a big
problem if you try to use session.merge() because you have to pad
contents of object fields otherwise the ORM will insert a duplicate.
Please help. I hope I am doing something wrong or missing something.
that's a little strange. have you tried:
In [28]: import cx_Oracle
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= :someparam, dict(someparam='EJV')).fetchall()
?
since the usage of bind parameters is how it actually runs. If same
behavior there then it's a cx_oracle behavior. you can verify this
by turning on SQL echoing. As a workaround you can build yourself a
TypeDecorator that elongates/truncates incoming strings though that
seems a little inconvenient.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
Andrew wrote: On Sep 29, 6:14 pm, Michael Bayer mike...@zzzcomputing.com wrote: Andrew wrote: This is very confusing; I have an ORM generated SQL statement that is joining on a specific id. However, when I run it, for some reason, the specific id (that was joined on) is occasionally None! However, when I run the generated SQL copied from the server's debug log in SQLDeveloper, I get all the IDs correctly. Sure, I expected as much, but since the query is complex, I needed some time to anonymize it :) The query's ORM is as follows: query = meta.Session.query( ss.c.session_sid, au, s, iyo, iy, i, hds )\ .filter( ss.c.start_date func.to_date(start_date, '-MM- DD') )\ .filter( au.c.user_sid == ss.c.user_sid )\ .filter( au.c.handle == handle )\ .filter( s.c.user_sid == ss.c.user_sid )\ .filter( iyo.c.year_sid == s.c.staff_sid)\ .filter( iy.c.year_sid == iyo.c.year_sid)\ .filter( iy.c.is_current == 1 )\ .filter( i.c.sid == iy.c.sid)\ .filter( ssd.session_sid(+) = ss.session_sid)\ .filter( hds.session_sid(+) = ss.session_sid)\ .order_by( desc(ss.c.start_date) )\ .distinct() So when I run this and just do a simple loop through the data, ss.session_sid is appearing as None about 5% of the time. However, when I copy and paste the generated SQL from the server logs, it is able to select all of the session_sid's And to answer your question, as you can see, yes, there *is* an outer join on the column that is not being displayed. Is there a problem with the join? Using the outerjoin() function isn't an option because this query has been specifically optimized for Oracle--and rewriting it with the joins adds almost 200% more time to the query. As always, your help is always greatly appreeciated! first of all, SQLA does generate (+) = if you set use_ansi=False in your oracle create_engine, and then use outerjoin. Its obviously not a widely used feature and I'd be curious if it holds up with all your queries (it holds up under the various tests we've constructed for it). secondly, the query object will run a uniqueness function on the rows returned but that shouldn't be affecting much here. If you turn on echo='debug', you'll see the full SQL issued and every row returned. If that dump of rows is exactly as you'd expect, and then the ORM result is inserting Nones, that would be an issue - but there is nothing I'm aware of which is capable of that. --~--~-~--~~~---~--~~ 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] vertical partitioning
Heyho! Is there a tutorial on vertical partitioning? I have a table Entry and a table EntryFlags (1:1 relation from EntryFlags to Entry). The idea is that while there is a large number of Entry rows only a small number has flags set (and thus needs an entry in EntryFlags; note that they don't need to be booleans, despite the name). So having a separate table for the EntryFlags keeps the rows of Entry smaller and should also speed up if I select by certain flags. How do I represent this in sqlalchemy? Obviously I can trivially do the relation stuff to get entry.flags.myflag, but nicer would be having the columns from the EntryFlags table appear in Entry as if they were inline, returning a default value where an EntryFlags column doesn't exist. (Bonus for removing the EntryFlags row if all values are back to default ;-) Thanks in advance. -- vbi -- The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offence. -- E. Dijkstra, 1975 signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
On Sep 30, 3:19 pm, Michael Bayer mike...@zzzcomputing.com wrote: first of all, SQLA does generate (+) = if you set use_ansi=False in your oracle create_engine, and then use outerjoin. Its obviously not a widely used feature and I'd be curious if it holds up with all your queries (it holds up under the various tests we've constructed for it). secondly, the query object will run a uniqueness function on the rows returned but that shouldn't be affecting much here. If you turn on echo='debug', you'll see the full SQL issued and every row returned. If that dump of rows is exactly as you'd expect, and then the ORM result is inserting Nones, that would be an issue - but there is nothing I'm aware of which is capable of that. I did what you recommended with the second one; not a single ID was None, and all the IDs appeared as expected; the same number of rows are returned (336 rows total). For example, here are the first three IDs from the session.Query object: 468811 None 468721 Here are the values from the log: Row (468811, ... Row (468810, ... Row (468721, ... So clearly, the values *are* being selected. But why are they disappearing in the ORM portion of SQLAlchemy? Andrew --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
volx wrote:
cx_Oracle actually has thread on that a topic at
http://sourceforge.net/mailarchive/message.php?msg_id=47BED8B8.3983.00E5.0%40uwinnipeg.ca
It boils down to having to specify a type for input parameter. Is that
something I could plug-in as a decorator or would that need to be
hacked on SqlAlchemy level?
here is the thing. We call setinputsizes() with oracle. So perhaps
OracleChar() needs to have dbapi.FIXED_CHAR established as its
get_dbapi_type(). would you care to try monkeypatching this (in 0.5, the
fix for 0.6 would be different) ?
class OracleChar(sqltypes.CHAR):
def get_col_spec(self):
return CHAR(%(length)s) % {'length' : self.length}
def get_dbapi_type(self, dbapi):
return dbapi.FIXED_CHAR
from sqlalchemy.databases import oracle
oracle.OracleChar = OracleChar
Here's how:
In [70]: query = select * from price_sources where description
= :someparam
In [71]: cursor.setinputsizes(someparam=cx_Oracle.FIXED_CHAR)
Out[71]: {'someparam': cx_Oracle.FIXED_CHAR with value None}
In [72]: cursor.execute(query, dict(someparam='EJV')).fetchall()
Out[72]:
[(149,
'EJV
')]
Any help would be appreciated.
On Sep 30, 2:36 pm, volx victor.o...@gmail.com wrote:
I have just now and it looks that this post probably belongs on
cx_Oracle mailing list.
In [47]: cursor.execute(select * from price_sources where desciption
= :someparam, dict(someparam='EJV')).fetchall()
Out[47]: []
In [49]: cursor.execute(select * from price_sources where desciption
= :someparam, dict
(someparam='EJV
')).fetchall()
Out[49]:
[(149,
'EJV
')]
On Sep 30, 2:18 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 30, 2:07 pm, volx victor.o...@gmail.com wrote:
Hello all:
Consider table:
CREATE TABLE PRICE_SOURCES (
ID decimal(22) PRIMARY KEY NOT NULL,
DESCRIPTION char(100) NOT NULL
)
and this screen dump from ipython session:
In [28]: import cx_Oracle
In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where
description
= 'EJV').fetchall()
Out[32]:
[(149,
'EJV
')]
Note that the result has padding in the column description as it
is
of type CHAR(100), with is fixed length.
Now, let's do the same in SqlAlchemy.
In [33]: soup = SqlSoup('oracle://myuser:myp...@mydb')
In [34]: soup.price_sources.filter_by(description='EJV').all()
Out[34]: []
As you can see it returns zero rows. Only after I pad the parameter
for where clause will I get my row back:
In [35]: soup.price_sources.filter_by
(desciption='EJV
').all()
Out[35]: [MappedPrice_sources(pricesource=Decimal
('149'),desciption='EJV
')]
The same behavior happens even if I use ORM and regardless whether
the
column metadata is defined with String, OracleString or CHAR.
I can understand the query result being padded as it is consistent
with behavior everywhere but the where clasues must not. It is a big
problem if you try to use session.merge() because you have to pad
contents of object fields otherwise the ORM will insert a duplicate.
Please help. I hope I am doing something wrong or missing something.
that's a little strange. have you tried:
In [28]: import cx_Oracle
In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
In [31]: cursor = con.cursor()
In [32]: cursor.execute(select * from price_sources where description
= :someparam, dict(someparam='EJV')).fetchall()
?
since the usage of bind parameters is how it actually runs. If same
behavior there then it's a cx_oracle behavior. you can verify this
by turning on SQL echoing. As a workaround you can build yourself a
TypeDecorator that elongates/truncates incoming strings though that
seems a little inconvenient.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
Andrew wrote: On Sep 30, 3:19 pm, Michael Bayer mike...@zzzcomputing.com wrote: first of all, SQLA does generate (+) = if you set use_ansi=False in your oracle create_engine, and then use outerjoin. Its obviously not a widely used feature and I'd be curious if it holds up with all your queries (it holds up under the various tests we've constructed for it). secondly, the query object will run a uniqueness function on the rows returned but that shouldn't be affecting much here. If you turn on echo='debug', you'll see the full SQL issued and every row returned. If that dump of rows is exactly as you'd expect, and then the ORM result is inserting Nones, that would be an issue - but there is nothing I'm aware of which is capable of that. I did what you recommended with the second one; not a single ID was None, and all the IDs appeared as expected; the same number of rows are returned (336 rows total). For example, here are the first three IDs from the session.Query object: 468811 None 468721 Here are the values from the log: Row (468811, ... Row (468810, ... Row (468721, ... So clearly, the values *are* being selected. But why are they disappearing in the ORM portion of SQLAlchemy? what happens if you say: engine.execute(myquery.statement).fetchall() ? Andrew --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
On Sep 30, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
what happens if you say:
engine.execute(myquery.statement).fetchall()
?
That worked (spaced out for readability):
[
(Decimal('468811'), ... ),
(Decimal('468810'), ... ),
(Decimal('468721'), ...) ,
...]
What does this mean, then?
Andrew
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: vertical partitioning
On Wed, Sep 30, 2009 at 1:04 PM, Adrian von Bidder avbid...@fortytwo.chwrote: Heyho! Is there a tutorial on vertical partitioning? I have a table Entry and a table EntryFlags (1:1 relation from EntryFlags to Entry). The idea is that while there is a large number of Entry rows only a small number has flags set (and thus needs an entry in EntryFlags; note that they don't need to be booleans, despite the name). So having a separate table for the EntryFlags keeps the rows of Entry smaller and should also speed up if I select by certain flags. How do I represent this in sqlalchemy? Obviously I can trivially do the relation stuff to get entry.flags.myflag, but nicer would be having the columns from the EntryFlags table appear in Entry as if they were inline, returning a default value where an EntryFlags column doesn't exist. (Bonus for removing the EntryFlags row if all values are back to default ;-) Thanks in advance. -- vbi -- The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offence. -- E. Dijkstra, 1975 Check out the Customizing Column Properties in the Mapper Configuation docs: http://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-properties If I understand correctly, this should do what you want. (Disclaimer: haven't done it myself, YMMV) Kevin Horn --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
On Sep 30, 4:00 pm, Andrew redmu...@gmail.com wrote:
On Sep 30, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
what happens if you say:
engine.execute(myquery.statement).fetchall()
?
That worked (spaced out for readability):
[
(Decimal('468811'), ... ),
(Decimal('468810'), ... ),
(Decimal('468721'), ...) ,
...]
What does this mean, then?
Andrew
I also went ahead and checked the raw output of query.all()--it worked
correctly; all the IDs were retrieved and displayed in raw format:
Decimal(###) and so on. HOWEVER, when parsing those rows using a
simple for loop, a'la
str =
for row in query.all():
if row.sync_session_sid == None:
str = str + Nonebr\n
else:
str = str + %dbr\n % row.sync_session_sid
return str
Which then returns:
468811
None
468721
In other words, the *raw data* that it returns is good. The mapping
to objects is not.
Andrew
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
Andrew wrote:
On Sep 30, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
what happens if you say:
engine.execute(myquery.statement).fetchall()
?
That worked (spaced out for readability):
[
(Decimal('468811'), ... ),
(Decimal('468810'), ... ),
(Decimal('468721'), ...) ,
...]
What does this mean, then?
I really have no idea. I can't tell you the issue unless I have
reproducing code here which I can step through. you might want to try
simplifying your statement to isolate what causes it to convert the
Decimal to a None.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
Andrew wrote: I also went ahead and checked the raw output of query.all()--it worked correctly; all the IDs were retrieved and displayed in raw format: Decimal(###) and so on. HOWEVER, when parsing those rows using a simple for loop, a'la str = for row in query.all(): if row.sync_session_sid == None: str = str + Nonebr\n else: str = str + %dbr\n % row.sync_session_sid return str Which then returns: 468811 None 468721 In other words, the *raw data* that it returns is good. The mapping to objects is not. query.all() returns a list of tuples (well, an ad-hoc subclass of tuple that provides the named attributes). if query.all() is correct, then it's correct. I notice you are comparing to None using ==. What happens if you change that to is? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
On Sep 30, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote: Andrew wrote: I also went ahead and checked the raw output of query.all()--it worked correctly; all the IDs were retrieved and displayed in raw format: Decimal(###) and so on. HOWEVER, when parsing those rows using a simple for loop, a'la str = for row in query.all(): if row.sync_session_sid == None: str = str + Nonebr\n else: str = str + %dbr\n % row.sync_session_sid return str Which then returns: 468811 None 468721 In other words, the *raw data* that it returns is good. The mapping to objects is not. query.all() returns a list of tuples (well, an ad-hoc subclass of tuple that provides the named attributes). if query.all() is correct, then it's correct. I notice you are comparing to None using ==. What happens if you change that to is? Same exact result. :( 468811 None 468721 Andrew --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy is dropping columns on Oracle 10g
Andrew wrote: On Sep 30, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote: Andrew wrote: I also went ahead and checked the raw output of query.all()--it worked correctly; all the IDs were retrieved and displayed in raw format: Decimal(###) and so on. HOWEVER, when parsing those rows using a simple for loop, a'la str = for row in query.all(): if row.sync_session_sid == None: str = str + Nonebr\n else: str = str + %dbr\n % row.sync_session_sid return str Which then returns: 468811 None 468721 In other words, the *raw data* that it returns is good. The mapping to objects is not. query.all() returns a list of tuples (well, an ad-hoc subclass of tuple that provides the named attributes). if query.all() is correct, then it's correct. I notice you are comparing to None using ==. What happens if you change that to is? Same exact result. :( 468811 None 468721 OK can you try poking a little deeper ? you say query.all() is correct and a simple loop breaks it ? can you pls look at the repr() of the result, do some pdb, etc., if query.all() is correct then there's only something trivial going on. its a list of tuples. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: vertical partitioning
On Wednesday 30 September 2009 21.58:55 Kevin Horn wrote: I have a table Entry and a table EntryFlags (1:1 relation from EntryFlags to Entry). The idea is that while there is a large number of Entry rows only a small number has flags set (and thus needs an entry in EntryFlags; note that they don't need to be booleans, despite the name). So having a separate table for the EntryFlags keeps the rows of Entry smaller and should also speed up if I select by certain flags. How do I represent this in sqlalchemy? [...] the columns from the EntryFlags table appear in Entry as if they were inline, returning a default value where an EntryFlags column doesn't exist. (Bonus for removing the EntryFlags row if all values are back to default ;-) Check out the Customizing Column Properties in the Mapper Configuation docs: http://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-propert ies If I understand correctly, this should do what you want. (Disclaimer: haven't done it myself, YMMV) Hmm. Mapping an outer join to my actual model class would be part of it. Not sure how to easily assign default values for the part of the row not backed by a real row on the EntryFlags side (they shouldn't just be NULL; default values on the EntryFlags table wouldn't be of much use since they'd be applied only when the row would be generated and not for access to the outer join, afaict) (And as an aside: this has become a a bit an academic question for now, I've thought about more about what I'm trying to do and redesigned the schema.) cheers -- vbi Kevin Horn --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- -- Do you understand now why we attacked Iraq? Because war is good for the economy, which means war is good for America. Also, since God is on America's side, anyone who opposes war is a godless un-American Communist. -- excerpt from one of those 'joke' mails floating around. signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: NULL values sorting
order_by(func.coalesce (items.c.code0, '00')) Works perfectly, thanks --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
