[sqlalchemy] Re: Insert from Select Implentation
To partially clarify and answer my own question here (I was very tired by the time I pasted this last night) In the case of: sa .insert (mytable ).values(myothertable.select().filter_by(foo=sa.bindparam(bar)) This doesn't currently work because the bindparam required for the select statement gets discarded. Having gone through the way SA compiles statements, it appears that parameters are used to fill in an insert statements values. The only way I can see of getting around this is that at the time of adding the values, they bindparams are extracted from the select and added to a new attribute on the insert statement, and around the time of creating the ExecutionContext, they are processed and added back into the query. My previous question about getting both the column names and the bindparams, I managed to solve by (duh!) getting the column names, compiling the select statement and then getting the bindparams. I would still be interested to know where the bindparams are stored in an uncompiled select though. I couldn't work it out. Sorry if all this is a little fuzzy. It's my first time at really trying to understand what SA does behind the scenes. Thanks Ed On 11 Feb 2010, at 22:23, Ed Singleton wrote: I've been having a clumsy hack at enabling myself to pass a select statement as a value to an insert statement. IE: sa.insert(mytable).values(myothertable.select()) I've got it working in that most basic case, but I'm struggling when the select statement has bindparams. The insert needs to take them, as well as (for some dialects) the column names of the select. The thing is I can work out how to get the bindparams from a compiled statement, and I can work out how to get the column names from a uncompiled statement, but I can't work out how to get both from one or the other. Any hints on this would be appreciated. Also, I slightly worried that there's an obvious reason why this hasn't been done before. Is there something stupid I'm missing? If anyone's tried patching SA to do this I would greatly appreciate any advice. Thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Insert from Select Implentation
Ed Singleton wrote: To partially clarify and answer my own question here (I was very tired by the time I pasted this last night) In the case of: sa .insert (mytable ).values(myothertable.select().filter_by(foo=sa.bindparam(bar)) This doesn't currently work because the bindparam required for the select statement gets discarded. Having gone through the way SA compiles statements, it appears that parameters are used to fill in an insert statements values. The only way I can see of getting around this is that at the time of adding the values, they bindparams are extracted from the select and added to a new attribute on the insert statement, and around the time of creating the ExecutionContext, they are processed and added back into the query. My previous question about getting both the column names and the bindparams, I managed to solve by (duh!) getting the column names, compiling the select statement and then getting the bindparams. I would still be interested to know where the bindparams are stored in an uncompiled select though. I couldn't work it out. Sorry if all this is a little fuzzy. It's my first time at really trying to understand what SA does behind the scenes. 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. Thanks Ed On 11 Feb 2010, at 22:23, Ed Singleton wrote: I've been having a clumsy hack at enabling myself to pass a select statement as a value to an insert statement. IE: sa.insert(mytable).values(myothertable.select()) I've got it working in that most basic case, but I'm struggling when the select statement has bindparams. The insert needs to take them, as well as (for some dialects) the column names of the select. The thing is I can work out how to get the bindparams from a compiled statement, and I can work out how to get the column names from a uncompiled statement, but I can't work out how to get both from one or the other. Any hints on this would be appreciated. Also, I slightly worried that there's an obvious reason why this hasn't been done before. Is there something stupid I'm missing? If anyone's tried patching SA to do this I would greatly appreciate any advice. Thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Insert from Select Implentation
On 12 Feb 2010, at 17:43, Michael Bayer wrote: Ed Singleton wrote: To partially clarify and answer my own question here (I was very tired by the time I pasted this last night) In the case of: sa .insert (mytable ).values(myothertable.select().filter_by(foo=sa.bindparam(bar)) This doesn't currently work because the bindparam required for the select statement gets discarded. Having gone through the way SA compiles statements, it appears that parameters are used to fill in an insert statements values. The only way I can see of getting around this is that at the time of adding the values, they bindparams are extracted from the select and added to a new attribute on the insert statement, and around the time of creating the ExecutionContext, they are processed and added back into the query. My previous question about getting both the column names and the bindparams, I managed to solve by (duh!) getting the column names, compiling the select statement and then getting the bindparams. I would still be interested to know where the bindparams are stored in an uncompiled select though. I couldn't work it out. Sorry if all this is a little fuzzy. It's my first time at really trying to understand what SA does behind the scenes. 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. You mean I've spent a day googling and two days reading SA source code and that's all I had to do? I am both miffed and delighted at the same time. I didn't know about this compiler extension and had been hacking away at the SA source to get it working as a default feature of insert statements. I'll try this out post-haste. Thanks again Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Insert from Select Implentation
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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Insert from Select Implentation
Ed Singleton wrote: 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), ) add the mixin sqlalchemy.sql.expression._Executable to your InsertFromSelect class.I'm going to rename it to Executable and will add it to the docs now. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Insert from Select Implentation
On 12 Feb 2010, at 19:36, Michael Bayer wrote: Ed Singleton wrote: 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), ) add the mixin sqlalchemy.sql.expression._Executable to your InsertFromSelect class.I'm going to rename it to Executable and will add it to the docs now. Okay, I've modified it to this, and it seems to work perfectly from sqlalchemy.sql.expression import ClauseElement, _Executable 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) %s % ( compiler.process(element.table, asfrom=True), , .join([col.name for col in element.select.columns]), compiler.process(element.select), ) Thanks for all your help. You are a king. (If not one who leads me to throw away the hacking I did on SA. And I was so close to getting it working). Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
