Re: [python-win32] Python ADO and Date Time Field in Access
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 +, Tim Golden [EMAIL PROTECTED]
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 = rc:\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@python.org
http://mail.python.org/mailman/listinfo/python-win32
___
python-win32 mailing list
python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32
Re: [python-win32] Python ADO and Date Time Field in Access
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 = rc:\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@python.org
http://mail.python.org/mailman/listinfo/python-win32
Re: [python-win32] Python ADO and Date Time Field in Access
leegold wrote: Hi, Given an MS-Access table with a date type field with a value of: 12:00:00 AM - just12:00:00 AM, there's nothing else in the field. I want to print exactly what's in the field, ie. 12:00:00 AM. Do you understand that this is not really what's present in that field? What's present in the field is a floating point number. The number happens to represent the number of days since December 30, 1899. Hours, minutes, and seconds are stored as the decimal part of the fraction. One hour is 0.0416..., for example. Access formats it as 12:00:00 AM for you, because that's the local time format on your machine, and as Bob said, Access omits the date portion in the formatting if the number is less than 1.0. That's part of the Access application, NOT the database engine. What's printed is: 12/30/0/ 00:00:00 How do I get exactly what's in the field? Note, there could be any legal date time in the field - I'm trying in all cases to get exactly what's in the field...or to put it another way, exactly what I see when I open Access and look. The value of the Value property is a PyTime object. It supports the Format method. To get the time only, use: v = oRS.Fields(dt).Value print v.Format( %H:%M:%S %p) See: http://aspn.activestate.com/ASPN/docs/ActivePython/2.3/pywin32/PyTime__Format_meth.html -- Tim Roberts, [EMAIL PROTECTED] Providenza Boekelheide, Inc. ___ python-win32 mailing list python-win32@python.org http://mail.python.org/mailman/listinfo/python-win32
Re: [python-win32] Python ADO and Date Time Field in Access
...snip...
I want to print exactly what's in the field, ie. 12:00:00 AM.
Do you understand that this is not really what's present in that field?
What's present in the field is a floating point number. The number
happens to represent the number of days since December 30, 1899. Hours,
minutes, and seconds are stored as the decimal part of the fraction.
One hour is 0.0416..., for example.
Access formats it as 12:00:00 AM for you, because that's the local
time format on your machine, and as Bob said, Access omits the date
portion in the formatting if the number is less than 1.0. That's part
of the Access application, NOT the database engine.
Yes, but I want Python to print what I see when I open Access and
look-see. Python is also printing dates w/out times differently
too compared to what I see in Access.
I was coming from the equivalent Perl code and now trying
it in Python.
The equivalent Perl code seems to print what I see in Access
for Date/Time. eg. it'll print 12:00:00 AM.
VS.
Python always changes the Date/Time format.
For what it's worth here's the Perl code vs Python.
Test on Access table with Date/Time Field and
you'll see what I'm saying.
#!/usr/bin/perl
use strict;
use warnings;
use Win32::OLE();
use Win32::OLE::Variant;
$Win32::OLE::Warn=2;
my $conn = Win32::OLE-new(ADODB.Connection);
my $db = 'C:\Folder4\datetest1.mdb';
$conn-Open('Provider = Microsoft.Jet.OLEDB.4.0; Data Source='.$db);
my $zztop = $conn-Execute(SELECT DATE_FIELD FROM dtest_table);
$zztop-MoveFirst();
while( !$zztop-EOF) {
my $ss = $zztop-Fields(DATE_FIELD)-value;
print $ss\n;
$zztop-MoveNext;
}
VS.
import win32com.client
from win32com.client import Dispatch
oConn=Dispatch('ADODB.Connection')
db = r'C:\GIS_Folder4\datetest1.mdb'
oConn.Open(Provider=Microsoft.Jet.OLEDB.4.0; data Source=+db)
oRS = Dispatch('ADODB.RecordSet')
oRS.ActiveConnection = oConn
oRS.Open(dtest_table)
(oRS, dt) = oConn.Execute('SELECT DATE_FIELD FROM dtest_table')
while not oRS.EOF:
ss = oRS.Fields(dt).Value
print ss
oRS.MoveNext()
___
python-win32 mailing list
python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32
