pandas 如何使用to_sql将pandas数据帧写入oracle数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47540837/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write pandas dataframe to oracle database using to_sql?
提问by Haven Shi
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.
我是一个新的 oracle 学习者。我正在尝试将 Pandas 数据框写入 oracle 表中。上网查了一下，发现代码本身很简单，但是不知道为什么我的代码不行。
I have read the pandas dataframe from my local file:
我已经从本地文件中读取了 Pandas 数据框：
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")
Now print df, the dataframe df shold be like this:
现在打印df，数据帧df应该是这样的：
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
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:
然后我使用 cx_Oracle 创建与数据库的连接，它工作正常。接下来，我尝试将数据帧 df 写入表 TEST。这个表TEST是oracle数据库中已经存在的一个空表，在oracle中有DATE、YEAR、MONTH、SOURCE、DESTINATION等列。所有数据类型都与 df 示例数据相匹配。我的代码如下：
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()
It shows error:
它显示错误：
DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ORA-01036: illegal variable name/number
DatabaseError: sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;' 上的执行失败：ORA-01036: 非法变量名/编号
How to solve the problem? Thank you very much for your time!
如何解决问题？非常感谢您的宝贵时间！
回答by MaxU
I've seen similar questions on SO - it happens when you try to write to Oracle DB using connection object created by cx_Oracle.
我在 SO 上看到过类似的问题 - 当您尝试使用由cx_Oracle.
Try to create connection using SQL Alchemy:
尝试使用 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')
