Python 操作 PostgreSQL 數(shù)據(jù)庫示例【連接、增刪改查等】

更新時間：2020年04月21日 14:17:11 作者：Ryan_zheng

這篇文章主要介紹了Python 操作 PostgreSQL 數(shù)據(jù)庫的方法,結(jié)合實例形式分析了Python 連接PostgreSQL及增刪改查等相關(guān)操作技巧,需要的朋友可以參考下

本文實例講述了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-psycopg2
pip3 install psycopg2-binary

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

以下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")

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

Open database successfully

創(chuàng)建表

以下Python程序?qū)⒂糜谠谙惹皠?chuàng)建的數(shù)據(jù)庫(testdb)中創(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('''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 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()

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

Opened database successfully
Records created successfully

SELECT操作

以下 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)生以下結(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

更新操作

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

Python

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

刪除操作

以下 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)生以下結(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