Hi,
This is working version of InsertFromSelect with columns support. Works
for me, but I could be useful for somebody else. And also I want to heart
other opinions about this.
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][1]. 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]))
[1]:
http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/075A7OE-wckJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
