PostgreSQL简介
以下摘自百度百科：
PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样，PostgreSQL也可以用许多方法扩展，例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外，因为许可证的灵活，任何人都可以以任何目的免费使用、修改和分发PostgreSQL。
PostgreSQL是一个完全开源的关系型数据库，其功能不逊色与MySQL，但是用户量并没有MySQL那么多。不过PostgreSQL的执行语句与MySQL和MSSQL基本相同，所以对于会MySQL或者MSSQL的人来说，PostgreSQL的学习成本并不高。国内也有不少PostgreSQL的论坛与资料网站，有需要可以自行搜索。
PostgreSQL的官网为：https://www.postgresql.org/
支持PostgreSQL的库也有不少，从PostgreSQL提供的WIKI可以得知，使用得最多的而且最成熟的是Psycopg2，故本文也使用这个库作为演示示例。
传送门：
本文代码已上传至GitHub，项目地址如下：
GitHub - XMNHCAS/ProgreSqlPythonDemo
安装Psycopg2
使用pip3安装psycopg2，命令如下：
pip3 install psycopg2
增删改查（CRUD）
新建数据库
PostgreSQL不支持"SHOW DATABASES"语句，需要使用"SELECT * FROM PG_DATABASE;"进行查询，才能获取当前数据库列表，由于这里只需要做创建数据库前后的对比，所以只获取数据库名字段，即dtname。该程序创建的数据库为root用户所有。
创建数据库的时候，为避免出错，需要将数据库连接切换到自动提交模式。
import psycopg2
# 打印数据函数
def ShowData(str, data):
arr = []
for item in data:
arr.append(item)
print(str, arr)
# 打开数据库连接
db = psycopg2.connect(host='localhost',
port='5432',
user='root',
password='123456')
# 设置数据库连接打开自动提交模式
db.autocommit = True
# 创建游标对象
cursor = db.cursor()
# 查看现有数据库
sql = 'SELECT DATNAME FROM PG_DATABASE;'
cursor.execute(sql)
ShowData("现有数据库：", cursor.fetchall())
# 创建test数据库
sql = 'CREATE DATABASE "test";'
cursor.execute(sql)
# 查看新数据库创建完成后的数据库列表
sql = 'SELECT DATNAME FROM PG_DATABASE;'
cursor.execute(sql)
ShowData("创建完成后的数据库：", cursor.fetchall())
# 释放游标及数据库连接
cursor.close()
db.close()
运行结果如下，可以看到数据库被成功创建：
新建数据表
新建数据表跟新建数据库相似。PostgreSQL也不支持"SHOW TABLES"，所以使用"SELECT * FROM PG_TABLES WHERE SCHEMANAME = 'public';" 来获取指定数据库的现有数据表，本示例仅获取tablename字段，即表名。
import psycopg2
# 打印数据函数
def ShowData(str, data):
arr = []
for item in data:
arr.append(item)
print(str, arr)
# 打开数据库连接
db = psycopg2.connect(host='localhost',
port='5432',
user='root',
password='123456',
database='test')
# 创建游标对象
cursor = db.cursor()
# 查看现有表
sql = "SELECT tablename FROM PG_TABLES WHERE SCHEMANAME = 'public';"
cursor.execute(sql)
ShowData("现有表：", cursor.fetchall())
# 创建Person表
sql = """CREATE TABLE "Person"(
"ID" SERIAL PRIMARY KEY NOT NULL,
"Name" TEXT,
"Age" INT)
"""
cursor.execute(sql)
# 查看新数据库创建完成后的数据库列表
sql = "SELECT tablename FROM PG_TABLES WHERE SCHEMANAME = 'public';"
cursor.execute(sql)
ShowData("创建完成后的表：", cursor.fetchall())
# 提交事务
db.commit()
# 释放游标及数据库连接
cursor.close()
db.close()
运行结果如下，Person表已经被成功创建：
由以上代码可以得知，建表语句与MySQL和MSSQL也基本相同，但是需要注意的是，ID字段的设置是“SERIAL PRIMARY KEY NOT NULL”，意思是ID为自增的非空主键，实际创建成功后，ID字段的数据类型还是int4，如下图所示：
新增记录（Create）
新增记录基本上是大同小异，但是需要注意的是，SQL语句的表名和字段名必须要使用双引号包裹起来 ，否则会执行出错。
import psycopg2
# 打开数据库连接
db = psycopg2.connect(host='localhost',
port='5432',
user='root',
password='123456',
database='test')
# 创建游标对象
cursor = db.cursor()
# 插入单条数据
sql = 'INSERT INTO "Person" ("Name", "Age") VALUES (\'%s\', %s)' % ("Tom", 18)
cursor.execute(sql)
print(f"成功插入{cursor.rowcount}条数据")
# 批量插入数据
sql = 'INSERT INTO "Person" ("Name", "Age") VALUES (%s, %s);'
cursor.executemany(sql, [('Tim', 20), ('Jane', 10)])
print(f"成功插入{cursor.rowcount}条数据")
# 提交事务
db.commit()
# 释放游标及数据库连接
cursor.close()
db.close()
运行结果如下：
使用Navicat查询，可以看到数据已经被成功插入到Person表中：
检索记录（Retrieve）
psycopg2同样提供了三种查询方法，fetchall、fetchmany和fetchone，使用方式与pymysql基本一致。
import psycopg2
# 打印数据函数
def ShowData(str, data):
arr = []
for item in data:
arr.append(item)
print(str, arr)
# 打开数据库连接
db = psycopg2.connect(host='localhost',
port='5432',
user='root',
password='123456',
database='test')
# 创建游标对象
cursor = db.cursor()
# 获取全部记录
cursor.execute('SELECT * FROM "Person";')
ShowData("直接获取全部记录：", cursor.fetchall())
# 获取前N条记录
cursor.execute('SELECT * FROM "Person";')
ShowData("获取部分结果：", cursor.fetchmany(2))
# 一次读取一条结果，循环获取所有记录
cursor.execute('SELECT * FROM "Person";')
print("一次读取一条结果，循环获取所有记录：")
while True:
singleData = cursor.fetchone()
if singleData is None:
break
print(singleData)
# 提交事务
db.commit()
# 释放游标及数据库连接
cursor.close()
db.close()
运行结果如下：
更新记录（Update）
psycopg2支持单条数据更新及批量更新，不过需要注意的是，PostgreSQL的数据被修改后，直接SELECT的话，数据并不会根据主键自动排序，所以本示例后面的查询添加了“ORDER BY”，用以对数据根据ID字段进行排序。
import psycopg2
# 打印数据函数
def ShowData(str, data):
arr = []
for item in data:
arr.append(item)
print(str, "\n", arr)
# 打开数据库连接
db = psycopg2.connect(host='localhost',
port='5432',
user='root',
password='123456',
database='test')
# 创建游标对象
cursor = db.cursor()
# 修改前的数据
cursor.execute('SELECT * FROM "Person";')
ShowData("修改前的数据：", cursor.fetchall())
# 执行单条数据修改
cursor.execute('UPDATE "Person" SET "Age" = 22 WHERE "Name" = \'Tom\'')
# 修改单条数据后的数据
cursor.execute('SELECT * FROM "Person" ORDER BY "ID";')
ShowData("修改单条数据后的数据：", cursor.fetchall())
# 执行批量修改
sql = 'UPDATE "Person" SET "Age" = %s WHERE "Name" = %s'
batchUpdate = cursor.executemany(sql, [(25, 'Tim'), (18, 'Jane')])
# 批量修改后的数据
cursor.execute('SELECT * FROM "Person" ORDER BY "ID";')
ShowData("批量修改后的数据：", cursor.fetchall())
# 提交事务
db.commit()
# 释放游标及数据库连接
cursor.close()
db.close()
运行结果如下：
删除数据（Delete）
删除数据与更新数据的操作基本相同。
在MySQL和MSSQL中，“TRUNCATE TABLE” 语句会将表的数据清空，而且会重置自增主键的计数，但是PostgreSQL中，必须要在这个语句后面添加“RESTART IDENTITY”，才能将主键重置，不然就只会清空表。
import psycopg2
# 打印数据函数
def ShowData(str, data):
arr = []
for item in data:
arr.append(item)
print(str, "\n", arr)
# 打开数据库连接
db = psycopg2.connect(host='localhost',
port='5432',
user='root',
password='123456',
database='test')
# 创建游标对象
cursor = db.cursor()
retrieveSql = 'SELECT * FROM "Person" ORDER BY "ID";'
# 删除前的数据
cursor.execute(retrieveSql)
ShowData("删除前的数据：", cursor.fetchall())
# 执行单条数据删除
cursor.execute('DELETE FROM "Person" WHERE "ID" = 1;')
# 删除单条数据后的数据
cursor.execute(retrieveSql)
ShowData("删除单条数据后的数据：", cursor.fetchall())
# 执行批量删除
sql = 'DELETE FROM "Person" WHERE "ID" = %s;'
cursor.executemany(sql, [('2'), ('3')])
# 批量删除后的的数据
cursor.execute(retrieveSql)
ShowData("删除单条数据后的数据：", cursor.fetchall())
# 清空表，并重置自增主键计数
sql = 'TRUNCATE TABLE "Person" RESTART IDENTITY'
cursor.execute(sql)
# 提交事务
db.commit()
# 释放游标及数据库连接
cursor.close()
db.close()
运行结果如下：
454
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
