0

I'd like to use the python wrapper psycopg2 to run PostgreSQL queries. I'm trying to create a connection to the database as follows:

  1. Connect to the local instance of PostgreSQL (127.0.0.1)
  2. Use the database/schema from the instance.
  3. The connection reaches out to the database (studentdb) and use the correct privilages to connect to the database (user and password = student).

But i'm unable to create the connect to the server. I am using Anaconda3 2020.02 (Python 3.7.6 64-bit) on Windows 10 to run my code.

Code

import psycopg2
conn = psycopg2.connect("host=127.0.0.1 dbname=studentdb user=student password=student")

Error Message

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-3-72130d62996a> in <module>
----> 1 conn = psycopg2.connect("host=127.0.0.1 dbname=studentdb user=student password=student")

~\Anaconda3\lib\site-packages\psycopg2\__init__.py in connect(dsn, connection_factory, cursor_factory, **kwargs)
    125 
    126     dsn = _ext.make_dsn(dsn, **kwargs)
--> 127     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
    128     if cursor_factory is not None:
    129         conn.cursor_factory = cursor_factory

OperationalError: could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "127.0.0.1" and accepting
    TCP/IP connections on port 5432?

Trials

I tried to listen to the port via Git Bash using netstat -nat | grep 5432 and got:

  UDP    127.0.0.1:65432        *:*

