PostgreSQL可以使用psycopg2模塊與Python集成。sycopg2是一個用于Python編程語言的PostgreSQL數(shù)據(jù)庫適配器。psycopg2的寫作目的是非常小，速度快，穩(wěn)定的磐石。您不需要單獨安裝此模塊，因為默認情況下，它是與Python2.5.x版一起提供的。
如果您的機器上沒有安裝它，那么您可以使用 yum 命令按照以下方式安裝它
$yum install python-psycopg2
要使用 psycopg2模塊，必須首先創(chuàng)建一個表示數(shù)據(jù)庫的 Connection 對象，然后可以選擇創(chuàng)建游標對象，這將有助于執(zhí)行所有 SQL 語句。
以下是重要的psycopg2 模塊，可以滿足您使用Python程序中的PostgreSQL數(shù)據(jù)庫的需求。如果您正在尋找更復雜的應用程序，那么可以查看Python psycopg2模塊的官方文檔。
| S. No. | API & 描述 |
|---|---|
| 1 | psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432") 這個 API 打開一個到 PostgreSQL 數(shù)據(jù)庫的連接。如果數(shù)據(jù)庫成功打開，它將返回一個連接對象。 |
| 2 | connection.cursor() 創(chuàng)建一個游標，這個游標將在使用 Python 進行數(shù)據(jù)庫編程的過程中使用。 |
| 3 | cursor.execute(sql [, optional 參數(shù)]) 執(zhí)行SQL語句。SQL語句可以參數(shù)化（即占位符而不是SQL文本）。psycopg2模塊支持使用%s符號的占位符 例如:cursor.execute("insert into people values (%s, %s)", (who, age)) |
| 4 | cursor.executemany(sql, seq_of_parameters) 對在序列SQL中找到的所有參數(shù)序列或映射執(zhí)行SQL命令。 |
| 5 | cursor.callproc(procname[, parameters]) 執(zhí)行具有給定名稱的存儲數(shù)據(jù)庫過程。參數(shù)的序列必須為過程期望的每個參數(shù)包含一個條目。 |
| 6 | cursor.rowcount 只讀屬性，它返回上次最后一次執(zhí)行execute*()修改、插入或刪除的數(shù)據(jù)庫行總數(shù)。 |
| 7 | connection.commit() 此方法提交當前事務。如果不調用此方法，則自上次調用commit()以來所做的任何操作都無法從其他數(shù)據(jù)庫連接中看到。 |
| 8 | connection.rollback() 返回回滾自上次調用commit()以來對數(shù)據(jù)庫所做的任何更改。 |
| 9 | connection.close() 此方法關閉數(shù)據(jù)庫連接。注意，這不會自動調用 commit ()。如果您只是關閉數(shù)據(jù)庫連接而沒有首先調用 commit () ，那么您的更改將丟失！ |
| 10 | cursor.fetchone() 此方法獲取查詢結果集的下一行，返回單個序列，或者在沒有更多數(shù)據(jù)可用時返回 None。 |
| 11 | cursor.fetchmany([size=cursor.arraysize]) 獲取查詢結果的下一組行，并返回一個列表。當沒有更多行可用時，將返回空列表。該方法嘗試獲取由size參數(shù)指示的盡可能多的行。 |
| 12 | cursor.fetchall() 獲取查詢結果的所有（剩余）行，并返回一個列表。當沒有行可用時，將返回空列表。 |
下面的Python代碼展示了如何連接到現(xiàn)有數(shù)據(jù)庫。如果數(shù)據(jù)庫不存在，那么將創(chuàng)建它，最后將返回一個數(shù)據(jù)庫對象。
#!/usr/bin/python import psycopg2 conn = psycopg2.connect(database="testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432") print "Opened database successfully"
在這里，您還可以提供數(shù)據(jù)庫 testdb 作為名稱，如果數(shù)據(jù)庫成功打開，那么它將給出以下消息
Open database successfully
下面的Python程序將用于在先前創(chuàng)建的數(shù)據(jù)庫-
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
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í)行上述程序時，它會在test.db中創(chuàng)建COMPANY表，并顯示以下消息
Opened database successfully Table created successfully
下面的Python程序演示了如何在上面示例中創(chuàng)建的COMPANY表中創(chuàng)建記錄
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
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
下面的Python程序展示了如何從上面示例中創(chuàng)建的COMPANY表中獲取和顯示記錄
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
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)生如下結果
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
下面的 Python 代碼展示了如何使用 UPDATE 語句更新任何記錄，然后從 COMPANY 表-獲取并顯示更新的記錄
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
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)生如下結果
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
下面的Python代碼展示了如何使用DELETE語句刪除任何記錄，然后從我們的COMPANY表中獲取并顯示剩余的記錄
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
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)生如下結果
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