python psycopg2_Python利用psycopg2对PostgreSQL进行DML

#!/usr/local/python3/bin/python

importsysimportosimportpsycopg2

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

defopen_database_connect():try:

conn= psycopg2.connect(database=DB_NAME, user=DB_USER, password=DB_PASSWORD, host=DB_HOST, port=DB_PORT)exceptException as e:print(e)else:returnconndefclose_database_connect(conn):

conn.commit()

conn.close()defcreate_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))

)''')exceptBaseException as e:print(e)else:

close_database_connect(conn)definsert_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)''')exceptBaseException as e:print(e)else:

close_database_connect(conn)defselect_database_object():

conn=open_database_connect()

cur=conn.cursor()try:

cur.execute('select empno, ename, job, sal from edbstore.t1')exceptBaseException as e:print(e)else:

rows=cur.fetchall()for row inrows:print("empno={0}, ename={1}, job={2}, sal={3}".format(row[0], row[1], row[2], row[3]))

close_database_connect(conn)defupdate_database_object():

conn=open_database_connect()

cur=conn.cursor()try:

cur.execute("update edbstore.t1 set job='MANAGER' where empno=7369 and ename='SMITH'")exceptBaseException as e:print(e)else:

close_database_connect(conn)