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 ?

This can be done. But you have to write some code yourself. If you already tried that, but the code didn't work, please edit you question and explain what you tried, and what didn't work as expected. [ask] — Håken Lid, Nov 02 '17 at 17:00

score 0 · Answer 1 · answered Nov 02 '17 at 14:16

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.

Updating data in postgres using python API

1 Answers1