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 [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. > -- 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.
