SQLAlchemy session.add()-CodePudding

I am trying to add new rows inside new database. Here is my class:

Base = declarative_base()


class project_properties(Base):
    __tablename__ = 'project_properties'
    # __table_args__ = {'schema': 'KPI_Coverage'}

    ID = Column(Integer, primary_key=True)
    name = Column(String(4000))

    def __repr__(self):
        return f'ID: {self.ID}\tname: {self.name}'

Here is my Table function:

def insert_project(session: Session, project: project_properties) -> tuple[int, int] | tuple[int, IntegrityError] | tuple[int, DataError]:
    try:
        if len(project.name) == 0:
            raise DataError

        session.add(project)
        session.commit()

        return 0, 0

    except IntegrityError as err:
        session.rollback()
        return -1, err
    except DataError as err:
        session.rollback()
        return -1, err


if __name__ == '__main__':
    engine = create_engine('mssql pyodbc://user:pw@localhost\\server/master?driver=ODBC Driver 17 for SQL Server')
    # engine.connect() <- dunno if I should use this or not
    session_maker = sessionmaker()
    session_maker.configure(bind=engine)
    tmp_session = session_maker()

    connection = connect('\\\\some_network_drive\\04_Q_Metrics\\KPI_Database\\AllDataCoverage.db')
    cursor = connection.cursor() <- sqlite3 migration to mssql

    for item in cursor.execute('SELECT * FROM Project').fetchall():
        print(item[1]) <- this returns simple string
        tmp_project = project_properties(name=item[1])
        insert_project(tmp_session, tmp_project)
        print(tmp_project)

Here is my exception which is really confusing since there are no other transactions and SQLAlchemy itself is an ORM:

IntegrityError('(pyodbc.IntegrityError) (\'23000\', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert the value NULL into column \'ID\', table \'master.dbo.project_properties\'; column does not allow nulls. INSERT fails. (515) (SQLExecDirectW); [23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated. (3621)")'

Without implemented exception I get this error:

Traceback (most recent call last):
  File "D:\GitHub\SA_KPIs\20_Implementation\myenv\lib\code.py", line 90, in runcode
    exec(code, self.locals)
  File "<input>", line 1, in <module>
  File "C:\Program Files\JetBrains\Pro_PyCharm 2022.2.3\plugins\python\helpers\pydev\_pydev_bundle\pydev_umd.py", line 198, in runfile
    pydev_imports.execfile(filename, global_vars, local_vars)  # execute the script
  File "C:\Program Files\JetBrains\Pro_PyCharm 2022.2.3\plugins\python\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents "\n", file, 'exec'), glob, loc)
  File "D:\GitHub\SA_KPIs\20_Implementation\KPI_Coverage\bin\orm\tableActive\project_propertiesTable.py", line 56, in <module>
    print(insert_project(tmp_session, tmp_project))
  File "D:\GitHub\SA_KPIs\20_Implementation\KPI_Coverage\bin\orm\tableActive\project_propertiesTable.py", line 29, in insert_project
    session.commit()
  File "D:\GitHub\SA_KPIs\20_Implementation\myenv\lib\site-packages\sqlalchemy\orm\session.py", line 1451, in commit
    self._transaction.commit(_to_root=self.future)
  File "D:\GitHub\SA_KPIs\20_Implementation\myenv\lib\site-packages\sqlalchemy\orm\session.py", line 827, in commit
    self._assert_active(prepared_ok=True)
  File "D:\GitHub\SA_KPIs\20_Implementation\myenv\lib\site-packages\sqlalchemy\orm\session.py", line 601, in _assert_active
    raise sa_exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (pyodbc.IntegrityError) ('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert the value NULL into column 'ID', table 'master.dbo.project_properties'; column does not allow nulls. INSERT fails. (515) (SQLExecDirectW); [23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated. (3621)")
[SQL: INSERT INTO project_properties (name) OUTPUT inserted.[ID] VALUES (?)]
[parameters: ('/Sensors/DHSGen3_E5_NFC',)]
(Background on this error at: https://sqlalche.me/e/14/gkpj) (Background on this error at: https://sqlalche.me/e/14/7s2a)

Ofcourse I took a look on those pages and other discussions but I did not find any leads.

CodePudding user response：

Probably check your strings and command executions and re-iterate it. That might help more, than trying to search for such specific use case on the internet.

Also you might be closing the connection, but if you don't, consider doing so. :)

CodePudding user response：

I am hell'a stupid.

Solution is that you SHOULD NEVER FORGET to CHECK if you have AUTOINCREMENT or IDENTITY set for certain COLUMNS, most likely for primary keys!!!