On Apr 19, 2011, at 9:55 PM, Sirko Schroeder wrote:
> Hi,
>
> I have a little problem with column names that start with a digit at
> an oracle databases which I inherited.
> When I try to do and update/insert with values from a dict like this
> i.e.:
> site_tbl = Table('TBL_SITE', metadata, autoload = True, schema='foo' )
> pks = {'Site_Id':5772}
> vals= {'100K_Name':'Foo','Accuracy':'Bar'}
>
> clauses = [site_tbl.c[k]==v for k,v in pks.iteritems()]
> update = site_tbl.update().where(and_(*clauses))
>
> session.execute(update,vals)
> session.commit()
> ---8<---
>
> I get this error:
> ---8<---
> DatabaseError: (DatabaseError) ORA-01036: illegal variable name/number
> 'UPDATE foo."TBL_SITE" SET "100K_Name"=:100K_Name,
> "Accuracy_Method"=:Accuracy_Method WHERE foo."TBL_SITE"."Site_Id"
> = :Site_Id_1' {'Site_Id_1': 5772, '100K_Name': 'Foo', 'Accuracy':
> 'Bar'}
yeah that is pretty awful, Oracle is super picky about names, and there's
already a lot of stuff to appease its limitations with bind parameters. The
bind name is ultimately derived from the "key" of the column so this would
change the name:
Table("my_table", metadata, Column("100K_Name", String,
key="hundredkname"), autoload=True)
if you want something more automated than that you can use 0.7 and redefine the
names with events, docs for that are at
http://www.sqlalchemy.org/docs/07/core/events.html#sqlalchemy.events.DDLEvents.column_reflect
.
beyond using the key thing, which changes how you reference the column object
in Python, we'd have to dig into the core and handle this case. if quoting the
bind name is enough that would make it an easier adjustment.
--
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.
