Write Python dataframe to Oracle

Question

I have a dataframe testdata like this: Here are the variables' types in Python:

detectorid:int64
starttime:str
volume:float64
speed:float64
occupancy:float64

Now I want to creat a datatable in oracle and insert this dataframe into it, here is what I tried:

import pandas as pd
import cx_Oracle
host = "192.168.1.100"    
port = "1521"    
sid = "orcl"  
dsn = cx_Oracle.makedsn(host, port, sid)
conn = cx_Oracle.connect("scott", "tiger", dsn)
cursor = conn.cursor()

#creat datatable：
sql_creat = "create table portland(detectorid number(32), starttime varchar(32), volume number(32), speed number(32), occupancy number(32))"
cursor.execute(sql_creat)

query = "insert into portland (detectorid,starttime,volume,speed,occupancy) VALUES (%d,'%s',%f,%f,%f)"
#insert by rows：
for i in range(len(testdata)):
        detectorid= testdata.ix[i,0]
        starttime= testdata.ix[i,1]
        volume= testdata.ix[i,2]
        speed= testdata.ix[i,3]
        occupancy= testdata.ix[i,4]
        cursor.execute(query % (detectorid,starttime,volume,speed,occupancy))

conn.commit()
cursor.close()
conn.close()

However it gives me DatabaseError: ORA-00984:column not allowed here. I think there are something wrong about the columns' types in my sql statement but I don't know how to solve it. Could somebody give me some instructions? Thank you for your attention!

The datatype NUMBER doesn't accept NaN, you might have to look into the datatype BINARY_DOUBLE, see this answer for instance. — wolφi
– wolφi, Commented Jun 8, 2018 at 7:32
And it's better (much better) to store dates as datatype DATE or, if you have timezones, TIMESTAMP. — wolφi
– wolφi, Commented Jun 8, 2018 at 7:33
Hi @wolφi. I changed all number(32) into BINARY_DOUBLE and VARCHAR(32) into DATE but still get the same error. Is my query = code correct? — YQ.Wang
– YQ.Wang, Commented Jun 8, 2018 at 9:26
Sorry, don't know as I'm new to python. I tried your example without testdata but simple variables instead, and this worked perfectly. — wolφi
– wolφi, Commented Jun 8, 2018 at 9:49
@wolφi Could you be so kind to provide the code that you have tried? It may be helpful. — YQ.Wang
– YQ.Wang, Commented Jun 8, 2018 at 10:52

wolφi · Accepted Answer · 2018-06-08 11:15:27Z

1

#!/usr/local/bin/python3

import cx_Oracle
import os

conn = cx_Oracle.connect("user", "xxx", "localhost:1512/ORCLPDB1", encoding="UTF-8")
cursor = conn.cursor()

#creat datatable：
sql_creat = "create table portland(detectorid number(32), starttime varchar(32), volume number(32), speed number(32), occupancy number(32))"
#cursor.execute(sql_creat)

query = "insert into portland (detectorid,starttime,volume,speed,occupancy) VALUES (%d,'%s',%f,%f,%f)"
detectorid = 1345
starttime = '2011-09-15 00:00:00'
volume = 0
speed = 0
occupancy= 0
cursor.execute(query % (detectorid,starttime,volume,speed,occupancy))

conn.commit()
cursor.close()
conn.close()

answered Jun 8, 2018 at 11:15

wolφi

8,3814 gold badges42 silver badges71 bronze badges

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

2 Comments

YQ.Wang Over a year ago

Since your code run perfectly, I think my problem is about the nan.

Christopher Jones Over a year ago

This code is open to security and scalability issues. It should be converted to use bind variables instead of the %d etc format specifiers.

Collectives™ on Stack Overflow

Write Python dataframe to Oracle

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related