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 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;
tar xvf python-monetdb-188.8.131.52.tar.gz
python setup.py install
Create Test Table:
Now we will connect to database created in last post, you can change database to your own.
Create table using following SQL;
mclient -u monetdb -d mydatabase
CREATE TABLE "sys"."test" (
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.