a Python-side value or SQL expression is required

[gbr]

I've upgraded from a SQLA version 0.9.x to 1.0.9. Previously, I did the following when inserting new records:

- Column('flag', Boolean, server_default=sql.expression.false()) I didn't set those columns locally and didn't include them in the insert statement when I wanted them to be False
- Column('date', Date, nullable=False) I didn't set those columns as part of the insert when I wanted `date=None`
- Column('number', Float, nullable=True) I assigned integer values to the column which were implicitly "casted" to floats

This behaviour changed which is described here (http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#python-side-defaults-invoked-for-each-row-invidually-when-using-a-multivalued-insert)

My question which (perhaps all?) of the above things I did could have caused the error message below (with the upgrade). Can I reinstate the old behaviour (is there a global SQLA parameter I could configure?) or some other way of making sure I fix all places where the above bullet points still apply?

  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", line 150, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1034, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1003, in _execute_clauseelement
    inline=len(distilled_params) > 1)
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 494, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 500, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 392, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 190, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 213, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1819, in visit_insert
    crud_params = crud._get_crud_params(self, insert_stmt, **kw)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/crud.py", line 113, in _get_crud_params
    values = _extend_values_for_multiparams(compiler, stmt, values, kw)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/crud.py", line 503, in _extend_values_for_multiparams
    for i, row in enumerate(stmt.parameters[1:])
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/crud.py", line 503, in <genexpr>
    for i, row in enumerate(stmt.parameters[1:])
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/crud.py", line 336, in _process_multiparam_default_bind
    "a Python-side value or SQL expression is required" % c)
CompileError: INSERT value for column table.number is explicitly rendered as a boundparameter in the VALUES clause; a Python-side value or SQL expression is required

[Mike Bayer]

