Python利用psycopg2对PostgreSQL进行DML

#!/usr/local/python3/bin/python
import sys
import os
import psycopg2

DB_HOST = "172.16.101.54"
DB_PORT = 5432
DB_USER = "dbadmin"
DB_PASSWORD = "agm43gadsg"
DB_NAME = "edbstore"


def open_database_connect():
    try:
        conn = psycopg2.connect(database=DB_NAME, user=DB_USER, password=DB_PASSWORD, host=DB_HOST, port=DB_PORT)
    except Exception as e:
        print(e)
    else:
        return conn


def close_database_connect(conn):
    conn.commit()
    conn.close()


def create_database_object():
    conn = open_database_connect()
    cur = conn.cursor()
    try:
        cur.execute('''create table if not exists edbstore.t1(
                    empno numeric(4,0) primary key,
                    ename varchar(24),
                    job varchar(10),
                    mgr numeric(4,0),
                    hiredate timestamp without time zone,
                    sal numeric(7,2),
                    comm numeric(7,2),
                    deptno numeric(2,0),
                    constraint t1_sal_ck check ((sal > (0)::numeric))
                    )
                    ''')
    except BaseException as e:
        print(e)
    else:
        close_database_connect(conn)


def insert_database_object():
    conn = open_database_connect()
    cur = conn.cursor()
    try:
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, NULL, 20)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000.00, NULL, 20)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30)''')
        cur.execute('''INSERT INTO edbstore.t1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
                        VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100.00, NULL, 20)''')
    except BaseException as e:
        print(e)
    else:
        close_database_connect(conn)


def select_database_object():
    conn = open_database_connect()
    cur = conn.cursor()
    try:
        cur.execute('select empno, ename, job, sal from edbstore.t1')
    except BaseException as e:
        print(e)
    else:
        rows = cur.fetchall()
        for row in rows:
            print("empno={0}, ename={1}, job={2}, sal={3}".format(row[0], row[1], row[2], row[3]))
        close_database_connect(conn)


def update_database_object():
    conn = open_database_connect()
    cur = conn.cursor()
    try:
        cur.execute("update edbstore.t1 set job='MANAGER' where empno=7369 and ename='SMITH'")
    except BaseException as e:
        print(e)
    else:
        close_database_connect(conn)