Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

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

    login = "dbname='YourDatabaseName' user='Adam' host='' " + \
    # 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()
except Exception as e:
    print("Unable to connect to database")

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.



No comments have yet been submitted. Be the first!

Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.