Python odbc time.struct_time undefined function

Question

DU = []
for i in range(len(DD)): 
    DU += [DD[i]+' '+UU[i]]


dtDate = []
for i in range(len(DU)):
    dtDate = dtDate + [time.strptime(DU[i],"%d.%m.%Y %H:%M:%S")]

#readin access
path = './'
acc_fname = 'test.mdb'
DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
MDB = path + acc_fname
PWD = ''
con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()
for i in range(3):
    SQL = 'SELECT ID FROM Tab WHERE Tab.FileZeit = {0};'.format(dtDate[i])
table_2 = cur.execute(SQL).fetchall()
print(table_2)

then i got this error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC-Treiber f\ufffdr Microsoft Access] Undefined function 'time.struct_time' in Ausdruck. (-3102) (SQLExecDirectW)")

DD & UU Are lists with date and time, then i convert them to datetime(dtDate). Filezeit is also a datetime type.

axel_ande · Answer 1 · 2016-02-23T08:20:03.610

0

Well, your db cant handle the time.struct_time, try to use a change it back to a string with SQL = 'SELECT ID FROM Tab WHERE Tab.FileZeit = {0};'.format(time.strftime(dtDate[i],"%d.%m.%Y %H:%M:%S"))

EDIT:

SQL = 'SELECT ID FROM Tab WHERE Tab.FileZeit = %s' % time.strftime("%d.%m.%Y %H:%M:%S", dtDate[i]))

edited Feb 23 '16 at 08:20

answered Feb 23 '16 at 06:31

axel_ande

359
1
4
20

then i got this error: TypeError: must be str, not time.struct_time – Nekoso Feb 23 '16 at 06:37
i used your edit version but the typeerror: must be str, not time.struct_time remains – Nekoso Feb 23 '16 at 06:58
Lol, it should be time.strftime("%d.%m.%Y %H:%M:%S", dtDate[i]) – axel_ande Feb 23 '16 at 07:10

Bryan · Answer 2 · 2016-02-23T14:07:40.633

Use parameters instead of string formatting for SQL queries. Add a parameter placeholder (?) to the query, and supply a value for that parameter when calling Cursor.execute.

con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()
for i in range(3):
    params = (dtDate[i],)
    SQL = 'SELECT ID FROM Tab WHERE Tab.FileZeit = ?;'
table_2 = cur.execute(SQL, params).fetchall()

When working with datetime values, this has the added benefit of letting pyodbc handle translation of Python datetime types to/from ODBC types, no string formatting required.

Python odbc time.struct_time undefined function

2 Answers2