In SA 0.1.x I was able to execute sql using my own cursor like this:
engine.execute(self.__sql, cursor=cursor, parameters=params)
the idea was to prepare some custom OUTPUT parameters. It all looked
like this:
cursor = engine.connection().cursor()
result_var = cursor.var(getattr(engine.module, result_type)) #
IMPORTANT
params['sqlalchemy_result'] = result_var
engine.execute(self.__sql, cursor=cursor, parameters=params)
result = result_var.getvalue()
I'm using all this inside my custom Oracle Function implementation.
How can I do something similar in SA 0.3 (the ability to execute
statements via my cursor is not important, but rather the ability to
bind/access output parameters)?
There are two reasons for my custom implementation of "func":
1. be able to call proc/func with keyword arguments - standard practice
in Oracle
2. be able to call procedures
I guess I can do part of this via "SELECT func FROM dual", but I had
some reason for not doing it that way. If I remember correctly, It was
because I wanted support for output parameters in general, so instead
of doing part of code via select from dual and part of code via
begin/end/output I chose to do it all via begin/end/output.
Entire code is this:
def encdict(encoding, **kw):
res = {}
for k,v in kw.items():
res[k] = isinstance(v,unicode) and v.encode(encoding) or v
return res
class OraFunExec(object):
def __init__(self, kind, sql, params):
self.__kind = kind
self.__sql = sql
self.__params = params
def sql(self):
return self.__sql
def execute(self, engine, result_type='NUMBER'): # STRING, DATETIME
params = encdict(engine.encoding, **self.__params)
if self.__kind=="proc":
engine.execute(self.__sql, params)
else:
cursor = engine.connection().cursor()
result_var = cursor.var(getattr(engine.module,
result_type))
params['sqlalchemy_result'] = result_var
engine.execute(self.__sql, cursor=cursor,
parameters=params)
result = result_var.getvalue()
if isinstance(result, str):
result = unicode(result, engine.encoding)
elif isinstance(result, float):
ir = int(result)
if result-ir==0:
result = ir
return result
class OraFunGen(object):
def __init__(self, kind):
self.__kind = kind
self.__names = []
def __getattr__(self, name):
self.__names.append(name)
return self
def __call__(self, **kwargs):
if self.__kind=='proc':
text = "BEGIN %s%s; END;"
else:
text = "BEGIN :sqlalchemy_result := %s%s; END;"
name = ".".join(self.__names)
if kwargs:
args = "(%s)" % ", ".join(["%s => :%s" % (k,k) for k in
kwargs])
else:
args = ""
sql = text % (name, args)
return OraFunExec(self.__kind, sql, kwargs)
class OraFunGateway(object):
"""returns a callable based on an attribute name, which then
returns a Function
object with that name."""
def __init__(self, kind):
self.__kind = kind
def __getattr__(self, name):
return getattr(OraFunGen(self.__kind), name)
orafun = OraFunGateway('func')
orapro = OraFunGateway('proc')
Currently, I have something like this, but it doesn't work:
conn = engine.contextual_connect()
cursor = conn.connection.cursor()
result_var = cursor.var(getattr(engine.dialect.module,
result_type))
params['sqlalchemy_result'] = result_var
engine.execute(self.__sql, cursor=cursor, parameters=params)
result = result_var.getvalue()
conn.close()
There are problems with "cursor=cursor" part.
Also, do I have to close conn (I'm using threadlocal strategy)?
Regards,
Tvrtko
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
