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'),
database=os.getenv('database'),
user=os.getenv('user'),
password=os.getenv('password'))
create_table_query = (
"""
create table TableName (
id numeric,
name VARCHAR(255) NOT NULL,
)
""")
try:
cur = conn.cursor()
cur.execute(create_table_query)
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")
""" insert a new data into the db table """
sql_query = '''SELECT * from tableName;'''
try:
cursor = conn.cursor()
cursor.execute(sql_query)
result = cursor.fetchall();
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn:
conn.close()
conn.close()
print("connection is closed")
""" insert a new data into the db table """
insert_query = """INSERT INTO tableName(
id,
name)
VALUES(%s,%s);"""
values_to_insert=(1, 'John')
try:
cur = conn.cursor()
cur.execute(insert_query, values_to_insert)
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn:
cur.close()
conn.close()
print("connection is closed")