monetdb / pymonetdb Goto Github PK

The Python API for MonetDB

License: Mozilla Public License 2.0

Python 99.09% Shell 0.28% Makefile 0.63%

pymonetdb's Introduction

The MonetDB Database System

The MonetDB database was originally developed by the CWI database research group (see https://www.monetdb.org/). Development has now shifted to the spin-off company MonetDB Solutions.

Via the MonetDB project we have brought the MonetDB system in open source, where it is accessible at https://www.monetdb.org/easy-setup/. Even though development happens mostly in a company, the MonetDB database system will remain open source. It is available under the Mozilla Public License 2.0.

The MonetDB database system is a high-performance database kernel for query-intensive applications. The MonetDB source can be found at our Mercurial server. There is also a github mirror that is updated once an hour.

Building

MonetDB is built using the cmake program. It is recommended to build in a directory that is not inside the source tree. In order to build, use the following commands when inside your build directory:

cmake [options] /path/to/monetdb/source
cmake --build .
cmake --build . --target install

In order to install into a different directory than the default /usr/local, add the option -DCMAKE_INSTALL_PREFIX:

cmake -DCMAKE_INSTALL_PREFIX=/path/to/install/monetdb /path/to/monetdb/source
cmake --build .
cmake --build . --target install

Build Options

There are many options that can be used to select how MonetDB is to be built. Options can be turned ON or OFF using a -D flag on the first of the cmake command lines. Except when specified otherwise, options are ON when the relevant libraries can be found. Available options are:

Option	Explanation
ASSERT	Enable asserts (default=ON for development sources, OFF for tarball installation)
CINTEGRATION	Enable support for C UDFs (default=ON except on Windows)
CMAKE_SUMMARY	Show a summary of the cmake configuration (for debug purposes, default=OFF)
CMAKE_UNITTESTS	Build and run the unittest for the build system (default=OFF)
FITS	Enable support for FITS
GEOM	Enable support for geom module (using libgeos library)
INT128	Enable support for 128-bit integers (if compiler supports them)
NETCDF	Enable support for netcdf
ODBC	Compile the MonetDB ODBC driver
PY3INTEGRATION	Enable support for Python 3 integration into MonetDB
RINTEGRATION	Enable support for R integration into MonetDB
SANITIZER	Enable support for the GCC address sanitizer (default=OFF)
SHP	Enable support for ESRI Shapefiles
STRICT	Enable strict compiler flags (default=ON for development sources, OFF for tarball installation)
TESTING	Enable support for testing
WITH_BZ2	Include bz2 support
WITH_CMOCKA	Include cmocka support (default=OFF)
WITH_CURL	Include curl support
WITH_LZ4	Include lz4 support
WITH_LZMA	Include lzma support
WITH_OPENSSL	Include TLS support (secure client/server connection)
WITH_PCRE	Include pcre support
WITH_PROJ	Include proj support
WITH_READLINE	Include readline support
WITH_VALGRIND	Include valgrind support
WITH_XML2	Include xml2 support
WITH_ZLIB	Include zlib support

Required and Optional Packages

On Fedora, the following packages are required: bison, cmake, gcc, pkgconf, python3.

The following packages are optional but recommended: bzip2-devel, lz4-devel, openssl-devel, pcre-devel, readline-devel, xz-devel, zlib-devel.

The following packages are optional: cfitsio-devel, gdal-devel, geos-devel, libasan, libcmocka-devel, libcurl-devel, libxml2-devel, netcdf-devel, proj-devel, python3-devel, python3-numpy, R-core-devel, unixODBC-devel, valgrind-devel.

On Ubuntu and Debian the following packages are required: bison, cmake, gcc, pkg-config, python3.

The following packages are optional but recommended: libbz2-dev, liblz4-dev, libpcre3-dev, libreadline-dev, liblzma-dev, libssl-dev, zlib1g-dev.

The following packages are optional: libasan5, libcfitsio-dev, libcmocka-dev, libcurl4-gnutls-dev, libgdal-dev, libgeos-dev, libnetcdf-dev, libproj-dev, libxml2-dev, python3-dev, python3-numpy, r-base-dev, unixodbc-dev, valgrind.

cmake must be at least version 3.12, python must be at least version 3.5.

Bugs

We of course hope there aren't any, but if you do find one, you can report bugs in our github repository.

Please note that we do not accept github Pull Requests. See the developers page for instructions.

Copyright Notice

SPDX-License-Identifier: MPL-2.0

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/.

pymonetdb's People

Contributors

Stargazers

Watchers

pymonetdb's Issues

Connection refused log message when everything is fine

Hi Gijs,

In the 1.3.1 release I noticed a INFO:pymonetdb.mapi:Connection refused message when everything is fine.
Try this script for example on a clean instance (running on port 53001):

import logging
from pymonetdb.sql.connections import Connection as connect

logging.basicConfig(filename='testconn.log',level=logging.DEBUG)

conn = connect(hostname='localhost',database='testdb',port=53001,username='monetdb',password='monetdb')

c = conn.cursor()
c.execute("create table t1 (i int,d double)")
c.execute("insert into t1 (i,d) values (1,3.14)")
conn.commit()

c.execute("select * from t1")
r = c.fetchall()
print(r)

conn.close()

conn = connect(hostname='localhost',database='testdb',port=53001,username='monetdb',password='monetdb')

and you'll see a log file output like:

INFO:pymonetdb.mapi:Connection refused
DEBUG:pymonetdb.mapi:restarting authentication
DEBUG:pymonetdb.mapi:executing command Xauto_commit 0
DEBUG:pymonetdb.mapi:executing command Xsizeheader 1
DEBUG:pymonetdb.mapi:executing command Xreply_size 100
DEBUG:pymonetdb.mapi:executing command screate table t1 (i int,d double)
;
DEBUG:pymonetdb.mapi:executing command sinsert into t1 (i,d) values (1,3.14)
;
DEBUG:pymonetdb.mapi:executing command sCOMMIT
;
DEBUG:pymonetdb.mapi:executing command sselect * from t1
;
DEBUG:pymonetdb.mapi:executing command sROLLBACK
;
INFO:pymonetdb.mapi:disconnecting from database
INFO:pymonetdb.mapi:Connection refused
DEBUG:pymonetdb.mapi:restarting authentication
DEBUG:pymonetdb.mapi:executing command Xauto_commit 0
DEBUG:pymonetdb.mapi:executing command Xsizeheader 1
DEBUG:pymonetdb.mapi:executing command Xreply_size 100

Logging goes fine with the pymonetdb 1.1.1 version, without Connection refused messages.

Cheers

Add MonetDB 10 protocol support

Described in this paper: http://www.vldb.org/pvldb/vol10/p1022-muehleisen.pdf

This fork implements the changes master...Mytherin:master

Investigate thread safety

at the moment we are not thread safe when sharing connections or cursors. This is okay, the DBAPI allowes it, as long as as the .threadsafety parameter is set correctly:

https://www.python.org/dev/peps/pep-0249/#threadsafety

which we do. But ideally we are also threadsafe when sharing connections and cursors.

`cursor.executemany` does not work as expected

Using cursor.executemany to run the same parametric query on many arguments discards all but the last result:

import pymonetdb


DATABASE = "demo"
c = pymonetdb.connect(DATABASE)
cc = c.cursor()

cc.execute("DROP TABLE IF EXISTS foo")
cc.execute("CREATE TABLE foo (i INT, t TEXT)")
cc.execute("INSERT INTO foo VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd')")

print("Using execute")
for i in range(1, 5):
    cc.execute('SELECT * FROM foo WHERE i=%s', [i])
    print(cc.fetchall())

print("Using executeall")
cc.executemany('SELECT * FROM foo WHERE i=%s', [(1,), (2,), (3,), (4,)])
print(cc.fetchall())

produces the following output:

Using execute
[(1, 'a')]
[(2, 'b')]
[(3, 'c')]
[(4, 'd')]
Using executeall
[(4, 'd'), (4, 'd'), (4, 'd'), (4, 'd')]

Moreover, passing to the executemany call an argument than does not exist in the database (cc.executemany('SELECT * FROM foo WHERE i=%s', [(1,), (2,), (3,), (4,), (5,)])), raises an Exception with fetchone and seems to block on a read with fetchall.

This was tested using pymonetdb version 1.4.0 and python 3.9.

Make 1.2.1 release

Support for COPY INTO ON CLIENT

The MonetDB server version 11.33.1 and later have support for reading and writing CSV files on the client side. The client side of this has so far only been implemented in mclient. It would be nice if pymonetdb could also do this.

Check out the forks

See if people did any interesting work.

Fix Python3 support

Some types missing on the mapping

On types.py file is missing some monetdb types such as UUID and JSON.

Fix master branch, travis is failing

Make code style checking stricter (mypy and flake8)

Fail build on flake8 errors
enforce type annotations everywhere

Fix mypy errors

λ  .venv/bin/mypy pymonetdb 
pymonetdb/sql/debug.py:103: error: Name 'execfile' is not defined
pymonetdb/sql/debug.py:178: error: Argument 1 to "loads" has incompatible type "str"; expected "bytes"
``

should probably also add stub files for libraries using pymonetdb as 3rd party lib.

PymonetDB low level MAPI interface: Increase the return array size?

I'm using MonetDB Mar2018 release and the low level MAPI Python interface. When requesting data from MonetDB, only 100 records are returned. Executing the same query from the mclient returns 584 records. My code goes like this:

from pymonetdb import mapi
mapiCon = mapi.Connection()
mapiCon.connect( hostname="localhost", port=50000, username= and so on....)
data = mapiCon.cmd(...myQuery...)

My question is: How does one change this 100 limit using the mapi interface?

can't use Control

This may not be a bug, but I can't understand what I'm doing wrong. (Tested with Python 2.7 and 3.6).

Creating a farm, a database, and populating it works:

export DOTMONETDBFILE="monetdb_config"

cat << EOF > $DOTMONETDBFILE
user=monetdb
password=monetdb
EOF

monetdbd create dbfarm
monetdbd start dbfarm

monetdb create demo
monetdb start demo

echo "CREATE TABLE names (id integer,name varchar(20));" | mclient -d demo
echo "INSERT INTO names VALUES (0, 'Alice');" | mclient -d demo
echo "INSERT INTO names VALUES (1, 'Bob');" | mclient -d demo
echo "SELECT * FROM names;" | mclient -d demo

Querying the database in Python works:

import pymonetdb

connection = pymonetdb.connect(username="monetdb", password="monetdb",
                               hostname="localhost", database="demo")

cursor = connection.cursor()

cursor.execute('SELECT * FROM names')
cursor.fetchall()

But I always get access denied when trying to use Control:

import pymonetdb.control
pymonetdb.control.Control(passphrase='monetdb', hostname='localhost')

No handlers could be found for logger "pymonetdb.mapi"
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "(...)/lib/python2.7/site-packages/pymonetdb/control.py", line 91, in __init__
    unix_socket=unix_socket)
  File "(...)/lib/python2.7/site-packages/pymonetdb/mapi.py", line 142, in connect
    self._login()
  File "(...)/lib/python2.7/site-packages/pymonetdb/mapi.py", line 165, in _login
    raise DatabaseError(prompt[1:])
