MonetDB Basic Example with Python

Overview

When size of your application database grows into millions of records, distributed over different tables, and business intelligence/ science becomes the prevalent application domain, a column-store database management system is called for. Unlike traditional row-stores, such as MySQL and PostgreSQL, a column-store provides a modern and scale-able solution without calling for substantial hardware investments.

In earlier blog post we have compiled MonetDB from source tarball and connected to shell for testing SQL from SQL reference manual. Now we are going to explore python API for connecting to MonetDB Database and execute SQL commands. 

Python Package:

Python package hosted by monetDB itself is available at pypi repository and can be installed using following commands.

pip install python-monetdb

of download source tarball and install manually using;

wget https://pypi.python.org/packages/source/p/python-monetdb/python-monetdb-11.19.3.2.tar.gz#md5=9031fd2ea4b86a2bc2d5dd1ab4b10a77
tar xvf python-monetdb-11.19.3.2.tar.gz
cd python-monetdb-11.19.3.2
python setup.py install

Create Test Table:

Now we will connect to database created in last post, you can change database to your own.

mclient -u monetdb -d mydatabase

Create table using following SQL;

CREATE TABLE "sys"."test" (
    "id"   INTEGER,
    "data" VARCHAR(30)
);

Now here is python code to insert data:

 import monetdb.sql  
 connection = monetdb.sql.connect(username="monetdb", password="monetdb", hostname="localhost", database="mydatabase")  
 cursor = connection.cursor()  
 cursor.arraysize = 100  
 for a in range(1, 200):    
   cursor.execute("INSERT into sys.test(id, data) values(%s, '%s')"%(a, 'testing %s'%a))  
 connection.commit()  
 cursor.execute("SELECT * FROM sys.test LIMIT 1")  
 # To Fetch all rows as list  
 print cursor.fetchall()  
 # To Fetch single row as list  
 print cursor.fetchone()  

You can perform all queries using cursor.execute. for queries and SQL use MonetDB SQL Reference manual.