Loading DataFrame from Pandas to SQL for ORACLE Yields and ERROR - ORA-00911: Invalid Character

Question

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

Here is the error:

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:[email protected]: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:[email protected]: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')

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 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

You should provide the engine itself to to_sql, and not the raw connection? (a connection is only supported for sqlite, and not for other database flavors) And a second question: what version of pandas do you have? — joris
– joris, Commented Feb 24, 2015 at 15:19
Hey there. Thanks for the tip, I'll give it a try. I am using 15.2. — Joseph Portello
– Joseph Portello, Commented Feb 24, 2015 at 15:23
@joris, I tried your suggestion and added the response I got from to the question above. One more thing; when using read_sql I do present the raw connection as - xx = pd.read_sql('SELECT * FROM SCHEMA.TABLE', cnx) and that works just fine. — Joseph Portello
– Joseph Portello, Commented Feb 24, 2015 at 15:38
Looking at the error traceback, I don't think you are using pandas 0.15.2. Can you show the output of pd.__version__? (maybe it is picking up an older system pandas?) — joris
– joris, Commented Feb 24, 2015 at 15:43
@joris, you were right. I was on 13.1 (installed via repo, not pip) So updated it. I have added the new and updated error above. — Joseph Portello
– Joseph Portello, Commented Feb 24, 2015 at 15:53

joris · Accepted Answer · 2015-02-24 16:30:59Z

0

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).

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

answered Feb 24, 2015 at 16:30

joris

140k37 gold badges258 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Loading DataFrame from Pandas to SQL for ORACLE Yields and ERROR - ORA-00911: Invalid Character

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related