How to insert empty excel dates into oracle with Python+Pandas? -


I have found a Python application which is using Panda to enter some Excel spreadsheets and insert value into an Oracle database is.

The values ​​that date for the cells, it works fine. For the cells of the empty date I am putting a NaT, which I thought would be fine, but some odd invalid time in the oracle Is happening which looks like "0001-255-255 00:00:00" (some maxiinty or 0 [72]: x.iloc [0] [9] out [72]: nat

Data is slightly above the data frame, you can see that it is a NaT.

But it is that I was looking at OracleI .. select 

  SQL & gt; TDATE to TDATE, select ID = 5067 and version = 5; TDATE --------- 01-Nov-SQL * Dump ("TDATE") table where id = 5067 and version = 5; dump ("TDATE") ---------------------------- ---------------- ---------------------------------- - Typing = 12 lane = 7: 100,101,255,255,1,1,1

I tried df.replace and / or df.where to change the night but in me Gets mixed errors with any one that is not valid in the way that the replacement reflects.

This issue has been fixed in Pandya 15.0.

If you can update the Pandas> = 15.0 from that version, NaN and NaT properly tap into the database Is stored in.

After using some experiments, it appears that the Pandas pass NaT to SQLAlchemy and below cx_Oracle - in return for that Oracle (which does not complain in return for it) Is) sent an invalid date for

Anyway, one wanted to add the before to fix an upcoming timestamp. To work for this, you have to manually create the table first. 

  - Create the table W ("ID" NUMBER (5), "TDATE" TIMESTAMP);

and then triggers: 

  - Create a trigger on the table, remove it before INSERT for each trial (Remove (Remove) month from new.tdate ) = 255) Start: new.tdate: = NULL; End; /

After that, using Python, using: 

  gt; & Gt; D = [{"id": 1, "tdate": datetime.now ()}, {"id": 2}]> gt; & Gt; & Gt; F = pd.DataFrame (d)> gt; & Gt; & Gt; F.to_sql ("w", engine, if_exists = 'append', index = false) # ^^^^^^^^^^^^^^^^^^ Do not drop the table! Attach data to an existing table

and check: 

  & gt; & Gt; & Gt; Results = Engine. Execute ("Choose from w")> gt; & Gt; & Gt; As a result for line: ... print (line) ... (1, datetime.datetime (2014, 10, 31, 1, 10, 2)) (2, none)
< Hr>

Be careful, if you have to rewrite other dataframes in the same table, then you have to delete its content first - but do not leave it, otherwise you will lose the trigger at the same time. For example: 

  # some new data & gt; & Gt; & Gt; D = [{"id": 3}]> gt; & Gt; & Gt; F = pd.DataFrame (d) #Translate table and write new data & gt; & Gt; & Gt; Engine.execute ("truncate table w") & gt; & Gt; & Gt; F.to_sql ("w", engine, if_exists = 'append', index = fail) & gt; & Gt; & Gt; Results = Engine. Execute ("Choose from w") #View Results & gt; & Gt; & Gt; As a result for the line: ... print (line) ... (3, none)

Comments

Post a Comment