Thanks for taking the time to do this. The problem was, of course, on
my side. What got me thinking it was a SQLAlchemy issue was that I
thought I had read that SQLAlchemy assumes that the first integer
column is an autoincrement. Sorry for the noise.
On Wed, Dec 16, 2020 at 5:00 PM Mike Bayer <mike...@zzzcomputing.com> wrote:
>
> I would ask if perhaps there are triggers in place or other server side
> constructs that might be at play here. The issue would need to be
> illustated via an MCVE otherwise. Below I've taken your model, extrapolated
> from it the required Application and Enumeration classes, and am able to run
> your bulk_save_objects() code without any errors on SQL Server Express
> database, SQLAlchemy 1.3.20. I would try running this test script on a
> **non-production** database (note that it drops the tables first so that it
> is repeatable) to see if it succeeds without issue.
>
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy import String
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import Session
>
> Base = declarative_base()
>
>
> class Application(Base):
> __tablename__ = "Application"
>
> appCode = Column(String(10), primary_key=True)
>
>
> class Enumeration(Base):
> __tablename__ = "Enumeration"
>
> enumID = Column(Integer, primary_key=True, autoincrement=False)
>
>
> class Component(Base):
> __tablename__ = "Component"
>
> appCode = Column(
> ForeignKey("Application.appCode"), primary_key=True, nullable=False
> )
> componentEnumID = Column(
> ForeignKey("Enumeration.enumID"),
> primary_key=True,
> nullable=False,
> autoincrement=False,
> )
>
>
> e = create_engine(
>
> "mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server",
> echo=True,
> )
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> session = Session(e)
> session.add(Application(appCode="VCP00001"))
> session.add(Enumeration(enumID=12))
> session.add(Enumeration(enumID=13))
> session.commit()
>
>
> models = []
> model = Component()
> model.appCode = "VCP00001"
> model.componentEnumID = 12
> models.append(model)
> model = Component()
> model.appCode = "VCP00001"
> model.componentEnumID = 13
> models.append(model)
> session.bulk_save_objects(models)
> session.commit()
>
>
> the INSERT statements look like:
>
> 2020-12-16 16:55:22,983 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
> 2020-12-16 16:55:22,986 INFO sqlalchemy.engine.base.Engine INSERT INTO
> [Enumeration] ([enumID]) VALUES (?)
> 2020-12-16 16:55:22,986 INFO sqlalchemy.engine.base.Engine ((12,), (13,))
> 2020-12-16 16:55:23,014 INFO sqlalchemy.engine.base.Engine INSERT INTO
> [Application] ([appCode]) VALUES (?)
> 2020-12-16 16:55:23,014 INFO sqlalchemy.engine.base.Engine ('VCP00001',)
> 2020-12-16 16:55:23,064 INFO sqlalchemy.engine.base.Engine COMMIT
> 2020-12-16 16:55:23,075 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
> 2020-12-16 16:55:23,077 INFO sqlalchemy.engine.base.Engine INSERT INTO
> [Component] ([appCode], [componentEnumID]) VALUES (?, ?)
> 2020-12-16 16:55:23,077 INFO sqlalchemy.engine.base.Engine (('VCP00001', 12),
> ('VCP00001', 13))
> 2020-12-16 16:55:23,133 INFO sqlalchemy.engine.base.Engine COMMIT
>
>
>
>
>
>
> On Wed, Dec 16, 2020, at 4:29 PM, Larry Martell wrote:
>
> I am providing both, e.g.:
>
> models = []
> model = Component()
> model.appCode = 'VCP00001'
> model.componentEnumID = 12
> models.append(model)
> model = Component()
> model.appCode = 'VCP00001'
> model.componentEnumID = 13
> models.append(model)
> session.bulk_save_objects(models)
>
> File "/opt/python/sqlalchemy/engine/base.py", line 1227, in _execute_context
> self.dialect.do_executemany(
> File "/opt/python/sqlalchemy/dialects/mssql/pyodbc.py", line 412, in
> do_executemany
> super(MSDialect_pyodbc, self).do_executemany(
> File "/opt/python/sqlalchemy/engine/default.py", line 587, in do_executemany
> cursor.executemany(statement, parameters)
> pyodbc.IntegrityError: ('23000', "[23000] [Microsoft][ODBC Driver 17
> for SQL Server][SQL Server]Cannot insert the value NULL into column
> 'componentEnumID', table 'DEC_CORE.dbo.Component'; column does not
> allow nulls. INSERT fails. (515) (SQLExecDirectW)")
>
> The above exception was the direct cause of the following exception:
>
> Traceback (most recent call last):
> File "/var/task/common/repository.py", line 52, in bulk_add
> self._persist_records_and_clear_batch(batch)
> File "/var/task/common/repository.py", line 82, in
> _persist_records_and_clear_batch
> self._session.bulk_save_objects(batch[:])
> File "/opt/python/sqlalchemy/orm/session.py", line 2733, in bulk_save_objects
> self._bulk_save_mappings(
> File "/opt/python/sqlalchemy/orm/session.py", line 2928, in
> _bulk_save_mappings
> transaction.rollback(_capture_exception=True)
> File "/opt/python/sqlalchemy/util/langhelpers.py", line 68, in __exit__
> compat.raise_(
> File "/opt/python/sqlalchemy/util/compat.py", line 178, in raise_
> raise exception
> File "/opt/python/sqlalchemy/orm/session.py", line 2916, in
> _bulk_save_mappings
> persistence._bulk_insert(
> File "/opt/python/sqlalchemy/orm/persistence.py", line 95, in _bulk_insert
> _emit_insert_statements(
> File "/opt/python/sqlalchemy/orm/persistence.py", line 1083, in
> _emit_insert_statements
> c = cached_connections[connection].execute(statement, multiparams)
> File "/opt/python/sqlalchemy/engine/base.py", line 984, in execute
> return meth(self, multiparams, params)
> File "/opt/python/sqlalchemy/sql/elements.py", line 293, in
> _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File "/opt/python/sqlalchemy/engine/base.py", line 1097, in
> _execute_clauseelement
> ret = self._execute_context(
> File "/opt/python/sqlalchemy/engine/base.py", line 1287, in _execute_context
> self._handle_dbapi_exception(
> File "/opt/python/sqlalchemy/engine/base.py", line 1481, in
> _handle_dbapi_exception
> util.raise_(
> File "/opt/python/sqlalchemy/util/compat.py", line 178, in raise_
> raise exception
> File "/opt/python/sqlalchemy/engine/base.py", line 1227, in _execute_context
> self.dialect.do_executemany(
> File "/opt/python/sqlalchemy/dialects/mssql/pyodbc.py", line 412, in
> do_executemany
> super(MSDialect_pyodbc, self).do_executemany(
> File "/opt/python/sqlalchemy/engine/default.py", line 587, in do_executemany
> cursor.executemany(statement, parameters)
> sqlalchemy.exc.IntegrityError: (pyodbc.IntegrityError) ('23000',
> "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot
> insert the value NULL into column 'componentEnumID', table
> 'DEC_CORE.dbo.Component'; column does not allow nulls. INSERT fails.
> (515) (SQLExecDirectW)")
> [SQL: INSERT INTO [Component] ([appCode]) VALUES (?)]
> [parameters: (('VCP00001',), ('VCP00001',))]
>
> On Wed, Dec 16, 2020 at 4:15 PM Mike Bayer <mike...@zzzcomputing.com> wrote:
> >
> > since these two columns have a ForeignKey on them, neither should default
> > to using autoincrement behavior within the DDL phase.
> >
> > the error you are getting indicates that when you created a Component()
> > object, one or both of these columns was not provided with an explicit
> > value to be INSERTed. as for why it's "ignoring the values you
> > provide" that can't be answered without a specific reproduction case.
> >
> > if the column does not have autoincrement, an explicit value must be
> > provided to the object, *or* since these are foreign key columns, it
> > implies you probably want to have the relationship() construct on other
> > classes reponsible for populating these columns. it is also possible
> > that if you do have relationship() constructs that refer to populating
> > these columns, they might be stepping over your manually-provided values
> > (although this is not what the error looks like), but again would need to
> > see a complete example to know if something like that is happening here.
> >
> >
> >
> > On Wed, Dec 16, 2020, at 4:04 PM, Larry Martell wrote:
> >
> > Is there any way to have a Non auto increment integer primary key? I
> > have this model:
> >
> > class Component(Base):
> > __tablename__ = 'Component'
> >
> > appCode = Column(ForeignKey('Application.appCode'),
> > primary_key=True, nullable=False)
> > componentEnumID = Column(ForeignKey('Enumeration.enumID'),
> > primary_key=True, nullable=False, autoincrement=False)
> >
> > And when I try and insert into it I get this warning:
> >
> > Column 'Component.componentEnumID' is marked as a member of the
> > primary key for table 'Component', but has no Python-side or
> > server-side default generator indicated, nor does it indicate
> > 'autoincrement=True' or 'nullable=True', and no explicit value is
> > passed. Primary key columns typically may not store NULL. Note that as
> > of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly
> > for composite (e.g. multicolumn) primary keys if
> > AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the
> > columns in the primary key.
> >
> > And it ignores the values I provide for componentEnumID and generates
> > SQL without that column, which then fails with:
> >
> > Cannot insert the value NULL into column 'componentEnumID', table
> > 'DEC_CORE.dbo.Component'; column does not allow nulls. INSERT fails.
> >
> > How to get around this issue?
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CACwCsY4s4C_SNDNHLWSDZxB%3DGTSL5q3UTuooAuxR68NshqRq%3DA%40mail.gmail.com.
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/dbe1586d-c6d9-45d9-a867-d7b747362387%40www.fastmail.com.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CACwCsY4DfQs382e4OUAb78_8ZVcxZDhYMGXfs5dUzF7F_tvmgQ%40mail.gmail.com.
