Skip to main content

Native MonetDB client Python API

Project description

This version of the Python 3 MonetDB client includes some additional features to support bulk copy operations

Introduction

This is the new native Python client API. This API is cross-platform, and doesn’t depend on any monetdb libraries. It has support for Python 3.2+ and is Python DBAPI 2.0 compatible.

This is a fork of the code distributed with MonetDB that adds support for asynchronous operation (requires greenlet) and bulk I/O with COPY.

Installation

To install the MonetDB Python API run the following command from the python source directory:

# python setup.py install

That’s all, now you are ready to start using the API.

Documentation

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.

Examples

There are some examples in the ‘examples’ folder, but here are is a line by line example of the SQL API:

> # import the SQL module
> import monetdb.sql
>
> # set up a connection. arguments below are the defaults
> connection = monetdb.sql.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 monetdb import mapi
> server = mapi.Server()
> server.connect(hostname="localhost", port=50000, username="monetdb", password="monetdb", database="demo", language="sql")
> server.cmd("sSELECT * FROM tables;")
...

Copying a table from PostgreSQL (using the Psycopg2 driver) to MonetDB:

data = io.StringIO()

pg_cursor = pg_conn.cursor()
pg_cursor.copy_expert(
    """COPY {} TO STDOUT WITH CSV QUOTE '"' ESCAPE '\\' NULL ''""".format(pg_table), data)
data.seek(0)

monetdb_cursor = monetdb_conn.cursor()
monetdb_cursor.copy_from(data, monetdb_table, string_quote='"', null_string='')
monetdb_conn.commit()

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for dw-python-monetdb-async, version 12.0.2
Filename, size File type Python version Upload date Hashes
Filename, size dw_python_monetdb_async-12.0.2-py3-none-any.whl (26.5 kB) File type Wheel Python version py3 Upload date Hashes View hashes

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page