pymonetdb.exceptions.DatabaseError: access denied

Thanks!

Note: same error with:

pymonetdb.control.Control(passphrase='monetdb', hostname=None)

Type analysis for OIDs

OID types in MonetDB are represented as 1235@0, which may cause an error in pymonetdb/sql/pythonize.py", line 144, in convert

A sample line received from the server:

line
'[ 4837,\t"monetdb",\t2019-08-22 12:33:52.000000,\tNULL,\t"running",\t4837@0,\t"select qtag, user, started, estimate, status, tag, query from sys.queue()\n;"\t]'

Add IPv6 support

Since Nov2019 release of MonetDB, both monetdbd (via ipv6 property) and mserver5 (via --set mapi_ipv6=true option) support IPv6 connections. However pymonetdb still lacks this option (via AF_INET6 property on the socket call).

Add support for where in params

I could not find any way to use pymonetdb with a list of ids for where in.
ids = 1,2
cursor.execute('SELECT*FROM raw.bla WHERE "ID" in %s', ids)

I already tried data, list and tuples, but none of them works.
Always "this type is not supported".

numpy support

That would be nice to have an equivalent of cursor.fetchall, which return results stored as column in numpy masked array.

For example, instead of returning:

[[1, 1.1], [2, 2.2], [3, None]]   # Column names are ['id', 'x']

