sqlalchemy compiler question

I have a custom InsertFromSelect class which does exactly what it's name says. The output query is exactly what I need, problem is that I can't seem to execute it.

Class:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement

class InsertFromSelect( Executable, ClauseElement ):
    def __init__( self, table, select ):
        self.table = table
        self.select = select

@compiles( InsertFromSelect )
def visit_insert_from_select( element, compiler, **kw ):
    return "INSERT INTO %s (%s)" % (
        compiler.process(element.table, asfrom=True),
   开发者_JAVA百科     compiler.process(element.select)
    )

Query:

import proxy.lib.sqlalchemy.compilers.insertFromSelect as ifs
from sqlalchemy import not_, literal_column, String, text
from proxy.database import engine

ifsTest = ifs.InsertFromSelect(
    user_ip_table,
    select(
        [
            user.id,
            ips_table.c.id, literal_column( "'inactive'", String ),
            literal_column( "'"+service_type+"'", String )
        ]
    ).where(
        not_( ips_table.c.id.in_(
            select(
                [user_ip_table.c.ip_id]
            )
        ) )
    ).where(
        ips_table.c.ip_address.in_( validIps )
    )
)

Query output (print ifsTest):

INSERT INTO user_ip (SELECT 5144, ip.id, 'inactive', 'proxy' 
FROM ip 
WHERE ip.id NOT IN (SELECT user_ip.ip_id 
FROM user_ip) AND ip.ip_address IN (:ip_address_1, :ip_address_2, :ip_address_3, :ip_address_4, :ip_address_5, :ip_address_6))

I've tested the query manually against the database (with the params in place of course) and it produces exactly what I need but I can't seem to execute it with sqlalchemy.

I've tried:
connection = engine.connect()
res = connection.execute( ifsTest )
connection.close()

....but nothing is inserted. Any idea how should I do this?

since you're not using a transaction, add the "autocommit" option to your construct:

class InsertFromSelect( Executable, ClauseElement ):
    _execution_options = \
        Executable._execution_options.union({'autocommit': True})
    def __init__( self, table, select ):
        self.table = table
        self.select = select

alternatively call it explicitly:

connection.execution_options(autocommit=True).execute(mystatement)

or use a transaction:

trans = connection.begin()
connection.execute(...)
trans.commit()

background:

http://www.sqlalchemy.org/docs/core/connections.html#understanding-autocommit