Python help for Access database

[John Machin]

shakir wrote:
> HI All,
>
> I need help for inserting recods into the access database using python
> script through ODBC. I can insert data without any problem if I hard
> coded the run_Date field. But I need run_Date field should be mytime..
> I am getting error Data type mismatch in criteria expression. in EXEC
>
> I really appreciate if anyone can help me.
>
> Thanks in advance
>
> Shakir
>
> # mdbupdate.py
>
> import sys
> import os
> import os.path
> import shutil
> import time
> import sys, string, os, win32com.client
> import odbc
>
>
> mytime = time.strftime('%m/%d/%Y')
> mytimeYMD = time.strftime('%Y%m%d')
> conn = odbc.odbc("test1_Data")
> cursor = conn.cursor()
>
> # data type in mdb is as follows: application -> Text, Run_Date ->
> Date, Run_dateYMD -> #number, status -> Script
>
> cursor.execute("INSERT INTO local_cmgenadm (Application,Run_Date,
> Run_DateYMD,status) values (\'MyApp1\',\'%s\', \'20060731\' ,
> \'Good\')")%mytime

1. Why do you think you need all those \ characters?

2. Try this elementary debugging procedure:

sql = "INSERT ......."
print sql
print mytime
print sql % mytime
# compare the result with your hard-coded effort
cursor.execute(sql)

3. You may wish to try the parameterised approach:

cursor.execute("insert ... values (?,?,?,?)", ( 'MyApp1',mytime,
'20060731' ,'Good'))

[google "SQL injection attack"]

4. You may wish to try another interface: adodbapi [I've used that OK
but the project seems dormant (last update 3 years ago)] or mxODBC
[very good but not free].
If you are not constrained to use MS Access, consider sqlite3.

Cheers,
John