On Monday, June 5, 2017 at 7:07:12 PM UTC+3, Mike Bayer wrote:
On 06/05/2017 11:36 AM, alexei....@ytech.by <javascript:> 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
<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/
<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.**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>
>
<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>
>
<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>>
>
> >
>
<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:> <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>>
> > <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>>
> > <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>
> <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:> <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.
