On Jan 28, 2014, at 6:06 PM, a_t_smith <[email protected]> wrote:
> Thanks for the feature request post and information.
>
> At the moment I'm just hoping to have a udt s.t. client code can do something
> like:
> a = [1,2,3,4,5]
> oat = OracleArrayType(cx_Oracle.INTEGER, a)
> func.some_func_expecting_intarray(oat)
> rp = session.execute(func)
>
> However, I don't have enough experience with Python or SqlAlchemy yet to know
> if I'm headed in a sane direction.
>
> Per udt listing below -
>
> Will the oraArray parameter passed into func.some_func_expecting_intarray get
> handled by the udt bind_expression?
>
> Is there a cleaner way to get at the cursor in the bind_expression method?
no, this is the part i was suggesting needs to be improved. for the moment,
the OracleExecutionContext is looking for parameters that need special things.
you can also intercept the parameters being sent to cx_oracle using a
before_cursor_execute event, which might be a way to interact with the cursor
directly without modifying the cx_oracle dialect directly.
that is
from sqlalchemy import event
@event.listens_for(Engine, “before_cursor_execute”)
def look_for_array_params(conn, cursor, statement, parameters, context,
executemany):
if context and context.compiled:
for key in parameters:
bindparam = context.compiled.binds[key]
if isinstance(bindparam.type_, OracleArrayType):
parameters[key] = cursor.call_special_oracle_api(parameters[key])
this is sort of deep stuff so you’d need to familiarize a bit with the
execution flow (assuming the above doesn’t work as written).
>
> -------------
>
> from sqlalchemy import func
> from sqlalchemy.types import UserDefinedType
>
> class OracleArrayType(UserDefinedType):
> def __init__(self, type, list):
> self._type = type
> self._list = list
>
> def get_col_spec(self):
> return "ARRAY"
>
> def bind_expression(self, bindvalue):
> session = Session.object_session(self)
> conn = session.connection()
> dbobj = conn.getConnection()
> curs = dbobj.cursor()
> a = curs.arrayvar(self._type, self._list)
>
> return a
>
> def column_expression(self, col):
> return None
>
> -------------------
>
>
>
>
>
> On Tuesday, January 28, 2014 12:37:34 PM UTC-8, a_t_smith wrote:
> During evaluation of Python 2.7 + SqlAlchemy 0.8.x using OracleDialect, I
> found most of the features I need exist out of the box; however I don't see a
> way to pass arrays as inputs to function calls.
>
> Am I overlooking an existing capability? ... my searches have come up empty
> for Oracle but looks like Postgres has this already.
>
> In my situation, there are a number of existing custom types in the db
> defined like:
> "create or replace TYPE int_list AS TABLE OF INTEGER NOT NULL;"
> ...
> "create or replace TYPE str_list AS TABLE OF VARCHAR2(256 CHAR) NOT NULL;"
>
> These db types are typically expected as input parameters to functions.
>
> If support for this does not exist yet, I would be interested in adding
> support.
> It's unfortunately way beyond the scope of my task to modify the db to
> accommodate SqlAlchemy.
>
> I found the sqlalchemy user defined type support here:
> http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#types-custom
>
> I also found what looks like underlying support for array types in cx_Oracle
> here:
> https://code.google.com/p/cx-oracle-demos/source/browse/trunk/todo/oracle-array.py?r=2
>
> I don't see a way to plumb these cx_Oracle features into sqlalchemy user
> defined types since they depend on cx_Oracle connection and cursor types.
>
> Maybe there's a completely alternate approach I'm overlooking?
>
> Thanks in advance for suggestions.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
