How to find columns being updated/inserted given a Update/Insert object
bool
updated (or Inserted).
I dont find any function that gives these?
t = Table("abc", MetaData(conn), Column("x", String), Column("y",
Integer))
u = t.update().values(x='a')
# Now I want to get list of columns being updated (i.e., x) given u
(i.e., Update object)
Michael Trier
Given an Update(or Insert) object how can I find the columns being
updated (or Inserted).
I dont find any function that gives these?
Michael Trier
http://michaeltrier.com/
empty
> > Given an Update(or Insert) object how can I find the columns being
> > updated (or Inserted).
> > I dont find any function that gives these?
>
> The attributes.get_history method will return this information to you as a
> History record for each attribute. See here:
>
Ugh misread your example. The above is only ORM related. Sorry.
Michael
empty
get you close to what you want. See the following doc on insert
expressions which demonstrates how this works.
http://www.sqlalchemy.org/docs/core/tutorial.html#insert-expressions
Michael
bool
But this wont probably work if I want to find the columns within
@Compiles(Insert)
I have a use-case for the below. Can you help....
@compiles(Insert)
def contextual_insert(insert, compiler, **kw):
print insert.compile().params--------------------------------->
This will gointo infinite loop.
return compiler.visit_insert(insert, **kw)
Michael Bayer
On Apr 18, 2011, at 6:56 AM, bool wrote:
>
> @compiles(Insert)
> def contextual_insert(insert, compiler, **kw):
> print insert.compile().params--------------------------------->
> This will gointo infinite loop.
> return compiler.visit_insert(insert, **kw)
OK this is useful detail, that you're doing a compiler for insert so thats why you need to get in there in the first place.
We don't yet have a nicely published interface on all the SQL constructs to give you the things inside of them. While the visitor interface tends to be the most appropriate for select() constructs due to their wide variety of structure, inserts and updates are pretty simplistic so you can look directly in them.
The insert and update both extend _ValuesBase, which provides values(). You can see this because the API docs show this, over here: http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.Insert . In 0.6 _ValuesBase itself isn't in the docs. It is in fact in the docs in 0.7 and is now called ValuesBase() as we are opening up these APIs to users, but I'm assuming you're on 0.6 for now.
So a quick peek at the source, which I know, is not ideal, but in Python when an open source project hasn't gotten there yet it's usually worth doing, reveals that _ValuesBase.values() puts everything into a collection called "parameters".
What's the format of "parameters" ? The quickest way to know exactly what .parameters is to just try it out (and in fact I didn't know offhand myself so I do this kind of thing all the time):
classics-MacBook-Pro:sqlalchemy classic$ python
>>> from sqlalchemy.sql import table, column
>>> i = table('t1', column('x'), column('y')).insert()
>>> i2= i.values(x=5)
>>> i2.parameters
{'x': 5}
>>> i2 = i2.values(y=7)
>>> i2.parameters
{'y': 7, 'x': 5}
>>>
So, the "values" for your insert or update construct are just in a dictionary, in .parameters. In fact the compile step does the work of converting that information into bindparam() objects or whatever else they are. If you stick SQL expressions in there, they just stay as what they are:
>>> u = table('t1', column('x'), column('y')).update().values(x=select([func.now()]))
>>> u.parameters
{'x': <sqlalchemy.sql.expression.Select at 0x11ebc30; Select object>}
>>> print u.parameters['x']
SELECT now() AS now_1
There is one slightly tricker part that is not as straightforward to figure out. The actual list of columns being inserted/updated can be affected at compile time - in the case where you said something like, connection.execute(table.insert(), x=5, y=4). values() isn't used in that case, but the resulting SQL still gets "x" and "y" in it. In that kind of case, the extra parameters are in fact passed to the compiler, and if you want those you need to look at compiler.column_keys to get the names of the columns. The values themselves aren't available at this point - the connection is going to pass those to DBAPI cursor.execute() as bound values.
So the full recipe to get all the names of the columns is:
from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert, table, column
@compiles(Insert)
def contextual_insert(insert, compiler, **kw):
all_the_keys = set()
if insert.parameters:
all_the_keys.update(insert.parameters)
if compiler.column_keys:
all_the_keys.update(compiler.column_keys)
return "-- ALL THE KEYS: %s\n%s" % (
",".join(all_the_keys),
compiler.visit_insert(insert, **kw)
)
t = table('t1', column('x'), column('y'), column('z'))
print t.insert().values(x=5, y=4).compile()
print t.insert().compile(column_keys=['x', 'y'])
print t.insert().values(x=5, y=4).compile(column_keys=['z'])
where "-- <some text>" is a SQL comment. the output then:
-- ALL THE KEYS: y,x
INSERT INTO t1 (x, y) VALUES (:x, :y)
-- ALL THE KEYS: y,x
INSERT INTO t1 (x, y) VALUES (:x, :y)
-- ALL THE KEYS: y,x,z
INSERT INTO t1 (x, y, z) VALUES (:x, :y, :z)
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>