Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

In SQL there is no way to do an "INSERT... SELECT". If you want to do it without using raw SQL in several places of your code you can create custom SQL compilation.

There is an example about how to do "INSERT...SELECT" in SA documentation. This example doesn't support column in the INSERT part of the sentence, some thing like: INSERT into table(col1, col2)...".

I've modified the example to that, this support table ("INSERT INTO table (SELECT...)") or columns ("INSERT INTO table (col1, col2) (SELECT...)".

Please, have a look an comment :)

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

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

@compiles(InsertFromSelect)
def visit_insert_from_select(element, compiler, **kw):
    if type(element.insert_spec) == list:
        columns = []
        for column in element.insert_spec:
            if element.insert_spec[0].table != column.table:
                raise Exception("Insert columns must belong to the same table")
            columns.append(compiler.process(column, asfrom=True))

        table = compiler.process(element.insert_spec[0].table)
        columns = ", ".join(columns)

        sql = "INSERT INTO %s (%s) (%s)" % (
                table, columns,
                compiler.process(element.select))

    else:
        sql = "INSERT INTO %s (%s)" % (
                compiler.process(element.insert_spec, asfrom=True),
                compiler.process(element.select))

    return sql

Example of its use with columns:

InsertFromSelect([dst_table.c.col2, dst_table.c.col1], select([src_table.c.col1, src_table.c.col1]))

Example of its use only with a table:

InsertFromSelect(dst_table, select(src_table]))

This works for me, but I want to hear other opinions.

share|improve this question
add comment

1 Answer 1

columns.append(compiler.process(column, asfrom=True))

should be simply

columns.append(column.name)

and

table = compiler.process(element.insert_spec[0].table)

should be

table = compiler.process(element.insert_spec[0].table, asfrom=True)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.