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

我使用的是 Python 3.7.0

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

sycopg2是用于Python編程語言的PostgreSQL數(shù)據(jù)庫適配器。

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

pip3 install python-psycopg2pip3 install psycopg2-binary

連接到數(shù)據(jù)庫

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

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

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

Open database successfully

創(chuàng)建表

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

#!/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()

當(dāng)執(zhí)行上述程序時，它將在數(shù)據(jù)庫testdb中創(chuàng)建COMPANY表，并顯示以下消息：

Opened database successfullyTable created successfully

插入操作

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

#!/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()

當(dāng)執(zhí)行上述程序時，它將在COMPANY表中創(chuàng)建/插入給定的記錄，并顯示以下兩行：

Opened database successfullyRecords created successfully

SELECT操作

以下 Python 程序顯示了如何從上述示例中創(chuàng)建的 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()

執(zhí)行上述程序時，會產(chǎn)生以下結(jié)果：

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

執(zhí)行上述程序時，會產(chǎn)生以下結(jié)果：

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

執(zhí)行上述程序時，會產(chǎn)生以下結(jié)果：

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

更多關(guān)于Python相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《Python常見數(shù)據(jù)庫操作技巧匯總》、《Python數(shù)學(xué)運算技巧總結(jié)》、《Python數(shù)據(jù)結(jié)構(gòu)與算法教程》、《Python函數(shù)使用技巧總結(jié)》、《Python字符串操作技巧匯總》、《Python入門與進(jìn)階經(jīng)典教程》及《Python文件與目錄操作技巧匯總》

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