Michael...
Thanks so much for your comments! I really appreciate the feedback. I
guess there are two threads here, so I'll tackle them one by one to
follow up... i'll use this mail to respond to the UPPER / lowercase
usage in table.c.columnname & then respond to your reply again
separately to address the interpretation of the == operator in
different db dialects. Please see below for my follow up questions re
the letter-case usage :-)
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).
BTW... the capitalization of the column names is different for the
two
databases because I've realized by trial and error that oracle column
names should be in lower case whereas sqlite column names should be
in
whatever letter-case they were specified in the DB...
this is due to the usage of uppercase characters in the column
name. By using them, you tell SQLalchemy that the name is "case
sensitive", and that all casing should be preserved. This is
described at http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html?highlight=column#sqlalchemy.schema.Column
. It's not clear why your examples use c.NORTHINFO_RATING in one
example, and c.northinfo_rating in the other - these are not
synonymous.
That's what I thought... but in that case a table created with upper
case column names should not be accessible by the lower case column
name... right? I found that the opposite was true using oracle... that
a table created using uppercase column names was actually not
accessible using the UPPERCASE name. Here's an example to illustrate...
i create a table as follows in both sqlite & oracle 11g:
bondData = Table('BOND_DATA', metadata,
Column('NORTHINFOID', String(35), primary_key = True),
Column('NORTHINFO_RATING',String(4))
# schema = daniel
)
using uppercase column names, toggling that last schema line on for
oracle & off for sqlite.
first warning signs come up when running the simple script:
for c in bondData.c:
print c
yields different results depending on whether i connect to oracle or
sqlite, namely for oracle i get:
BOND_DATA.northinfoid
BOND_DATA.northinfo_rating
whereas for sqlite i get the more intuitive:
BOND_DATA.NORTHINFOID
BOND_DATA.NORTHINFO_RATING
upon populating both tables with identical data, running the query
s = select([bondData.NORTHINFO_RATING])
result = db_engine.execute(s)
works for sqlite, but for oracle returns the error:
AttributeError: NORTHINFO_RATING
whereas running
s = select([bondData.northinfo_rating])
result = db_engine.execute(s)
works for oracle but not for sqlite, returning
AttributeError: northinfo_rating
Since the table was created with uppercase column names shouldn't the
oracle instance behave like the sqlite instance, i.e requiring caps in
the column name specifier?
Thanks again for your help...
d.
--
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
.
--
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.