Returning:

{'id': ma.array([1, 2, 3], dtype='int32'), 
 'x': ma.array([1.1, 2.2, 0], mask=[False, False, True], dtype='float64')}

For a column-store database, it makes sense to have column-store results, and for a scientific Python API, it makes sense to have numpy arrays.

I seems easy to implement, I can submit a pull-request if you are interested.

Support day interval type

In the Oct2020 version of MonetDB, we added day interval as a new SQL type, but pymonetdb doesn't handle it. The format is the same as the second interval. I can try to make a pull request.

import pymonetdb
c1 = pymonetdb.connect(database='demo',port=50000)
cc = c1.cursor()
cc.execute('select interval '1' day')
....
gives
pymonetdb.exceptions.ProgrammingError: type day_interval is not supported

slow data transfer

[this is just for the record]

Heard from several users that transferring a large amount of data using pymonetdb is so slow that they all resort to alternatives, such as using mclient.

I have no more detailed information, but when we get around to it, we should benchmark this and try to improve it.

Drop python 2 support

Unicode support is hairy, we can make it less hairy by dropping python2 support. Python2 is EOL in 2020.

Rename pymonetdb (back) to (python-)monetdb

Update travis to newer OS/container

test suite fails now

license file

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/.

Would it be possible to include the license file in the repo, and add it to MANIFEST.in? (discussed here)

