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)
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.