Psycopg2 接口

1.簡介

Psycopg 是一種用于執(zhí)行SQL語句的PythonAPI，可以為PostgreSQL、HighGo 數(shù)據(jù)庫提供統(tǒng)一訪問接口，應(yīng)用程序可基于它進行數(shù)據(jù)操作。

Psycopg2是對libpq的封裝，主要使用C語言實現(xiàn)，既高效又安全。它具有客戶端游標和服務(wù)器端游標、異步通信和通知、支持“copy to/copy from”功能。支持多種類型Python開箱即用，適配PostgreSQL數(shù)據(jù)類型；通過靈活的對象適配系統(tǒng)，可以擴展和定制適配。

Psycopg2兼容Unicode和Python 3。

HighGo 數(shù)據(jù)庫提供了對 psycopg2 特性的支持，并且支持連接認證方式為國密 SM3。

2.下載安裝 Psycopg2

2.1.下載地址

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

2.2.Windows 環(huán)境

• 下載Python對應(yīng)版本的驅(qū)動
• 解壓到Python安裝目錄\Lib\site-packages下
• 打開一個終端，執(zhí)行如下操作無報錯說明 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 環(huán)境

1. 下載驅(qū)動
2. 將對應(yīng)版本的psycopg2驅(qū)動解壓，把psycopg2文件夾放在Python的模塊路徑中，如“/usr/lib64/python2.7/site-packages”，如果不確定Python模塊路徑,可命令執(zhí)行如下

[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']

3. 配置lib
   Python與數(shù)據(jù)庫不同服務(wù)器部署，需要系統(tǒng)中引入步驟2中解壓出來的libpq.so.5文件，例如把文件放到系統(tǒng)“/usr/lib64/”路徑。
   Python與數(shù)據(jù)庫同服務(wù)器部署，需配置環(huán)境變量如下：

export LD_LIBRARY_PATH=$HGDB_HOME/lib:$LD_LIBRARY_PATH

4. 打開一個終端，執(zhí)行如下操作無報錯說明 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程序中使用瀚高數(shù)據(jù)庫的要求。如果您正在尋找更復(fù)雜的應(yīng)用程序，則可以查閱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用于打開連接到瀚高數(shù)據(jù)庫。如果數(shù)據(jù)庫成功打開，則返回一個連接對象。
**connection.cursor() ** 該例程用于創(chuàng)建一個游標，它將在您使用 Python 進行數(shù)據(jù)庫編程的整個過程中使用。
cursor.execute(sql [, optional parameters]) 該例程執(zhí)行一條 SQL 語句。 SQL 語句可以參數(shù)化（即，占位符而不是 SQL 文字）。 psycopg2 模塊支持使用 %s 符號的占位符 例如：cursor.execute(“insert into people values (%s, %s)”, (who, age))
cursor.executemany(sql, seq_of_parameters) 此例程針對在序列 sql 中找到的所有參數(shù)序列或映射執(zhí)行 SQL 命令。
cursor.callproc(procname[, parameters]) 此例程執(zhí)行具有給定名稱的存儲數(shù)據(jù)庫過程。 對于過程期望的每個參數(shù)，參數(shù)序列必須包含一個條目。
cursor.rowcount 此只讀屬性返回最后一次執(zhí)行 *() 修改、插入或刪除的數(shù)據(jù)庫行總數(shù)。
connection.commit() 此方法提交當(dāng)前事務(wù)。 如果您不調(diào)用此方法，那么自上次調(diào)用 commit() 以來您所做的任何事情都不會從其他數(shù)據(jù)庫連接中看到。
connection.rollback() 此方法回滾自上次調(diào)用 commit() 以來對數(shù)據(jù)庫的任何更改。
connection.close() 此方法關(guān)閉數(shù)據(jù)庫連接。 請注意，這不會自動調(diào)用 commit()。 如果您只是關(guān)閉數(shù)據(jù)庫連接而不先調(diào)用 commit()，您的更改將會丟失！
cursor.fetchone() 此方法獲取查詢結(jié)果集的下一行，返回單個序列，或者當(dāng)沒有更多數(shù)據(jù)可用時返回 None。
cursor.fetchmany([size=cursor.arraysize]) 該例程獲取查詢結(jié)果的下一組行，并返回一個列表。 當(dāng)沒有更多行可用時，將返回一個空列表。 該方法嘗試獲取 size 參數(shù)所指示的行數(shù)。
cursor.fetchall() 此例程獲取查詢結(jié)果的所有（剩余）行，并返回一個列表。 當(dāng)沒有行可用時返回一個空列表。

4.數(shù)據(jù)庫操作

4.1.連接數(shù)據(jù)庫

以下 Python 代碼顯示了如何連接到現(xiàn)有數(shù)據(jù)庫。 如果數(shù)據(jù)庫不存在，則創(chuàng)建它，最后返回一個數(shù)據(jù)庫對象。

import psycopg2

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

print ("Opened database successfully")

如果數(shù)據(jù)庫成功打開，則會給出以下消息 ~

Opened database successfully

4.2.建表操作

以下 Python 程序?qū)⒂糜谠谙惹皠?chuàng)建的數(shù)據(jù)庫中創(chuàng)建表。

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

執(zhí)行上述程序時，它將在您的 testdb 中創(chuàng)建 COMPANY 表，并顯示以下消息 ~

Opened database successfully
Table created successfully

4.3.插入操作

以下 Python 程序顯示了我們?nèi)绾卧谏厦媸纠袆?chuàng)建的 COMPANY 表中創(chuàng)建記錄。

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

執(zhí)行上述給定程序時，它將在 COMPANY 表中創(chuàng)建給定記錄，并顯示以下兩行 ~

Opened database successfully
Records created successfully

4.4.查詢操作

以下 Python 程序展示了我們?nèi)绾螐纳厦媸纠袆?chuàng)建的 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()

執(zhí)行上述給定程序時，將產(chǎn)生以下結(jié)果 ~

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 代碼展示了我們?nèi)绾问褂?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()

執(zhí)行上述給定程序時，將產(chǎn)生以下結(jié)果 ~

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 代碼顯示了我們?nèi)绾问褂?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()

執(zhí)行上述給定程序時，將產(chǎn)生以下結(jié)果 ~

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.函數(shù)操作

以下代碼將用于在先前創(chuàng)建的數(shù)據(jù)庫 testdb 中創(chuàng)建函數(shù)。

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 代碼顯示了我們?nèi)绾握{(diào)用函數(shù)。

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

執(zhí)行上述給定程序時，將產(chǎn)生以下結(jié)果 ~

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

Operation done successfully

4.8.存儲過程

以下代碼將用于在先前創(chuàng)建的數(shù)據(jù)庫 testdb 中創(chuàng)建存儲過程。

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 代碼顯示了我們?nèi)绾握{(diào)用存儲過程。

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

執(zhí)行上述給定程序時，將產(chǎn)生以下結(jié)果 ~

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

Operation done successfully