我正在尝试在新的database内add新行。这是我的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}'
下面是我的Table函数:
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)
这是我的exception，这是真正令人困惑的，因为没有其他事务和SQLAlchemy本身是一个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)")'
没有实现exception我得到这个错误:
Traceback (most recent call last):
File "D:GitHubSA_KPIs20_Implementationmyenvlibcode.py", line 90, in runcode
exec(code, self.locals)
File "<input>", line 1, in <module>
File "C:Program FilesJetBrainsPro_PyCharm 2022.2.3pluginspythonhelperspydev_pydev_bundlepydev_umd.py", line 198, in runfile
pydev_imports.execfile(filename, global_vars, local_vars) # execute the script
File "C:Program FilesJetBrainsPro_PyCharm 2022.2.3pluginspythonhelperspydev_pydev_imps_pydev_execfile.py", line 18, in execfile
exec(compile(contents+"n", file, 'exec'), glob, loc)
File "D:GitHubSA_KPIs20_ImplementationKPI_CoveragebinormtableActiveproject_propertiesTable.py", line 56, in <module>
print(insert_project(tmp_session, tmp_project))
File "D:GitHubSA_KPIs20_ImplementationKPI_CoveragebinormtableActiveproject_propertiesTable.py", line 29, in insert_project
session.commit()
File "D:GitHubSA_KPIs20_Implementationmyenvlibsite-packagessqlalchemyormsession.py", line 1451, in commit
self._transaction.commit(_to_root=self.future)
File "D:GitHubSA_KPIs20_Implementationmyenvlibsite-packagessqlalchemyormsession.py", line 827, in commit
self._assert_active(prepared_ok=True)
File "D:GitHubSA_KPIs20_Implementationmyenvlibsite-packagessqlalchemyormsession.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)
当然，我看了那些页面和其他讨论，但我没有找到任何线索。
可能检查您的字符串和命令执行并重新迭代它。这可能比在互联网上搜索这些特定的用例更有帮助。
你也可能关闭连接，但如果你不这样做，考虑这样做。:)
我真笨。
的解决方案是你的永远不要忘记检查如果你为COLUMNS设置了AUTOINCREMENT或IDENTITY，很可能是primary keys!!