Python ms access linux

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Cross-platform Python reader for MS Access files

gilesc/mdbread

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.rst

A simple Cython-based wrapper for the excellent MDBTools package to read data from MS Access MDB files. Currently, it supports a few basic operations like listing tables and table columns, iterating through rows, or exporting a table to a pandas DataFrame. It does not support SQL or inserts.

On Ubuntu this can be satisfied by:

sudo apt-get install -y mdbtools-dev

mdbtools is also available under Cygwin. However, I haven’t tested this package on Windows and there are much easier ways to use Access files under Windows, such as ODBC or the Python Windows API.

To complete the installation, run the following command from this directory:

(sudo) python setup.py install

>>> import mdbread >>> db = mdbread.MDB("MyDB.mdb") >>> print db.tables ["tbl1", "tbl2", "tbl3"] >>> tbl = db["tbl1"] >>> print tbl.columns ["foo","bar","baz"]

To get the data in a table, you have three options:

• mdbread.Table.records() returns a generator of dictionaries, where the keys are column names and the values are the data.
• iter(mdbread.Table) will return a namedtuple for each row. You can also use this form with for row in tbl:
• mdbread.Table.to_data_frame() will return a pandas DataFrame containing all the data for the entire table (possibly requiring lots of memory) .

The biggest current limitation is that not all MS Access datatypes are coerced to Python objects. So, if you are iterating through rows in an MDB and the column has an unusual type, the program may fail with a KeyError. You can file an issue or e-mail me and I can add your favorite datatype. Or, you can simply add your own coercion to the «transformers» dictionary within mdbread.pyx. I hope to find time to fix this soon.

Pull requests and issues are welcomed.

Источник

Connecting from Python on Linux to MS Access [duplicate]

I am trying to connect to an access .mdb file in a linux environment. Until now, I have done this in windows like this:

import pyodbc DRIVER="" def connect(): PATH = '. file.mdb' con = pyodbc.connect('DRIVER=<>;DBQ=<>'.format(DRIVER,PATH)) return con 

Now trying this in Ubuntu 18, this won’t work because the Microsoft access driver is not available. I have been all over trying to solve this, mainly with MDBTools. After installing MDBTools and changing the driver to MDBTools I get this error:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'libmdbodbc.so' : file not found (0) (SQLDriverConnect)") 

I no idea how to resolve this, sources say download a libmdbodbc package, but it seems this package no longer exists.

@JoshuaSchlichting So I dont really understand how packages work in linux, so I don’t know where that file is supposed to be, but I did find it at /usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so . So I also tried changing the driver to that file directly, which gives me the error pyodbc.Error: (‘HY000’, ‘The driver did not supply an error!’) . Again, no idea if I’m doing anything that makes sense.

This sounds very similar to this issue: stackoverflow.com/q/47180843/3589609 The accepted answer here installs an older version of the library needed. Maybe test out something along those lines?

@JoshuaSchlichting those all have to do with mssql drivers, I have no idea where to get the access mdbodbc file from

1 Answer 1

I don’t know anything about ‘linux environments’, whatever that is, but here are a few options that work fine for me.

# MS Access import pyodbc conn = pyodbc.connect(r'Driver=;DBQ=C:\\your_path_here\\your_DB.accdb;') cursor = conn.cursor() cursor.execute('select * from tracking_sales') for row in cursor.fetchall(): print (row) # https://datatofish.com/how-to-connect-python-to-ms-access-database-using-pyodbc/ ******** ******** ******** ******** ******** ******** ******** ******** #import pypyodbc import pyodbc # MS ACCESS DB CONNECTION pyodbc.lowercase = False conn = pyodbc.connect( r"Driver=;" + r"Dbq=C:\\your_path\\your_DB.mdb;") # OPEN CURSOR AND EXECUTE SQL cur = conn.cursor() # Option 1 - no error and no update cur.execute("UPDATE dbo_test SET Location = 'New York' Where Status = 'Scheduled'"); conn.commit() cur.close() conn.close() ******** ******** ******** ******** ******** ******** ******** ******** # select records from a table import pyodbc conn = pyodbc.connect(r'Driver=;DBQ=C:\\your_path\\your_DB.accdb;') cursor = conn.cursor() cursor.execute('select * from Table2') for row in cursor.fetchall(): print (row) # insert data into 2 fields in a table import pyodbc conn = pyodbc.connect(r'Driver=;DBQ=C:\\Users\\Excel\\Desktop\\Coding\\Microsoft Access\\Split_and_Transpose.accdb;') cursor = conn.cursor() cursor.execute(''' INSERT INTO Table2 (ExplodeKey, ExplodeField) VALUES(5, 'RMS') ''') conn.commit() ******** ******** ******** ******** ******** ******** ******** ******** 

Источник

Microsoft Access mdb and accdb Database

PyPyODBC is a pure Python script module providing ODBC interfacing functions, it runs on CPython / IronPython / PyPy , Version 2.4 / 2.5 / 2.6 / 2.7 , Win / Linux , 32 / 64 bit.

However, on Windows Platform, PyPyODBC also provides a set of methods bringing PyPyODBC’s extra support for Access on Windows Platform

mxODBC

http://www.egenix.com/ License eGenix.com Commercial License Platforms Windows, Unix Python versions 2.4 — 2.7

On Windows, you can use mxODBC with the ODBC driver that comes with MS Access, or use the ODBC driver that comes with the MDAC 2.8 SP1 database access package, if you don’t have MS Access installed on the machine.

On Unix platforms, you can use one of the ODBC drivers available from commercial ODBC vendors. If you only need to query data from MS Access files, you can also have a look at the very limited ODBC driver that comes with the MDBTools. This works reasonably well to extract data from the MS Access files.

mxODBC Connect

http://www.egenix.com/products/python/mxODBCConnect/ License eGenix Commercial License 1.3.0 Platforms Client: all Python platforms; Server: Windows, Linux Python versions 2.5 — 2.7

mxODBC Connect is a commercial client-server product that allows connecting Python to ODBC compatible databases running on remote servers without requiring an ODBC driver on the client side. The product uses mxODBC on the server side and provides a highly portable Python library for the client side. As such it supports all database backend that mxODBC supports, but allows connecting to these from many different Python-supported platforms.

mxODBC Connect supports asynchronous query execution via the popular gevent package, provides secure certificate based authentication, SSL encrypted database connections, comes with full support for stored procedures, multiple result sets, Unicode, a common interface on all platforms and implements many other useful features.

mxODBC Connect Server is compatible with the MS Access ODBC driver on Windows and allows both reading and writing to MDB/ACCDB Access files. Provided you have a Windows server available to run those drivers, you can then work with MS Access database files from any Python platform using the mxODBC Connect Client.

Microsoft Access (last edited 2014-06-07 10:50:56 by MarcAndreLemburg )

Источник