Re: [sqlalchemy] a Python-side value or SQL expression is required

Mon, 05 Jun 2017 09:07:19 -0700 


On 06/05/2017 11:36 AM, alexei.bogda...@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
    
<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 <javascript:> 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
    
<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>


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

     >
     >      >
     >      > 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
     >              )
     >
     >
     >
     >
     >
     >      >
     >      > 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|
     >      >
     >      > --
     >      > 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 <javascript:>
     >      > <mailto:sqlalchemy+unsubscr...@googlegroups.com
    <javascript:> <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>
     >     <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>
     >     <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
    <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 <javascript:>
     > <mailto:sqlalchemy+unsubscr...@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+unsubscr...@googlegroups.com 
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com 
<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit 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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.






















					Reply via email to