Psycopg2 接口

1.简介

Psycopg 是一种用于执行SQL语句的PythonAPI，可以为PostgreSQL、HighGo 数据库提供统一访问接口，应用程序可基于它进行数据操作。

Psycopg2是对libpq的封装，主要使用C语言实现，既高效又安全。它具有客户端游标和服务器端游标、异步通信和通知、支持“copy to/copy from”功能。支持多种类型Python开箱即用，适配PostgreSQL数据类型；通过灵活的对象适配系统，可以扩展和定制适配。

Psycopg2兼容Unicode和Python 3。

HighGo 数据库提供了对 psycopg2 特性的支持，并且支持连接认证方式为国密 SM3。

2.下载安装 Psycopg2

2.1.下载地址

链接：https://pan.baidu.com/s/1xuz6uJz0utRgKWecXhpOiA?pwd=o0tj

2.2.Windows 环境

  • 下载Python对应版本的驱动
  • 解压到Python安装目录\Lib\site-packages下
  • 打开一个终端，执行如下操作无报错说明 psycopg2 安装并成功加载
C:\Users\Administrator>pip list
DEPRECATION: Loading egg at d:\python\python311\lib\site-packages\psycopg2-2.9.9-py3.11-win-amd64.egg is deprecated. pip 24.3 will enforce this behaviour change. A possible replacement is to use pip for package installation.. Discussion can be found at https://github.com/pypa/pip/issues/12330
Package    Version
---------- -------
asgiref    3.7.2
Django     5.0.3
pip        24.0
psycopg2   2.9.9
setuptools 65.5.0
sqlparse   0.4.4
tornado    6.4
torndb     0.3
tzdata     2024.1
C:\Users\Administrator>python
Python 3.11.8 (tags/v3.11.8:db85d51, Feb  6 2024, 22:03:32) [MSC v.1937 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'psycopg2'
>>> import psycopg2
>>> psycopg2.__version__
'2.9.9 (dt dec pq3 ext lo64)'
>>>

2.3.Linux 环境

  1. 下载驱动
  2. 将对应版本的psycopg2驱动解压，把psycopg2文件夹放在Python的模块路径中，如“/usr/lib64/python2.7/site-packages”，如果不确定Python模块路径,可命令执行如下
[root@localhost ~]# python
Python 2.7.5 (default, Nov 14 2023, 16:14:06) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> sys.path
['', '/usr/lib64/python27.zip', '/usr/lib64/python2.7', '/usr/lib64/python2.7/plat-linux2', '/usr/lib64/python2.7/lib-tk', '/usr/lib64/python2.7/lib-old', '/usr/lib64/python2.7/lib-dynload', '/usr/lib64/python2.7/site-packages', '/usr/lib/python2.7/site-packages']
  1. 配置lib
    Python与数据库不同服务器部署，需要系统中引入步骤2中解压出来的libpq.so.5文件，例如把文件放到系统“/usr/lib64/”路径。
    Python与数据库同服务器部署，需配置环境变量如下：
export LD_LIBRARY_PATH=$HGDB_HOME/lib:$LD_LIBRARY_PATH
  1. 打开一个终端，执行如下操作无报错说明 psycopg2 安装并成功加载
[root@localhost ~]# python
Python 2.7.5 (default, Nov 14 2023, 16:14:06) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> psycopg2.__version__
'2.7.5 (dt dec pq3 ext lo64)'
>>>

3.psycopg2 模块 API

以下是重要的 psycopg2 模块例程，它们可以满足您在Python程序中使用瀚高数据库的要求。如果您正在寻找更复杂的应用程序，则可以查阅Python psycopg2模块的官方文档。

API和描述
conn=psycopg2.connect(dbname=”testdb”, user=”sysdba”, password=”pass123”, host=”127.0.0.1”, port=”5866”)
conn=psycopg2.connect(database=”testdb”, user=”sysdba”, password=”pass123”, host=”127.0.0.1”, port=”5866”)
该API用于打开连接到瀚高数据库。如果数据库成功打开，则返回一个连接对象。
**connection.cursor() **
该例程用于创建一个游标，它将在您使用 Python 进行数据库编程的整个过程中使用。
cursor.execute(sql [, optional parameters])
该例程执行一条 SQL 语句。 SQL 语句可以参数化（即，占位符而不是 SQL 文字）。 psycopg2 模块支持使用 %s 符号的占位符
例如：cursor.execute(“insert into people values (%s, %s)”, (who, age))
cursor.executemany(sql, seq_of_parameters)
此例程针对在序列 sql 中找到的所有参数序列或映射执行 SQL 命令。
cursor.callproc(procname[, parameters])
此例程执行具有给定名称的存储数据库过程。 对于过程期望的每个参数，参数序列必须包含一个条目。
cursor.rowcount
此只读属性返回最后一次执行 *() 修改、插入或删除的数据库行总数。
connection.commit()
此方法提交当前事务。 如果您不调用此方法，那么自上次调用 commit() 以来您所做的任何事情都不会从其他数据库连接中看到。
connection.rollback()
此方法回滚自上次调用 commit() 以来对数据库的任何更改。
connection.close()
此方法关闭数据库连接。 请注意，这不会自动调用 commit()。 如果您只是关闭数据库连接而不先调用 commit()，您的更改将会丢失！
cursor.fetchone()
此方法获取查询结果集的下一行，返回单个序列，或者当没有更多数据可用时返回 None。
cursor.fetchmany([size=cursor.arraysize])
该例程获取查询结果的下一组行，并返回一个列表。 当没有更多行可用时，将返回一个空列表。 该方法尝试获取 size 参数所指示的行数。
cursor.fetchall()
此例程获取查询结果的所有（剩余）行，并返回一个列表。 当没有行可用时返回一个空列表。

4.数据库操作

4.1.连接数据库

以下 Python 代码显示了如何连接到现有数据库。 如果数据库不存在，则创建它，最后返回一个数据库对象。

import psycopg2

conn = psycopg2.connect(database="testdb", user = "sysdba", password = "pass123", host = "127.0.0.1", port = "5866")

print ("Opened database successfully")

如果数据库成功打开，则会给出以下消息 ~

Opened database successfully

4.2.建表操作

以下 Python 程序将用于在先前创建的数据库中创建表。

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "", password = "pass123", host = "127.0.0.1", port = "5866")
print ("Opened database successfully")

