Insert data from pandas to oracle sql developer table-CodePudding

I receive some data in excel files and I would like to process it in pandas in order to have the proper format and then insert it into a table in sql developer.

I found several tutorials in the internet and I tried the following code but I can't understand why is not working.

import cx_Oracle
import datetime as dt
import pandas as pd

# connection string in the format
# <username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName>
connStr = '<username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName>'

# initialize the connection object
conn = None
try:
    # create a connection object
    conn = cx_Oracle.connect(connStr)

    # get a cursor object from the connection
    cur = conn.cursor()


    # read dataframe from excel
    df = pd.read_excel('C:...', sheet_name='...', header=11)

    
    # reorder the columns as per the requirement
    df.columns = ["COL_1","COL_2","COL_3","COL_4"]
    
    # prepare data insertion rows from dataframe
    dataInsertionTuples = [tuple(x) for x in df.values]

    # create sql for data insertion
    sqlTxt = 'INSERT INTO MYTABLE\
                (COL_1, COL_2, COL_3, COL_4)\
                VALUES (:1, :2, :3, :4)'
    # execute the sql to perform data extraction
    cur.executemany(sqlTxt, dataInsertionTuples)

    rowCount = cur.rowcount
    print("number of inserted rows =", rowCount)

    # commit the changes
    conn.commit()

except Exception as err:
    print('Error while inserting rows into db')
    print(err)
finally:
    if(conn):
        # close the cursor object to avoid memory leaks
        cur.close()

        # close the connection object also
        conn.close()
print("number of inserted rows =", rowCount)

The error that I get is expecting Number Does anyone understand what I am doing wrong. Here i use the username and pass that I have in the DB then the "HOST", "PORT" and "Service Name" from the tns file. connStr = '/@:/'

Also i am using a VPN to connect to my db, i don't know if that affects. But anyway while I am executing this script I am connected to the VPN.

CodePudding user response：

You already have a dataframe. Use df.to_sql(). https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

During debugging, consider creating a new table with that call, one which you're prepared to DROP between runs.

Consider paring your large df down to one that has just a couple of columns while you are testing.

Pay attention to the if_exists parameter.

When you're ready to insert "for real" into an existing table with a given schema, pay close attention to df.dtypes. Your goal is to have those types match up with what the RDBMS is expecting.

Missing values will sometimes e.g. promote an integer column to float, so NaN can represent a NULL. Recent versions of pandas offer better control about how missing integer values can be represented.

Unrecognized column types will show up as "object", essentially "str". You can coerce them to other types as needed, perhaps after filtering out bad values.

A common need is to turn text into date values:

df['created'] = pd.to_datetime(df.created)

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html

CodePudding user response：

from sqlalchemy.engine import create_engine

DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'USERNAME ' #enter your username
PASSWORD = 'PASSWORD ' #enter your password
HOST = 'HOST ' #enter the oracle db host url
PORT = PORT # enter the oracle port number
SERVICE = 'SERVICE_NAME' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT   ' '   SQL_DRIVER   '://'   USERNAME   ':'   PASSWORD  '@'   HOST   ':'   str(PORT)   '/?service_name='   SERVICE

engine = create_engine(ENGINE_PATH_WIN_AUTH)


#test query
df.to_sql('MY_TABLE',engine,if_exists='append',index=False)

This was the solution, as I found I should use the sqlalchemy directly instead the cx_Oracle since sqlalchemy include the cx_Oracle.

Anyway thanks.