Non auto increment integer primary key

[Larry Martell]

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?

[Mike Bayer]

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.

-- 

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CACwCsY5rmMQaTmHBeFsuOcum1wT7-mG1UOAm%2BJQhDVaqyqsCwA%40mail.gmail.com.

[Larry Martell]

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',))]

[Mike Bayer]

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

-- 

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CACwCsY4s4C_SNDNHLWSDZxB%3DGTSL5q3UTuooAuxR68NshqRq%3DA%40mail.gmail.com.

[Larry Martell]

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.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dbe1586d-c6d9-45d9-a867-d7b747362387%40www.fastmail.com.