Query a PostgreSQL database via Python

Use this simple template script created in Python to connect to your PostgreSQL database and run a query.

I frequently need to query a PostgreSQL database that I have running on a Linux server. Whilst I can SSH into the server and run queries on the command line, this is cumbersome, especially when the task is repetitive. It’s far easier to write a Python programme that can query the database and return the answers that I require.

Thankfully this is fairly simple with the psycopg2 package.

The following is a bare-bones Python script that will access a PostgreSQL database and run the following query: SELECT * from table. From this simple code, it’s possible to build up something more elaborate.

#!/usr/bin/env python

import psycopg2

try:
    login = "dbname='YourDatabaseName' user='Adam' host='127.0.0.1' " + \
        "password='MyPa$$word'"
    # Establish a connection
    conn = psycopg2.connect(login)
    # Create a psycopg2 cursor that can execute queries
    cursor = conn.cursor()
    # Run a query
    cursor.execute("""SELECT * from table""")
    rows = cursor.fetchall()
    print(rows)
    cursor.close()
    conn.close()
except Exception as e:
    print("Unable to connect to database")
    print(e)

The parameters dbname, user, host and password need to be changed to match your database details and the SQL query should also be customised to match your requirements.

Output will be in the form of a list.

This code is also available on GitHub Gist.

Have Your Say

Δ