def monetSchema(tbl,
host=os.getenv('MAPIHOST', 'localhost'),
port=int(os.getenv('MAPIPORT', '50000')),
database=os.getenv('TSTDB', 'demo'),
username='******',
password='******'):
dbh = pymonetdb.connect(hostname=host,
port=port,
database=database,
username=username,
password=password,
autocommit=True)
cursor = dbh.cursor()
drop = 'drop table %s' % tbl
create = 'create table %s (' \
'p01000 char(4) not null, ' \
'p01001 CHAR(2) NOT NULL, ' \
'p01002 CHAR(2) NOT NULL, ' \
'p01003 CHAR(2) NOT NULL, ' \
'p01004 CHAR(8) NOT NULL, ' \
'p01005 CHAR(8) NOT NULL, ' \
'p01006 CHAR(1) NOT NULL, ' \
'p01007 CHAR(1) NOT NULL, ' \
'p01008 CHAR(8) NOT NULL, ' \
'p01009 CHAR(8) NOT NULL, ' \
'p01010 CHAR(8) NOT NULL, ' \
'p01011 CHAR(8) NOT NULL, ' \
'p01012 CHAR(6) NOT NULL, ' \
'p01013 CHAR(7) NOT NULL, ' \
'p01014 CHAR(2) NOT NULL, ' \
'p01015 CHAR(3) NOT NULL, ' \
'p01016 CHAR(8) NOT NULL, ' \
'p01017 CHAR(10) NOT NULL, ' \
'p01018 CHAR(3) NOT NULL, ' \
'p01019 DECIMAL(11,3) NOT NULL)' % tbl
for i in range(1000):
for j in range(100):
try:
cursor.execute(drop)
except pymonetdb.OperationalError as e1:
if 'no such table' in e1.args[0]:
break
except pymonetdb.ProgrammingError as e2:
pass
else:
break
time.sleep(0.5)
for j in range(100):
try:
cursor.execute(create)
except pymonetdb.OperationalError as e1:
if 'already in use' in e1.args[0]:
break
except pymonetdb.ProgrammingError as e2:
pass
else:
break
time.sleep(0.5)
cursor.close()
dbh.close()
def add_connection(self, conn_id, username='******', password='******'):
if self.conn_map.get(conn_id, None) is None:
hostname = self.hostname
port = self.port
database = self.database
language = self.language
if language == 'sql':
dbh = pymonetdb.connect(username=username,
password=password,
hostname=hostname,
port=port,
database=database,
autocommit=True)
crs = dbh.cursor()
else:
dbh = malmapi.Connection()
dbh.connect(database=database,
username=username,
password=password,
language=language,
hostname=hostname,
port=port)
crs = MapiCursor(dbh)
conn = SQLLogicConnection(conn_id,
dbh=dbh,
crs=crs,
language=language)
self.conn_map[conn_id] = conn
return conn
def connection():
_conn = pymonetdb.connect(username='******',
hostname='localhost',
password='******',
database='db',
port='50000')
yield _conn
def clean_tables():
cursor = _conn.cursor()
cursor.execute(
'SELECT NAME, TYPE FROM SYS.TABLES WHERE SYSTEM=0 ORDER BY TYPE DESC'
)
has_cleaned = False
for table in cursor.fetchall():
has_cleaned = True
_conn.execute(f'DROP TABLE {table[0]}' if table[1] ==
0 else f'DROP VIEW {table[0]}')
return has_cleaned
max_retry = 2
has_cleaned = True
tries = 0
while tries < max_retry and has_cleaned:
has_cleaned = clean_tables()
tries += 1
_conn.close()
def load(file_path):
connection = pymonetdb.connect(username="******",
password="******",
hostname="localhost",
database="demo")
cursor = connection.cursor()
try:
cursor.execute("drop table %s" % table_name)
except:
print "table not exist can not drop"
try:
cursor.execute("create table if not exists %s (name VARCHAR(256));" %
table_name)
connection.commit()
except:
print "table exists"
f = open(file_path)
count = 0
for line in f:
pos = line.find('|')
line = line[pos + 1:]
line = line.strip()
line = line.replace("'", "")
count += 1
try:
cmd = """INSERT INTO %s VALUES ('%s');""" % (table_name, line)
cursor.execute(cmd)
if count % 1000 == 0:
connection.commit()
except:
print cmd
connection.commit()
cmd = "select count(*) from %s" % table_name
cursor.execute(cmd)
print cursor.fetchone()
def create_connection(self):
conn = pymonetdb.connect(username=self.config["username"],
password=self.config['password'],
hostname=self.config["host"],
port=self.config['port'],
database=self.config['database-name'])
return conn
def open_connection(database=CTTDB,
port=MAPIPORT,
hostname=TSTHOSTNAME,
username=TSTUSERNAME,
password=TSTPASSWORD,
autocommit=False):
# Connect to the database
connect_args = ()
connect_kwargs = dict(database=database,
port=port,
hostname=hostname,
username=username,
password=password,
autocommit=autocommit)
db = None
try:
db = pymonetdb.connect(*connect_args, **connect_kwargs)
except socket_error as serr:
if serr.errno != errno.ECONNREFUSED:
# Not the error we are looking for, re-raise
raise serr
# connection refused
# handle here
msg = "You maybe forgot to start monetdb, try this command:\n"
msg += "\tmonetdbd start <fullpath/to/monetdb/databases>\n"
msg += "like this example:\n\tmonetdbd start /home/patechoc/monetdb\n"
logging.critical(msg)
sys.exit()
return db
def create_workers(fn_template, nworkers, cmovies, ratings_table_def_fk):
workers = []
for i in range(nworkers):
workerport = freeport()
workerdbname = 'worker_{}'.format(i)
workerrec = {
'num': i,
'port': workerport,
'dbname': workerdbname,
'dbfarm': os.path.join(TMPDIR, workerdbname),
'mapi': 'mapi:monetdb://localhost:{}/{}/sys/ratings'.format(workerport, workerdbname),
}
os.mkdir(workerrec['dbfarm'])
workerrec['proc'] = process.server(mapiport=workerrec['port'], dbname=workerrec['dbname'], dbfarm=workerrec['dbfarm'], stdin=process.PIPE, stdout=process.PIPE)
workerrec['conn'] = pymonetdb.connect(database=workerrec['dbname'], port=workerport, autocommit=True)
filename = fn_template.format(workerrec['num'])
t = threading.Thread(target=worker_load, args=[filename, workerrec, cmovies, ratings_table_def_fk])
t.start()
workerrec['loadthread'] = t
workers.append(workerrec)
for wrec in workers:
wrec['loadthread'].join()
return workers
def connect(self,
username='******',
password='******',
hostname='localhost',
port=None,
database='demo',
language='sql'):
self.language = language
self.hostname = hostname
self.port = port
self.database = database
if language == 'sql':
self.dbh = pymonetdb.connect(username=username,
password=password,
hostname=hostname,
port=port,
database=database,
autocommit=True)
self.crs = self.dbh.cursor()
else:
dbh = malmapi.Connection()
dbh.connect(database=database,
username=username,
password=password,
language=language,
hostname=hostname,
port=port)
self.crs = MapiCursor(dbh)
def connect(autocommit):
return pymonetdb.connect(database = os.getenv('TSTDB'),
hostname = '127.0.0.1',
port = int(os.getenv('MAPIPORT')),
username = '******',
password = '******',
autocommit = autocommit)
def connect(username, password):
return pymonetdb.connect(database = os.getenv('TSTDB'),
hostname = 'localhost',
port = int(os.getenv('MAPIPORT')),
username = username,
password = password,
autocommit = True)
def exec_query(pid):
dbh = None
try:
# sleep for some random time less than 1 sec. to avoid immediate
# transaction conflicts on the `create merge table`
time.sleep(random.random())
dbh = pymonetdb.connect(database=os.environ['TSTDB'],
port=int(os.environ['MAPIPORT']),
hostname=os.environ['MAPIHOST'],
autocommit=True)
#dbh = pymonetdb.connect(database = 'demo', port=60000, autocommit=True)
cur = dbh.cursor()
cur.execute(
'create merge table concurrent_mt.cncrnt_mrg_tbl{} (like concurrent_mt.base_tbl);'
.format(pid))
cur.execute(
'alter table concurrent_mt.cncrnt_mrg_tbl{} add table base_tbl;'.
format(pid))
rowcnt = cur.execute(
'select * from concurrent_mt.cncrnt_mrg_tbl{};'.format(pid))
if rowcnt != 2:
raise ValueError('select * from concurrent_mt.cncrnt_mrg_tbl{}: 2 rows expected, got {}: {}'\
.format(pid, rowcnt, str(cur.fetchall())))
cur.execute('drop table concurrent_mt.cncrnt_mrg_tbl{};'.format(pid))
except pymonetdb.exceptions.Error as e:
if "transaction is aborted because of concurrency conflicts" not in str(
e):
print(e)
finally:
if dbh is not None:
dbh.close()
def initialize(args):
server = mapi.Connection()
server.connect(hostname="127.0.0.1",
port=50000,
username="******",
password="******",
database=args[1],
language="sql")
global_node.append(server)
global_node.append(args[1])
global_node.append(
pymonetdb.connect(username="******",
password="******",
port=50000,
hostname="127.0.0.1",
database=args[1]))
for db in args[2:]:
server = mapi.Connection()
server.connect(hostname="127.0.0.1",
port=50000,
username="******",
password="******",
database=db,
language="sql")
local_nodes.append((server, db))
def connect(self,
username='******',
password='******',
hostname='localhost',
port=None,
database='demo',
language='sql',
timeout=None):
self.language = language
self.hostname = hostname
self.port = port
self.database = database
self.timeout = timeout
if language == 'sql':
self.dbh = pymonetdb.connect(username=username,
password=password,
hostname=hostname,
port=port,
database=database,
autocommit=True)
self.dbh.set_uploader(transfer_handler)
self.dbh.set_downloader(transfer_handler)
self.crs = self.dbh.cursor()
else:
dbh = malmapi.Connection()
dbh.connect(database=database,
username=username,
password=password,
language=language,
hostname=hostname,
port=port)
self.crs = MapiCursor(dbh)
def create_revisions_table(db_name,
tbl_name: str = 'mroll_revisions',
hostname: str = '127.0.0.1',
port: int = 50000,
username: str = 'monetbd',
password: str = 'monetdb') -> None:
"""
Creates revisons table with columns (id string, description string, ts timestamp)
"""
conn = pymonetdb.connect(db_name,
hostname=hostname,
port=port,
username=username,
password=password)
sql = """
create table sys."{}"(id string, description string, ts timestamp);
alter table sys."{}" add constraint mroll_rev_pk primary key (id);
""".format(tbl_name, tbl_name)
try:
conn.execute(sql)
conn.commit()
except Exception as e:
conn.rollback()
raise (e)
finally:
conn.close()
def add_revisions(revisions: List[Revision],
db_name,
tbl_name: str = 'mroll_revisions',
hostname: str = '127.0.0.1',
port: int = 50000,
username: str = 'monetbd',
password: str = 'monetdb') -> None:
"""
Executes upgrade_sql and adds new revision records.
"""
conn = pymonetdb.connect(db_name,
hostname=hostname,
port=port,
username=username,
password=password)
cur = conn.cursor()
sql = """
insert into sys."{}" values (%s, %s, %s)
""".format(tbl_name)
try:
for rev in revisions:
for stmt in rev.upgrade_stmts:
conn.execute(stmt)
cur.execute(sql, (rev.id, rev.description, rev.ts))
conn.commit()
except Exception as e:
conn.rollback()
raise RevisionOperationError(rev, stmt, repr(e))
finally:
conn.close()
def remove_revisions(revisions: List[Revision],
db_name,
tbl_name: str = 'mroll_revisions',
hostname: str = '127.0.0.1',
port: int = 50000,
username: str = 'monetbd',
password: str = 'monetdb') -> None:
"""
Removes list of revisions in one transaction.
"""
conn = pymonetdb.connect(db_name,
hostname=hostname,
port=port,
username=username,
password=password)
cur = conn.cursor()
sql = """delete from sys."{}" where id=%s""".format(tbl_name)
try:
for rev in revisions:
for stmt in rev.downgrade_stmts:
conn.execute(stmt)
cur.execute(sql, (rev.id, ))
conn.commit()
except Exception as e:
conn.rollback()
raise RevisionOperationError(rev, stmt, repr(e))
finally:
conn.close()
def create_db(tbname):
'''
'''
conn = None
try:
# print('Connecting to the MonetDB database ...')
# set up a connection. arguments below are the defaults
conn = pymonetdb.connect(username="******",
password="******",
hostname="localhost",
database="demo")
# create a cursor
cur = conn.cursor()
# create a table
cur.execute(
'CREATE TABLE IF NOT EXISTS ' + tbname +
' (voxid SERIAL, x INTEGER NOT NULL, y INTEGER NOT NULL, z INTEGER NOT NULL, objID INTEGER);'
)
# close connection
cur.close()
conn.commit()
except (Exception, pymonetdb.exceptions.DatabaseError) as error:
print(error)
finally:
return conn
def create_workers(fn_template, nworkers, cmovies, ratings_table_def_fk):
workers = []
for i in range(nworkers):
workerport = freeport()
workerdbname = 'worker_{}'.format(i)
workerrec = {
'num': i,
'port': workerport,
'dbname': workerdbname,
'dbfarm': os.path.join(TMPDIR, workerdbname),
'mapi': 'mapi:monetdb://localhost:{}/{}/sys/ratings'.format(workerport, workerdbname),
}
os.mkdir(workerrec['dbfarm'])
workerrec['proc'] = process.server(mapiport=workerrec['port'], dbname=workerrec['dbname'], dbfarm=workerrec['dbfarm'], stdin=process.PIPE, stdout=process.PIPE)
workerrec['conn'] = pymonetdb.connect(database=workerrec['dbname'], port=workerport, autocommit=True)
filename = fn_template.format(workerrec['num'])
t = threading.Thread(target=worker_load, args=[filename, workerrec, cmovies, ratings_table_def_fk])
t.start()
workerrec['loadthread'] = t
workers.append(workerrec)
for wrec in workers:
wrec['loadthread'].join()
return workers
def create_connection(self):
conn = pymonetdb.connect(username="******",
password="******",
hostname="localhost",
port=50000,
database="crossfilter-eval-db")
return conn
def setUp(self):
self.setup_res = self.run_setup_cmd()
self.set_config_db_name(self.db_name)
self.init_res = self.run_init_cmd()
conn = pymonetdb.connect(self.db_name)
conn.execute('create schema if not exists test;')
conn.commit()
def setUp(self):
db = pymonetdb.connect(autocommit=False, **test_args)
self.connection = db
self.cursor = db.cursor()
self.BLOBText = ''.join([chr(i) for i in range(33, 127)] * 100)
self.BLOBBinary = bytes(range(256)) * 16
self.BLOBUText = ''.join([chr(i) for i in range(1, 16384)])
def connect(username, password):
return pymonetdb.connect(database = os.getenv('TSTDB'),
hostname = 'localhost',
port = int(os.getenv('MAPIPORT')),
username = username,
password = password,
autocommit = True)
def connect(autocommit):
return pymonetdb.connect(database = os.getenv('TSTDB'),
hostname = '127.0.0.1',
port = int(os.getenv('MAPIPORT')),
username = '******',
password = '******',
autocommit = autocommit)
def setUp(self):
self.con = pymonetdb.connect(database=TSTDB, port=MAPIPORT,
hostname=TSTHOSTNAME,
username=TSTUSERNAME,
password=TSTPASSWORD)
cursor = self.con.cursor()
cursor.execute('create table bla (s VARCHAR(1000))')
def main():
mstdbh = None
mstcur = None
try:
mstdbh = pymonetdb.connect(database=db, port=pt, autocommit=True)
mstcur = mstdbh.cursor()
query = 'create procedure sleep(i int) external name alarm.sleep;'
mstcur.execute(query)
query = 'select username, status, query from sys.queue() where status = \'running\' order by query'
expected_res = [(
'monetdb', 'running',
'select username, status, query from sys.queue() where status = \\\'running\\\' order by query\n;'
)]
rowcnt = mstcur.execute(query)
res = mstcur.fetchall()
if rowcnt != len(expected_res) or res != expected_res:
do_error(query, res, expected_res)
# Setup a list of processes that we want to run
jobs = [mp.Process(target=worker_task, args=()) for x in range(3)]
# Run processes
[p.start() for p in jobs]
# Check the long running query, but lets first wait for a moment for the
# workers to start with their queries
mstcur.execute('call sys.sleep(500)')
query = 'select username, status, query from sys.queue() where query like \'call sys.sleep(5000)%\' order by query'
expected_res = [
('monetdb', 'running', 'call sys.sleep(' + SLEEP_TIME + ')\n;'),
('monetdb', 'running', 'call sys.sleep(' + SLEEP_TIME + ')\n;'),
('monetdb', 'running', 'call sys.sleep(' + SLEEP_TIME + ')\n;')
]
rowcnt = mstcur.execute(query)
res = mstcur.fetchall()
if rowcnt != len(expected_res) or res != expected_res:
do_error(query, res, expected_res)
# Exit the completed processes
[p.join() for p in jobs]
# sys.queue() should have been expanded from 4 to 8, so we should be able
# to have 7 queries in the queue
mstcur.execute('select 6')
mstcur.execute('select 7')
query = 'select count(*) from sys.queue()'
expected_res = 7
rowcnt = mstcur.execute(query)
res = mstcur.fetchall()
if rowcnt != 1 or res[0][0] != expected_res:
do_error(query, res, expected_res)
except pymonetdb.exceptions.Error as e:
print(e)
finally:
if mstdbh is not None:
if mstcur is not None:
mstcur.execute('drop procedure sleep;')
mstdbh.close()
def setUp(self):
self.con = pymonetdb.connect(database=TSTDB,
port=MAPIPORT,
hostname=TSTHOSTNAME,
username=TSTUSERNAME,
password=TSTPASSWORD)
cursor = self.con.cursor()
cursor.execute('create table bla (s VARCHAR(1000))')
def __init__(self):
self._connection = pymonetdb.connect(
hostname=node_config.monetdb.ip,
port=node_config.monetdb.port,
username=node_config.monetdb.username,
password=node_config.monetdb.password,
database=node_config.monetdb.database,
)
def open(self):
self._connection = connect(
hostname=self.hostname,
port=self.port,
username=self.user_name,
password=self.user_password,
database=self.database, autocommit=False)
self._cursor = self._connection.cursor()
def create_connection(self):
connection = pymonetdb.connect(username="******",
password="******",
hostname="localhost",
port=50000,
database="demo")
cursor = connection.cursor()
return connection, cursor
def connect(self, username='******', password='******',
hostname='localhost', port=None, database='demo'):
self.dbh = pymonetdb.connect(username=username,
password=password,
hostname=hostname,
port=port,
database=database,
autocommit=True)
self.crs = self.dbh.cursor()
def _get_connection(self):
conn = pymonetdb.connect(
hostname=self.container.config['MONETDB_HOST'],
username=self.container.config['MONETDB_USER'],
password=self.container.config['MONETDB_PASSWORD'],
database=self.container.config['MONETDB_DATABASE'],
autocommit=True)
return conn
def __init__(self, barrier1, barrier2):
self._wconn = pymonetdb.connect(
port=int(os.getenv('MAPIPORT', '50000')),
database=os.getenv('TSTDB', 'demo'),
hostname=os.getenv('MAPIHOST', 'localhost'),
autocommit=True)
self._wcursor = self._wconn.cursor()
self._barrier1 = barrier1
self._barrier2 = barrier2
threading.Thread.__init__(self)
def drop_all(self):
conn = pymonetdb.connect(self.db_name)
try:
conn.execute("drop table if exists sys.mroll_revisions;")
conn.execute("drop schema test cascade")
conn.commit()
except Exception as e:
print(e)
finally:
conn.close()
def connect(self):
self.initConnection()
try:
self.connector = pymonetdb.connect(hostname=self.hostname, username=self.user, password=self.password, database=self.db, port=self.port, connect_timeout=conf.timeout)
except pymonetdb.OperationalError as ex:
raise SqlmapConnectionException(getSafeExString(ex))
self.initCursor()
self.printConnected()
from __future__ import print_function
import pymonetdb
import os
dbh = pymonetdb.connect(database = os.environ['TSTDB'],
port = int(os.environ['MAPIPORT']),
hostname = os.environ['MAPIHOST'])
cursor = dbh.cursor()
try:
cursor.execute('create table python_table (i smallint,s string)')
s = [0, 'row2']
try:
x = cursor.execute("insert into python_table VALUES (%s, %s)", s)
except:
print('execute failed with list')
else:
print(x)
s = [1, 'row2']
try:
x = cursor.execute("insert into python_table VALUES (%s, %s)", s)
except:
print('execute failed with list')
else:
print(x)
s = {'i': 2, 's': 'row1'}
def __init__(self, client):
threading.Thread.__init__ (self)
self.client = client
self.dbh = pymonetdb.connect(port=int(os.getenv('MAPIPORT')),hostname=os.getenv('MAPIHOST'),database=os.getenv('TSTDB'))
def freeport():
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.bind(('', 0))
port = sock.getsockname()[1]
sock.close()
return port
farm_dir = tempfile.mkdtemp()
os.mkdir(os.path.join(farm_dir, 'db1'))
node1_port = freeport()
node1_proc = process.server(mapiport=node1_port, dbname='db1', dbfarm=os.path.join(farm_dir, 'db1'), stdin=process.PIPE, stdout=process.PIPE, stderr=process.PIPE)
node1_conn = pymonetdb.connect(database='db1', port=node1_port, autocommit=True)
node1_cur = node1_conn.cursor()
os.mkdir(os.path.join(farm_dir, 'db2'))
node2_port = freeport()
node2_proc = process.server(mapiport=node2_port, dbname='db2', dbfarm=os.path.join(farm_dir, 'db2'), stdin=process.PIPE, stdout=process.PIPE, stderr=process.PIPE)
node2_conn = pymonetdb.connect(database='db2', port=node2_port, autocommit=True)
node2_cur = node2_conn.cursor()
node2_cur.execute('CREATE TABLE "tb2" ("col1" int, "col2" int);')
node2_cur.execute('INSERT INTO "tb2" VALUES (1, 1), (2, 2), (3, 3);')
node1_cur.execute('CREATE MERGE TABLE "tb1" ("col1" int, "col2" int) PARTITION BY RANGE ON ("col1");')
node1_cur.execute('CREATE REMOTE TABLE "tb2" ("col1" int, "col2" int) ON \'mapi:monetdb://localhost:'+str(node2_port)+'/db2\';')
try:
node1_cur.execute('ALTER TABLE "tb1" ADD TABLE "tb2" AS PARTITION FROM 0 TO 1;') # error
filename = fn_template.format(workerrec['num'])
t = threading.Thread(target=worker_load, args=[filename, workerrec, cmovies, ratings_table_def_fk])
t.start()
workerrec['loadthread'] = t
workers.append(workerrec)
for wrec in workers:
wrec['loadthread'].join()
return workers
# Start supervisor database
supervisorport = freeport()
os.mkdir(os.path.join(TMPDIR, "supervisor"))
supervisorproc = process.server(mapiport=supervisorport, dbname="supervisor", dbfarm=os.path.join(TMPDIR, "supervisor"), stdin=process.PIPE, stdout=process.PIPE)
supervisorconn = pymonetdb.connect(database='supervisor', port=supervisorport, autocommit=True)
supervisor_uri = "mapi:monetdb://localhost:{}/supervisor".format(supervisorport)
c = supervisorconn.cursor()
# Create the movies table and load the data
movies_filename=os.getenv("TSTDATAPATH")+"/netflix_data/movies.csv"
movies_create = "CREATE TABLE movies {}".format(MOVIES_TABLE_DEF)
c.execute(movies_create)
load_movies = "COPY INTO movies FROM '{}' USING DELIMITERS ',','\n','\"'".format(movies_filename)
c.execute(load_movies)
# Declare the ratings merge table on supervisor
mtable = "CREATE MERGE TABLE ratings {}".format(RATINGS_TABLE_DEF)
c.execute(mtable)
# Create the workers and load the ratings data
def freeport():
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.bind(('', 0))
port = sock.getsockname()[1]
sock.close()
return port
ssbmpath = os.path.join(os.environ['TSTSRCBASE'], 'sql', 'benchmarks', 'ssbm', 'Tests')
ssbmdatapath = os.path.join(ssbmpath, 'SF-0.01')
tmpdir = tempfile.mkdtemp()
os.mkdir(os.path.join(tmpdir, 'master'))
masterport = freeport()
masterproc = process.server(mapiport=masterport, dbname="master", dbfarm=os.path.join(tmpdir, 'master'), stdin = process.PIPE, stdout = process.PIPE)
masterconn = pymonetdb.connect(database='', port=masterport, autocommit=True)
# split lineorder table into one file for each worker
# this is as portable as an anvil
lineordertbl = os.path.join(ssbmdatapath, 'lineorder.tbl')
lineorderdir = os.path.join(tmpdir, 'lineorder')
if os.path.exists(lineorderdir):
shutil.rmtree(lineorderdir)
if not os.path.exists(lineorderdir):
os.makedirs(lineorderdir)
inputData = open(lineordertbl, 'r').read().split('\n')
linesperslice = len(inputData) // nworkers + 1
i = 0
for lines in range(0, len(inputData), linesperslice):
outputData = inputData[lines:lines+linesperslice]
outputStr = '\n'.join(outputData)
def query(conn, sql):
print(sql)
cur = conn.cursor()
cur.execute(sql)
r = cur.fetchall()
cur.close()
return r
farm_dir = tempfile.mkdtemp()
os.mkdir(os.path.join(farm_dir, 'node1'))
os.mkdir(os.path.join(farm_dir, 'node2'))
# node1 is the worker
prt1 = freeport()
prc1 = process.server(mapiport=prt1, dbname='node1', dbfarm=os.path.join(farm_dir, 'node1'), stdin=process.PIPE, stdout=process.PIPE, stderr=process.PIPE)
conn1 = pymonetdb.connect(database='node1', port=prt1, autocommit=True)
q = "create table s1 (i int)"
print(q); conn1.execute(q)
q = "insert into s1 values (23), (42)"
print(q); conn1.execute(q)
q = "create table t1 (s varchar(10))"
print(q); conn1.execute(q)
q = "insert into t1 values ('abc'), ('efg')"
print(q); conn1.execute(q)
# node2 is the master
prt2 = freeport()
prc2 = process.server(mapiport=prt2, dbname='node2', dbfarm=os.path.join(farm_dir, 'node2'), stdin=process.PIPE, stdout=process.PIPE, stderr=process.PIPE)
conn2 = pymonetdb.connect(database='node2', port=prt2, autocommit=True)
import pymonetdb
import os
c = pymonetdb.connect(port = int(os.getenv('MAPIPORT')),
database = os.getenv('TSTDB'))
c.close()
c = pymonetdb.connect(port = int(os.getenv('MAPIPORT')),
database = os.getenv('TSTDB'),
user = '******')
c.close()
c = pymonetdb.connect(port = int(os.getenv('MAPIPORT')),
database = os.getenv('TSTDB'),
user = '******',
username = '******')
c.close()
c = pymonetdb.connect(port = int(os.getenv('MAPIPORT')),
database = os.getenv('TSTDB'),
username = '******')
c.close()
c = pymonetdb.connect(port = int(os.getenv('MAPIPORT')),
database = os.getenv('TSTDB'))
c.close()
c = pymonetdb.connect(port = int(os.getenv('MAPIPORT')),
database = os.getenv('TSTDB'),
host = os.getenv('HOST'))
c.close()
# import the SQL module
import pymonetdb
# set up a connection. arguments below are the defaults
connection = pymonetdb.connect(username="******", password="******", hostname="localhost", database="fixture")
# create a cursor
cursor = connection.cursor()
# increase the rows fetched to increase performance (optional)
cursor.arraysize = 100000000
# execute a query (return the number of rows to fetch)
cursor.execute('SELECT * FROM test')
result = cursor.fetchall()
print(result)
for r in result:
print(r)
print(r[1])
from __future__ import print_function
import pymonetdb, os
dbh = pymonetdb.connect(port=int(os.getenv('MAPIPORT')),hostname=os.getenv('MAPIHOST'),database=os.getenv('TSTDB'))
cursor = dbh.cursor();
cursor.execute('create table bug3439 (ts timestamp)')
cursor.execute("insert into bug3439 values ('2014-04-24 17:12:12.415')")
cursor.execute('select * from bug3439')
print(cursor.fetchall())
cursor.execute('drop table bug3439')
cursor.execute('create table bug3439 (ts timestamp with time zone)')
cursor.execute("insert into bug3439 values ('2014-04-24 17:12:12.415 -02:00')")
cursor.execute('select * from bug3439')
print(cursor.fetchall())
cursor.execute('drop table bug3439')
cursor.execute('create table bug3439 (dt date)')
cursor.execute("insert into bug3439 values ('2014-04-24')")
cursor.execute('select * from bug3439')
print(cursor.fetchall())
cursor.execute('drop table bug3439')
cursor.execute('create table bug3439 (ts time)')
cursor.execute("insert into bug3439 values ('17:12:12.415')")
cursor.execute('select * from bug3439')
print(cursor.fetchall())
cursor.execute('drop table bug3439')
#!/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 - 2019 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('CREATE TABLE python_int128 (i HUGEINT);')
cursor.execute('INSERT INTO python_int128 VALUES (123456789098765432101234567890987654321);')
cursor.execute('SELECT * FROM python_int128;')
result = cursor.fetchall()
print(result)
print(result[0])
print(result[0][0])
cursor.execute('DROP TABLE python_int128;')
import pymonetdb
import os
port = int(os.environ['MAPIPORT'])
db = os.environ['TSTDB']
dbh = pymonetdb.connect(port=port,database=db,autocommit=True)
cursor = dbh.cursor()
cursor.execute("explain select count(*) from sys.tables")
cursor.fetchone()
