[sqlalchemy] Re: Uppercase column names in table.insert({ })
On Oct 1, 2008, at 10:03 AM, Gaetan de Menten wrote:
>
> On Mon, Sep 29, 2008 at 4:46 PM, Itamar Ravid
> <[EMAIL PROTECTED]> wrote:
>> Thanks for the answer, Mike. I was used to Oracle's behavior while
>> writing
>> raw SQL, in which the case of unquoted column identifiers doesn't
>> matter.
>> This behavior seems reasonable enough, although the inconsistency
>> between
>> the cursor description and SQLA's column identifiers could throw
>> some people
>> off.
>>
>> This causes the versioned plugin from Elixir.ext to fail on Oracle,
>> so I'll
>> submit a patch against it to lowercase column names in
>> table.insert()'s.
>> Thanks again.
>
> I can't accept this patch as it breaks for people using non-lowercase
> column names on non-Oracle database.
> See http://elixir.ematia.de/trac/ticket/73
>
> Some comments:
> - it fails silently: Mike, is it expected that when you do:
> connection.execute(table.insert(), {"inexistant_field": somevalue}),
> it doesn't complain in any way?
I think that is the current behavior, but it is not ideal. It's
likely that validating the incoming dict would add palpable overhead
to SQL execution, but I haven't analyzed deeply.
>
> - I really think this should be fixed at SA-level (probably in the
> reflection code), as we don't do anything fancy in there...
>
> Here is roughly what happens:
>
> my_table = Table("my_table", metadata, autoload=True)
> my_table2 = Table("my_table2", metadata, *[col.copy() for col in
> my_table.c])
> metadata.create_all()
im not sure about this one, oracle reflection definitely works.
> values = my_table.select().execute().fetchone()
> connection.execute(my_table2.insert(), values)
still somewhat controversial for the reasons I mentioned earlier,
perhaps oracle's _normalize_name() should be applied to
cursor.description.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Uppercase column names in table.insert({ })
On Mon, Sep 29, 2008 at 4:46 PM, Itamar Ravid <[EMAIL PROTECTED]> wrote:
> Thanks for the answer, Mike. I was used to Oracle's behavior while writing
> raw SQL, in which the case of unquoted column identifiers doesn't matter.
> This behavior seems reasonable enough, although the inconsistency between
> the cursor description and SQLA's column identifiers could throw some people
> off.
>
> This causes the versioned plugin from Elixir.ext to fail on Oracle, so I'll
> submit a patch against it to lowercase column names in table.insert()'s.
> Thanks again.
I can't accept this patch as it breaks for people using non-lowercase
column names on non-Oracle database.
See http://elixir.ematia.de/trac/ticket/73
Some comments:
- it fails silently: Mike, is it expected that when you do:
connection.execute(table.insert(), {"inexistant_field": somevalue}),
it doesn't complain in any way?
- I really think this should be fixed at SA-level (probably in the
reflection code), as we don't do anything fancy in there...
Here is roughly what happens:
my_table = Table("my_table", metadata, autoload=True)
my_table2 = Table("my_table2", metadata, *[col.copy() for col in my_table.c])
metadata.create_all()
values = my_table.select().execute().fetchone()
connection.execute(my_table2.insert(), values)
> On Mon, Sep 29, 2008 at 5:39 PM, Michael Bayer <[EMAIL PROTECTED]>
> wrote:
>>
>>
>>
>> This is the expected behavior. SQLA operates in a "case sensitive"
>> fashion whenever a table or column identifier is given in mixed case
>> or upper case. Use all lower case for "case insensitive". Since
>> SQLA seeks to provide a database-agnostic API to the backend, this
>> includes Oracle as well."case sensitive" means that the identifier
>> will be quoted, in which case the database expects to match the
>> identifier against an identifier of the exact same characters.
>> Oracle's usual "UPPERCASE" identifiers are in fact case insensitive.
>> So for case insensitive identifiers, make sure you use all lower case
>> names like 'book_id'.
>>
>> The keys() method of the ResultProxy, OTOH, doesn't attempt to
>> editorialize what comes back from the cursor, so in the case of oracle
>> you get upper case names (these are acceptable to use as keys for
>> row['somekey']). While we maybe could try to connect the original
>> select() statement's SQLA-encoded column names back to the
>> cursor.description's keys and return them as defined on the SQLAlchemy
>> side, you'd still get the uppercase names when we didn't have that
>> information, like execute("select * from table"). I have a vague
>> recollection of someone having a specific issue with that behavior but
>> I'm not finding what it was at the moment.
>>
>>
>> On Sep 29, 2008, at 10:04 AM, Itamar Ravid wrote:
>>
>> >
>> > Hey guys - I've ran into a strange bug in 0.4.7p1 while trying to make
>> > use of Elixir's versioned plugin. In Oracle, given a table created as
>> > such:
>> >
>> > CREATE TABLE books
>> > (book_id NUMBER PRIMARY KEY);
>> >
>> > The following code fails:
>> >
>> > dbEng = create_engine("oracle://:@pearl")
>> > meta = MetaData()
>> > meta.bind = dbEng
>> >
>> > booksTable = Table("books", meta, autoload=True)
>> >
>> > booksTable.insert({'BOOK_ID': 200}).execute()
>> >
>> > Whilst the following, succeeds:
>> >
>> > booksTable.insert({'book_id': 200}).execute()
>> >
>> > This is strange, considering the fact that the following:
>> >
>> > result = booksTable.select().execute().fetchone()
>> > print result.keys()
>> >
>> > ... results in the column names in uppercase.
>> >
>> > Am I doing anything wrong?
>> >
--
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Uppercase column names in table.insert({ })
Thanks for the answer, Mike. I was used to Oracle's behavior while writing
raw SQL, in which the case of unquoted column identifiers doesn't matter.
This behavior seems reasonable enough, although the inconsistency between
the cursor description and SQLA's column identifiers could throw some people
off.
This causes the versioned plugin from Elixir.ext to fail on Oracle, so I'll
submit a patch against it to lowercase column names in table.insert()'s.
Thanks again.
On Mon, Sep 29, 2008 at 5:39 PM, Michael Bayer <[EMAIL PROTECTED]>wrote:
>
>
>
> This is the expected behavior. SQLA operates in a "case sensitive"
> fashion whenever a table or column identifier is given in mixed case
> or upper case. Use all lower case for "case insensitive". Since
> SQLA seeks to provide a database-agnostic API to the backend, this
> includes Oracle as well."case sensitive" means that the identifier
> will be quoted, in which case the database expects to match the
> identifier against an identifier of the exact same characters.
> Oracle's usual "UPPERCASE" identifiers are in fact case insensitive.
> So for case insensitive identifiers, make sure you use all lower case
> names like 'book_id'.
>
> The keys() method of the ResultProxy, OTOH, doesn't attempt to
> editorialize what comes back from the cursor, so in the case of oracle
> you get upper case names (these are acceptable to use as keys for
> row['somekey']). While we maybe could try to connect the original
> select() statement's SQLA-encoded column names back to the
> cursor.description's keys and return them as defined on the SQLAlchemy
> side, you'd still get the uppercase names when we didn't have that
> information, like execute("select * from table"). I have a vague
> recollection of someone having a specific issue with that behavior but
> I'm not finding what it was at the moment.
>
>
> On Sep 29, 2008, at 10:04 AM, Itamar Ravid wrote:
>
> >
> > Hey guys - I've ran into a strange bug in 0.4.7p1 while trying to make
> > use of Elixir's versioned plugin. In Oracle, given a table created as
> > such:
> >
> > CREATE TABLE books
> > (book_id NUMBER PRIMARY KEY);
> >
> > The following code fails:
> >
> > dbEng = create_engine("oracle://:@pearl")
> > meta = MetaData()
> > meta.bind = dbEng
> >
> > booksTable = Table("books", meta, autoload=True)
> >
> > booksTable.insert({'BOOK_ID': 200}).execute()
> >
> > Whilst the following, succeeds:
> >
> > booksTable.insert({'book_id': 200}).execute()
> >
> > This is strange, considering the fact that the following:
> >
> > result = booksTable.select().execute().fetchone()
> > print result.keys()
> >
> > ... results in the column names in uppercase.
> >
> > Am I doing anything wrong?
> >
> > >
>
>
> >
>
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Uppercase column names in table.insert({ })
This is the expected behavior. SQLA operates in a "case sensitive"
fashion whenever a table or column identifier is given in mixed case
or upper case. Use all lower case for "case insensitive". Since
SQLA seeks to provide a database-agnostic API to the backend, this
includes Oracle as well."case sensitive" means that the identifier
will be quoted, in which case the database expects to match the
identifier against an identifier of the exact same characters.
Oracle's usual "UPPERCASE" identifiers are in fact case insensitive.
So for case insensitive identifiers, make sure you use all lower case
names like 'book_id'.
The keys() method of the ResultProxy, OTOH, doesn't attempt to
editorialize what comes back from the cursor, so in the case of oracle
you get upper case names (these are acceptable to use as keys for
row['somekey']). While we maybe could try to connect the original
select() statement's SQLA-encoded column names back to the
cursor.description's keys and return them as defined on the SQLAlchemy
side, you'd still get the uppercase names when we didn't have that
information, like execute("select * from table"). I have a vague
recollection of someone having a specific issue with that behavior but
I'm not finding what it was at the moment.
On Sep 29, 2008, at 10:04 AM, Itamar Ravid wrote:
>
> Hey guys - I've ran into a strange bug in 0.4.7p1 while trying to make
> use of Elixir's versioned plugin. In Oracle, given a table created as
> such:
>
> CREATE TABLE books
> (book_id NUMBER PRIMARY KEY);
>
> The following code fails:
>
> dbEng = create_engine("oracle://:@pearl")
> meta = MetaData()
> meta.bind = dbEng
>
> booksTable = Table("books", meta, autoload=True)
>
> booksTable.insert({'BOOK_ID': 200}).execute()
>
> Whilst the following, succeeds:
>
> booksTable.insert({'book_id': 200}).execute()
>
> This is strange, considering the fact that the following:
>
> result = booksTable.select().execute().fetchone()
> print result.keys()
>
> ... results in the column names in uppercase.
>
> Am I doing anything wrong?
>
> >
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
