Python help for Access database

[shakir]

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("test 1_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,Ru n_Date,
Run_DateYMD,sta tus) values (\'MyApp1\',\'% s\', \'20060731\' ,
\'Good\')")%myt ime



cursor.close()

conn.close()

[John Machin]

Re: Python help for Access database


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("test 1_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,Ru n_Date,
Run_DateYMD,sta tus) values (\'MyApp1\',\'% s\', \'20060731\' ,
\'Good\')")%myt ime

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

[BartlebyScrivener]

Re: Python help for Access database

John Machin wrote:

>or mxODBC
>[very good but not free].

I love mxODBC. It's free for noncommercial use.

http://www.egenix.com/files/python/mxODBC.html

rd

[John Machin]

Re: Python help for Access database


BartlebyScriven er wrote:

John Machin wrote:
>

or mxODBC
[very good but not free].

>
I love mxODBC. It's free for noncommercial use.
>

I was presuming that the OP was mucking about with Access only because
he was so constrained by his job :-)