pymonetdb.control doesn't work over tcp

socket works.

drop python 2.6 support

py 2.6 support is broken since @sjoerdmullender his change to the external six module. It looks like six depends on the typing module, which only has Python 2.7(+) support.

Please stop me if 2.6 support is crucial but I guess we are living in the new millennium now.

Make description a named tuple

like postgresql does

http://initd.org/psycopg/docs/cursor.html#cursor.description

is backwards compatible.

Make python UDF debug code work with Python3

it is quite python2 specfic

Database Version is not filled for Monetdb

Hi,

I am working on an application that uses sqlalchemy for translating machine learning models into SQL code. I need to get the database version from the dialect for debugging purposes.

For Monetdb, the database version is always None, it probably needs to be a tuple.

Some script to reproduce:

import sqlalchemy as sa

lDSN1 = "monetdb://monetdb:monetdb@localhost/db"
engine1 = sa.create_engine(lDSN1, encoding='utf-8', echo=True)

print("INITIALIZE_ENGINE_DRIVER_SERVER_VERSION" , engine1.dialect.driver, engine1.dialect.server_version_info)

Sample outputs (with other databases):

INITIALIZE_ENGINE_DRIVER_SERVER_VERSION ibm_db_sa ('DB2/LINUXX8664', '11.01.0000')
INITIALIZE_ENGINE_DRIVER_SERVER_VERSION kinterbasdb (3, 0, 33100, 'firebird')
INITIALIZE_ENGINE_DRIVER_SERVER_VERSION pymonetdb None
INITIALIZE_ENGINE_DRIVER_SERVER_VERSION pymssql (15, 0, 2000, 5)
INITIALIZE_ENGINE_DRIVER_SERVER_VERSION mysqldb (10, 2, 21, 'MariaDB', 10, 2, '21+maria~sid', 'log')
INITIALIZE_ENGINE_DRIVER_SERVER_VERSION cx_oracle (11, 2, 0, 2, 0)
INITIALIZE_ENGINE_DRIVER_SERVER_VERSION psycopg2 (10, 5)
INITIALIZE_ENGINE_DRIVER_SERVER_VERSION pysqlite (3, 30, 1)

