Using cx_Oracle SQL Query and writing the result to a non-spatial feature class

Question

Dear Python experts, Using Python and cx_Oracle, I would like to query an Oracle SDE table (50 columns) and write the resulting data rows into a non-spatial File GDB feature class. Eventually, I will use this feature class and UpdateCursor to update custom fields in a mosaic dataset. Is there a way I can convert the SQL query directly into a File GDB feature class? Trying the numpy method (field mapping the 50 columns is going to be fun!) , I converted the query results to a numpy array and then wrote them to a feature class but the date fields get converted to datetime fields. I need dates only and I want to get it right before I proceed further. Here's the code I have so far: import arcpy import numpy as np import cx_Oracle out_tbl = r'C:\Temp\work.gdb\img_source_qry' connstr='username/password@port/service' con = cx_Oracle.connect(connstr) curs = con.cursor() sqlQry = """ SELECT n_frame, t_available, d_flying FROM db.table where ROWNUM < 10 """ curs.execute(sqlQry) datArray = [] cxRows = curs.fetchall() for cxRow in cxRows: datArray.append(cxRow) # delete output feature class if it exists if arcpy.Exists(out_tbl): arcpy.Delete_management(out_tbl) #define array and write fc numpyarr_out = np.array(datArray, np.dtype([('Frame','), ('Available', 'S2'), ('Date_Fly', 'M8[us]')])) arcpy.da.NumPyArrayToTable(numpyarr_out, out_tbl) #close the connections curs.close() del cxRows, curs print datArray con.close() ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ This is the output, I am getting Appreciate your advice. Thanks!

JoshuaBixby · Answer

I point out the following not to nitpick but to educate.  The phrase "non-spatial File GDB feature class" contradicts itself.  By definition, a feature class is spatial.  A feature class doesn't have to actually have any geometry data stored in it, but the schema has to have a geometry column.  A file geodatabase table does not contain a geometry column, so it is non-spatial (non-spatial in the sense it isn't natively spatial, one can still store geometry information as text or blob in a table but ArcGIS won't treat it directly as spatial).

In terms of file geodatabase data types, according to Data types in the DBMS—ArcGIS Help | ArcGIS Desktop :

File geodatabase data types are the same as ArcGIS data types. For DBMS products, though, data types can differ.

When looking at ArcGIS field data types—ArcGIS Help | ArcGIS Desktop, there is only one date data type:

The date data type can store dates, times, or dates and times. The default format in which the information is presented is mm/dd/yyyy hh:mm:ss and a specification of AM or PM. When you enter date fields in the table through ArcGIS, they are converted to this format.

There is no way to just store a date and not time in an ArcGIS date field.  That said, Esri software commonly only displays the date if the time is set to 00:00:00.  If you only want people to see the date when opening a table in Esri software, change the time and then insert the records into a file geodatabase table.

I agree with Dan, use SQL date formatting to drop the time before inserting it into a file geodatabase.

DanPatterson_Retired · Answer

numpy was just a convenient example.

it is your use of 'us' … you are specifying a microsecond datetime.

You can probably do the sql format thing and get it in the format used in the U.S. (on a side note... you aren't confusing 'us' with US are you?, since US … the country... uses the DD/MM/YYYY format, like feet … metres and YYYY/MM/DD etc are international)

Thiru_P · Answer

Thanks Dan. I want the date DD/MM/YYYY only. Is there a dtype specifically available for that?

My Oracle field is datetime. When I try any other type, I get the following error.

Traceback (most recent call last):
 File "C:\Working\Scripts\ConnectOracle.py", line 127, in <module>
 numpyarr_out = np.array(datArray, np.dtype([('Frame','<f8'), ('Available', 'S2'), ('Date_Fly', 'datetime64')]))
TypeError: Cannot cast datetime.datetime object from metadata [us] to  according to the rule 'same_kind'

Possibly, I could truncate the datetime field in the SQL query and store it in a string in numpy and convert it into date field again. Seems like a long detour.