Query Crate Database by SQLAlchemy Dialect: No more Servers available

Question

I'd like to give it a try with crate. I first tried it by loading some twitter data and query from admin UI successfully. However, when I tried to make a query with SQLAlchemy, I've got the following error.

OperationalError: (crate.client.exceptions.ConnectionError) 
No more Servers available, exception from last server: HTTPConnectionPool(host='localhost', port=4200): 
Max retries exceeded with url: 
/_sql (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7f51485b2c18>:
Failed to establish a new connection: [Errno 111] Connection refused'))

[SQL: SELECT * FROM tweets LIMIT 100; ]

(Background on this error at: http://sqlalche.me/e/e3q8)

This is the code I ran that caused the error.

from crate import client
import sqlalchemy as sa
import pandas as pd

engine = sa.create_engine('crate://localhost:4200')
query = (
            "SELECT * "
            "FROM tweets "
            "LIMIT 100; "
    )
df = pd.read_sql(query, engine)

Any idea how to fix it (the settings are all default)?

Update:

Crate is running on host system while Jupyter is running in a container on the same machine. I can access Crate via "http://localhost:4200" on the host machine but can't do "http://xxx.xxx.xxx.xxx:4200" from other machine in the same natwork.

I tried both:

engine = sa.create_engine('crate://10.7.116.222:4200')
engine = sa.create_engine('crate://')

which caused the same error:

Removed server http://127.0.0.1:4200 from active pool
Removed server http://127.0.0.1:4200 from active pool

OperationalError: (crate.client.exceptions.ConnectionError) 
No more Servers available, exception from last server: 
HTTPConnectionPool(host='127.0.0.1', port=4200): 
Max retries exceeded with url: 
/_sql (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7f5147faaa58>: 
Failed to establish a new connection: [Errno 111] Connection refused'))
[SQL: SELECT * FROM tweets LIMIT 10; ]
(Background on this error at: http://sqlalche.me/e/e3q8)

score 0 · Answer 1 · answered Jun 24 '19 at 00:43

0

I haven't tried it but according to the documentation 'localhost:4200' is assumed so you could connect using just engine = sa.create_engine('crate://')

answered Jun 24 '19 at 00:43

metase

1,169
2
16
29

Thank you but unfortunately, it didn't work. I've added some info to the question as well. – bensw Jun 24 '19 at 01:12
oh so are you saying you can't access crate from inside the container? – metase Jun 24 '19 at 18:31
No I cannot access crate from inside the container. – bensw Jun 25 '19 at 02:02
well that makes sense, since crate would be bound on local port and your jupyter notebook cant see it. try exposing crate on a public interface – metase Jun 25 '19 at 14:28
Anywhere that I can find more information about that? – bensw Jun 25 '19 at 14:30
https://stackoverflow.com/questions/50883996/running-cratedb-in-production buried in the comments – metase Jun 26 '19 at 00:37

Query Crate Database by SQLAlchemy Dialect: No more Servers available

1 Answers1