I then used netstat -nat | grep 127.0.0.1 to check the open TCP ports and got:

  TCP    127.0.0.1:5037         0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:5999         0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:7305         0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:8888         0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:9592         0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:18080        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:21584        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:27015        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:35253        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:46200        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:49672        0.0.0.0:0              LISTENING       InHost 
  TCP    127.0.0.1:49672        127.0.0.1:49697        ESTABLISHED     InHost   
  TCP    127.0.0.1:49677        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:49697        127.0.0.1:49672        ESTABLISHED     InHost   
  TCP    127.0.0.1:49715        127.0.0.1:49716        ESTABLISHED     InHost   
  TCP    127.0.0.1:49716        127.0.0.1:49715        ESTABLISHED     InHost   
  TCP    127.0.0.1:49784        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:49822        127.0.0.1:49823        ESTABLISHED     InHost   
  TCP    127.0.0.1:49823        127.0.0.1:49822        ESTABLISHED     InHost   
  TCP    127.0.0.1:49824        127.0.0.1:65443        ESTABLISHED     InHost   
  TCP    127.0.0.1:65187        127.0.0.1:65188        ESTABLISHED     InHost   
  TCP    127.0.0.1:65188        127.0.0.1:65187        ESTABLISHED     InHost   
  TCP    127.0.0.1:65197        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:65197        127.0.0.1:65256        ESTABLISHED     InHost   
  TCP    127.0.0.1:65198        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:65198        127.0.0.1:65246        ESTABLISHED     InHost   
  TCP    127.0.0.1:65198        127.0.0.1:65264        ESTABLISHED     InHost   
  TCP    127.0.0.1:65199        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:65199        127.0.0.1:65265        ESTABLISHED     InHost   
  TCP    127.0.0.1:65200        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:65200        127.0.0.1:65218        ESTABLISHED     InHost   
  TCP    127.0.0.1:65200        127.0.0.1:65259        ESTABLISHED     InHost   
  TCP    127.0.0.1:65201        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:65202        127.0.0.1:65203        ESTABLISHED     InHost   
  TCP    127.0.0.1:65203        127.0.0.1:65202        ESTABLISHED     InHost   
  TCP    127.0.0.1:65204        127.0.0.1:65205        ESTABLISHED     InHost   
  TCP    127.0.0.1:65205        127.0.0.1:65204        ESTABLISHED     InHost   
  TCP    127.0.0.1:65206        127.0.0.1:65207        ESTABLISHED     InHost   
  TCP    127.0.0.1:65207        127.0.0.1:65206        ESTABLISHED     InHost   
  TCP    127.0.0.1:65208        127.0.0.1:65209        ESTABLISHED     InHost   
  TCP    127.0.0.1:65209        127.0.0.1:65208        ESTABLISHED     InHost   
  TCP    127.0.0.1:65211        127.0.0.1:65212        ESTABLISHED     InHost   
  TCP    127.0.0.1:65212        127.0.0.1:65211        ESTABLISHED     InHost   
  TCP    127.0.0.1:65218        127.0.0.1:65200        ESTABLISHED     InHost   
  TCP    127.0.0.1:65219        127.0.0.1:65220        ESTABLISHED     InHost   
  TCP    127.0.0.1:65220        127.0.0.1:65219        ESTABLISHED     InHost   
  TCP    127.0.0.1:65221        127.0.0.1:65222        ESTABLISHED     InHost   
  TCP    127.0.0.1:65222        127.0.0.1:65221        ESTABLISHED     InHost   
  TCP    127.0.0.1:65223        127.0.0.1:65224        ESTABLISHED     InHost   
  TCP    127.0.0.1:65224        127.0.0.1:65223        ESTABLISHED     InHost   
  TCP    127.0.0.1:65225        127.0.0.1:65226        ESTABLISHED     InHost   
  TCP    127.0.0.1:65226        127.0.0.1:65225        ESTABLISHED     InHost   
  TCP    127.0.0.1:65227        127.0.0.1:65228        ESTABLISHED     InHost   
  TCP    127.0.0.1:65228        127.0.0.1:65227        ESTABLISHED     InHost   
  TCP    127.0.0.1:65229        127.0.0.1:65230        ESTABLISHED     InHost   
  TCP    127.0.0.1:65230        127.0.0.1:65229        ESTABLISHED     InHost   
  TCP    127.0.0.1:65231        127.0.0.1:65232        ESTABLISHED     InHost   
  TCP    127.0.0.1:65232        127.0.0.1:65231        ESTABLISHED     InHost   
  TCP    127.0.0.1:65233        127.0.0.1:65234        ESTABLISHED     InHost   
  TCP    127.0.0.1:65234        127.0.0.1:65233        ESTABLISHED     InHost   
  TCP    127.0.0.1:65235        127.0.0.1:65236        ESTABLISHED     InHost   
  TCP    127.0.0.1:65236        127.0.0.1:65235        ESTABLISHED     InHost   
  TCP    127.0.0.1:65237        0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:65238        127.0.0.1:65239        ESTABLISHED     InHost   
  TCP    127.0.0.1:65239        127.0.0.1:65238        ESTABLISHED     InHost   
  TCP    127.0.0.1:65240        127.0.0.1:65241        ESTABLISHED     InHost   
  TCP    127.0.0.1:65241        127.0.0.1:65240        ESTABLISHED     InHost   
  TCP    127.0.0.1:65242        127.0.0.1:65243        ESTABLISHED     InHost   
  TCP    127.0.0.1:65243        127.0.0.1:65242        ESTABLISHED     InHost   
  TCP    127.0.0.1:65244        127.0.0.1:65245        ESTABLISHED     InHost   
  TCP    127.0.0.1:65245        127.0.0.1:65244        ESTABLISHED     InHost   
  TCP    127.0.0.1:65246        127.0.0.1:65198        ESTABLISHED     InHost   
  TCP    127.0.0.1:65247        127.0.0.1:65248        ESTABLISHED     InHost   
  TCP    127.0.0.1:65248        127.0.0.1:65247        ESTABLISHED     InHost   
  TCP    127.0.0.1:65250        127.0.0.1:65251        ESTABLISHED     InHost   
  TCP    127.0.0.1:65251        127.0.0.1:65250        ESTABLISHED     InHost   
  TCP    127.0.0.1:65252        127.0.0.1:65253        ESTABLISHED     InHost   
  TCP    127.0.0.1:65253        127.0.0.1:65252        ESTABLISHED     InHost   
  TCP    127.0.0.1:65254        127.0.0.1:65255        ESTABLISHED     InHost   
  TCP    127.0.0.1:65255        127.0.0.1:65254        ESTABLISHED     InHost   
  TCP    127.0.0.1:65256        127.0.0.1:65197        ESTABLISHED     InHost   
  TCP    127.0.0.1:65257        127.0.0.1:65258        ESTABLISHED     InHost   
  TCP    127.0.0.1:65258        127.0.0.1:65257        ESTABLISHED     InHost   
  TCP    127.0.0.1:65259        127.0.0.1:65200        ESTABLISHED     InHost   
  TCP    127.0.0.1:65260        127.0.0.1:65261        ESTABLISHED     InHost   
  TCP    127.0.0.1:65261        127.0.0.1:65260        ESTABLISHED     InHost   
  TCP    127.0.0.1:65262        127.0.0.1:65263        ESTABLISHED     InHost   
  TCP    127.0.0.1:65263        127.0.0.1:65262        ESTABLISHED     InHost   
  TCP    127.0.0.1:65264        127.0.0.1:65198        ESTABLISHED     InHost   
  TCP    127.0.0.1:65265        127.0.0.1:65199        ESTABLISHED     InHost   
  TCP    127.0.0.1:65443        127.0.0.1:49824        ESTABLISHED     InHost   
  UDP    127.0.0.1:53           *:*                                             
  UDP    127.0.0.1:1900         *:*                                             
  UDP    127.0.0.1:49666        *:*                                             
  UDP    127.0.0.1:50113        *:*                                             
  UDP    127.0.0.1:52129        *:*                                             
  UDP    127.0.0.1:54835        *:*                                             
  UDP    127.0.0.1:63481        *:*                                             
  UDP    127.0.0.1:63934        *:*                                             
  UDP    127.0.0.1:65432        *:*

