Trying to set a date field in a access databse

[M.-A. Lemburg]

On 2008-11-05 21:12, tedpottel at gmail.com wrote:
> 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)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: 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