I'm looking for a method to programmatically access an access database file (.mdb) using python in an Ubuntu environment (hosted on a Windows 10 machine) to export tables to pandas dataframes. I've attempted the solutions previously posted, such as using pandas access, pyodbc, or mdbtools, however all have failed due to errors relating to missing tables or files in the packages.
Any feedback or insights would be much appreciated!
pyodc failure:
OperationalError Traceback (most recent call last)
Cell In[6], line 2
1 pypyodbc.lowercase = False
----> 2 conn = pypyodbc.connect(
3 r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
4 r"/home/kevin/scratch/HDINEWTON.mdb;")
5 cur = conn.cursor()
6 # cur.execute("SELECT CreatureID, Name_EN, Name_JP FROM Creatures");
7 # while True:
8 # row = cur.fetchone()
(...)
11 # print(u"Creature with ID {0} is {1} ({2})".format(
12 # row.get("CreatureID"), row.get("Name_EN"), row.get("Name_JP")))
File ~/miniconda3/envs/mdb_access/lib/python3.11/site-packages/pypyodbc.py:2454, in Connection.__init__(self, connectString, autocommit, ansi, timeout, unicode_results, readonly, **kargs)
2450 if self.connection_timeout != 0:
2451 self.set_connection_timeout(connection_timeout)
-> 2454 self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
File ~/miniconda3/envs/mdb_access/lib/python3.11/site-packages/pypyodbc.py:2507, in Connection.connect(self, connectString, autocommit, ansi, timeout, unicode_results, readonly)
2505 else:
2506 ret = odbc_func(self.dbc_h, 0, c_connectString, len(self.connectString), None, 0, None, SQL_DRIVER_NOPROMPT)
-> 2507 check_success(self, ret)
2510 # Set the connection's attribute of "autocommit"
2511 #
2512 self.autocommit = autocommit
File ~/miniconda3/envs/mdb_access/lib/python3.11/site-packages/pypyodbc.py:1009, in check_success(ODBC_obj, ret)
1007 ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
1008 elif isinstance(ODBC_obj, Connection):
-> 1009 ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
1010 else:
1011 ctrl_err(SQL_HANDLE_ENV, ODBC_obj, ret, False)
File ~/miniconda3/envs/mdb_access/lib/python3.11/site-packages/pypyodbc.py:983, in ctrl_err(ht, h, val_ret, ansi)
981 raise NotSupportedError(state,err_text)
982 elif state in (raw_s('HYT00'),raw_s('HYT01'),raw_s('01000')):
--> 983 raise OperationalError(state,err_text)
984 elif state[:2] in (raw_s('IM'),raw_s('HY')):
985 raise Error(state,err_text)
OperationalError: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found")
pandas access failure:
---------------------------------------------------------------------------
FileNotFoundError Traceback (most recent call last)
Cell In[4], line 2
1 path_to_mdb_database = '/home/kevin/scratch/HDINEWTON.mdb'
----> 2 mdb.list_tables(path_to_mdb_database)
File ~/miniconda3/envs/mdb_access/lib/python3.11/site-packages/pandas_access/__init__.py:25, in list_tables(rdb_file, encoding)
17 def list_tables(rdb_file, encoding="latin-1"):
18 """
19 :param rdb_file: The MS Access database file.
20 :param encoding: The content encoding of the output. I assume `latin-1`
(...)
23 :return: A list of the tables in a given database.
24 """
---> 25 tables = subprocess.check_output(['mdb-tables', rdb_file]).decode(encoding)
26 return tables.strip().split(" ")
File ~/miniconda3/envs/mdb_access/lib/python3.11/subprocess.py:465, in check_output(timeout, *popenargs, **kwargs)
462 empty = b''
463 kwargs['input'] = empty
--> 465 return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
466 **kwargs).stdout
File ~/miniconda3/envs/mdb_access/lib/python3.11/subprocess.py:546, in run(input, capture_output, timeout, check, *popenargs, **kwargs)
543 kwargs['stdout'] = PIPE
544 kwargs['stderr'] = PIPE
--> 546 with Popen(*popenargs, **kwargs) as process:
547 try:
548 stdout, stderr = process.communicate(input, timeout=timeout)
File ~/miniconda3/envs/mdb_access/lib/python3.11/subprocess.py:1022, in Popen.__init__(self, args, bufsize, executable, stdin, stdout, stderr, preexec_fn, close_fds, shell, cwd, env, universal_newlines, startupinfo, creationflags, restore_signals, start_new_session, pass_fds, user, group, extra_groups, encoding, errors, text, umask, pipesize, process_group)
1018 if self.text_mode:
1019 self.stderr = io.TextIOWrapper(self.stderr,
1020 encoding=encoding, errors=errors)
-> 1022 self._execute_child(args, executable, preexec_fn, close_fds,
1023 pass_fds, cwd, env,
1024 startupinfo, creationflags, shell,
1025 p2cread, p2cwrite,
1026 c2pread, c2pwrite,
1027 errread, errwrite,
1028 restore_signals,
1029 gid, gids, uid, umask,
1030 start_new_session, process_group)
1031 except:
1032 # Cleanup if the child failed starting.
1033 for f in filter(None, (self.stdin, self.stdout, self.stderr)):
File ~/miniconda3/envs/mdb_access/lib/python3.11/subprocess.py:1899, in Popen._execute_child(self, args, executable, preexec_fn, close_fds, pass_fds, cwd, env, startupinfo, creationflags, shell, p2cread, p2cwrite, c2pread, c2pwrite, errread, errwrite, restore_signals, gid, gids, uid, umask, start_new_session, process_group)
1897 if errno_num != 0:
1898 err_msg = os.strerror(errno_num)
-> 1899 raise child_exception_type(errno_num, err_msg, err_filename)
1900 raise child_exception_type(err_msg)
FileNotFoundError: [Errno 2] No such file or directory: 'mdb-tables'
mdb tools failure:
ModuleNotFoundError Traceback (most recent call last)
Cell In[2], line 5
3 import os
4 import pandas_access as mdb
----> 5 import mdbtools
7 print('Imports Successful')
File ~/miniconda3/envs/mdb/lib/python3.11/site-packages/mdbtools/__init__.py:1
----> 1 from .MDBcli import *
2 from .MDBnetrc import *
3 from .MDBtool import *
File ~/miniconda3/envs/mdb/lib/python3.11/site-packages/mdbtools/MDBcli.py:21
19 import sys
20 import argparse
---> 21 from MDButils import *
23 # Stop Python from complaining when I/O pipes are closed
24 from signal import signal, SIGPIPE, SIG_DFL
ModuleNotFoundError: No module named 'MDButils'