psycopg2 接口

psycopg2库是python用来操作PostgreSQL数据库的第三方库，是PostgreSQL官方唯一指定与支持的Python驱动，也是使用最广泛的、最稳定的PostgreSQL Python driver。主要优势在于完全支持Python DB API 2.0，以及安全的多线程支持。它适用于随时创建、销毁大量游标的、和产生大量并发INSERT、UPDATE操作的多线程数据库应用。

psycopg 2 对 Unicode 和 Python 3 友好。

特性

  • 对libpq v3协议的完全支持；

  • COPY TO/COPY FROM语句；

  • 对所有基本Python类型的支持:字符串（支持Unicode）、整型、长整型、浮点型、缓冲区（二进制对象）、布尔型（逻辑型）、mx.DateTime和内置的日期时间型。

  • 还支持Unicode查询，和Python列表型与HGDB数组类型的映射。

运行步骤

  • 使用*.ini文件(python的configparser包能够解析这种类型的配置文件)保存数据库链接的配置信息。

  • 使用psycopg2.connect函数得到connection对象。

  • 使用connection对象建立cursor对象。

  • 使用cursor对象执行sql语句提交或者回滚transaction。

  • 使用cursor对象fetchone得到查询结果。

  • 关闭cursor对象和connection对象。

安装插件

pip install psycopg2

image-20220506152000063

  • connection类

connection类表示数据库连接对象。由psycopg2.connect()方法创建。

psycopg2.connect()函数创建一个新的数据库会话（Session）并且返回一个连接对象。该函数的参数为：

dbname – 数据库名字
user – 数据库角色名称
password – 数据库角色密码
host – 数据库地址
port – 端口

connection的主要方法：

commit()：提交任何未提交的事务（transaction）到数据库。
rollback()：回滚。
close()：关闭数据库。如果关闭数据库时仍有未提交的事务，则执行回滚操作。
  • cursor类

psycopg2提供了一个cursor类，用来在数据库Session里执行PostgreSQL命令。cursor对象由connection.cursor()方法创建：

cur = conn.cursor()

cursor的主要方法

execute(query, vars=None)：执行SQL语句。
fetchone()：返回单个的元组，也就是一条记录(row)，如果没有结果 则返回 None
fetchall()：返回多个元组，即返回多个记录(rows),如果没有结果 则返回 ()
fetchmany(n)：获取前 n 行数据。

有了cursor对象，就可以操作数据库了。

示例代码

企业版

psycopg2_conn.py

# -*-coding:utf-8-*-
import datetime
import psycopg2

