SQLAlchemy CORE: bindparam()-like solution but for the type_ argument of func.cast()

27 views
Skip to first unread message

Massimiliano della Rovere

unread,
Apr 6, 2020, 5:42:22 AM4/6/20
to sqlalchemy
In SQLAlchemy CORE is there any way to use bindparam() to late-bind the type_ parameter of the func.cast function instead of a column?

I wrote a function returning a "baked query", that extracts settings stored in a XML column. I want the extracted value to be cast to a type depending on the specific extracted value (it depends on the xpath bound parameter).

Maybe there is to @compile something to allow bindparam() to return a type instead of a column element...

    def bake_extract_settings_query(self):
        return (
            select([
                func.cast(
                    func.cast(
                        func.xpath(
                            func.cast(
                                bindparam("xpath"),
                                Text),
                            func.cast(adm_preference.c.data, XMLType),
                            type_=ARRAY(XMLType))[1],
                        Text),
                    bindparam("output_type")  # <---- the problem
                ).label("preference")
            ])
            .where(adm_preference.c.code == "_mainpref_")
            .limit(1))

Mike Bayer

unread,
Apr 6, 2020, 10:30:32 AM4/6/20
to noreply-spamdigest via sqlalchemy

On Mon, Apr 6, 2020, at 5:42 AM, Massimiliano della Rovere wrote:
In SQLAlchemy CORE is there any way to use bindparam() to late-bind the type_ parameter of the func.cast function instead of a column?

I wrote a function returning a "baked query", that extracts settings stored in a XML column. I want the extracted value to be cast to a type depending on the specific extracted value (it depends on the xpath bound parameter).

OK so, this is actually the "type" within a CAST so this is not something that gets "bound" into a SQL query, "bound parameters" are only used for data values, not syntactical elements of a query as a type would be.

what's not clear here is when it is that you know what this type would be.   do you know what the type would be before you invoke the query, or is the type a product of something in the data that's being read?   if the latter case, you'd need to use a CASE so that the determination is made within the SQL process.

otherwise the easiest way to do this is to just parameterize your bake_extract_settings_query() function to return different select() structures with the different type inside the CAST.

The other two ways to make the statement change per execution is as you noted the @compiles version, and then you could also use visit replacement in conjunction with the before_execute event.   The latter approach might work better in that you don't need to worry that the SQL was cached since you will be intercepting it each time, and you also get to work upon the whole statement at once.     Yet another way would be to process the string SQL directly using the before_cursor_execute() event.

the challenge with all of those is knowing what type you want to use each time as it's not clear what mechanism you are using in order to know this.  if some part of your program wants to say, "execute_query(desired_type=String)" then you have to get that "desired_type" somewhere that an event hook can get it, like in a threading.local() or somehow carried along with the compiler or the connection.   But if you're doing that I don't see why you wouldn't just send that type to bake_extract_settings_query() and make a new select().  

so short answer yes there are ways to change the inside of the statement each time but those are inside of event hooks where you'd need to invent some way of passing the desired value inside, and it's not clear why you need to do that rather than just call bake_extract_settings_query() again.  If there are say five different datatypes you could always just put five select() structures in a dictionary if you are trying to avoid calling select() many times.   (is the issue performance?  there's a lot to say about that if so)


Maybe there is to @compile something to allow bindparam() to return a type instead of a column element...

    def bake_extract_settings_query(self):
        return (
            select([
                func.cast(
                    func.cast(
                        func.xpath(
                            func.cast(
                                bindparam("xpath"),
                                Text),
                            func.cast(adm_preference.c.data, XMLType),
                            type_=ARRAY(XMLType))[1],
                        Text),
                    bindparam("output_type")  # <---- the problem
                ).label("preference")
            ])
            .where(adm_preference.c.code == "_mainpref_")
            .limit(1))


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
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+...@googlegroups.com.

Massimiliano della Rovere

unread,
Apr 6, 2020, 12:31:37 PM4/6/20
to sqlal...@googlegroups.com
Il giorno lun 6 apr 2020 alle ore 16:30 Mike Bayer <mik...@zzzcomputing.com> ha scritto:

On Mon, Apr 6, 2020, at 5:42 AM, Massimiliano della Rovere wrote:
In SQLAlchemy CORE is there any way to use bindparam() to late-bind the type_ parameter of the func.cast function instead of a column?

I wrote a function returning a "baked query", that extracts settings stored in a XML column. I want the extracted value to be cast to a type depending on the specific extracted value (it depends on the xpath bound parameter).

OK so, this is actually the "type" within a CAST so this is not something that gets "bound" into a SQL query, "bound parameters" are only used for data values, not syntactical elements of a query as a type would be.

what's not clear here is when it is that you know what this type would be.   do you know what the type would be before you invoke the query, or is the type a product of something in the data that's being read?   if the latter case, you'd need to use a CASE so that the determination is made within the SQL process.

Hi Mike, thanks for your answer.
The type is related to the xpath as in bindparam("xpath").
I know the type only when the query is passed to connection.execute(),and only the caller of bake_extract_settings_query() knows the type; what I am writing is at framework-level, someone else - with the knowledge of <xpath, types> pairing will write the code with the .execute().
That said, I should add that in theory the xpath should contain not just the value as text() but also an attribute specifying the type, coded with symbols known to the application, e.g. 'DH'  =  TIMESTAMP, but the presence of the attribute is not guaranteed.
So it would be best to force the caller of .execute() to pass both xpath=... and output_type=...

This is the reason why I did not use CASE. 
Also we are using asyncio and sqlalchemy-aio, so CPU-bound activities should be limited as much as possible, that's the reason why I'd like the cast to be performed by the DB and not in python.
 

Mike Bayer

unread,
Apr 6, 2020, 12:47:07 PM4/6/20
to noreply-spamdigest via sqlalchemy
if the caller of bake_extract_settings_query() knows the type then it is most expedient that they pass it right there.   

Otherwise, you have to invent some way for this parameter to be sent somewhere, it can be in lots of places, like connection.execution_options(my_special_parameter="foo") or something like that, then use either replacement traversal with before_execute() or string replacement with before_cursor_execute().

however if you are using sqlalchemy-aio, I have no idea what event hooks that system supports as those tools typically have re-implemented the statement execution internals.







 


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
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+...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages