On 12 Feb 2010, at 17:43, Michael Bayer wrote:
Ed Singleton wrote:
In the case of:
sa
.insert
(mytable
).values(myothertable.select().filter_by(foo=sa.bindparam("bar"))
This doesn't currently work because... [snip]
if you're using the @compiler extension to generate this, the same
compiler object would generate the string representation of both the
insert() and the select(), gathering bindparam() objects from the
structure as it goes. the params structure embedded on the Compiled
at
the end is what gets sent to execute() along with any ad-hoc values.
The example here:
http://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=compiler#compiling-sub-elements-of-a-custom-expression-construct
should accomplish all this.
I've had a go at this. I had to modify the string template slightly
to suit sqlites insert from syntax, but otherwise pretty much copy and
pasted from the docs. I keep getting the following error (sample
script below):
Traceback (most recent call last):
File "lib/nm_mail/insert_from_test.py", line 58, in <module>
session.execute(ins)
File "/Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/session.py",
line 737, in execute
clause, params or {})
File "/Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py",
line 1035, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py",
line 1095, in _execute_clauseelement
parameters=params
File "/Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py",
line 1170, in __create_execution_context
return dialect.execution_ctx_cls(dialect, connection=self,
**kwargs)
File "/Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/default.py",
line 271, in __init__
raise exc.ArgumentError("Not an executable clause: %s" % compiled)
sqlalchemy.exc.ArgumentError: Not an executable clause: INSERT INTO
"Foo" (id, name, body) SELECT "Bar".id, "Bar".name, "Bar".body
FROM "Bar"
# -*- coding: utf-8 -*-
import sqlalchemy.orm
import sqlalchemy as sa
uri = "sqlite://"
metadata = sa.MetaData()
engine = sa.create_engine(uri)
metadata.bind = engine
Session = sa.orm.sessionmaker(bind=engine)
foo_table = sa.Table("Foo", metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("name", sa.String(64)),
sa.Column("body", sa.String),
)
bar_table = sa.Table("Bar", metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("name", sa.String(64)),
sa.Column("body", sa.String),
)
metadata.create_all()
session = Session()
q = bar_table.insert()
session.execute(q.values(name="bar1", body="blah"))
session.execute(q.values(name="bar2", body="blah"))
session.execute(q.values(name="bar3", body="flibble"))
session.commit()
session.close()
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ClauseElement
class InsertFromSelect(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) %s" % (
compiler.process(element.table, asfrom=True),
", ".join([col.name for col in element.select.columns]),
compiler.process(element.select),
)
s = bar_table.select()
ins = InsertFromSelect(foo_table, s)
print ins
session.execute(ins)
session.commit()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
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.
