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 <[email protected]> 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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/dbe1586d-c6d9-45d9-a867-d7b747362387%40www.fastmail.com.
