CODEWITHSUNDEEP
pythonoracle-databasedatetimetimestampcx-oracle
Umut TEKİN
Umut TEKİN

Reputation: 962

Python cx_Oracle Insert Timestamp With Milliseconds

I have read the posts: Insert TIMESTAMP with milliseconds in Oracle with cx_Oracle and Python 3.6 and sqlserver datetime to oracle timestamp problems , but none of them solved my problem.

Because, column number of my Oracle table is more than one.

I have tested both above links(thanks to them). Yet, their efficiency are limited. Such as, if the column order of the timestamp column on Oracle table is not 1 then setting cursor.setinputsizes(cx_Oracle.TIMESTAMP) does not work.

According to Panagiotis Kanavos(sqlserver datetime to oracle timestamp problems):

ts = datetime.datetime.now()
cursor.prepare("INSERT INTO python_tstamps VALUES(:t_val)")

cursor.setinputsizes(t_val=cx_Oracle.TIMESTAMP)

cursor.execute(None, {'t_val':ts})
db.commit()

On the other hand, I have multiple records to insert like ~250k. So, I need to fetch all my data and convert to them into dictionary, and finally append that dictionary object to a list in order to use input object in executemany function.

column_name = ["instance_id", "record_id", "record_id1"]

dict1 = dict(zip(column_name,outputsql[0]))

for key in dict1:
    print(key)

t = []
t.append(dict1)
cursororacle.prepare("INSERT /*+ append */INTO schematest.test  VALUES (:instance_id, :record_id, :record_id1)")
cursororacle.setinputsizes(instance_id=cx_Oracle.TIMESTAMP, record_id=cx_Oracle.TIMESTAMP)
cursororacle.executemany(None, t)

Conveting 250k records is not efficient. Is there any other solution/s?

Edit: I want to insert my output data(outputsql) without dictionary conversion. Is it possible; if it is then how?

Thanks in advance!

Ps: outputsql is output of another db session query and it returns records with correct milliseconds precision.

cursorsql = connsql.cursor()
cursorsql.execute('select top 1  record_id, instance_id, record_id from dbo.recordlog  (nolock)')
outputsql = cursorsql.fetchall()

Upvotes: 0

Views: 5009

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

There is no need to convert your data to dictionaries. The documentation for cursor.setinputsizes() shows that you can pass either positional arguments or keyword arguments. So, if you need to indicate that the third column is supposed to be a timestamp, you can do the following:

cursor.setinputsizes(None, None, cx_Oracle.TIMESTAMP)

What this does is says that the first two binding positions can use the default binding. This will be determined by examining the data that is supplied when cursor.execute() or cursor.executemany() is called. The third binding position will be bound as a timestamp column.

I hope this is clear enough!

Upvotes: 2

Related Questions