Daniel Plas Rivera Daniel Plas Rivera - 1 year ago 90
SQL Question

Send a list of lists including multiple timestamp queries using cx_oracle.execute_many

I'm trying to send multiple timestamp queries using cx_oracle from a large list. I've only seen an article sending one query per execution but I haven't seen any examples with executing multiple using executemany. How would I get a list of multiple timestamps to go through? I made the following below but no luck!

import datetime
import cx_Oracle

listrows =[

[56, 'steveJob', 'Apple', '2016-08-15 20:23:03.317909', '2015-09-08 20:46:30.456299', ''],
[32, 'markZuck', 'Faceb', '2015-09-08 20:46:30.456299', '2015-09-08 20:46:30.456299', ''],
[45, 'elonMusk', 'Tesla', '2016-02-18 16:53:20.959984', '2016-02-18 17:17:05.664715', '']


con = cx_Oracle.connect("system","oracle","localhost:1521/xe.oracle.docker")
cursor = con.cursor()
cursor.prepare("""INSERT INTO lifexp (age, name, lastcompany, created_at, deleted_at, turnout) VALUES (:1,:2,:3,:4,:5,:6)""")

cursor.executemany(None, listrows)

My table I'm connecting to is setup as this:

age NUMBER (19,0) primary key,
name VARCHAR(256),
lastcompany VARCHAR(256),
created_at timestamp,
deleted_at timestamp,
turnout timestamp

I get the error:

TypeError: expecting timestamp data

When I remove the line "cursor.setinputsizes(cx_Oracle.TIMESTAMP)" I get:

cx_Oracle.DatabaseError: ORA-01843: not a valid month

Answer Source

The problem is that you are sending strings, not timestamp values! So in the case where you are using setinputsizes() you are telling cx_Oracle that you have timestamp values to give....but are not providing them. Without the setinputsizes() call you are telling Oracle to convert those strings to timestamp values but the default timestamp format doesn't match the format of the dates you are passing.

You should either (a) convert your strings to Python datetime.datetime values or (b) specify the conversion in your SQL statement

Converting strings to Python datetime.datetime values is simple enough:

datetime.datetime.strptime("2016-08-15 20:23:03.317909", "%Y-%m-%d %H:%M:%S.%f")

Specifying the conversion in your SQL statement is fairly straightforward, too.

        insert into lifexp
        (age, name, lastcompany, created_at, deleted_at, turnout)
        VALUES (:1,:2,:3,
            to_timestamp(:4, 'YYYY-MM-DD HH24:MI:SS.FF'),
            to_timestamp(:5, 'YYYY-MM-DD HH24:MI:SS.FF'):6)""")

I believe the first option is the better one but both should work for you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download