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

[leegold]

Thank for the stellar explanations and code. 

You plus the other posters and esp. the code examples make it clear
what's going on.

It's been very helpful. Thanks,
Lee G.



On Tue, 29 Jan 2008 10:39:25 +0000, "Tim Golden" <mail at timgolden.me.uk>
said:
> 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
> _______________________________________________
> python-win32 mailing list
> python-win32 at python.org
> http://mail.python.org/mailman/listinfo/python-win32