PYTHON

postgres fecth python

import psycopg2
import os

'''
* dotenv is a function from load_dotenv whichis used to to store sensitive data in separate file and 
can be accesed using os and loadenv
* psycopg2 is a module to connect postgreSql to python
* can install by using `pip install psycopg2` '''
from dotenv import load_dotenv
load_dotenv()

try:
	conn = psycopg2.connect(
            host=os.getenv('host'),  #localhost/127.0.0.1
            database=os.getenv('database'), #database_name 
            user=os.getenv('user'), #username of db
            password=os.getenv('password')) #password of db
	
	# connection is established and you can use all postgreSql queries using conn
   

    # create tables in the PostgreSQL database
    	create_table_query = (
        """
          	create table TableName (
            id numeric, 
            name VARCHAR(255) NOT NULL,
            )
        """)

    try:
        # define cursor
        cur = conn.cursor()
        # create table (single)
        cur.execute(create_table_query)
        # commit the changes
        conn.commit()
    except (Exception, psycopg2.Error) as error :
    	print ("Error while connecting to PostgreSQL", error)
    finally:
        if conn:
        	cur.close()
		  	conn.close()
			print("connection is closed")


# fetch records from db
    """ insert a new data into the db table """
	
	# Here the tableName refers to the name you used to create table in db

    sql_query = '''SELECT * from tableName;'''
    
    try:
       
        #Creating a cursor object using the cursor() method
        cursor = conn.cursor()

        #Retrieving data
        cursor.execute(sql_query)

        #Fetching 1st row from the table
        # result = cursor.fetchone();
        # print(result)

        # #Fetching all rows from the table
        result = cursor.fetchall();
        # print(result)

        #Commit your changes in the database
        conn.commit()

    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn:
          	conn.close()
            conn.close()
			print("connection is closed")
            
# inserting data to db
 """ insert a new data into the db table """
	# Here the tableName refers to the name you used to create table in db

    insert_query = """INSERT INTO tableName(
								id,
                                name)
     VALUES(%s,%s);"""
    values_to_insert=(1, 'John')
    try:
        #define cursor
        cur = conn.cursor()
        # insert table (single)
        cur.execute(insert_query, values_to_insert)
        # commit the changes
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn:
          	cur.close()
            conn.close()
            print("connection is closed")