0

I want to read a *.mdb database in odoo and import some data to odoo but I cannot read some fields.

In *.mdb I have 2 tables
first table is USERINFO with fields: SSN, Name, USERID
second table is CHECKINOUT with fields: USERID, CHECKTIME, CHECKTYPE

I use mdbtools and it runs well when I make this query

query1 = 'SELECT CHECKTIME,CHECKTYPE FROM CHECKINOUT'

The data I want is out.

But it errors when I run these two queries.

query2 = 'SELECT USERID,CHECKTIME FROM CHECKINOUT'

pyodbc.Error: ('HY000', 'The driver did not supply an error!')

it's look the problem in in "USERID" field

and in the other query

query3 = """
SELECT c.CHECKTIME, c.CHECKTYPE, u.SSN, u.`Name`, u.USERID 
FROM CHECKINOUT c, USERINFO u  
WHERE c.USERID = u.USERID """

Error at Line : syntax error near c.CHECKTIME syntax error near c.CHECKTIME Got no result for 'SELECT c.CHECKTIME, c.CHECKTYPE, u.SSN FROM CHECKINOUT c, USERINFO u WHERE c.USERID=u.USERID; ' command

pyodbc.OperationalError: ('08001', "[08001] Couldn't parse SQL\n (1) (SQLExecDirectW)")

The query runs well in the DBeaver, but didn't running well in python.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
niyuzuku
  • 11
  • 2
  • Maybe this is caused by reserved words? Try to encapsulate specific fieldnames/tablenames in square brackets (`'SELECT [USERID], CHECKTIME FROM CHECKINOUT'`) to prevent such behaviour. Regarding your second query, shouldn't it be `query3 = "SELECT c.CHECKTIME, c.CHECKTYPE, u.SSN, u.Name, u.USERID FROM CHECKINOUT c, USERINFO u WHERE c.USERID = u.USERID"` (without the taking care of possibly reserved names)? – AHeyne Nov 02 '18 at 05:26
  • @UnhandledException I've tried your note. but nothing change. – niyuzuku Nov 02 '18 at 06:25
  • You could try to rename field by field to a 'safe' name in the table (and in the query) to check out if any of them causes the trouble. – AHeyne Nov 02 '18 at 06:30
  • @UnhandledException the mdb files is from another aplication, so I think I cant change it as I please. that why I just want to select it and copy it to my db. – niyuzuku Nov 02 '18 at 06:37
  • But for the purpose of finding the reason for error it could be done on a copy, or? – AHeyne Nov 02 '18 at 06:40
  • Any specific reason you need to use mdbtools and can't just use the Access database engine? This might just be a mdbtools bug – Erik A Nov 02 '18 at 06:55
  • @UnhandledException yeah I will copy the mdb and rename it – niyuzuku Nov 02 '18 at 09:16
  • @ErikvonAsmuth I use linux where I can't open Ms Access file directly. Right now I open the mdb with DBeaver. – niyuzuku Nov 02 '18 at 09:17
  • I recommend JayDeBeApi + UCanAccess over mdbtools for Python and Linux, UCanAccess is a more mature and well-maintained afaik. – Erik A Nov 02 '18 at 09:24
  • Further information re: suggestion from @ErikvonAsmuth can be found in [this answer](https://stackoverflow.com/a/25614063/2144390). – Gord Thompson Nov 02 '18 at 20:59

0 Answers0