Problem with inserting multiple rows as lists/tuples

[Stefan Urbanek]

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'

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.

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"):

[Michael Bayer]

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

[Stefan Urbanek]

On 27.12.2012, at 23:21, Michael Bayer <mik...@zzzcomputing.com> wrote:

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.   

Thanks for clear explanation.

Is there a way how can I specify the order of params explicitly? I know the order always, as I am holding metadata for whole data processing pipeline.

Stefan Urbanek

data analyst and data brewmaster

Twitter: @Stiivi

Home: http://stiivi.com

Brewery: http://databrewery.org

Github: https://github.com/Stiivi

[Michael Bayer]

Well its a matter of the syntax of the SQL statement, thats why positional parameters are a little less than ideal to start with.    There's ways to reconcile these two systems but there's no natural API suggested by it.   In the case of an insert(), there is a natural order to the params which is the order of columns in the table.   You can do a quick dictionary translation like this:

dict(zip((c.key for c in table.c), mytuple))

if the compiler were to accept positional parameters according to some ordering system it would need to be doing a step like this internally in any case.