try:
    host = "127.0.0.1"
    port = 5866
    dbname = "test"
    user = "test"
    password = "test"
   
    conn = psycopg2.connect(host=host, port=int(port), user=user, password=password, dbname=dbname)
    cursor = conn.cursor()
    cursor.execute("select '数据库版本:['||version()||']'")
    result = cursor.fetchall()
    print(result)
    
    print("开始进行查询操作....")
    
    #查询数据 
    cursor.execute("select count(*) from student")
    row = cursor.fetchone()   
    print("目前已有: " + str(row[0]) + "条数据")
    
    print("开始进行新增操作....")
   
    #新增
    
    """ 
    在DML 操作中，大部分情况都是套好“模版” 然后将实际值带入到传参,实际中postgresql 中的字段都是有不同的类型在使用，
    而在psycopg2中,所有的字段类型占位符均使用 %s,而不使用 %d (int),%f (float)等带入
    
    """
    insert_text = "insert into student(name, age, birthday) values (%s, %s, %s)"
    cursor.execute(insert_text, ("psycopg2_1", 24, datetime.datetime.now()))
    cursor.execute("insert into student(name, age, birthday) values (%s, %s, %s)", ("psycopg2_2", 24, datetime.datetime.now()))
    params = [('psycopg2_3', 18, '2019-07-06 15:59:58'), ('psycopg2_4', 20, '2019-07-06 15:59:58')]
    cursor.executemany("insert into student(name,age,birthday) values (%s, %s, %s)", params)
    
    #等价于等价于执行execute()函数两次    
    #for p in params:
    #    cursor.execute("insert into student(name,age,birthday) values (%s, %s, %s)", p)
    
    #提交数据（只有提交之后，所有的操作才会对实际的物理表格产生影响）    
    conn.commit()

    #查询数据
    cursor.execute("select * from student")
    rows = cursor.fetchall()
    print("查询到:" + str(rows.__len__()) + "条数据")
    print('{0}   {1}   {2}   {3}'.format('ID', '姓名', '年龄', '出生日期'))   
    for row in rows: 
        print('{0}   {1}   {2}   {3}'.format(row[0], row[1], row[2], row[3].strftime('%Y-%m-%d %H:%M:%S')))
   
    print("开始进行存储过程操作....")
    
    #执行存储过程 
    """
    1、使用cursor.callproc('function name', tuple), 函数的第一个参数是存储过程的名字，函数的第二个参数是实参tuple。这个调用和cursor.execute('select * from functionanme(%s)', tuple)相同
    2、使用cursor.fetchone, cursor.fetchmany, cursor.fetchall来得到返回值    
    """
    params = ("Dinsdale", 14, '')   
    cursor.callproc("t_return_val", params)
    result_args = cursor.fetchone()
    print("存储过程返回值:" + str(result_args[0]))    
    
    #关闭Cursor对象   
    cursor.close()
    #关闭连接   
    conn.close()
except Exception as e:
    print(e)

执行结果：

image-20220506184119921

image-20220506184455437

安全版

由于HGDB安全版对原生PostgreSQL的通信协议进行了安全加固，这导致与PostgreSQL的默认通信协议互相不兼容了，因此，使用psycpog2 的PostgreSQL原生版本默认是不能连接HGDB安全版的。会报类似下述错误：

image-20220506152424296

涉及的GUC参数是password_encryption，PostgreSQL默认的加密方式是md5，为了提高HGDB的安全能力，HGDB安全版支持sm3, 并且默认是sm3的加密方式，这就导致了上述报错。

通过修改参数进行规避

alter system set password_encryption to md5;

通过替换libpq 解决问题

如果想要使用更安全的加密算法，则必须要替换PostgreSQL原生的libpq了。方法如下：

1、执行 find / -name *_psycopg*命令，发现/usr/local/lib/python3.7/dist-packages/psycopg2/_psycopg.cpython-37m-aarch64-linux-gnu.so文件，该文件用于访问数据库。

2、执行ldd _psycopg.cpython-37m-aarch64-linux-gnu.so，发现该so文件引用了libpq.so.5文件，目录是/usr/lib/aarch64-linux-gnu。

3、用瀚高库lib目录下的libpq.so.5文件替换上面目录的对应文件，同时把libpq.so.5软链接引用的libpq.so.5.12文件复制到上面目录中。

通过重新编译psycopg2 解决问题

除手动替换之外，还可以在已经部署好HGDB的环境中通过编译psycopg2的源代码出包，这样经过编译的psycopg2包就会自带HGDB的libpq及其依赖文件，也更方便打包出包。方法如下：

1、在环境中安装HGDB，并配置好环境变量。

2、下载psycopg2的源代码，切换到源代码根目录中。

3、执行 python setup.py build 命令，编译好后会在跟目录下生成 build 子目录，内部即是编译后的包。

示例代码

psycopg2_conn_sm3.py

import os
import sys
import psycopg2

try:
    host = "192.168.2.5"
    port = 5866
    dbname = "test"
    user = "test"
    password = "test"
    conn = psycopg2.connect(host=host, port=int(port), user=user, password=password, dbname='test')
    cursor = conn.cursor()
    cursor.execute("select '数据库版本:['||version()||']'")
    result = cursor.fetchall()
    print(result)
except Exception as e:
    print(e)

执行结果：

image-20220506202303022