SELECT .. BULK COLLECT INTO ...

[mkmo...@gmail.com]

Hello,

I am generating Oracle PLSQL using sqlalchemy core.

I would like to generate the following output:

SELECT foo, bar

BULK COLLECT INTO l_foos

FROM baz;

Is there a mechanism to insert a string after the select column list and before the FROM?

In sql/compiler.py:visit_select (https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L3188) I do not see a way to hook into this specific spot via compiler customizations, unless I am missing something.

I suppose I can simply do `query_str.replace('FROM', 'BULK COLLECT INTO l_foos FROM', 1) but I was hoping to make a nice API via a compiler customization.

Thanks and best regards,

Matthew

[Mike Bayer]

Select does not have a hook for that particular spot in the SQL compilation, so unless you rewrote all of visit_select, your best bet is to stick with simple string replacement.  

this does not in any way preclude you from using the compiler extension, get the text from the compiler then do the process

import re

from sqlalchemy import column

from sqlalchemy import select

from sqlalchemy import table

from sqlalchemy.ext.compiler import compiles

from sqlalchemy.sql.expression import Select

@compiles(Select)

def _compile(element, compiler, **kw):

    text = compiler.visit_select(element, **kw)

    match = re.search(r"BULK COLLECT INTO (.*)", text)

    if match:

        text = re.sub(r"BULK COLLECT INTO (.*)", "", text)

        text = text.replace("FROM", f"BULK COLLECT INTO {match.group(1)} FROM")

    return text

baz = table('baz', column('foo'), column('bar'))

stmt = select(baz).with_statement_hint("BULK COLLECT INTO I_foos")

print(stmt)

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8e55ea83-2396-4cda-972a-f011d6000c6an%40googlegroups.com.

[mkmo...@gmail.com]

Perfect, thank you Mike.

Best regards,

Matthew