How to write pandas dataframe to oracle database using to_sql?

Question

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

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

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

It shows error:

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!

MaxU - stand with Ukraine · Accepted Answer · 2017-11-28 21:09:34Z

18

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

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

answered Nov 28, 2017 at 21:09

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

8 Comments

Haven Shi Over a year ago

Thank you for you reply. I tried, then it shows DatabaseError: (cx_Oracle.DatabaseError) ORA-01950: no privileges on tablespace 'xxx_DATA' [SQL: 'INSERT INTO 'TEST' ("index", "DATE", "YEAR", "MONTH",...) VALUES (:"index", :"DATE", :YEAR,...)]

MaxU - stand with Ukraine Over a year ago

@HavenShi, this is a different story. That means you (or your DBA) have to add quota on that tablespace for that oracle user. I think your original question has been answered ;-)

Haven Shi Over a year ago

,can you explain a little more about tablespace? I can write directly into the oracle table TEST, what do I miss?

Superdooperhero Over a year ago

Looks like it needed the table name in lowercase and it does not like it if the table exists. Creates everything as CLOBs which is quite annoying.

MaxU - stand with Ukraine Over a year ago

@Superdooperhero, you might want to check this answer ;)

|

Hussain Bohra · Accepted Answer · 2021-02-23 21:00:26Z

I am able to load an Oracle table using the following code:

import pandas as pd
import os

creds = {}
creds['tns_admin'] = 'Wallet_Path'
creds['sid'] = 'dev_low'
creds['user'] = 'username'
creds['password'] = pwd

os.environ['TNS_ADMIN'] = creds['tns_admin']


uri = 'oracle+cx_oracle://' + creds['user'] + ':' + creds['password'] + '@' + creds['sid']
df = pd.read_csv("test.csv")
df.to_sql('test', uri, schema='PRD', if_exists='replace')

Instead of connection we need to build and pass an URI.

Note: New Oracle databases (Autonomous) requires a wallet, so we need to set wallet path in TNS_ADMIN environment variable.

Also, I didn't have to import cx_Oracle, I did double check that

To ensure, I am not getting fooled, I dropped the table and did commit

And I executed above code, it created new table with data.

Ransaka Ravihara · Accepted Answer · 2022-01-25 07:01:50Z

2

After referring to this solution, I was able to get this done using the following steps.

from sqlalchemy.engine import create_engine

DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'your_username' 
PASSWORD = 'your_password'
HOST = 'subdomain.domain.tld' 
PORT = 1521 
SERVICE = 'your_oracle_service_name'
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE

engine = create_engine(ENGINE_PATH_WIN_AUTH)

After successfully creating the SQLAlchemy engine you can pass it to pandas to_sql() function.

df.to_sql('name_of_sql_table',engine,schema='your_schema')

answered Jan 25, 2022 at 7:01

Ransaka Ravihara

2,0142 gold badges16 silver badges30 bronze badges

Comments

Conrad Eggan · Accepted Answer · 2025-01-27 21:10:23Z

1

Here's a current example using Pandas, SQL Alchemy 2.0.37 and oracledb which replaces cx_oracle. This example appends data to an existing Oracle EBS (E-Business Suite) import view for quality records. Writing to a table will work the same way.

from sqlalchemy.engine import create_engine
import pandas as pd

username = "your_username"
password = "your_password"
host = "oracle-ebs.anycompany.com"
port = "1521"
service_name = "ebsdev"

dsn = f"oracle+oracledb://{username}:{password}@{host}:{port}/?service_name={service_name}"
engine = create_engine(dsn, thick_mode={})

df = pd.DataFrame(
    {
        "TRANSACTION_INTERFACE_ID": [1198,1199],
        "PROCESS_STATUS": [1,1],
        "ORGANIZATION_CODE": ["888","888"],
        "PLAN_NAME": ["QA PLAN 1","QA PLAN 1"],
        "QALAB_SPM_L": [93.3,93.5],
        "QALAB_SPM_A": [-0.2,-0.1],
        "QALAB_SPM_B": [0.7,0.6],
    }
)
df.to_sql(
    schema="apps",
    name="q_qa_plan_1_iv",
    con=engine,
    if_exists="append", # Append to existing table
    index=False,        # Do not write row index
)

answered Jan 27 at 21:10

Conrad Eggan

531 silver badge7 bronze badges

1 Comment

Christopher Jones Jan 28 at 21:14

To pass other connection arguments to create_engine() (and also to avoid issues with passwords containing some special characters), see the SQLAlchemy doc Passing python-oracledb connect arguments.

Collectives™ on Stack Overflow

How to write pandas dataframe to oracle database using to_sql?

4 Answers 4

8 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related