Created
August 4, 2018 12:29
Revisions
-
sonhmai created this gist
Aug 4, 2018 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,67 @@ #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