MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Python-Postgres Query

without comments

As I committed to a student, here are sample programs for writing a Python query against the Postgres 11 database. The first one returns rows or tuples. The latter formats the text returned as columns.

If you’re one of many looking for the key psycopg2 driver library, you can find it in most distro repositories as: python3-psycopg2. You can use dnf or yum to install it separately or you can install pgadmin4, which includes the psycopg2 library.

The first example returns the entire row from a new_hire table with two rows:

import psycopg2
 
try:
  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")
 
  # Open a cursor.
  cursor = connection.cursor()
 
  # Assign a static query.
  query = "SELECT * FROM new_hire"
 
  # Parse and execute the query.
  cursor.execute(query)
 
  # Fetch all rows from a table.
  records = cursor.fetchall()
 
  # Read through and print the rows as tuples.
  for row in range(0, len(records)):
    print(records[row]) 
 
except (Exception, psycopg2.Error) as error :
  print("Error while fetching data from PostgreSQL", error)
 
finally:
  # Close the database connection.
  if (connection):
    cursor.close()
    connection.close()

The first example returns the rows as tuples, which is probably desired if you want to consume the result in another Python program. Here’s the output retrieved:

(1001, 'Malcolm', 'Jacob', 'Lewis', datetime.date(2018, 2, 14))
(1002, 'Henry', None, 'Chabot', datetime.date(1990, 7, 31))

The second one returns the rows and formats the columns into output for a csv style file:

import psycopg2
 
try:
  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")
 
  # Open a cursor.
  cursor = connection.cursor()
 
  # Assign a static query.
  query = "SELECT * FROM new_hire"
 
  # Parse and execute the query.
  cursor.execute(query)
 
  # Read through and print the formatted columns of each row.
  for (new_hire_id, first_name, middle_name, last_name, hire_date) in cursor:
    if (isinstance(middle_name,type(None))):
      print("{},'{} {}','{:%d-%b-%Y}'".format(new_hire_id, first_name, last_name, hire_date))
    else:
      print("{},'{} {} {}','{:%d-%b-%Y}'".format(new_hire_id, first_name, middle_name, last_name, hire_date))
 
except (Exception, psycopg2.Error) as error :
  print("Error while fetching data from PostgreSQL", error)
 
finally:
  # Close the database connection.
  if (connection):
    cursor.close()
    connection.close()

The second one returns the rows and formatted columns for a csv style file:

1001,'Malcolm Jacob Lewis','14-Feb-2018'
1002,'Henry Chabot','31-Jul-1990'

As always, I hope these help those looking for a starting place with Python and Postgres.

Written by maclochlainn

September 23rd, 2019 at 1:23 am

Posted in Fedora,Linux,Postgres,PostgreSQL,Python,Python 3.x

Tagged with

«
»