On 01/18/2016 02:23 AM, gbr wrote:
> |I've upgraded from a SQLA version 0.9.x to 1.0.9. Previously, I did the
> following when inserting new records:
>
> - Column('flag', Boolean, server_default=sql.expression.false()) |||I didn't set those columns locally and didn't include them in the
> insert statement when I wanted them to be False
> |- Column('date', Date, nullable=False) I didn't set those columns as
> part of the insert when I wanted `date=None`
> - Column('number', Float, nullable=True) I assigned integer values to
> the column which were implicitly "casted" to floats
>
> This behaviour changed which is described here
> (http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#python-side-defaults-invoked-for-each-row-invidually-when-using-a-multivalued-insert)
>
> My question which (perhaps all?) of the above things I did could have
> caused the error message below (with the upgrade). Can I reinstate the
> old behaviour (is there a global SQLA parameter I could configure?) or
> some other way of making sure I fix all places where the above bullet
> points still apply?

that error should only occur if the list of elements you're passing to
insert.values() contains inconsistent keys. As long as each dictionary
has the same keys, that error won't occur. The old behavior was a bug,
we just kept it at 1.1 in case people were relying upon the buggy
behavior of it forcing NULL for columns where a default generator should
have been used.

If you think something else is happening, then you need to show me a
sample of data being passed to values() along with the Table metadata
that causes this error to ensure there's not some other bug.

here's the test that illustrates what causes the error:

def test_server_default_absent_value(self):
metadata = MetaData()
table = Table('sometable', metadata,
Column('id', Integer, primary_key=True),
Column('data', String),
Column('foo', Integer, server_default=func.foobar()))

values = [
{'id': 1, 'data': 'data1', 'foo': 'plainfoo'},
{'id': 2, 'data': 'data2'},
{'id': 3, 'data': 'data3', 'foo': 'otherfoo'},
]

assert_raises_message(
exc.CompileError,
"INSERT value for column sometable.foo is explicitly rendered "

"as a boundparameter in the VALUES clause; a Python-side
value or "

"SQL expression is required",
table.insert().values(values).compile
)

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[gbr]

Sorry, I'm having difficulties following your explanation. Would you mind extending on it a bit?

that error should only occur if the list of elements you're passing to
insert.values() contains inconsistent keys.  As long as each dictionary
has the same keys, that error won't occur.

With "keys", do you mean the primary key column in your example (the 'id' column)? Or column 'foo'?

The old behavior was a bug, we just kept it at 1.1 in case people were relying upon the buggy
behavior of it forcing NULL for columns where a default generator should
have been used.

So, if I upgrade to 1.1 the old behaviour will be restored? Are there any plans of making it a feature and maintaining it?

values = [
            {'id': 1, 'data': 'data1', 'foo': 'plainfoo'},
            {'id': 2, 'data': 'data2'},
            {'id': 3, 'data': 'data3', 'foo': 'otherfoo'},
        ] 

Which one of those dicts causes the issue and why? Is it the fact that 'foo' is being set client-side, that the id=2 row has no value, that the id=1 and id=3 rows have different values? What's the "proper" way of resolving this without relying on buggy (and potentially not maintained) behaviour?

Thanks for your help.

[Mike Bayer]

On 01/18/2016 07:41 PM, gbr wrote:
> Sorry, I'm having difficulties following your explanation. Would you
> mind extending on it a bit?
>
> that error should only occur if the list of elements you're passing to
> insert.values() contains inconsistent keys. As long as each dictionary
> has the same keys, that error won't occur.
>
>
> With "keys", do you mean the primary key column in your example (the
> 'id' column)? Or column 'foo'?
>
> The old behavior was a bug, we just kept it at 1.1 in case people
> were relying upon the buggy
> behavior of it forcing NULL for columns where a default generator
> should
> have been used.
>
>
> So, if I upgrade to 1.1 the old behaviour will be restored? Are there
> any plans of making it a feature and maintaining it?
>
>
> values = [
> {'id': 1, 'data': 'data1', 'foo': 'plainfoo'},
> {'id': 2, 'data': 'data2'},
> {'id': 3, 'data': 'data3', 'foo': 'otherfoo'},
> ]
>
>
> Which one of those dicts causes the issue and why? Is it the fact that
> 'foo' is being set client-side, that the id=2 row has no value, that the
> id=1 and id=3 rows have different values? What's the "proper" way of
> resolving this without relying on buggy (and potentially not maintained)
> behaviour?

in SQL, an INSERT statement looks like this:


INSERT INTO table (col1, col2, col3) VALUES ('x', 'y', 'z')

Above, note that the INSERT statement has one section that names out the
target columns individually; the second part is the VALUES clause that
names out the values.

With the error message you are getting, even though I've yet to see a
code example here, it indicates the kind of SQL you're going for is this:

INSERT INTO table (col1, col2, col3) VALUES
('x', 'y', 'z'), ('q', 'p', 'r'), ('a', 'b', 'c')

that is, there are *multiple* groups of values, each indicating an
individual row. If you weren't using this form of statement then you
wouldn't be getting that error message.

Above, it should be apparent that each set of "values" has to correspond
with the original list of column names that serve as the targets; in
this case "col1, col2, col3".

the SQL would not be valid if we said:

INSERT INTO table (col1, col2, col3) VALUES
('x', 'y', 'z'), ('q', 'r'), ('a', 'b', 'c')


can you see why? The second tuple doesn't have enough values.

Looking at the code example, we see:

values = [
{'id': 1, 'data': 'data1', 'foo': 'plainfoo'},
{'id': 2, 'data': 'data2'},
{'id': 3, 'data': 'data3', 'foo': 'otherfoo'},
]

Even though we have three independent dictionaries, when you pass a list
of these to the SQLAlchemy insert().values() method, it means you want
it to render the VALUES all at once as above. So to render SQL of this
form, each dictionary needs to have the *identical* set of keys, in this
case those keys being 'id', 'data', and 'foo'.

The solution then is to ensure all dictionaries have the same keys:

values = [
{'id': 1, 'data': 'data1', 'foo': 'plainfoo'},

{'id': 2, 'data': 'data2', 'foo': None},

{'id': 3, 'data': 'data3', 'foo': 'otherfoo'},
]

SQLAlchemy also supports a so-called "executemany" form, where you
instead pass a list of dictionaries to connection.execute(). This is
the more common form. However, as it needs to apply each dictionary to
the statement as fixed SQL, it requires the same condition on the
incoming data.

> The old behavior was a bug, we just kept it at 1.1 in case people
> were relying upon the buggy
> behavior of it forcing NULL for columns where a default generator
> should
> have been used.
>
>
> So, if I upgrade to 1.1 the old behaviour will be restored? Are there
> any plans of making it a feature and maintaining it?

OK let me restate that sentence, as it is unclear and I also made a
typo: "The old behavior, that of the 0.9 series and earlier, is a bug.
It is never correct and will not be restored. The reason the bug was
not fixed in 0.9, and instead was added as a behavioral change in 1.0
(not 1.1), was because it is possible that application were relying upon
the old and broken behavior in order to function as expected.
Applications which upgrade to 1.0 must correct their reliance on the 0.9
and earlier behavior in order to function correctly, by ensuring that
each dictionary passed to insert().values() in a list contains the
identical collection of string dictionary key values".

Does that help?

>
> Thanks for your help.
>
> On Monday, 18 January 2016 18:23:04 UTC+11, gbr wrote:
>
> |I've upgraded from a SQLA version 0.9.x to 1.0.9. Previously, I did
> the following when inserting new records:
>
> - Column('flag', Boolean, server_default=sql.expression.false()) |||I didn't set those columns locally and didn't include them in the
> insert statement when I wanted them to be False
> |- Column('date', Date, nullable=False) I didn't set those columns
> as part of the insert when I wanted `date=None`
> - Column('number', Float, nullable=True) I assigned integer values
> to the column which were implicitly "casted" to floats
>
> This behaviour changed which is described here
> (http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#python-side-defaults-invoked-for-each-row-invidually-when-using-a-multivalued-insert

> <http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#python-side-defaults-invoked-for-each-row-invidually-when-using-a-multivalued-insert>)

[gbr]

Thanks Mike - it makes absolute sense now. I didn't consider how passing the dicts gets converted into SQL.

I conclude that this error has nothing to do with server_default or nullable=True, but stems from the fact that some keys of the dictionaries are set while others aren't.

Thanks again for taking the time to respond (and of course for creating such a great library).

On Monday, 18 January 2016 18:23:04 UTC+11, gbr wrote:

[alexei....@ytech.by]

Hi, sorry for interfering, but the question is still the same, but with a little bit tricky condition. Please, have a look stackoverflow.  

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

this takes strings:

print sa.insert(SomeClass).values([
{"service_id": '12'},
{"service_id": '34'},
]).compile(dialect=postgresql.dialect())

see the docs and examples at:

https://docs.sqlalchemy.org/en/latest/core/dml.html?highlight=insert%20values#sqlalchemy.sql.expression.Insert.values.params.**kwargs

https://docs.sqlalchemy.org/en/latest/core/dml.html?highlight=insert%20values#sqlalchemy.sql.expression.Insert.values.params.*args

On 06/02/2017 12:16 PM, alexei....@ytech.by wrote:
> Hi, sorry for interfering, but the question is still the same, but with
> a little bit tricky condition. Please, have a look stackoverflow

> <https://stackoverflow.com/questions/44333141/python-side-defaults-are-not-invoked-for-each-row-individually-when-using-a-mult>.

>
>
> On Monday, January 18, 2016 at 5:34:57 PM UTC+3, Michael Bayer wrote:
>
>
>
> On 01/18/2016 02:23 AM, gbr wrote:
> > |I've upgraded from a SQLA version 0.9.x to 1.0.9. Previously, I
> did the
> > following when inserting new records:
> >
> > - Column('flag', Boolean, server_default=sql.expression.false())
> |||I didn't set those columns locally and didn't include them in the
> > insert statement when I wanted them to be False
> > |- Column('date', Date, nullable=False) I didn't set those
> columns as
> > part of the insert when I wanted `date=None`
> > - Column('number', Float, nullable=True) I assigned integer
> values to
> > the column which were implicitly "casted" to floats
> >
> > This behaviour changed which is described here
> >
> (http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#python-side-defaults-invoked-for-each-row-invidually-when-using-a-multivalued-insert

> <http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#python-side-defaults-invoked-for-each-row-invidually-when-using-a-multivalued-insert>)

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com

> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.

> > Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---

> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

[alexei....@ytech.by]

Thanks Mike,

I understand what you're talking about, but still this alchemy machinery is pretty much ambiguous. For, example:

if SomeClass was a Table instance with Column attributes, this insert would work successfully:

print sa.insert(SomeClass).values([ 

         {SomeClass.c.service_id: '12'}

]).compile(dialect=postgresql.dialect()) 

Same is for Declarative models, where each field is InstrumentedAttribute:

print sa.insert(SomeClass).values([ 

         {SomeClass.service_id: '12'}

]).compile(dialect=postgresql.dialect()) 

These last 2 are not mentioned in doc. But what concerning the multi-valued insert, this is something magical) It works only for string key representation.

Is there any rule of good practice, elaborating this peculiar  thing on how the Mapper instance renders these different column formats and what should be kept in mind?

>      > <mailto:sqlalchemy+unsub...@googlegroups.com <javascript:>>.

>      > To post to this group, send email to sqlal...@googlegroups.com
>     <javascript:>
>      > <mailto:sqlal...@googlegroups.com <javascript:>>.
>      > Visit this group at https://groups.google.com/group/sqlalchemy
>     <https://groups.google.com/group/sqlalchemy>.
>      > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

On 06/05/2017 11:36 AM, alexei....@ytech.by wrote:
> Thanks Mike,
>
> I understand what you're talking about, but still this alchemy machinery
> is pretty much ambiguous. For, example:
> if SomeClass was a Table instance with Column attributes, this insert
> would work successfully:
>
> print sa.insert(SomeClass).values([
> {SomeClass.c.service_id: '12'}
> ]).compile(dialect=postgresql.dialect())
>
> Same is for Declarative models, where each field is InstrumentedAttribute:
>
> print sa.insert(SomeClass).values([
> {SomeClass.service_id: '12'}
> ]).compile(dialect=postgresql.dialect())
>
> These last 2 are not mentioned in doc.

because they work by accident. the documentation shows the keys as
strings for inserts, or more frequently just as kwargs, e.g.
values(colname='value')

For updates, many examples show the use of values() with SQL expressions
as keys, but updates don't support "multi-values" so that case was never
covered.

it's certainly doable that col expressions can be supported by
insert.values() everywhere but that will not help you in the current
release.

> But what concerning the
> multi-valued insert, this is something magical) It works only for string
> key representation.

strings should work for updates too

> Is there any rule of good practice, elaborating this peculiar thing on
> how the Mapper instance renders these different column formats and what
> should be kept in mind?

Mapper is not involved when you use Core insert(). Best practice is
to follow the many examples in the tutorial at
http://docs.sqlalchemy.org/en/latest/core/tutorial.html and if something
you think should work doesn't (e.g. col expression everywhere), make a
very succinct and complete test case illustrating the behavior you
expect and post a feature request at
https://bitbucket.org/zzzeek/sqlalchemy/issues/ (or better yet a pull
request).

