Thanks - makes sense.
I have a pre-existing need to treat these arrays as columns in some instances,
so I think there's merit in the more complex solution using UDT.
Anticipated eventual use of ArrayType is something like:
class MyClass(Base):
__tablename__ = 'test'
# Columns
my_int = Column(Integer, primary_key=True)
my_list = Column(ArrayType)
... I'm not sure how the LiteralBindParam class fits in this scenario.
----- Original Message -----
From: "Michael Bayer" <mike...@zzzcomputing.com>
To: sqlalchemy@googlegroups.com
Sent: Thursday, February 13, 2014 11:35:47 AM
Subject: Re: [sqlalchemy] array types using OracleDialect
you know if you just want to spit out a string and don’t mind calling a
function it’s much easier than that:
from sqlalchemy import literal_column
def oracle_array(arr):
return literal_column("int_array(" + ", ".join(str(v) for v in arr) + ")”)
ret = session.execute(func.some_db_func(oracle_array([1,2,3,4,5]))).scalar()
On Feb 13, 2014, at 1:38 PM, Amos Smith <asm...@aseg.com> wrote:
> Thanks! ... this is working nicely.
>
> With a bit of customization this gives me an interim solution for passing
> lists to stored procs using Oracle dialect.
>
> I'm still hoping to find a way to bind array parameters outside of PL/SQL
> through cx_Oracle later.
>
> ----- Original Message -----
> From: "Michael Bayer" <mike...@zzzcomputing.com>
> To: sqlalchemy@googlegroups.com
> Sent: Thursday, February 13, 2014 10:20:58 AM
> Subject: Re: [sqlalchemy] array types using OracleDialect
>
> like this:
>
> class LiteralBindParam(BindParameter):
> pass
>
> @compiles(LiteralBindParam)
> def literal_bind(element, compiler, **kw):
> kw['literal_binds'] = True
> return compiler.visit_bindparam(element, **kw)
>
> class ArrayType(UserDefinedType):
>
> def get_col_spec(self):
> return "ARRAY"
>
> def literal_processor(self, dialect):
> def process(value):
> return 'int_array(' + ", ".join(str(v) for v in value) + ')'
> return process
>
> from sqlalchemy.orm import Session
> engine = sqlalchemy.create_engine("sqlite://", echo=True)
> metadata = MetaData(engine)
>
> session = Session(engine)
> a = LiteralBindParam(None, [1,2,3,4,5], type_=ArrayType)
>
> ret = session.execute(func.some_db_func(a)).scalar()
>
>
>
>
> On Feb 13, 2014, at 1:03 PM, Amos Smith <asm...@aseg.com> wrote:
>
>> I found the missing space before literal_processor in my earlier listing,
>> poor font selection hid that pretty well - still not sure why I couldn't see
>> that earlier apologies again for that post.
>>
>> The new listing below now runs to completion but literal_processor method is
>> not called for my UDT 'ArrayType', it's just being bound as the value NULL.
>>
>> It's still not clear to me if there is an issue with literal_processor being
>> called, or I'm misusing the LiteralBindParam recipe from earlier post, or
>> possibly still and issue with the array type udt.
>>
>> I'm not sure what type should be used in following line; currently just None.
>>
>> a = LiteralBindParam(None, ArrayType([1,2,3,4,5]))
>>
>> I'm also not sure if the return from ArrayType get_col_spec is valid; or if
>> this needs to be registered or defined somewhere.
>>
>> def get_col_spec(self):
>> return "ARRAY"
>>
>> ------------------------
>>
>> import os
>>
>> import sqlalchemy
>> from sqlalchemy.orm import sessionmaker
>> from sqlalchemy import MetaData
>> from sqlalchemy.ext.declarative import declarative_base
>>
>> from sqlalchemy import func
>> from sqlalchemy.types import UserDefinedType
>>
>> from sqlalchemy.sql.expression import BindParameter
>> from sqlalchemy.ext.compiler import compiles
>>
>> #================================================
>> class LiteralBindParam(BindParameter):
>> pass
>>
>> @compiles(LiteralBindParam)
>> def literal_bind(element, compiler, **kw):
>> kw['literal_binds'] = True
>> return compiler.visit_bindparam(element, **kw)
>>
>> #================================================
>> class ArrayType(UserDefinedType):
>> def __init__(self, listVal):
>> self.listVal = listVal
>> return
>>
>> def get_col_spec(self):
>> return "ARRAY"
>>
>> def literal_processor(self, dialect):
>> print 'got called'
>> def process(value):
>> return 'int_array(' + self.listValue + ')'
>> return process
>>
>> #================================================
>> dbUser = os.environ.get('uid')
>> dbPwd = os.environ.get('pwd')
>> oraSID = os.environ.get('sid')
>> connstr = 'oracle://%s:%s@%s' % (dbUser, dbPwd, oraSID)
>>
>> #================================================
>> engine = sqlalchemy.create_engine(connstr)
>> metadata = MetaData(engine)
>> Base = declarative_base(metadata=metadata)
>>
>> sessionMaker = sessionmaker(bind=engine)
>> session = sessionMaker()
>>
>> a = LiteralBindParam(None, ArrayType([1,2,3,4,5]))
>>
>> ret = session.execute(func.some_db_func(a)).scalar()
>>
>> print ret
>>
>> ----- Original Message -----
>> From: "Amos Smith" <asm...@aseg.com>
>> To: sqlalchemy@googlegroups.com
>> Sent: Wednesday, February 12, 2014 3:57:40 PM
>> Subject: Re: [sqlalchemy] array types using OracleDialect
>>
>> I migrated to 0.9.2 as I understood the literal_processor was new feature.
>>
>> I always get this error from listing shown below:
>>
>> def literal_processor(self, dialect):
>> ^
>> IndentationError: unindent does not match any outer indentation level
>>
>>
>> ArrayType is obviously just a stub in this listing.
>>
>> Apologies in advance if this is some obvious Python formatting issue of
>> mine, I'm still learning this language.
>>
>>
>>
>>
>> #===== Begin Listing =============================
>>
>> import os
>>
>> import sqlalchemy
>> from sqlalchemy.orm import sessionmaker
>> from sqlalchemy import MetaData
>> from sqlalchemy.ext.declarative import declarative_base
>>
>> from sqlalchemy import func
>> from sqlalchemy.types import UserDefinedType
>>
>> from sqlalchemy.sql.expression import BindParameter
>> from sqlalchemy.ext.compiler import compiles
>>
>> #================================================
>> class LiteralBindParam(BindParameter):
>> pass
>>
>> @compiles(LiteralBindParam)
>> def literal_bind(element, compiler, **kw):
>> kw['literal_binds'] = True
>> return compiler.visit_bindparam(element, **kw)
>>
>> #================================================
>> class ArrayType(UserDefinedType):
>> def get_col_spec(self):
>> return "ARRAY"
>>
>> def column_expression(self, col):
>> return None
>>
>> def literal_processor(self, dialect):
>> def process(value):
>> return "int_array(1, 2, 3, 4, 5)"
>> return process
>>
>> #================================================
>> dbUser = os.environ.get('uid')
>> dbPwd = os.environ.get('pwd')
>> oraSID = os.environ.get('sid')
>> connstr = 'oracle://%s:%s@%s' % (dbUser, dbPwd, oraSID)
>>
>> #================================================
>> engine = sqlalchemy.create_engine(connstr)
>> metadata = MetaData(engine)
>> Base = declarative_base(metadata=metadata)
>> dialectMgr = DialectManager()
>> sessionMaker = sessionmaker(bind=engine)
>> session = sessionMaker()
>>
>> a = LiteralBindParam(None, ArrayType())
>>
>> session.execute(func.some_db_func(a)).scalar()
>>
>> #===== End Listing ===============================
>>
>> ----- Original Message -----
>>
>>
>>> Regarding the following:
>>>> if the type of the LiteralBindParameter implements “literal_processor()”,
>>>> that controls how the literal value is rendered into the statement.
>>>
>>> How does one implement the "literal_processor()" for a new type? Is
>>> literal_processor() method applicable for UserDefinedTypes?
>>
>> the method is literal_processor:
>> http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#sqlalchemy.types.TypeEngine.literal_processor
>>
>>
>> its new as of 0.9 and applies to any type, including UserDefinedType. If
>> you don’t see it taking effect, that’s a bug.
>>
>> --
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>> --
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/groups/opt_out.
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
