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!
[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)""")
CREATE TABLE lifexp(
age NUMBER (19,0) primary key,
TypeError: expecting timestamp data
cx_Oracle.DatabaseError: ORA-01843: not a valid month
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.
cursor.prepare(""" 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.