Python odbc time.struct_time undefined function [duplicate]

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 · Accepted Answer · 2016-02-23 08:20:03Z

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, 2016 at 8:20

answered Feb 23, 2016 at 6:31

axel_ande

4671 gold badge6 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nekoso Over a year ago

then i got this error: TypeError: must be str, not time.struct_time

Nekoso Over a year ago

i used your edit version but the typeerror: must be str, not time.struct_time remains

axel_ande Over a year ago

Lol, it should be time.strftime("%d.%m.%Y %H:%M:%S", dtDate[i])

Bryan · Accepted Answer · 2016-02-23 14:07:40Z

0

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.

edited Feb 23, 2016 at 14:07

answered Feb 23, 2016 at 13:37

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Collectives™ on Stack Overflow

Python odbc time.struct_time undefined function [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related