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

[Thiru_P]

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','<f8'), ('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!

[DanPatterson_Retired]

you are specifying microseconds as the datetime format is that what you want?

# ---- changing the specifier in datetime64
# day
np.array(['2019-01-01', '2019-01-02','2019-01-03', '2019-01-04'], dtype='datetime64')
array(['2019-01-01', '2019-01-02', '2019-01-03', '2019-01-04'],
      dtype='datetime64')
# month
np.array(['2019-01-01', '2019-01-02','2019-01-03', '2019-01-04'], dtype='datetime64')
array(['2019-01', '2019-01', '2019-01', '2019-01'], dtype='datetime64')
# microsecond
np.array(['2019-01-01', '2019-01-02','2019-01-03', '2019-01-04'], dtype='datetime64[us]')
array(['2019-01-01T00:00:00.000000', '2019-01-02T00:00:00.000000',
       '2019-01-03T00:00:00.000000', '2019-01-04T00:00:00.000000'],
      dtype='datetime64[us]')‍‍‍‍‍‍‍‍‍‍‍‍‍

other info in numpy/scipy help Datetimes and Timedeltas — NumPy v1.17.dev0 Manual 

[Thiru_P]

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.

[DanPatterson_Retired]

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)

[JoshuaBixby]

Is there a way I can convert the SQL query directly into a File GDB feature class?

About the closest one can get to using an SQL query directly as a layer in ArcGIS is through Make Query Layer—Data Management toolbox | ArcGIS Desktop.  Since you are using an enterprise database, i.e., Oracle, you could create a query layer using your SQL and then use that layer as input to another tool to copy the data to a feature class.

[JoshuaBixby]

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.