On Dec 27, 2012, at 4:55 PM, Stefan Urbanek wrote:
> Hi, > > I am trying to pass multiple rows as tuples into an INSERT statement.: > > # buffer is list of lists/tuples, like: [ [1, "foo"], [2, "bar"], ... ] > insert = self.table.insert() > engine.execute(insert, buffer) > > This fails with: > > File > "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-macosx-10.8-x86_64.egg/sqlalchemy/engine/base.py", > line 740, in _execute_clauseelement > keys = distilled_params[0].keys() > AttributeError: 'list' object has no attribute 'keys' It's the exception, rather than the rule, that the ordering of bound parameters in a "compiled" statement can be controlled - a simple INSERT statement yes, but for SELECT, UPDATE, DELETE the ultimate order of the params in the compiled string might not correspond to the order in which the bindparam() objects were created in the Python code. So you can pass a list of lists in there but only if the statement you're executing is a raw string, using the paramstyle of the target DBAPI. ClauseElement structures only support named parameters as input params and you can see the error where it's trying to detect "keys" is in _execute_clauseelement. the path for a straight string is _execute_text. > > According to the documentation: > > http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Connection.execute > > I can pass list of lists/tuples, not only a list of dictionaries. The doc makes it clear this is for straight strings only: In the case that a plain SQL string is passed, and the underlying DBAPI accepts positional bind parameters, a collection of tuples or individual values in *multiparams may be passed > > For performance reasons (for ETL) I want to avoid creation of dictionaries if > not necessary. What is the correct way to do insert of multiple values if I > have rows as list of values? > > Stefan > > p.s.: I do not know much about SQLAlchemy internals, but the offending code: > > distilled_params = _distill_params(multiparams, params) > if distilled_params: > keys = distilled_params[0].keys() > else: > keys = [] > > expects distilled_params to return a list of dictionaries, which is not 100% > true according to python fallback implementation of the _distill_params > method which might return a list/tuple: "Given arguments from the calling > form *multiparams, **params, return a list of bind parameter structures, > usually a list of dictionaries.". Code: > > if isinstance(zero, (list, tuple)): > if not zero or hasattr(zero[0], '__iter__') and \ > not hasattr(zero[0], 'strip'): > # execute(stmt, [{}, {}, {}, ...]) > # execute(stmt, [(), (), (), ...]) > return zero > > Zero might be [(), (), (), ...]. > > The keys in offending code are used only for caching. Will this change of > offending code work? > > if distilled_params and hasattr(distilled_params[0], "keys"): > > -- > 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/-/oBZTXe8ZnuIJ. > 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.
