The MonetDB python SQL client API is the native python client API for MonetDB. This API library is cross-platform and doesn't depend on any MonetDB libraries. It has support for python 3.7+ and is Python DB API 2.0 compatible.
To install the MonetDB python API run the following shell command:
$ pip install pymonetdb
That's all, now you are ready to start using the API.
Besides the functionality required by DB API 2.0, pymonetdb also provides some MonetDB-specific functionality, in particular file data transfers. See the documentation on how to use these extensions.
The pymonetdb source code is well documented.
If you encouter any issues with this library, please report them on github.com/MonetDB/pymonetdb/issues.
Tip: We recommend that you use virtual environments to avoid polluting your global python installation.
Below is an interactive example on how to use the pymonetdb API which should get you started quite fast.
There are more examples in the 'examples' folder.
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)]
# when done, close the cursor to release resources on the server
cursor.close()
# do not forget to close the connection (to release resources on the server)
connection.close()
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;")
...