Vertica 7.1.0 is not supported

[afsungur]

When I try to connect Vertica 7.1.0 I am getting the below message ( version string has been changed ):
Mac OS X 10.9.4, unixODBC 2.3.2
Traceback (most recent call last):
File "main2.py", line 13, in
sessionx.execute('select * from tables')
File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 977, in execute
return self._connection_for_bind(bind, close_with_result=True).execute(
File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 847, in _connection_for_bind
return self.transaction._connection_for_bind(engine)
File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 315, in _connection_for_bind
conn = bind.contextual_connect()
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1729, in contextual_connect
self.pool.connect(),
File "/Library/Python/2.7/site-packages/sqlalchemy/pool.py", line 332, in connect
return _ConnectionFairy._checkout(self)
File "/Library/Python/2.7/site-packages/sqlalchemy/pool.py", line 626, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/Library/Python/2.7/site-packages/sqlalchemy/pool.py", line 433, in checkout
rec = pool._do_get()
File "/Library/Python/2.7/site-packages/sqlalchemy/pool.py", line 945, in _do_get
return self._create_connection()
File "/Library/Python/2.7/site-packages/sqlalchemy/pool.py", line 278, in _create_connection
return _ConnectionRecord(self)
File "/Library/Python/2.7/site-packages/sqlalchemy/pool.py", line 409, in init
exec_once(self.connection, self)
File "/Library/Python/2.7/site-packages/sqlalchemy/event/attr.py", line 247, in exec_once
self(_args, *_kw)
File "/Library/Python/2.7/site-packages/sqlalchemy/event/attr.py", line 257, in call
fn(_args, *_kw)
File "/Library/Python/2.7/site-packages/sqlalchemy/util/langhelpers.py", line 1203, in go
return once_fn(_arg, *_kw)
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/strategies.py", line 165, in first_connect
dialect.initialize(c)
File "/Library/Python/2.7/site-packages/sqlalchemy/connectors/pyodbc.py", line 142, in initialize
super(PyODBCConnector, self).initialize(connection)
File "/Library/Python/2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1560, in initialize
super(PGDialect, self).initialize(connection)
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/default.py", line 232, in initialize
self._get_server_version_info(connection)
File "build/bdist.macosx-10.9-intel/egg/sqlalchemy_vertica/base.py", line 99, in _get_server_version_info
AssertionError: Could not determine version from string 'Vertica Analytic Database v7.1.0-1'

[bradsokol]

This seems to be isolated to OS X. I see the same issue with Vertica v7.1.1-0 on OS X, but it works fine on Ubuntu using the same Vertica instance with the same Python requirements:

$ pip freeze
SQLAlchemy==0.9.8
pyodbc==3.0.7
vertica-sqlalchemy==0.13

On Ubuntu 12.04.5:

$ python
Python 2.7.3 (default, Feb 27 2014, 19:58:35)
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> engine = sqlalchemy.create_engine('vertica+pyodbc://dbadmin:@PDW')
>>> connection = engine.connect()
>>> conection.scalar('select version()')
u'Vertica Analytic Database v7.1.1-0'
>>>

On OS X Yosemite:

$ python
Python 2.7.8 (default, Oct 19 2014, 16:03:53)
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.51)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> engine = sqlalchemy.create_engine('vertica+pyodbc://dbadmin:@PDW')
>>> connection = engine.connect()
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1778, in connect
    return self._connection_cls(self, **kwargs)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 60, in __init__
    self.__connection = connection or engine.raw_connection()
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1847, in raw_connection
    return self.pool.unique_connection()
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/pool.py", line 280, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/pool.py", line 644, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/pool.py", line 440, in checkout
    rec = pool._do_get()
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/pool.py", line 963, in _do_get
    return self._create_connection()
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/pool.py", line 285, in _create_connection
    return _ConnectionRecord(self)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/pool.py", line 416, in __init__
    exec_once(self.connection, self)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 250, in exec_once
    self(*args, **kw)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 260, in __call__
    fn(*args, **kw)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 1219, in go
    return once_fn(*arg, **kw)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 166, in first_connect
    dialect.initialize(c)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/connectors/pyodbc.py", line 143, in initialize
    super(PyODBCConnector, self).initialize(connection)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1688, in initialize
    super(PGDialect, self).initialize(connection)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 230, in initialize
    self._get_server_version_info(connection)
  File "/Users/brad.sokol/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy_vertica/base.py", line 99, in _get_server_version_info
    "Could not determine version from string '%s'" % v)
AssertionError: Could not determine version from string 'Vertica Analytic Database v7.1.1-0'
>>>

I wonder if it's an encoding issue. I'll dig a bit more.

[jamescasbon]

I no longer even have access to a vertica database, so if someone wants to take over this project I would be grateful.

OC if vertica are listening perhaps they could support devs who help their product out ;)

[bradsokol]

This appears to be an encoding issue, but likely with PyODBC. Here's another test:

Ubuntu 12.04.5

$ python
Python 2.7.3 (default, Feb 27 2014, 19:58:35)
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> connection = pyodbc.connect('DSN=PDW;PWD=')
>>> cursor = connection.cursor()
>>> cursor.execute('select version()')
<pyodbc.Cursor object at 0x226b690>
>>> row = cursor.fetchone()
>>> row
(u'Vertica Analytic Database v7.1.1-0', )

OS X Yosemite

$ python
Python 2.7.8 (default, Oct 19 2014, 16:03:53)
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.51)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> connection = pyodbc.connect('DSN=PDW;PWD=')
>>> cursor = connection.cursor()
>>> cursor.execute('select version()')
<pyodbc.Cursor object at 0x10df52570>
>>> row = cursor.fetchone()
>>> row
(u'V\x00e\x00r\x00t\x00i\x00c\x00a\x00 \x00A\x00n\x00a\x00l\x00y\x00t\x00i\x00c\x00 \x00D\x00a\x00t\x00a\x00b\x00a\x00s\x00e\x00 \x00v\x007\x00.\x001\x00.\x001\x00-\x000\x00', )

I'll spend some more time looking in to this as a PyODBC issue.

@jamescasbon FWIW, Vertica does have a free community edition :)

[bradsokol]

Also, my Ubuntu Python is built with UCS4 support while the OS X version - both system and Brew - are UCS2.

[bradsokol]

It turns out this was a configuration error. I created a vertica.ini file that looks like this:

[Driver]
DriverManagerEncoding=UTF-16
# ...

And then set the location in the environment:

export VERTICAINI=~/path/to/my/vertica.ini

After these changes, the test case above passed on OS X.

[bradleyhurley]

@jamescasbon - I would be willing to take over this project. I have access to several Vertica clusters, and would like to merge in some of the upstream changes.

Thanks,
Brad