cur = conn.cursor()
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 created successfully")

conn.commit()
conn.close()

执行上述程序时，它将在您的 testdb 中创建 COMPANY 表，并显示以下消息 ~

Opened database successfully
Table created successfully

4.3.插入操作

以下 Python 程序显示了我们如何在上面示例中创建的 COMPANY 表中创建记录。

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "sysdba", password = "pass123", host = "127.0.0.1", port = "5866")
print ("Opened database successfully")

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()

执行上述给定程序时，它将在 COMPANY 表中创建给定记录，并显示以下两行 ~

Opened database successfully
Records created successfully

4.4.查询操作

以下 Python 程序展示了我们如何从上面示例中创建的 COMPANY 表中获取和显示记录。

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "sysdba", password = "pass123", host = "127.0.0.1", port = "5866")
print ("Opened database successfully")

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print("Operation done successfully");
conn.close()

执行上述给定程序时，将产生以下结果 ~

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

4.5.更新操作

以下 Python 代码展示了我们如何使用 UPDATE 语句更新任何记录，然后从我们的 COMPANY 表中获取并显示更新的记录。

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "sysdba", password = "pass123", host = "127.0.0.1", port = "5866")
print("Opened database successfully")

cur = conn.cursor()

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print ("Total number of rows updated :", cur.rowcount)

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print ("Operation done successfully");
conn.close()

执行上述给定程序时，将产生以下结果 ~

Opened database successfully
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

4.6.删除操作

以下 Python 代码显示了我们如何使用 DELETE 语句删除任何记录，然后从我们的 COMPANY 表中获取并显示剩余记录。

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "sysdba", password = "pass123", host = "127.0.0.1", port = "5866")
print ("Opened database successfully")

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print ("Total number of rows deleted :", cur.rowcount)

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print ("Operation done successfully");
conn.close()

执行上述给定程序时，将产生以下结果 ~

Opened database successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

4.7.函数操作

以下代码将用于在先前创建的数据库 testdb 中创建函数。

create function fun_refcursor(refcursor,integer) returns refcursor as $$
begin 
   open $1 for select id, name, address, salary from company where id = $2;
   return $1;
end;
$$ language plpgsql;

以下 Python 代码显示了我们如何调用函数。

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "sysdba", password = "pass123", host = "127.0.0.1", port = "5866")
print("Opened database successfully")

cur = conn.cursor()
cur.callproc('fun_refcursor', ['fun_refcursor', 1])

rows = conn.cursor('fun_refcursor')
for row in rows:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print ("Operation done successfully");
conn.close()

执行上述给定程序时，将产生以下结果 ~

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

Operation done successfully

4.8.存储过程

以下代码将用于在先前创建的数据库 testdb 中创建存储过程。

create procedure proc_refcursor(integer, inout refcursor) as $$
begin 
   open $2 for select id, name, address, salary from company where id = $1;
   exception when others then 
     raise exception 'sql exception--%',sqlerrm; 
end;
$$ language plpgsql;

以下 Python 代码显示了我们如何调用存储过程。

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "sysdba", password = "pass123", host = "127.0.0.1", port = "5866")
print("Opened database successfully")

cur = conn.cursor()
cur.execute("CALL proc_refcursor(%s, %s);", (1, 'proc_refcursor'))

rows = conn.cursor('proc_refcursor')
for row in rows:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print ("Operation done successfully");
conn.close()

执行上述给定程序时，将产生以下结果 ~

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

Operation done successfully