Thanks in advance

Antoine

make a binary wheel for Monetdb Lite

error starting monetdb?

I'm not clear how to start monetdb programmatically.
I downloaded monetdb with homebrew.

I thought the following ought to work:

import os
import pymonetdb
from pymonetdb.control import Control
MAPIPORT = int(os.environ.get('MAPIPORT', 50000))
TSTHOSTNAME = os.environ.get('TSTHOSTNAME', 'localhost')
TSTPASSPHRASE = os.environ.get('TSTPASSPHRASE', 'testdb')

control = Control(TSTHOSTNAME, MAPIPORT, TSTPASSPHRASE)

But I get:

ConnectionRefusedError                    Traceback (most recent call last)
<ipython-input-15-9b314d5f4743> in <module>()
      3 TSTPASSPHRASE = os.environ.get('TSTPASSPHRASE', 'testdb')
      4 
----> 5 control = Control(TSTHOSTNAME, MAPIPORT, TSTPASSPHRASE)

/Users/e/anaconda/lib/python3.5/site-packages/pymonetdb/control.py in __init__(self, hostname, port, passphrase, unix_socket)
     89                             password=passphrase,
     90                             database='merovingian', language='control',
---> 91                             unix_socket=unix_socket)
     92         self.server.disconnect()
     93 

/Users/e/anaconda/lib/python3.5/site-packages/pymonetdb/mapi.py in connect(self, database, username, password, language, hostname, port, unix_socket)
    141             # control doesn't require authentication over socket
    142             self._login()
--> 143 
    144         self.state = STATE_READY
    145 

ConnectionRefusedError: [Errno 61] Connection refused

Day interval issue?

Today I was testing with pymonetdb and I found this:
select interval '1000000000' day;
gives
OverflowError: days=1000000000; must have magnitude <= 999999999

From mclient is fine, I guess the size limit on pymonetdb is not as much.

SQLSTATE error code removed from error messages

This is related to #74. As a workaround, I try to check for the SQLSTATE error code to distinguish an SQL error from a connection error, since the SQL standard says that every SQL error should have an SQLSTATE error code.

