如何使用to_sql将pandas数据框写入oracle数据库? [英] How to write pandas dataframe to oracle database using to_sql?

问题描述

我是一名新的oracle学习者.我正在尝试将pandas数据框写入到oracle表中.在网上进行研究之后，我发现代码本身非常简单，但是我不知道为什么我的代码不起作用.

I'm a new oracle learner. I'm trying to write a pandas dataframe into an oracle table. After I have made research online, I found the code itself is very simple, but I don't know why my code doesn't work.

我已经从本地文件中读取了熊猫数据框:

I have read the pandas dataframe from my local file:

import cx_Oracle
import pandas as pd
import os

dir_path = os.path.dirname(os.path.realpath("__file__"))
df = pd.read_csv(dir_path+"/sample.csv")

现在打印df，数据框df的显示如下:

Now print df, the dataframe df shold be like this:

   DATE            YEAR     MONTH      SOURCE      DESTINATION
0  11/1/2017 1:00  2017     1          AL          CO  
1  11/2/2017 1:00  2017     5          GA          ID  
2  11/3/2017 1:00  2017     12         GA          MO    

然后我使用cx_Oracle创建与数据库的连接，它可以工作.接下来，我尝试将数据帧df写入表TEST中.该表TEST是一个空表，已经存在于oracle数据库中，它在oracle中具有DATE，YEAR，MONTH，SOURCE，DESTINATION列.所有数据类型都与df样本数据匹配.我的代码如下:

Then I create connection with the database by using cx_Oracle, it works. Next I try to write the dataframe df into the table TEST. This table TEST is an empty table which already exist in oracle database, it has columns including DATE, YEAR, MONTH, SOURCE, DESTINATION in oracle. All the datatype matches the df sample data. My code is as follows:

conn_str = u'account/password@host:1521/server'
conn = cx_Oracle.connect(conn_str)

# Write records stored in a DataFrame to a oracle database
df.to_sql('TEST', conn, if_exists='replace') # the error shows here

conn.close()

显示错误:

DatabaseError:在SQL'SELECT name from sqlite_master中执行失败 WHERE type ='table'AND name = ?;':ORA-01036:非法变量 名称/号码

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ORA-01036: illegal variable name/number

如何解决问题?非常感谢您的宝贵时间！

How to solve the problem? Thank you very much for your time!

推荐答案

我在SO上也遇到过类似的问题-当您尝试使用cx_Oracle创建的连接对象写入Oracle DB时，就会发生这种情况.

I've seen similar questions on SO - it happens when you try to write to Oracle DB using connection object created by cx_Oracle.

尝试使用SQL Alchemy创建连接:

Try to create connection using SQL Alchemy:

import cx_Oracle
from sqlalchemy import types, create_engine

conn = create_engine('oracle+cx_oracle://scott:tiger@host:1521/?service_name=hr')

df.to_sql('TEST', conn, if_exists='replace')

这篇关于如何使用to_sql将pandas数据框写入oracle数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！