Trying to set a date field in a access databse

[tedpottel]

Hi,
I cannot get the following code to work

import win32com.client
import time

engine = win32com.client.Dispatch("DAO.DBEngine.36")
db=engine.OpenDatabase(r"testdate2.mdb")
access = db.OpenRecordset("select * from test")

access.AddNew()
access.Fields("test").value=time.strptime('10:00AM', '%I:%M%p')
access.Update()

wherer test is a datetime field,
How can I do this???????
-Ted

 

[Peter Otten]

Hi,
I cannot get the following code to work

import win32com.client
import time

engine = win32com.client.Dispatch("DAO.DBEngine.36")
db=engine.OpenDatabase(r"testdate2.mdb")
access = db.OpenRecordset("select * from test")

access.AddNew()
access.Fields("test").value=time.strptime('10:00AM', '%I:%M%p')
access.Update()

wherer test is a datetime field,
How can I do this???????

A first step would be to find out what the expected type of the value
attribute is. For that you can put one record into the test table using
Access with the value 10:00AM for the test field and then run

# all code untested

import win32com.client

engine = win32com.client.Dispatch("DAO.DBEngine.36")
db = engine.OpenDatabase("testdate2.mdb")
access = db.OpenRecordset("select * from test")

access.MoveFirst()
v = access.Fields("test").value
print type(v), v

If you cannot guess what Access expects from the output of the script, post
it here (in this thread, no need to start yet another one). Don't just
say "didn't work", give tracebacks and the exact code you ran.

Judging from
http://en.wikibooks.org/wiki/JET_Database/Data_types#Dates_and_times
you will see something like

<type 'float'> 0.416666666667

If that's correct you can modify your script

def time_to_float(h, m, s):
return (h + m/60.0 + s/3600.0)/24.0

# ...

access.AddNew()
access.Fields("test").value = time_to_float(10, 0, 0)
access.Update()

Peter

 

[M.-A. Lemburg]

Hi,
I cannot get the following code to work

import win32com.client
import time

engine = win32com.client.Dispatch("DAO.DBEngine.36")
db=engine.OpenDatabase(r"testdate2.mdb")
access = db.OpenRecordset("select * from test")

access.AddNew()
access.Fields("test").value=time.strptime('10:00AM', '%I:%M%p')
access.Update()

wherer test is a datetime field,
How can I do this???????

You could try to use mxODBC for this:

http://www.egenix.com/products/python/mxODBC/

Here's a very simple example for using mxODBC:

# On Windows:
from mx.ODBC import Windows as Database

# On Mac OS X:
from mx.ODBC import iODBC as Database

# On Linux/BSD/etc.:
from mx.ODBC import unixODBC as Database
# or
from mx.ODBC import iODBC as Database

# Open a connection to the database
connection = Database.DriverConnect('DSN=<datasourcename>;'
'UID=<username>;'
'PWD=<password>;'
'KEYWORD=<value>')
# replace the values accordingly, add new keyword-value pairs as
# necessary for your data source; data sources are configured
# in the ODBC manager

# Create a cursor; this is used to execute commands
cursor = connection.cursor()

# Create a table
cursor.execute('CREATE TABLE mxodbcexample1 '
' (id integer, name varchar(10), data varchar(254))')
# this command does not create a result set, so there's nothing
# to fetch from the database; however in order to make the
# change permanent, we need to commit the change
connection.commit()

# Prepare some data rows to add to the table, ie. a list of tuples
rows = []
for i in range(42):
name = 'name-%i' % i
data = 'value-%i' % i
rows.append((i, name, data))

# Add the data in one go; the values from the tuples get assigned
# to the ?-mark parameter markers in the SQL statement based on
# their position and the SQL statement is executed once for
# each tuple in the list of rows
cursor.executemany('INSERT INTO mxodbcexample1 VALUES (?,?,?)',
rows)

# If you apply changes to the database, be sure to commit or
# rollback your changes; a call to .commit() or .rollback()
# will implicitly start a new transaction
connection.commit()

# Now fetch some data rows
from_id = 40
to_id = 42
cursor.execute('SELECT * FROM mxodbcexample1'
' WHERE (id >= ?) and (id < ?)',
(from_id, to_id))

# Fetch the results
for i, row in enumerate(cursor.fetchall()):
print 'Row %i: %r' % (i, row)

# Remove the table again
cursor.execute('DROP TABLE mxodbcexample1')
connection.commit()

# Close the connection
connection.close()


With MS Access this gives:

Row 0: (40, 'name-40', 'value-40')
Row 1: (41, 'name-41', 'value-41')

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Nov 06 2008)________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611

 

[Roger Upole]

You should be able to pass a PyTime or datetime.datetime object.

Roger