Then I noticed that pymonetdb removes the SQLSTATE error code for several cases including COPY INTO.
(see https://github.com/gijzelaerr/pymonetdb/blob/8e6830d005d0f25e7d4630423e40014057058919/pymonetdb/mapi.py#L46 and until line 74)

What's the retional behind the removal of those error codes? Is it possible to add them back?

Cannot use the library on 3.7

When using the library and try to build a project using python3.7, we have recently this error.

ERROR: You need Python 2.7 or 3.4 to install the typing package.

I was able to reproduce this error from the version 1.3.1 until 1.2.1, works fine on 1.1.1

don't use nose anymore

seems to be unmaintained, switch to py.test

Use URI to connect to server

It would be nice if the connect() has a variant that can distangle the URI for monetdb, e.g.
pymonetdb.connect(URI=mapi:monetdb://Martins-MacBook-Pro-3.local:50000/sf1)
This can be broken down into host,port, database

Use of BokenPipeError breaks DBAPI compatability in 1.4.0

The recent commits for issue #74 to use BrokenPipeError instead of OperationalError breaks DPAPI compatiblity.

PEP 249 indicates that "The module should make all error information available through these exceptions or subclasses thereof"

This change impacts getting SQLAlchemy's pool pre-ping functionality to work with pymonetdb. It catches only exceptions that are subclasses of the DBAPI Error class, and won't catch BrokenPipeError.

This could be fixed by using the existing InterfaceError exception, or create a new BrokenPipeError class that subclasses InterfaceError.

Separate SQL errors from connection errors during query execution

Currently, when something (anything?) went wrong during a query execution, the user gets an OperationalError. For instance,

>>> c = pymonetdb.connect("demo", host="localhost", port=50000)
>>> cc = c.cursor()
>>> c.set_autocommit(True)
>>> cc.execute("select * from sys.tables t1, sys.tables t2, sys.tables t3")

# I killed the server during the execution of this query!

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/kutsurak/.cache/pypoetry/virtualenvs/test-pymonetdb-_ivNU08G-py3.8/lib/python3.8/site-packages/pymonetdb/sql/cursors.py", line 165, in execute
    block = self.connection.execute(query)
  File "/home/kutsurak/.cache/pypoetry/virtualenvs/test-pymonetdb-_ivNU08G-py3.8/lib/python3.8/site-packages/pymonetdb/sql/connections.py", line 140, in execute
    return self.command('s' + query + '\n;')
  File "/home/kutsurak/.cache/pypoetry/virtualenvs/test-pymonetdb-_ivNU08G-py3.8/lib/python3.8/site-packages/pymonetdb/sql/connections.py", line 145, in command
    return self.mapi.cmd(command)
  File "/home/kutsurak/.cache/pypoetry/virtualenvs/test-pymonetdb-_ivNU08G-py3.8/lib/python3.8/site-packages/pymonetdb/mapi.py", line 240, in cmd
    response = self._getblock()
  File "/home/kutsurak/.cache/pypoetry/virtualenvs/test-pymonetdb-_ivNU08G-py3.8/lib/python3.8/site-packages/pymonetdb/mapi.py", line 317, in _getblock
    return self._getblock_inet()
  File "/home/kutsurak/.cache/pypoetry/virtualenvs/test-pymonetdb-_ivNU08G-py3.8/lib/python3.8/site-packages/pymonetdb/mapi.py", line 323, in _getblock_inet
    flag = self._getbytes(2)
  File "/home/kutsurak/.cache/pypoetry/virtualenvs/test-pymonetdb-_ivNU08G-py3.8/lib/python3.8/site-packages/pymonetdb/mapi.py", line 347, in _getbytes
    raise OperationalError("Server closed connection")
pymonetdb.exceptions.OperationalError: Server closed connection

Would it be possible to separate SQL errors from other errors, in particular, connection errors? In that way, it would be much easier for the users to capture and deal with the query exeuction errors differently than all other types of errors.

Re-enable travis integration

module 'pymonetdb.mapi' has no attribute 'Server' ??

Tried to run the following code (as per the example in the doc)

from pymonetdb import mapi
server = mapi.Server()
server.connect(hostname="localhost", port=50000, username="monetdb",
                 password="monetdb", database="demo", language="sql")

But I got:

AttributeError                            Traceback (most recent call last)
<ipython-input-1-f3ddc95aff5b> in <module>()
      1 from pymonetdb import mapi
----> 2 server = mapi.Server()
      3 server.connect(hostname="localhost", port=50000, username="monetdb",
      4                  password="monetdb", database="demo", language="sql")

AttributeError: module 'pymonetdb.mapi' has no attribute 'Server'

What do I do wrong?

cursor.fetchall fails

cursor.fetchall fails if the result is larger than the cursor.arraysize. The code throws an exception while I expect all result rows.

Raise IntegrityError on UNIQUE constraint violated

Add more specific error handling

The API always raises a OperationalError when there is a problem, while for example an IntegrityErrorshould be raised on a unique or primairy key constrain.

I already added an error handler that parses the undocumented MAPI error format:

bfcb9c8#diff-3bb0f456e3f3d8ae030fe234e75ca7e8R64

Value error in dates

pymonetdb cannot handle all acceptable monetdb dates.

Replicate bug

SELECT DATE '2-01-01'

Or in VOC database execute:

SELECT min(cape_arrival) FROM voyages;

Result

ValueError: time data '2-01-01' does not match format '%Y-%m-%d'

UDFs don't work with python3 embedding enabled

could be related that i'm using the python3 backend.

Traceback (most recent call last):
  File "/home/gijs/Work/pymonetdb/tests/test_udf.py", line 33, in test_debug_udf
    self.cursor.export('SELECT test_python_udf(1)', 'test_python_udf', filespath=fname)
  File "/home/gijs/Work/pymonetdb/pymonetdb/sql/cursors.py", line 193, in export
    return export(self, query, fname, sample, filespath)
  File "/home/gijs/Work/pymonetdb/pymonetdb/sql/debug.py", line 229, in export
    arguments = exportparameters(cursor, ftype, fname, query, quantity_parameters, sample)
  File "/home/gijs/Work/pymonetdb/pymonetdb/sql/debug.py", line 174, in exportparameters
    cursor.execute(query)
  File "/home/gijs/Work/pymonetdb/pymonetdb/sql/cursors.py", line 165, in execute
    block = self.connection.execute(query)
  File "/home/gijs/Work/pymonetdb/pymonetdb/sql/connections.py", line 140, in execute
    return self.command('s' + query + '\n;')
  File "/home/gijs/Work/pymonetdb/pymonetdb/sql/connections.py", line 145, in command
    return self.mapi.cmd(command)
  File "/home/gijs/Work/pymonetdb/pymonetdb/mapi.py", line 249, in cmd
    raise exception(msg)
OperationalError: PY000!Invalid string encoding used. Please return a regular ASCII string, or a Numpy_Unicode object.

Connection object leaks the password

The connection object has a mapi object, which has a password field. The password is plain text.
I was wondering if there is a way to hide that field.

Psycopg for example doesn't return the password, but probably if someone inspects the memory of the program they could find it:
https://www.psycopg.org/docs/connection.html?highlight=password

Using MonetDB with schema

Hi,

I'm trying to connect to a monetdb via sqlalchemy ORM and a given schema "iacm".

When trying to do a simple query I get an error like:

pymonetdb.exceptions.OperationalError: 42000!TODO: column names of level >= 3

which I do not understand really.

The same happens also with a minimal example using pymonetdb 1.3.1 directly (assuming a table users with column id exists in schema iacm.

from pymonetdb.sql.connections import Connection as connect

conn = connect(hostname="localhost",database="demo",port=50000,
               username="monetdb",password="monetdb")

c = conn.cursor()
c.execute("SELECT iacm.users.id FROM iacm.users")
r = c.fetchall()
print(r)

conn.close()

Traceback (most recent call last):
  File "test.py", line 119, in <module>
    c.execute("SELECT iacm.users.id FROM iacm.users")
  File "C:\Users\user\Apps\Anaconda3\lib\site-packages\pymonetdb\sql\cursors.py", line 165, in execute
    block = self.connection.execute(query)
  File "C:\Users\user\Apps\Anaconda3\lib\site-packages\pymonetdb\sql\connections.py", line 140, in execute
    return self.command('s' + query + '\n;')
  File "C:\Users\user\Apps\Anaconda3\lib\site-packages\pymonetdb\sql\connections.py", line 145, in command
    return self.mapi.cmd(command)
  File "C:\Users\user\Apps\Anaconda3\lib\site-packages\pymonetdb\mapi.py", line 266, in cmd
    raise exception(msg)
pymonetdb.exceptions.OperationalError: 42000!TODO: column names of level >= 3

Does this mean that monetdb cannot be used with sqlalchemy and schemas?
That would be really a pity as it would mean for me that there is no way accessing the given db data...

Multi line response handling

Hello Gijs,

Continuing from yesterday's PR I noticed the following:

In mapi.py, when executing a command the response might consist of multiple lines. For instance if autocommit mode is on and one transaction fails because of concurrency conflicts the client gets the following response:

>>&2 1 -1
!40000!COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead
<<

(I have added the >> and << sequence to indicate newlines).

In this case I believe that an exception should be raised, since the client should be notified that the transaction failed. What do you think? I am not an expert in the mapi protocol (not by a long shot), but I will try to create some tests and send a pull request early next week. Would that be ok with you?

Best regards,
Panos.

Check PEP-249 compatibility

Github actions broken

two issues during running unit tests:

42000!CREATE TABLE: 'dbapi20test_booze' name conflict for Linux
OS X timeout