Updating data in postgres using python API

Question

I have a python API that hits a URL and receives a JSON. There is a table that contains order data

Order_id   ZipCode   delivery_date   total

The json returned by the API contains the city and state of the zipcode being passed.

I want to :

• Add new column to the table City and State
• Update the above 2 column based on the corresponding zip code

How can this be done through python ?

Answer 1

you can use an adapter to establish first a connection to python like psycopg2 or sqlalchemy

using psycopg2

import psycopg2


class Postgres(object):
    """docstring for Postgres"""
    _instance = None

    def __new__(cls):
       if cls._instance is None:
           cls._instance = object.__new__(cls)
           # normally the db_credenials would be fetched from a config file or the enviroment
           # meaning shouldn't be hardcoded as follow
           db_config = {'dbname': 'demo', 'host': 'localhost',
                 'password': 'postgres', 'port': 5432, 'user': 'postgres'}
           try:
               print('connecting to PostgreSQL database...')
               connection = Postgres._instance.connection = 
               psycopg2.connect(**db_config)
               cursor = Postgres._instance.cursor = connection.cursor()
               cursor.execute('SELECT VERSION()')
               db_version = cursor.fetchone()

           except Exception as error:
               print('Error: connection not established {}'.format(error))
               Postgres._instance = None

           else:
               print('connection established\n{}'.format(db_version[0]))

       return cls._instance

   def __init__(self):
       self.connection = self._instance.connection
       self.cursor = self._instance.cursor

   def write(self, query):
       try:
           self.cursor.execute(query)
       except Exception as error:
           print('error execting query "{}", error: {}'.format(query, error))
           return None


   def read(self, query):
       try:
           self.cursor.execute(query)
           return self.cursor.fetchall()
       except Exception as error:
           print('error execting query "{}", error: {}'.format(query, error))
           return None
       else:
           return result

   def __del__(self):
       self.connection.close()
       self.cursor.close()

and then instantiate the class postgress, and call the methode read or write giving the if the goal is to write or read from the database.