【Python使用psycopg2三方库操作PostgreSQL的数据】

psycopg2，是Python语言的PostgreSQL数据库接口,它的主要优势在于完全支持Python DB API 2.0，以及安全的多线程支持。它适用于随时创建、销毁大量游标的、和产生大量并发INSERT、UPDATE操作的多线程数据库应用。本文简单介绍了使用psycopg2对PostgreSQL增删改查的基本操作，供参考。

首先，要安装python,pip以及psycopg2。

[xmaster@mogdb-kernel-0005 demo2]$ python3 --version
Python 3.6.8
[xmaster@mogdb-kernel-0005 demo2]$ pip3 --version
pip 9.0.3 from /usr/lib/python3.6/site-packages (python 3.6)
[xmaster@mogdb-kernel-0005 ~]$ pip3 install psycopg2
Collecting psycopg2
Installing collected packages: psycopg2
Successfully installed psycopg2-2.9.4

可以使用pip list查看是否安装该类库

[xmaster@mogdb-kernel-0005 ~]$ pip3 list --format=columns| grep psycopg2
psycopg2                  2.9.4    

一、select操作

编辑python代码文件psycopg2_demo.py ，内容如下

import psycopg2
  
'''连接串'''
conn = psycopg2.connect(database="postgres", user="puser", password="Enmo@123", host="172.16.0.176", port="5432")
'''开启游标'''
cur = conn.cursor()
'''执行语句'''
cur.execute("select * from public.demotable limit 10;")
'''获取结果集的每一行'''
rows = cur.fetchall()
'''循环输出结果行'''
for row in  rows:
print(row)
'''关闭游标'''
conn.close()

初次运行代码报错了，是没找到libpq.so.5的位置，

[xmaster@mogdb-kernel-0005 demo2]$ python3 psycopg2_demo.py 
Traceback (most recent call last):
  File "psycopg2_demo.py", line 1, in <module>
    import psycopg2
  File "/usr/local/lib64/python3.6/site-packages/psycopg2/__init__.py", line 51, in <module>
    from psycopg2._psycopg import (                     # noqa
ImportError: libpq.so.5: cannot open shared object file: No such file or directory

解决方法为：

[root@mogdb-kernel-0005 lib64]# cd /etc/ld.so.conf.d
[root@mogdb-kernel-0005 ld.so.conf.d]# ls
bind-export-x86_64.conf  kernel-4.18.0-348.7.1.el8_5.x86_64.conf  kernel-4.18.0-80.el8.x86_64.conf  oracle-instantclient.conf

[root@mogdb-kernel-0005 ld.so.conf.d]# ps -ef | grep postgres | grep xmaster
xmaster   182525       1  0 Oct15 ?        00:00:00 /opt/ysl_pg14/soft/bin/postgres -D /opt/ysl_pg14/data
xmaster   182526  182525  0 Oct15 ?        00:00:00 postgres: enmo_6001: logger 
xmaster   182528  182525  0 Oct15 ?        00:00:00 postgres: enmo_6001: checkpointer 
xmaster   182529  182525  0 Oct15 ?        00:00:00 postgres: enmo_6001: background writer 
xmaster   182530  182525  0 Oct15 ?        00:00:00 postgres: enmo_6001: walwriter 
xmaster   182531  182525  0 Oct15 ?        00:00:00 postgres: enmo_6001: autovacuum launcher 
xmaster   182532  182525  0 Oct15 ?        00:00:01 postgres: enmo_6001: stats collector 
xmaster   182533  182525  0 Oct15 ?        00:00:00 postgres: enmo_6001: logical replication launcher 
[root@mogdb-kernel-0005 ld.so.conf.d]# echo  "/opt/ysl_pg14/soft/lib" >pgsql.conf
[root@mogdb-kernel-0005 ld.so.conf.d]# ldconfig

再次执行，结果如下，其中Decimal模块旨在支持"无偏差,精确无舍入的十进制算术(有时称为定点数算术)和有舍入的浮点数算术"。

[root@mogdb-kernel-0005 ld.so.conf.d]# su - xmaster
Last login: Mon Oct 17 16:05:10 CST 2022 on pts/2
[xmaster@mogdb-kernel-0005 ~]$ cd python/demo2/
[xmaster@mogdb-kernel-0005 demo2]$ python3 psycopg2_demo.py 
(Decimal('204.649350891795'), 1)
(Decimal('413.041252643858'), 2)
(Decimal('172.191698622928'), 3)
(Decimal('82.1547082639427'), 4)
(Decimal('204.480204721943'), 5)
(Decimal('992.275551901436'), 6)
(Decimal('382.85296867706'), 7)
(Decimal('530.280781107436'), 8)
(Decimal('655.705775705879'), 9)
(Decimal('245.699153678395'), 10)

二、insert和update操作

insert和update的如下

import psycopg2
  
'''连接串'''
conn = psycopg2.connect(database="postgres", user="puser", password="Enmo@123", host="172.16.0.176", port="5432")
'''开启游标'''
cur = conn.cursor()
'''执行语句'''
sql1="insert into  public.demotable values('0',0)"
sql2="update public.demotable set num='111' where id=0 "

cur.execute(sql1)
print('insert success!')

cur.execute(sql2)
print('update succes!')
'''提交操作'''
conn.commit()
'''关闭游标'''
conn.close()

执行效果如下

[xmaster@mogdb-kernel-0005 demo2]$ psql -d postgres -c "select * from public.demotable where id=0"
 num | id 
-----+----
(0 rows)

[xmaster@mogdb-kernel-0005 demo2]$ python3 psycopg2_demo.py 
insert success!
update succes!
[xmaster@mogdb-kernel-0005 demo2]$ psql -d postgres -c "select * from public.demotable where id=0"
 num | id 
-----+----
 111 |  0
(1 row)

三、delete操作

delete操作的代码如下

import psycopg2
  
'''连接串'''
conn = psycopg2.connect(database="postgres", user="puser", password="Enmo@123", host="172.16.0.176", port="5432")
'''开启游标'''
cur = conn.cursor()
'''执行语句'''
sql1="delete from public.demotable where id=0"

cur.execute(sql1)
print('delete success!')

'''提交操作'''
conn.commit()
'''关闭游标'''
conn.close()

结果如下

[xmaster@mogdb-kernel-0005 demo2]$ psql -d postgres -c "select * from public.demotable where id=0"
 num | id 
-----+----
 111 |  0
(1 row)

[xmaster@mogdb-kernel-0005 demo2]$ python3 psycopg2_demo.py 
delete success!
[xmaster@mogdb-kernel-0005 demo2]$ psql -d postgres -c "select * from public.demotable where id=0"
 num | id 
-----+----
(0 rows)