Yuri
Yuri

Reputation: 135

Are there exist any alternatives for mdbtools which doesn't use Java?

So I am tried to work with MsAccess DB file at Linux systems (Ubuntu 20.04 and CentOs 8) using Python and SqlAlchemy. And can't get to work mdbtools driver even with the simpliest query. At my home Ubuntu I installed latest sqlalchemy_access, pyodbc, unixodbc. As driver I installed odbc-mdbtools.

Made correct connection at /etc/odbcinst.ini:

[MDBTools]
Description=MDBTools Driver
Driver=libmdbodbc.so
Setup=libmdbodbc.so
FileUsage=1
UsageCount=1

DSN URI is:

MAIN_DB_CONNECTION_STRING = (
'DRIVER={MDBTools};'
f'DBQ={os.path.join(BASE_DIR, DB_FILENAME)};'
)

MAIN_DB_CONNECTION_URI = f"access+pyodbc:///?odbc_connect={parse.quote_plus(MAIN_DB_CONNECTION_STRING)}"

My DB models are:

class ChargeArticleMainDB(Base):
    __tablename__ = 'Articles'
    id = Column(Integer, name='id', primary_key=True, autoincrement=True)
    article = Column(String(8), name='article')
    article_ext = Column(String(10), name='article_ext')
    codex_id = Column(Integer, name='codex_id')
    description = Column(String(255), name='description')
    query = main_session.query_property()

So everything Ok, and runs without errors until I make first request. If I try to simply get all Articles from base, I get following error:

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', "[08001] Couldn't parse 

SQL\n (1) (SQLExecDirectW)")
[SQL: SELECT [Articles].id AS [Articles_id], [Articles].article AS [Articles_article], [Articles].article_ext AS [Articles_article_ext], [Articles].codex_id AS [Articles_codex_id], [Articles].description AS [Articles_description] 
FROM [Articles]]

As you might see, the SQL statement is correct and runs in DBeaver without problems. Seems that there some problems with mdbtools driver. Therefore I tried to find another MsAccess driver that can work under Linux. AS per this old answer there was only 2 alternatives for mdbtools (JayDeBeApi and Jython). Also DBeaver uses UCanAccess driver. They all relies on Java VM, which can't be used on target system due to some internal issues. Are there any known driver alternatives for MsAccess without Java?

P.S. the same problem even if I left only one ID column in DB and in model, and run raw request:

    query = """SELECT * FROM Articles;"""
    print(session.execute(query))

P.P.S. The same problem if I use isql utility. Tool mdb-sql works fine - showing UTF-8 characters (DB has russian language).

Upvotes: 0

Views: 490

Answers (0)

Related Questions