topic Re: Preserving a MySql datetime field in Python in Python Questions

Preserving a MySql datetime field in Python

JoeBorgione — Thu, 13 Feb 2020 15:47:14 GMT

I have a python script that extracts data from a MySql database and writes it to a csv. The original table is a mix of all sorts of field types including datetime and timestamp. Once the csv is written and I perform an an arcpy table to table to get it into a fgdb, the datetime fields automagically become text fields. Not so good if you need to filter by date....

CSV files are both a boon and bane: is there a way to go right from MySql to a File geodatabse, thus preserving the field types? Unfortunately I do not have admin rights to our e-gdb (Sql server back-end) so I can't go MySql to SqlServer....

Re: Preserving a MySql datetime field in Python

DanPatterson_Retired — Fri, 10 Dec 2021 23:11:51 GMT

s = "2020-01-15 23:15:30"  # --- universal

print(datetime.datetime(*time.strptime(s, "%Y-%m-%d %H:%M:%S")[:6]))

2020-01-15 23:15:30

t = "01-15-2020 23:15:30"  # mon, day year

print(datetime.datetime(*time.strptime(t, "%m-%d-%Y %H:%M:%S")[:6]))
2020-01-15 23:15:30‍‍‍‍‍‍‍‍‍‍

YYYY-MM-DD date format is it?

Re: Preserving a MySql datetime field in Python

JoeBorgione — Thu, 13 Feb 2020 16:21:24 GMT

In mySql the values (when not Null) are:

2019-07-08 10:59:11

Bruce Harold‌ provides: https://community.esri.com/community/open-platform-standards-and-interoperability/blog/2019/09/26/using-odbc-data-sources-in-core-arcgis-pro which discusses odbc connections: my 64 bit windows 10 desktop has:

And the mySql site ( MySQL :: Download Connector/ODBC ) offers a 32 bit and a 64 bit driver. So I'm a little confused as to which odbc driver to down load and for that matter how to use pyodbc to traverse it. (Installing pyodbc package as I type this...)

Re: Preserving a MySql datetime field in Python

DanPatterson_Retired — Thu, 13 Feb 2020 16:24:29 GMT

I added to my post, YYYY-MM-DD is universal format great for sorting. If you need to parse by month, you can split on the string's - separator

Re: Preserving a MySql datetime field in Python

JoeBorgione — Thu, 13 Feb 2020 16:28:31 GMT

It's a little more complicated that that: these tables get geocoded, and work their way into a published service which is then consumed by an Operations Dashboard app. Ultimately, it's in OD where I need to filter by date.

Re: Preserving a MySql datetime field in Python

DanPatterson_Retired — Thu, 13 Feb 2020 16:33:09 GMT

Ahhh sorry ... don't do the 'consumed' side of things

Re: Preserving a MySql datetime field in Python

JoeBorgione — Thu, 13 Feb 2020 16:43:01 GMT

I was afraid of losing you with that point!

As mentioned, I'm adding the pydobc package through the ArcGIS Python Package Manager, and its still running, which if you notice my post regarding that is about at an hour. Something is quite right there.....

Re: Preserving a MySql datetime field in Python

BruceHarold — Fri, 14 Feb 2020 17:00:22 GMT

All, this pain is totally avoidable, just obtain the Data Interoperability extension and go directly from MySQL to File GDB. In Data Interop the reader is called MariaDB, in fact there are two, one spatial, one not. Bouncing data through CSV will throw the schema overboard as even writing a schema.ini file can't handle all data types.

Re: Preserving a MySql datetime field in Python

JoeBorgione — Fri, 14 Feb 2020 17:03:15 GMT

Thanks Bruce Harold!

Re: Preserving a MySql datetime field in Python

BruceHarold — Fri, 14 Feb 2020 17:07:37 GMT

OK full disclosure I'm the Product Manager for Data Interoperability 😉

Re: Preserving a MySql datetime field in Python

JoeBorgione — Fri, 14 Feb 2020 17:16:32 GMT

I've got a couple of humongous databases to plow through. It just might save not only time, but my sanity!

Re: Preserving a MySql datetime field in Python

JoeBorgione — Fri, 14 Feb 2020 18:28:08 GMT

Hey Bruce Harold I just went to the ArcGIS Interoperabilty page, clicked on the Learn More link and it sent me to an ArcGIS Help 10.2, 20.21, and 10.2.2 page....

Re: Preserving a MySql datetime field in Python

BruceHarold — Fri, 14 Feb 2020 18:53:06 GMT

Oh my word, a doc bug! Thanks for reporting it, I'll send it on.

Data Interoperability is an extension like Network Analyst, Spatial Analyst etc., you can evaluate it at no cost.