>
>
> On Saturday, June 3, 2017 at 12:15:01 AM UTC+3, Mike Bayer wrote:
>
> this takes strings:
>
> print sa.insert(SomeClass).values([
> {"service_id": '12'},
> {"service_id": '34'},
> ]).compile(dialect=postgresql.dialect())
>
> see the docs and examples at:
>
> https://docs.sqlalchemy.org/en/latest/core/dml.html?highlight=insert%20values#sqlalchemy.sql.expression.Insert.values.params.**kwargs

> <https://docs.sqlalchemy.org/en/latest/core/dml.html?highlight=insert%20values#sqlalchemy.sql.expression.Insert.values.params.**kwargs>
>
>
> https://docs.sqlalchemy.org/en/latest/core/dml.html?highlight=insert%20values#sqlalchemy.sql.expression.Insert.values.params.*args

> > > <mailto:sqlalchemy+...@googlegroups.com
> <javascript:> <javascript:>>.

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

[alexei....@ytech.by]

Yes, I see, thank you!
The only reason I insist trying to understand the logic, because IMHO the mechanism of attributes invocation like:

sa.insert(SomeClass).values(

     {SomeClass.service_id: '12'}

)

is more natural and explicit then using string col names, even from point of OOP.

>      >      > <mailto:sqlalchemy+unsub...@googlegroups.com

>      > <mailto:sqlalchemy+unsub...@googlegroups.com <javascript:>>.

>      > To post to this group, send email to sqlal...@googlegroups.com
>     <javascript:>
>      > <mailto:sqlal...@googlegroups.com <javascript:>>.
>      > Visit this group at https://groups.google.com/group/sqlalchemy
>     <https://groups.google.com/group/sqlalchemy>.
>      > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

On 06/06/2017 08:46 AM, alexei....@ytech.by wrote:
> Yes, I see, thank you!
> The only reason I insist trying to understand the logic, because IMHO
> the mechanism of attributes invocation like:
>
> sa.insert(SomeClass).values(
> {SomeClass.service_id: '12'}
> )
>
> is more natural and explicit then using string col names, even from
> point of OOP.

sure, insert.values() was not really how insert() was usually used
originally but as it has been expanded it can be enhanced to support this.

> > > > <mailto:sqlalchemy+...@googlegroups.com
> <javascript:>

> > > <mailto:sqlalchemy+...@googlegroups.com

> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.