Not getting any results (using Vertica dialect)

[timolingh]

Hi, I'm stuck here. Maybe someone can help. I added the Vertica dialect for SQLAlchemy. But I can't even get a simple result. I'll try to see if I can reproduce this on a simple sqllite database, but I'm wondering if there is anything obvious that I'm missing.

Thanks!

-Tim

import sqlalchemy as sa

## Connection to  vertica server -- maybe i don't have to do this
sa.create_engine("vertica+vertica_python://tim:pwd@host:5433/database")

Engine(vertica+vertica_python://tim:***@host:5433/database)

%load_ext sql

## connection
%sql vertica+vertica_python://tim:pwd@host:5433/database

Connected: tim@database

%sql "select import_id from table limit 5;"

Done.

import_id

%sql select 1+1 as foo;

Done.

foo

[oualib]

Hello @timolingh, I'm trying to use ipython-sql with Vertica, did you solve your problem? How can I do it? Thank you by advance for your help. Best Regards, Badr

[ghost]

The same issue here. Do you have any idea what that could be? Best Regards, Krystian

[timolingh]

It still doesn't work for me. I can tell the statements are submitted, so the connection works - just no results. Actually, the column names are returned but no data underneath.

Have you tried connecting via ODBC? Have you tried a different database other than Vertica?

These are things I was planning to try.

[ghost]

When you use vertica_python directly with sqlalchemy you have to execute fetchall() to get the values from the result. Maybe that is not executed? I haven't tried with ODBC. In the env I want to use it ODBC is cumbersome.

[oualib]

Thanks for the answers guys. Can you please give me the few line of codes to do the connection using ipython-sql. Or have I to just pass my ODBC cursor? I don't know... I'm really lost, could you please help me. Here are my info:

import pyodbc
driver="/Library/Vertica/ODBC/lib/libverticaodbc.dylib"
server="10.211.55.14"
database="testdb"
port="5433"
uid="dbadmin"
pwd="pwd"
information=("DRIVER={};SERVER={};DATABASE={};PORT={};UID={};PWD={};").format(
            driver,server,database,port,uid,pwd)
cur=pyodbc.connect(information).cursor()

Everything is working fine for my ODBC connection. I just want to have the possibility to use queries such as ipython-sql, I do not find the correct syntax for Vertica.

Thank you by advance,

Badr

[ghost]

No results:

%load_ext sql
%sql vertica+vertica_python://user:pass@localhost:port/docker
%sql select count(*) from table

Results:

import sqlalchemy as sa
db=sa.create_engine('vertica+vertica_python://user:pass@localhost:port/docker')
engine=db.connect() 
result = engine.execut('select count(*) from table')
result.fetchall()      

Krystian

[oualib]

Thank you for your answer @kryszczyn. However, i'm trying to use an odbc connection from my mac to Vertica. I have all the information I told before but I do not understand why sqlalchemy does not ask on the driver location... They should do something easier...

[timolingh]

Howdy, I managed to get this to work using ODBC.

import sqlalchemy as sa

sa.create_engine("vertica+pyodbc://tim:pwd@mydsn")

Engine(vertica+pyodbc://tim:***@mydsn)

%load_ext sql

%sql vertica+pyodbc://mydsn

'Connected: None@None'

% sql select 5 as foo

1 rows affected.

foo
5

% sql select timeofday()

1 rows affected.

timeofday
Sat Jan 20 01:30:18.520591 2018 GMT=

[oualib]

Thank you so much, My problem is my DSN on mac which is not working. I'm trying to fix it (no solution for now) but when it will be fixed, I think your code will work !

[bryanherger]

The underlying issue is with the cursor implementation in the vertica-python driver. I've submitted a pull request over there with a fix that works for ipython-sql. I think this issue can be closed since it's not an issue with ipython-sql.