0

I'm having a little trouble. I've tried using psycopg2 and sqlalchemy to get into my local PostgreSQL database and, while it's not throwing an error, it won't make the connection. Instead it's just stuck executing the code. Here's where it's going wrong.

conn = psycopg2.connect(database='db', user="user", password="password", host="127.0.0.1", port="55212")

And likewise when using sqlalchemy here is where it's going wrong.

engine = create_engine('postgresql://pw:db@localhost:55212/DB')

Please help, I can't do anything until this is resolved!

Improve this question
7
  • It could be the server isn't active, or a firewall? Why are you using different ports in each attempt? Commented May 18, 2021 at 12:19
  • I'm going to edit the question. I'm using the same port. Maybe if I connect to the VPN it will work. I'll try that now. Commented May 18, 2021 at 12:21
  • So I tried connecting to the VPN and that's not working either. I don't know if it's a firewall but that sounds promising. Can you help me figure out how to get around it so I can check if that is the issue? Commented May 18, 2021 at 12:28
  • Are you sure you are using the correct port? 5432 is the default. Commented May 18, 2021 at 12:39
  • This is the url to get into pgadmin: 127.0.0.1:55212/browser Commented May 18, 2021 at 12:42

1 Answer 1

Reset to default
1

Without seeing the logs, one can only guess. But basically, when using sqlalchemy your connection string that you pass to create_engine should look like: "postgresql+psycopg2://user_name:password@host_address:port/schema". If the data you passed is correct, make sure your DB server is open for connections. If you are attempting to connect to a docker container from within a different docker container, make sure they share a network, and change host_address from localhost to the name of the container.

The default port for PostgreSQL is 5432 so make sure what port to use before trying a different one. After creating the Engine, connect to the db with engine.connect(), which returns a connection (preferably with a context manager).

You can then use that connection to execute queries or pass it to pandas read_sql_query method.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

It seems as though this has worked. Can you tell me why this sql query is not executing now? df = pd.read_sql_query("SELECT * FROM public.TestDataFlow_SLG LIMIT 1000",con=engine) Do I need to specify a cursor? I don't know if it actually worked but it allowed me to create the engine...
First and foremost, please provide more information such as logs when asking, this would make it much easier to help you. Second - i'm not very familiar with pandas read_sql_query, but it seems the con argument is expecting a connection, not an engine (which is what create_engine returns). use con = engine.connect() and then pass that to read_sql_query.
Got it. I'll edit your answer and mark it as correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.