Ok Michael... I do have some followup questions re the '==' operator
in oracle & in sqlite below...
On 25 May 2010, at 15:15, Michael Bayer wrote:
On May 25, 2010, at 9:50 AM, daniel wrote:
Hi...
I'm having some trouble with creating a select statement with a where
clause that uses a literal value ... in the process of debugging i've
narrowed it down to the way the interpreter treats the == operator
when bound to an sqlite database (this problem goes away when I use
oracle). Basically, if I print the statement:
print bondData.c.NORTHINFO_RATING == "AAA"
I get:
"BOND_DATA"."NORTHINFO_RATING" = ?
which, when used as a where clause in a query, produces no records
whereas when i use oracle via cx_oracle, the statement
print bondData.c.northinfo_rating == "AAA"
gives me:
"BOND_DATA".northinfo_rating = :northinfo_rating_1
which produces a query that returns a data set from the table
"BOND_DATA" where values of the column northinfo_rating = "AAA"...
This clearly is the desired result...
Any tips as to what I'm doing wrong wrong in the sqlite case?
not at all, the clause is correct for SQLite. the pysqlite driver
defaults to the "qmark" style of bind parameter, whereas cx_oracle
defaults to "named" (hence '?' in one case and ':some_name' for the
other).
would this binding style issue have an effect in the context of a
conditional query? Let me give an example based on the same dataset i
used in my previous mail:
given the following table in both sqlite & oracle 11g, populated with
identical data:
bondData = Table('BOND_DATA', metadata,
Column('NORTHINFOID', String(35), primary_key = True),
Column('NORTHINFO_RATING',String(4))
# schema = daniel
)
toggling that last schema line on for oracle & off for sqlite...
i want to create an sql query that returns me a subset of the data
where NORTHINFO_RATING's value = "AAA". to do this while connected to
the sqlite instance of the DB i run the following 2 lines:
s = select([bondData], bondData.c.NORTHINFO_RATING == "AAA")
result = db_engine.execute(s)
here's my problem... this returns an empty dataset, even though there
are thousands of records that fit the description. when I modify those
lines slightly to run while connected to oracle, however, I get my
desired result:
s=select([bondData], bondData.c.northinfo_rating=="AAA")
result = db_engine.execute(s)
thousands of records as expected. I modify that first line, due to
having to use lowercase column names to access the data in oracle (i
read and understood your previous post & will recreate the DB with all
lowercase, case insensitive table and column names to prevent this
problem reoccurring in the future, thanks!)
while trying to debug this i inserted a simple line to make sure that
the same sql was being sent to both databases:
print s
when interpreted via oracle this yieded:
SELECT daniel."BOND_DATA".northinfoid,
daniel."BOND_DATA".northinfo_rating
FROM daniel."BOND_DATA"
WHERE daniel."BOND_DATA".northinfo_rating = :northinfo_rating_1
when interpreted via sqlite this gave me:
SELECT "BOND_DATA"."NORTHINFOID", "BOND_DATA"."NORTHINFO_RATING"
FROM "BOND_DATA"
WHERE "BOND_DATA"."NORTHINFO_RATING" = ?
since the only difference i could see was the binding style, i assumed
that this was the problem - the reason why the query provided the
expected result on oracle, and an empty dataset using sqlite. so my
question (finally!) is, if the binding style's not the problem, could
there be some other reason for the query's failure on the sqlite
instance? how would you recommend i debug this? could this also be
linked with the uppercase / lowercase issue i've been having?
thanks again, d.
my assumption was that this had to do with the
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
