[sqlalchemy] Re: Oracle reserved words
On Apr 29, 12:30 am, Michael Bayer wrote: > The next issue is, this was a known issue that is fixed in both 0.6.7 and 0.7 > (ticket 2100, unfortunately I had to go through creating a whole test just > now to figure that out, but there you go). And, if you name your column > "group" or "Group", and *not* u"group", or u"Group", i.e. don't specify as > unicode, it will probably work in older 0.6's too. Thank you a lot. I was still using 0.7b1. I upgraded to 0.7b4, the fixes from ticket 2100 work just fine, love your work! :D Kind Regards, Sirko -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Oracle reserved words
I have a problem with oracle column names that are oracle reserved
words (http://download.oracle.com/docs/cd/B19306_01/em.102/b40103/
app_oracle_reserved_words.htm). I read through the archive and found:
https://groups.google.com/group/sqlalchemy/browse_thread/thread/5e2699594c73fe1c/41f35436c9149818?hl=en&lnk=gst&q=oracle+reserved+words#41f35436c9149818
sadly I have the same problem with the current quoting of oracle
reserved words as bindvars, i.e. I get something
like this:
---8<---
DatabaseError: (DatabaseError) ORA-01036: illegal variable name/number
'UPDATE SET "Group"=:"Group"' {u'"Group"': u'Undefined'}
---8<---
The workaround with a different local key works, but is IMHO not
ideal.
Is there any other way to get around this issue? I noticed that an
unquoted bindvar does work on this oracle db.
Kind Regards,
Sirko
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Oracle column names beginning with a digit
Hi Michael,
On Apr 20, 11:20 am, Michael Bayer wrote:
> 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)
Thank you for the key tip. For now I'll do something like this:
---8<---
FOUNDDIGITNAME=False
digitcols = []
for c in vals:
if re.match('^[0-9]',c):
digitcols.append(c)
FOUNDDIGITNAME=True
if FOUNDDIGITNAME:
cols = []
for dc in digitcols:
dckey='dc_' + dc
col = Column(dc,site_tbl.c[dc].type,key=dckey)
cols.append(col)
vals[dckey]=vals[dc]
del vals[dc]
site_tbl = Table('TBL_SITE',
metadata,
*cols,
schema='foo',
useexisting=True,
autoload=True
)
---8<---
> 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.
This would be a great rainy day project.
Kind Regards,
Sirko
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Oracle column names beginning with a digit
Hi Michael,
On Apr 20, 11:20 am, Michael Bayer wrote:
> 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)
Thank you for the key tip, for now I'll use something like this:
---8<---
FOUNDDIGITNAME=False
digitcols = []
for c in vals:
if re.match('^[0-9]',c):
digitcols.append(c)
FOUNDDIGITNAME=True
if FOUNDDIGITNAME:
cols = []
for dc in digitcols:
dckey='dc_' + dc
col = Column(dc,site_tbl.c[dc].type,key=dckey)
cols.append(col)
vals[dckey]=vals[dc]
del vals[dc]
site_tbl = Table('TBL_SITE',
metadata,
*cols,
schema='foo',
useexisting=True,
autoload=True
---8<---
> 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.
That would be a great rainy day project but for now I am happy with
that little overhead.
Kind Regards,
Sirko
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Oracle column names beginning with a digit
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.:
---8<--
engine =create_engine('oracle+cx_oracle://')
engine.echo = True
Session = sessionmaker(bind=engine)
session = Session()
metadata = MetaData()
metadata.bind = engine
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'}
---8<---
The problem is that the bindname ':100K_Name'.
Something like this:
---8<---
myupdate='UPDATE foo."TBL_SITE" SET "100K_Name"=:bv0,
"Accuracy"=:Accuracy_Method WHERE "Site_Id" = :Site_Id'
myvals={'Site_Id':5772,'Accuracy_Method ':'Bar','bv0':'Foo'}
session.execute(text(myupdate),myvals)
---8<---
works fine but I really would like to know if there is a way to let SA
handle these column names binds automatically? ( It would make things
a lot easier for me as the vals dict is generated dynamically. )
Kind Regards,
Sirko
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
