pyodbc not enough values\n (0)-CodePudding

cursor.execute('''CREATE TABLE PEDIDO(
        CPEDIDO INT GENERATED AS IDENTITY PRIMARY KEY,
        CCLIENTE INT NOT NULL,
        FECHA DATE NOT NULL
        )''')
    valores=[]
    for i in range(10):
        print(i)
        x=datetime.date(year=2022,month=11,day=i 1)
        valores.append((i,x)) 

cursor.executemany("INSERT INTO PEDIDO VALUES(?,?);", valores) #doesn't work writing [valores] instead of valores

That results in:

pyodbc.Error: ('HY000', '[HY000] [Devart][ODBC][Oracle]ORA-00947: not enough values\n (0) (SQLExecDirectW)') #when inserting the instances

I have tried to save data in two different tuples: cclients = (...) and dates=(...) and then write:

cursor.executemany("INSERT INTO PEDIDO VALUES(?,?);", [cclients, dates]).

But doesn't work

CodePudding user response：

Name the columns you are inserting into (and you may not need/require including the statement terminator ; in the query):

INSERT INTO PEDIDO (CCLIENTE, FECHA) VALUES (?,?)

If you do not then Oracle will expect you to provide a value for every column in the table (including CPEDIDO).

CodePudding user response：

You created a table with three columns, but you only provide two values in your SQL: INSERT INTO PEDIDO VALUES(?,?). The column CPEDIDO is defined as GENERATED AS IDENTITY. This means that you can provide a value for this column, but you don't have to. But if you leave out this column your SQL statement has to be adjusted.

INSERT INTO PEDIDO (CCLIENTE, FECHA) VALUES(?,?)