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,故本文也使用这个库作为演示示例。

传送门: 

Python数据库操作【一】—— Sqlite

Python数据库操作【二】—— MySQL

Python数据库操作【三】—— SQLServer

本文代码已上传至GitHub,项目地址如下:

GitHub - XMNHCAS/ProgreSqlPythonDemo


安装Psycopg2

使用pip3安装psycopg2,命令如下:

pip3 install psycopg2

GitHub

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

运行结果如下: 

 

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