The MonetDB MAPI and SQL client python API is the native python client API. This API is cross-platform, and doesn't depend on any monetdb libraries. It has support for python 2.7 and 3.3+ and is Python DBAPI 2.0 compatible.
To install the MonetDB python API run the following command:
pip install pymonetdb
That's all, now you are ready to start using the API. We recommend that you use virtual environments to avoid polluting your global python installation.
The python code is well documented, so if you need to find documentation you should have a look at the source code. Below is an interactive example on how to use the monetdb SQL API which should get you started quite fast.
There are some examples in the 'examples' folder, but here is an example of the SQL API:
import pymonetdb
# set up a connection. arguments below are the defaults
connection = pymonetdb.connect(username="monetdb", password="monetdb", hostname="localhost", database="demo")
# create a cursor
cursor = connection.cursor()
# increase the rows fetched to increase performance (optional)
cursor.arraysize = 100
# execute a query (return the number of rows to fetch)
cursor.execute('SELECT * FROM tables')
26
# fetch only one row
cursor.fetchone()
(1062, 'schemas', 1061, None, 0, True, 0, 0)
# fetch the remaining rows
cursor.fetchall()
[(1067, 'types', 1061, None, 0, True, 0, 0),
(1076, 'functions', 1061, None, 0, True, 0, 0),
(1085, 'args', 1061, None, 0, True, 0, 0),
(1093, 'sequences', 1061, None, 0, True, 0, 0),
(1103, 'dependencies', 1061, None, 0, True, 0, 0),
(1107, 'connections', 1061, None, 0, True, 0, 0),
(1116, '_tables', 1061, None, 0, True, 0, 0),
...
(4141, 'user_role', 1061, None, 0, True, 0, 0),
(4144, 'auths', 1061, None, 0, True, 0, 0),
(4148, 'privileges', 1061, None, 0, True, 0, 0)]
# Show the table meta data
cursor.description
[('id', 'int', 4, 4, None, None, None),
('name', 'varchar', 12, 12, None, None, None),
('schema_id', 'int', 4, 4, None, None, None),
('query', 'varchar', 168, 168, None, None, None),
('type', 'smallint', 1, 1, None, None, None),
('system', 'boolean', 5, 5, None, None, None),
('commit_action', 'smallint', 1, 1, None, None, None),
('temporary', 'tinyint', 1, 1, None, None, None)]
If you would like to communicate with the database at a lower level you can use the MAPI library:
from pymonetdb import mapi
mapi_connection = mapi.Connection()
mapi_connection.connect(hostname="localhost", port=50000, username="monetdb", password="monetdb", database="demo", language="sql", unix_socket=None, connect_timeout=-1)
mapi_connection.cmd("sSELECT * FROM tables;")
...