本文實例講述了Python 操作 PostgreSQL 數據庫。分享給大家供大家參考，具體如下：

我使用的是 Python 3.7.0

PostgreSQL可以使用psycopg2模塊與Python集成。

sycopg2是用于Python編程語言的PostgreSQL數據庫適配器。

psycopg2是非常小，快速，穩定的。 您不需要單獨安裝此模塊，因為默認情況下它會隨著Python 2.5.x版本一起發布。

pip3 install python-psycopg2pip3 install psycopg2-binary

連接到數據庫

以下Python代碼顯示了如何連接到現有的數據庫。 如果數據庫不存在，那么它將自動創建，最后將返回一個數據庫對象。

#!/usr/bin/pythonimport psycopg2conn = psycopg2.connect(database='testdb', user='postgres', password='pass123', host='127.0.0.1', port='5432')print('Opened database successfully')

在這里指定使用testdb作為數據庫名稱，如果數據庫已成功打開連接，則會提供以下消息：

Open database successfully

創建表

以下Python程序將用于在先前創建的數據庫(testdb)中創建一個表：

#!/usr/bin/pythonimport psycopg2conn = 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()

當執行上述程序時，它將在數據庫testdb中創建COMPANY表，并顯示以下消息：

Opened database successfullyTable created successfully

插入操作

以下Python程序顯示了如何在上述示例中創建的COMPANY表中創建記錄：

#!/usr/bin/pythonimport psycopg2conn = 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()

當執行上述程序時，它將在COMPANY表中創建/插入給定的記錄，并顯示以下兩行：

Opened database successfullyRecords created successfully

SELECT操作

以下 Python 程序顯示了如何從上述示例中創建的 COMPANY 表中獲取和顯示記錄：

#!/usr/bin/pythonimport psycopg2conn = 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()

執行上述程序時，會產生以下結果：

Opened database successfullyID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000.0

ID = 2NAME = AllenADDRESS = TexasSALARY = 15000.0

ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0

ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000.0

Operation done successfully

更新操作

以下 Python 代碼顯示了如何使用UPDATE語句來更新任何記錄，然后從COMPANY表中獲取并顯示更新的記錄：

#!/usr/bin/pythonimport psycopg2conn = 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.commitprint('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()

Python

執行上述程序時，會產生以下結果：

Opened database successfullyTotal number of rows updated : 1ID = 1NAME = PaulADDRESS = CaliforniaSALARY = 25000.0

ID = 2NAME = AllenADDRESS = TexasSALARY = 15000.0

ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0

ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000.0

Operation done successfully

刪除操作

以下 Python 代碼顯示了如何使用 DELETE 語句來刪除記錄，然后從 COMPANY 表中獲取并顯示剩余的記錄：

#!/usr/bin/pythonimport psycopg2conn = 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.commitprint('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 successfullyTotal number of rows deleted : 1ID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000.0

ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0

ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000.0

Operation done successfully

更多關于Python相關內容感興趣的讀者可查看本站專題：《Python常見數據庫操作技巧匯總》、《Python數學運算技巧總結》、《Python數據結構與算法教程》、《Python函數使用技巧總結》、《Python字符串操作技巧匯總》、《Python入門與進階經典教程》及《Python文件與目錄操作技巧匯總》

希望本文所述對大家Python程序設計有所幫助。