I am not quite sure how to proceed from there. I tried to use the first port 5037 to esablish my connection, but the cell keeps running for a long time without giving me any output. Although when I check the ports again, I get this:

  TCP    127.0.0.1:5037         0.0.0.0:0              LISTENING       InHost   
  TCP    127.0.0.1:5037         127.0.0.1:65284        ESTABLISHED     InHost   
  .
  .
  .

How can I fix the problem? I am new to PostgreSQL and my skills in bash are still basic. I have read so many posts here but the solutions did not work for me.

Lod
  • 247
  • 1
  • 2
  • 8
  • Is your PostgreSQL server running? Usually it runs on port 5432. – mechanical_meat Apr 26 '20 at 04:52
  • @mechanical_meat I am unsure. How to check for this? I used `netstat -nat | grep 127.0.0.1` and found no 5432 ports as shown above. Is there any other way to check? – Lod Apr 26 '20 at 05:09
  • My guess is it's not running. Look at this answer for how to start it: https://stackoverflow.com/a/36631146/42346 – mechanical_meat Apr 26 '20 at 05:10
  • @mechanical_meat I am a newbie to this and hence a little confused. Do I need to install _Postgresql_ separately to be able to use _psycopg2_ with python? – Lod Apr 26 '20 at 05:17
  • Oh, yes, you do. The `psycopg2` module is just the interface between Python and PostgreSQL. – mechanical_meat Apr 26 '20 at 05:19
  • @mechanical_meat Thanks! I haven't seen this mentioned in tutorials i'm following. I'll install now and try again! – Lod Apr 26 '20 at 05:23
  • You're welcome. I'm not sure why it wasn't mentioned. Let us know how it goes. – mechanical_meat Apr 26 '20 at 05:24
  • @mechanical_meat it works! I am just getting another error, i.e., `FATAL: database "studentdb" does not exist`. I guess I have to create `studentdb` first in bash. Thanks though! – Lod Apr 26 '20 at 06:09
  • I'm glad it's working! Happy coding to you :) – mechanical_meat Apr 26 '20 at 06:42

0 Answers0