Python 操作 PostgreSQL 数据库完整指南：连接、建表与增删改查
本文详细介绍如何使用 Python 的 psycopg2 模块连接 PostgreSQL 数据库。内容涵盖从环境准备、建立数据库连接，到创建表、插入、查询、更新及删除记录的完整代码示例，帮助开发者快速掌握 Python 对 PostgreSQL 的基础操作。
本文介绍利用 Python 操作 PostgreSQL 数据库的具体方法，供读者参考学习。
本文示例基于 Python 3.7.0 版本环境。
PostgreSQL 数据库可通过 psycopg2 模块与 Python 进行集成。
psycopg2 是专为 Python 设计的 PostgreSQL 数据库适配器。
psycopg2 具有体积小、运行速度快且稳定性高的特点。需要注意的是，该模块默认并不随 Python 2.5.x 版本发布，需单独安装。
pip3 install python-psycopg2 pip3 install psycopg2-binary
数据库连接
以下 Python 代码演示了如何连接到现有数据库。若目标数据库尚不存在，系统将自动创建该数据库，并最终返回一个数据库连接对象。
#!/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 作为数据库名称。若数据库连接成功建立，控制台将输出以下提示信息：
Open database successfully
创建数据表
以下 Python 程序用于在先前创建的数据库（testdb）中创建一个新表：
#!/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()
运行上述程序后，将在数据库 testdb 中成功创建 COMPANY 表，并显示如下消息：
Opened database successfully Table created successfully
插入数据操作
以下 Python 程序演示了如何向上述示例中创建的 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("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 successfully Records created successfully
查询数据操作
以下 Python 程序展示了如何从 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()
运行上述程序后，将输出以下结果：
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
运行上述程序后，将产生以下结果：
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()
运行上述程序后，将产生以下结果：
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
更多关于 Python 数据库操作的内容，感兴趣的读者可参考本站专题：《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》。
希望本文所述的 Python 数据库操作示例能对读者的程序设计工作有所帮助。
本站声明
本文内容仅供参考，不代表本站观点。转载或引用请注明出处。如发现内容问题，请通过联系我们页面反馈。
