将 pandas 的DataFrame加载到SQL以获取ORACLE产量和错误-ORA-00911:无效字符 [英] Loading DataFrame from Pandas to SQL for ORACLE Yields and ERROR - ORA-00911: Invalid Character

问题描述

我正在尝试将数据帧从Pandas加载到Oracle.我可以正常读取read_sql，但df.to_sql无法正常工作.

I am attempting to load a dataframe from Pandas to Oracle. I can read_sql just fine, but df.to_sql does not work.

这是错误:

Error on sql SELECT name FROM sqlite_master WHERE type='table' AND name='table_name_here'; Traceback (most recent call last): File "
<stdin>", line 1, in
  <module>
    File "/usr/lib/python2.7/dist-packages/pandas/core/frame.py", line 1261, in to_sql self, name, con, flavor=flavor, if_exists=if_exists, **kwargs) File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 207, in write_frame exists = table_exists(name,
    con, flavor) File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 275, in table_exists return len(tquery(query, con)) > 0 File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 90, in tquery cur = execute(sql, con, cur=cur) File
    "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 47, in execute cur.execute(sql) cx_Oracle.DatabaseError: ORA-00911: invalid character

我正在按照我的代码进行操作

I am doing this as for my code:

import pandas as pd
from sqlalchemy import create_engine
import cx_Oracle

engine = create_engine('oracle://name:[e-mail]:port/sid')
cnx = engine.raw_connection()

merged = pd.DataFrame.from_csv('name.csv', sep=',', index_col=False)
merged.to_sql('schema.table_name_here', cnx, if_exists='append')

我还尝试通过添加如下模式:

And I have also tried by adding the schema like this:

import pandas as pd
from sqlalchemy import create_engine
import cx_Oracle

engine = create_engine('oracle://name:[e-mail]:port/sid')
cnx = engine.raw_connection()

merged = pd.DataFrame.from_csv('name.csv', sep=',', index_col=False)
merged.to_sql('table_name_here', cnx, schema='name', if_exists='append')

每个Joris，我都尝试过:

Per Joris, I tried:

>>> merged.to_sql('name_of_table', engine, schema='XXCOST', if_exists='append')
Error on sql SELECT name FROM sqlite_master WHERE type='table' AND  name='name_of_table';
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/lib/python2.7/dist-packages/pandas/core/frame.py", line 1261,  in to_sql
self, name, con, flavor=flavor, if_exists=if_exists, **kwargs)
File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 207, in  write_frame
exists = table_exists(name, con, flavor)
File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 275, in table_exists
return len(tquery(query, con)) > 0
File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 90, in tquery
cur = execute(sql, con, cur=cur)
File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 53, in execute
con.rollback()
AttributeError: 'Engine' object has no attribute 'rollback'

Joris确定我的Pandas版本已经过时，为13.1，而不是15.2.我对其进行了更新，并在下面添加了新错误.

Joris determined my Pandas version was out of date at 13.1 and not 15.2. I updated it and have added the new error below.

>>> merged.to_sql('name_of_table', ora, schema='schema',     if_exists='append')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 966, in to_sql
dtype=dtype)
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 538, in to_sql
chunksize=chunksize, dtype=dtype)
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1504, in to_sql
table.create()
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 637, in create
if self.exists():
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 625, in exists
return self.pd_sql.has_table(self.name, self.schema)
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1514, in has_table
return len(self.execute(query).fetchall()) > 0
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1421, in execute
raise_with_traceback(ex)
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1410, in execute
cur.execute(*args)
pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM   sqlite_master WHERE type='table' AND name='name_of_table';':     ORA-00911: invalid character

推荐答案

您必须向to_sql提供sqlalchemy engine，而不是原始的connection.仅sqlite3数据库支持提供DBAPI连接(请参见 docstring ).

You have to provide the sqlalchemy engine to to_sql and not the raw connection. Supplying the DBAPI connection is only supported for sqlite3 databases (see docstring).

此外，此功能需要0.14或更高版本的熊猫版本(之前仅支持sqlite和mysql)

Further, a pandas version of 0.14 or larger is needed for this functionality (before, only sqlite and mysql were supported)

这篇关于将 pandas 的DataFrame加载到SQL以获取ORACLE产量和错误-ORA-00911:无效字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！