How to use pymonetdb - 8 common examples

# License, v. 2.0.  If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
#
# Copyright 1997 - July 2008 CWI, August 2008 - 2018 MonetDB B.V.

from __future__ import print_function

import logging

#configure the logger, so we can see what is happening
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger('monetdb')

import pymonetdb

x = pymonetdb.connect(username="monetdb", password="monetdb", hostname="localhost", database="demo")
c = x.cursor()

# some basic query
c.arraysize=100
c.execute('select * from tables')
results = c.fetchall()
x.commit()
print(results)

import pymonetdb
import os, sys, time

port = int(os.environ['MAPIPORT'])
db = os.environ['TSTDB']
host = os.environ['MAPIHOST']

dbh = pymonetdb.connect(hostname=host,port=port,database=db,autocommit=True)

cursor = dbh.cursor();

cursor.execute('select p.*, "location", "count", "column" from storage(), (select value from env() where name = \'gdk_dbpath\') as p where "table"=\'lineitem\' order by "column"');
res = (cursor.fetchall())
for (dbpath, fn, count, column) in res:
    fn =  os.path.join(dbpath, 'bat', fn + '.tail');
    print(column, int(os.path.getsize(fn)), count)

cursor.execute('delete from lineitem;');

cursor.execute('select "column", "count" from storage() where "table" = \'lineitem\' order by "column"');
print(cursor.fetchall())

cursor.execute('select p.*, "location", "count", "column" from storage(), (select value from env() where name = \'gdk_dbpath\') as p where "table"=\'lineitem\' order by "column"');
res = (cursor.fetchall())

#!/usr/bin/env python

# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0.  If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
#
# Copyright 1997 - July 2008 CWI, August 2008 - 2018 MonetDB B.V.

from __future__ import print_function

import pymonetdb
import sys

dbh = pymonetdb.connect(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True)

cursor = dbh.cursor();
cursor.execute('select 1;')
print(cursor.fetchall())

cursor = dbh.cursor();
cursor.execute('select 2;')
print(cursor.fetchone())

# deliberately executing a wrong SQL statement:
try:
    cursor.execute('( xyz 1);')
except pymonetdb.OperationalError as e:
    print(e)

cursor.execute('create table python_table (i smallint,s string);');

import pymonetdb
import os, sys, time

port = int(os.environ['MAPIPORT'])
db = os.environ['TSTDB']
host = os.environ['MAPIHOST']

dbh = pymonetdb.connect(port=port,database=db,hostname=host,autocommit=True)

cursor = dbh.cursor();

cursor.execute('select p.*, "location", "count", "column" from storage(), (select value from env() where name = \'gdk_dbpath\') as p where "table"=\'lineitem\' order by "column"');
res = (cursor.fetchall())
for (dbpath, fn, count, column) in res:
    fn =  os.path.join(dbpath, 'bat', fn + '.tail');
    print(column, int(os.path.getsize(fn)), count)

# file, You can obtain one at http://mozilla.org/MPL/2.0/.
#
# Copyright 1997 - July 2008 CWI, August 2008 - 2018 MonetDB B.V.

import time

#configure the logger, so we can see what is happening
#import logging
#logging.basicConfig(level=logging.DEBUG)
#logger = logging.getLogger('monetdb')


import pymonetdb

t = time.time()
x = pymonetdb.connect(database="demo")
c = x.cursor()
c.arraysize=10000
c.execute('select * from tables, tables')
results = c.fetchall()

elif o == '--passwd':
            password = a
        elif o == '--language':
            language = a
        elif o == '--database':
            database = a
        elif o == '--encoding':
            encoding = a

    if encoding is None:
        import locale
        encoding = locale.getlocale()[1]
        if encoding is None:
            encoding = locale.getdefaultlocale()[1]

    s = mapi.Server()

    s.connect(hostname = hostname,
              port = int(port),
              username = username,
              password = password,
              language = language,
              database = database)
    print("#mclient (python) connected to %s:%d as %s" % (hostname, int(port), username))
    fi = sys.stdin
    prompt = '%s>' % language

    sys.stdout.write(prompt.encode('utf-8'))
    line = fi.readline()
    if encoding != 'utf-8':
        prompt = unicode(prompt, 'utf-8').encode(encoding, 'replace')
    while line and line != "\q\n":

elif o == '--passwd':
            password = a
        elif o == '--language':
            language = a
        elif o == '--database':
            database = a
        elif o == '--encoding':
            encoding = a

    if encoding is None:
        import locale
        encoding = locale.getlocale()[1]
        if encoding is None:
            encoding = locale.getdefaultlocale()[1]

    s = mapi.Server()

    s.connect(hostname = hostname,
              port = int(port),
              username = username,
              password = password,
              language = language,
              database = database)
    print("#mclient (python) connected to %s:%d as %s" % (hostname, int(port), username))
    fi = sys.stdin
    prompt = '%s>' % language

    sys.stdout.write(prompt.encode('utf-8'))
    line = fi.readline()
    if encoding != 'utf-8':
        prompt = str(prompt, 'utf-8').encode(encoding, 'replace')
    while line and line != "\q\n":

import sys

dbh = pymonetdb.connect(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True)

cursor = dbh.cursor();
cursor.execute('select 1;')
print(cursor.fetchall())

cursor = dbh.cursor();
cursor.execute('select 2;')
print(cursor.fetchone())

# deliberately executing a wrong SQL statement:
try:
    cursor.execute('( xyz 1);')
except pymonetdb.OperationalError as e:
    print(e)

cursor.execute('create table python_table (i smallint,s string);');
cursor.execute('insert into python_table values ( 3, \'three\');');
cursor.execute('insert into python_table values ( 7, \'seven\');');
cursor.execute('select * from python_table;');
print(cursor.fetchall())

s = ((0, 'row1'), (1, 'row2'))
x = cursor.executemany("insert into python_table VALUES (%s, %s);", s)
print(x);

cursor.execute('drop table python_table;');