2

How do i access a postgre database which is on a linux server machine in python? I have accessed it with putty terminal but i need to access the tables in the database through python and I am unable to do that.

S-Man
  • 22,521
  • 7
  • 40
  • 63
hinata
  • 45
  • 1
  • 9
  • psycopg2 is the library used for connecting to postgresql http://initd.org/psycopg/docs/install.html#binary-install-from-pypi – monkut Sep 18 '19 at 04:09
  • I tried psycopg2 but I was unable to because it kept returning an error, connection refused. – hinata Sep 18 '19 at 04:12
  • 1
    `connection refused` is not a _psycopg2_ library issue, it's typically a permissions/connection issue. – monkut Sep 18 '19 at 05:03
  • Post some code/ssh example, we may be able to help you. – monkut Sep 18 '19 at 05:04
  • I have entire module to make this async and easy. You can see it here. https://stackoverflow.com/questions/48532301/python-postgres-psycopg2-threadedconnectionpool-exhausted/49366850#49366850 – eatmeimadanish Sep 19 '19 at 15:18

2 Answers2

3
#!/usr/bin/env python3
import sys
import psycopg2

try:
    conn = psycopg2.connect("dbname='dbname' user='dbuser' host='localhost' port='5432' password='dbpass'")
except psycopg2.DatabaseError:
    sys.exit('Failed to connect to database')


try:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM test_table WHERE id < 10000")
    dbRecord = cursor.fetchone()

    if dbRecord == None:
        print('ERROR: First record not found', file=sys.stderr)
    else:
        print('Loaded {}'.format(dbRecord))
    dbRecordId = dbRecord[0]

    conn.commit()
    cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:
        conn.close()

Also you might need to allow access in postgres. To allow users connecting to server with login/password pair uncomment or add the following lines in pg_hba.conf (typically located in /etc/postgresql/{postgres_version}/main/pg_hba.conf):

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Edit: oh, I just realized that your postgres server is on a remote machine. The following options may apply then:

  • Add your IP to the pg_hba.conf configuration above and change host='localhost' to host='your.server.address.or.ip' in the script. OR:
  • Use ssh port forwarding to be able to access the remote server's 5432 port at your local machine. In that case leave localhost in the script as is.

The latter might be a cleanest and fastest option as you seem to already have the ssh access and you won't need to mess with database config that way. Moreover, you might not be permitted to change configs.

Mikhail Antonov
  • 1,297
  • 3
  • 21
  • 29
-1

To connect PostgreSQL with python, you need to install psycopg2 module. Additionally, there are some other modules like bpgsql,ocpgdb,PyGreSQL

Install psycopg2

pip install psycopg2

You can also install a specific version using the following command.

pip install psycopg2=2.7.5

Connect the PostgreSQL database and perform SQL queries on the database name you want to connect. You ought to get the below output after connecting to PostgreSQL from Python

You are connected to -  ('PostgreSQL 10.3')
PostgreSQL connection is closed

Refer this link for complete guide: https://pynative.com/python-postgresql-tutorial/#targetText=Use%20the%20connect()%20method,connection%20after%20your%20work%20completes.

Community
  • 1
  • 1
Ryan
  • 44
  • 1