SQLALCHEMY SQL炼金术.exc.数据库错误:(cx_Oracle.数据库错误)ORA-32795:无法插入到生成的始终标识列

inn6fuwd  于 2022-12-18  发布在  Oracle
关注(0)|答案(1)|浏览(301)

我创建了以下Oracle表:

CREATE TABLE "OHM"."TRANSACTION_MASTER" (
    "TRANS_TYPE" CHAR(1 BYTE), 
    "INVOICE_NO" VARCHAR2(20 BYTE), 
    "INVOICE_DATE" DATE, 
    "VENDOR" VARCHAR2(100 BYTE), 
    "VENDOR_ADDRESS" VARCHAR2(250 BYTE), 
    "TRANS_CATEGORY" VARCHAR2(20 BYTE), 
    "TRANS_AMOUNT" NUMBER, 
    "TRANS_DESC" VARCHAR2(250 BYTE), 
    "PAYMENT_MODE" VARCHAR2(20 BYTE), 
    "PAYMENT_DESC" VARCHAR2(250 BYTE), 
    "TRANS_NO" NUMBER  GENERATED ALWAYS AS IDENTITY (
        START WITH 1000000000 INCREMENT BY 1 NOCYCLE
    ), 
    "TRANS_AMOUNT_WORDS" VARCHAR2(500 BYTE), 
    "VENDOR_EMAIL" VARCHAR2(50 BYTE), 
    CONSTRAINT "TRANSACTION_MASTER_PK" PRIMARY KEY ("TRANS_NO")`

以及

CREATE TABLE "OHM"."TRANSACTION_DETAILS" (
    "TRANS_NO" NUMBER NOT NULL ENABLE, 
    "ITEM_CODE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "ITEM_NAME" VARCHAR2(250 BYTE), 
    "ITEM_CATEGOTY" VARCHAR2(250 BYTE), 
    "ITEM_UNIT_PRICE" NUMBER, 
    "ITEM_QUANTITY" NUMBER, 
    "ITEM_QUANTITY_MEASURE" VARCHAR2(20 BYTE), 
    "ITEM_NET_AMOUNT" NUMBER, 
    "ITEM_DISCOUNT_VALUE" NUMBER, 
    "ITEM_TOTAL_AMOUNT" VARCHAR2(20 BYTE), 
        CONSTRAINT "TRANSACTION_DETAILS_PK" PRIMARY KEY ("TRANS_NO", "ITEM_CODE")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    TABLESPACE "USERS"  ENABLE, 
        CONSTRAINT "TRANSACTION_DETAILS_FK1" FOREIGN KEY ("TRANS_NO")
        REFERENCES "OHM"."TRANSACTION_MASTER" ("TRANS_NO") ON DELETE CASCADE ENABLE
    )

我有下面的Python代码:

from sqlalchemy import Column, String, Integer, Date,Float,ForeignKey,Identity
from sqlalchemy.orm import relationship
from base import Base

class TRANSACTION_MASTER(Base):
    __tablename__ = 'TRANSACTION_MASTER'
    trans_no =Column( Integer,Identity(start=1000000000, cycle=True),primary_key=True,autoincrement=True)
    trans_type = Column(String(1))
    invoice_no = Column(String(20))
    invoice_date =Column(Date) 
    vendor = Column(String(100))
    vendor_address = Column(String(250))
    trans_category = Column(String(20))
    trans_amount =Column(Float)
    trans_desc = Column(String(250))
    payment_mode = Column(String(20))
    payment_desc = Column(String(250))
    items = relationship('TRANSACTION_DETAILS', back_populates = "trans")
    def __init__(self, trans_no,trans_type,invoice_no,invoice_date,vendor,vendor_address,trans_category,trans_amount,trans_desc,payment_mode,payment_desc): #CHANGE THIS
        self.trans_no=trans_no
        self.trans_type=trans_type
        self.invoice_no=invoice_no
        self.invoice_date=invoice_date
        self.vendor=vendor
        self.vendor_address=vendor_address
        self.trans_category=trans_category
        self.trans_amount=trans_amount
        self.trans_desc=trans_desc
        self.payment_mode=payment_mode
        self.payment_desc=payment_desc

class TRANSACTION_DETAILS(Base):
    __tablename__ = 'TRANSACTION_DETAILS'
    trans_no =Column( Integer,ForeignKey('TRANSACTION_MASTER.trans_no'),primary_key=True )
    item_code = Column(String(20),primary_key=True)
    item_name  = Column(String(250))
    item_categoty  = Column(String(250))
    item_unit_price =Column(Float)
    item_quantity =Column(Float)
    item_quantity_measure  = Column(String(20))
    item_net_amount =Column(Float)
    item_discount_value =Column(Float)
    item_total_amount  = Column(String(20))
    trans = relationship('TRANSACTION_MASTER', back_populates = "items")
    def __init__(self,trans_no,item_code,item_name,item_categoty,item_unit_price,item_quantity,item_quantity_measure,item_net_amount,item_discount_value,item_total_amount):
            self.trans_no=trans_no
            self.item_code=item_code
            self.item_name=item_name
            self.item_categoty=item_categoty
            self.item_unit_price=item_unit_price
            self.item_quantity=item_quantity
            self.item_quantity_measure=item_quantity_measure
            self.item_net_amount=item_net_amount
            self.item_discount_value=item_discount_value
            self.item_total_amount=item_total_amount


我尝试通过以下代码将数据插入transaction_master表:

from datetime import date
from base import Session, engine, Base
from Transaction_Master import TRANSACTION_MASTER
from Transaction_Master import TRANSACTION_DETAILS    
    
session = Session()
    
Tm = TRANSACTION_MASTER('','C','ENTHE','10-JAN-2022','MANJUSHA','ADDRES','GROCERY',123.00,'DESC','CC','BII')
        
# 9 - persists data
session.add(Tm)
        
# 10 - commit and close session
session.commit()
session.close()

它抛出错误:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-32795: cannot insert into a generated always identity column
[SQL: INSERT INTO "TRANSACTION_MASTER" (trans_no, trans_type, invoice_no, invoice_date, vendor, vendor_address, trans_category, trans_amount, trans_desc, payment_mode, payment_desc) VALUES (:trans_no, :trans_type, :invoice_no, :invoice_date, :vendor, :vendor_address, :trans_category, :trans_amount, :trans_desc, :payment_mode, :payment_desc)]
[parameters: {'trans_no': '', 'trans_type': 'C', 'invoice_no': 'ENTHE', 'invoice_date': '10-JAN-2022', 'vendor': 'MANJUSHA', 'vendor_address': 'ADDRES', 'trans_category': 'GROCERY', 'trans_amount': 123.0, 'trans_desc': 'DESC', 'payment_mode': 'CC', 'payment_desc': 'BII'}]

请帮助我解决此错误。我知道我不需要为标识列插入传递此trans_no字段。add函数默认情况下会考虑所有字段。如何绕过此问题?是否应该为查询和更新目的定义表之间的此关系?如何解决插入问题?

mgdq6dx1

mgdq6dx11#

我不知道Python,但是--尽管如此--让我试试。
正如您自己已经得出的结论,您不应该尝试为始终自动生成值的标识列提供值。
更糟糕的是,您使用了相同的TRANS_NO作为TRANSACTION_DETAILS表中的外键列。这意味着您必须在插入明细行之前知道它的值。如果 masterTRANS_NO是自动生成的,你必须返回它的值(转换为局部变量-从Oracle的Angular 讲),以便您可以在insert into transaction_details语句中使用它(局部变量)。
Oracle提供了returning子句(参见here的例子),但我不知道Python中是否有类似的东西。如果没有,那么考虑而不是来使用标识列,而是自己提供主键(以及随后的外键)值。如何?创建一个序列(在Oracle中)并获取其值-这是您要使用的sequence.nextval

相关问题