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 -~----------~----~----~----~------~----~------~--~---
