Skip to content

Instantly share code, notes, and snippets.

@sonhmai
Created August 4, 2018 12:29

Revisions

  1. sonhmai created this gist Aug 4, 2018.
    67 changes: 67 additions & 0 deletions basic-psysopg2.py
    Original 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