basic-psysopg2.py

#basic python code for working with POSGRESQL through  psycopg2



import psycopg2

conn = psycopg2.connect(database="testdb", user="postgres", password="pass123", host="127.0.0.1", port="5432")
print("Opened database sucessfully")
cur = conn.cursor()

#CREATE A TABLE

cur.execute('''
  CREATE TABLE COMPANY
  (ID INT PRIMARY  KEY NOT NULL,
  NAME TEXT NOT NULL,
  AGE INT NOT NULL,
  ADDRESS CHAR(50)
  SALARY REAL);
''')

print("Table create successfully")
conn.commit()
conn.close()

#INSERT operation

import psycopg2

conn = psycopg2.connect(database="testdb", user="postgres", password="pass123", host="127.0.0.1", port="5432")
print("Opened database sucessfully")
cur = conn.cursor()

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

#SELECT OPERATION
import psycopg2
conn = psycopg2.connect(database="testdb", user="postgres", password="pass123", host="127.0.0.1", port="5432")
print("Opened database sucessfully")
cur = conn.cursor()

cur.execute("SELECT id, name, address, salary from COMPANY;")
rows = cur.fetchall() #fetch all remaining rows of a query result, returning them as a list of tuples
for row in rows:
  print('ID = ', row[0])
  print('name = ', row[1])
  print('address = ', row[2])
  print('salary = ', row[3], '\n')
print('Operation done");
conn.close() #there is no conn.commit when executing select operation