sqlalchemy 'SELECT * INTO ##temptable' recipe fails to compile with literal binds

Question

for my particular use-case I need the:

SELECT * INTO ##tmp FROM some_table

SQL server construct, and I need the fully compiled statement (with inline parameters included) to be shown to a human on the commandline before the statement is actually emitted to the database.

I've used the following recipe (slightly tweaked for SQL Server syntax): https://groups.google.com/forum/#!msg/sqlalchemy/O4M6srJYzk0/B8Umq9y08EoJ

and while it does work, I can't get it to display the parameters inline.

Here is the actual code:

from sqlalchemy.sql import Select, table, column
from sqlalchemy.ext.compiler import compiles

class SelectInto(Select):
    def __init__(self, columns, into, *arg, **kw):
        super(SelectInto, self).__init__(columns, *arg, **kw)
        self.into = into

@compiles(SelectInto)
def s_into(element, compiler, **kw):
    text = compiler.visit_select(element)
    text = text.replace('FROM', f'INTO {element.into} \nFROM')
    return text


employee = table('employee', column('id'), column('name'))
select_into = SelectInto([employee.c.id, employee.c.name], "##tmp").select_from(employee).where(employee.c.id.in_([1, 3, 6]))
print(select_into.compile(compile_kwargs={'literal_binds': True}).string)

However, this returns:

SELECT employee.id, employee.name 
INTO ##tmp 
FROM employee 
WHERE employee.id IN (:id_1, :id_2, :id_3)

rather than:

SELECT employee.id, employee.name 
INTO ##tmp 
FROM employee 
WHERE employee.id IN (1, 3, 6)

I've spent so much time trying to figure out why and I just have no idea. This SelectInto class subclasses the sqlalchemy Select class, and that class compiles statements with inline literal binds without any issue.

Why is it not working here?

Any help would be so much appreciated

possible duplicate: https://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query — SuperShoot, Nov 08 '18 at 03:51

score 1 · Accepted Answer · answered Nov 08 '18 at 04:32

Forward the keyword arguments in your custom compiler:

@compiles(SelectInto)
def s_into(element, compiler, **kw):
    text = compiler.visit_select(element, **kw)
    text = text.replace('FROM', f'INTO {element.into} \nFROM')
    return text

At the moment visit_select() does not see the literal_binds argument, so it defaults to compiling placeholders instead.

sqlalchemy 'SELECT * INTO ##temptable' recipe fails to compile with literal binds

1 Answers1