我如何将数据帧插入到Oracle表非法/可变Oracle [英] How do I insert a dataframe to an oracle table illegal/variable oracle

问题描述

我有以下数据框

df2 = {names: [PEPE, LUIS], id: [1,2], stages: [0,1], ord: [3, 2]}

但是这些是必填字段，要插入表格，您还有更多字段允许空值.

but these are the required fields, the table to insert, you have more fields, which allow nulls.

数据框等于表中这些字段的位置

And where the dataframe is equivalent to these fields in the table

df2 = {labels= :1, id= :2, stages= :3, ord= :4}

桌子

CREATE TABLE customer_prf
(
  names      VARCHAR2(80) NOT NULL, 
  label      VARCHAR2(80) NOT NULL,
  type       VARCHAR2(80) NOT NULL,
  type_flag  INT,
  type_flag2 INT,
  conc       VARCHAR2(80), 
  id         INT NOT NULL,
  n_stage    INT NOT NULL,
  ctry       INT NOT NULL,
  "order"    INT NOT NULL
);

您是如何从具有查询字符串中预先定义的值的insert数据中插入数据的，我做错了，ora-01036发送给我的.此外， id 值本身是否会自动递增?

How did you manage to insert the data from the data with the insert with values ​​already previously defined in the query string, which I am doing wrong, which ora-01036 sends me. Besides, does the id value auto increment itself?

我的代码

import cx_Oracle
import pandas as pd

...
df2.to_dict(orient='records')
print(df2)

conn = cx_Oracle.connect(connection)
cur = conn.cursor()
id = 0
insert_qry = cur.execute("insert into customer_prf values(
'references',
:1,
'text',
'',
'',
:2,
:3,
2,
:4)"
   cursor.prepare(insert)

您将如何插入

推荐答案

您可以使用 df2.values.tolist 来获取参数列表，并使用以下代码来插入数据框中的值:

You can use df2.values.tolist in order to get a parameter list, and such a code as below in order to insert the values within the dataframe :

import pandas as pd
import cx_Oracle
conn = cx_Oracle.connect('un/pwd@host:port/dbname')

try:
    cursor = conn.cursor()
    df2 = pd.DataFrame({'names':['Pepe','Luis'], 'stages': [0,1], 'order': [3, 2]}) 
    col_list = df2.values.tolist()

    cursor.prepare("INSERT INTO customer_prf(names,label,type,id,n_stage,ctry,\"order\") VALUES(:1,'references','text',seq_customer.nextval,:2,2,:3)")
    cursor.executemany(None,col_list)
    print("executed!")
except Exception as err:
    print('Error', err)
else:
    conn.commit()

其中

• 更喜欢使用 executemany 而不是 execute 表演者

创建一个序列( seq_customer )并将其添加到您的值列表中( seq_customer.nextval )，如使用数据库版本11g，如果您的数据库版本为12c +，则可以在create table ddl语句中标识ID列(例如，在创建表期间)，例如

create a sequence (seq_customer) and add to your values list (seq_customer.nextval) as using DB version 11g, if your database was of version 12c+, then the ID column could be identified within the create table ddl statement(eg. during table creation) such as

ID INT始终作为身份主键生成

这篇关于我如何将数据帧插入到Oracle表非法/可变Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！