[python-win32] Python ADO and Date Time Field in Access

[Tim Golden]

leegold wrote:
> ...snip...

> I was coming from the equivalent Perl code and now trying 
> it in Python. 

[... snip code examples ...]

Well, seeing you've gone to the trouble of posting the code...
even if you didn't provide a working database for those of
use without access to Access (pun entirely intended)....
I've put together a working test case to make sure we're
talking about the same thing.

This code creates a new Jet/.mdb databasem, creates a test
table with an INT id and a TIME dt field and populates it
with one row. I hope this is sufficient to simulate your
source data.

It then queries that table, looping over the (one) rows
it contains and printing out the values *and their repr*.
It can be cut-and-pasted directly into a Python console
session or you can save it as a file and run it.

<code>
import os, sys
from win32com.client.gencache import EnsureDispatch as Dispatch

DATABASE_FILEPATH = r"c:\temp\test.mdb"
CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; data Source=%s" % 
DATABASE_FILEPATH

if os.path.exists (DATABASE_FILEPATH):
   os.remove (DATABASE_FILEPATH)

adox = Dispatch ("ADOX.Catalog")
adox.Create (CONNECTION_STRING)

db = Dispatch ('ADODB.Connection')
db.Open (CONNECTION_STRING)
try:
   db.Execute ('CREATE TABLE dtest (id INT, dt TIME)')
   db.Execute ("INSERT INTO dtest (id, dt) VALUES (1, NOW ())")
   (rs, n) = db.Execute ('SELECT id, dt FROM dtest')
   while not rs.EOF:
     for field in rs.Fields:
       print field.Name, "=>", field.Value, repr (field.Value)
     print
     rs.MoveNext ()
finally:
   db.Close ()

</code>

The results on my machine show that the TIME field is
returned as a PyTime value. Assuming the same is true
for you, you should be able to use this technique:

http://timgolden.me.uk/python/win32_how_do_i/use-a-pytime-value.html#from-timestamp

to produce a standard Python datetime value, from which
you can then format it as you like with .strftime.

If you *don't* get a PyTime value, then can you post the
output from your run and let's take it from there?

TJG