Python工具箱系列(三十)

PostgreSQL

MySQL的口号是“世界上最流行的开源关系型数据库”,而PostgreSQL的Slogan则是“世界上最先进的开源关系型数据库(PostgreSQL: The World's Most Advanced Open Source Relational Database)”,一看这就是一对老冤家了。这两个口号很好的反映出了两者的形象特质:PostgreSQL是功能丰富,高大上的严谨的学院派数据库;MySQL是轻简粗陋,糙猛快的“工程派”数据库。的确,除了MySQL外PostgreSQL(以后简称PG)是我们经常选择的数据库之一。它不仅仅是关系型数据库,同时也添加了对JSON数据的支持、全文检索功能,以及其他扩展。2020年2月的总排名为第四名,关系型数据库中排名第四名。在开源关系型数据库中排名第二,[排名信息参考DB-Engines](https://db-engines.com/en/ranking)。

PostgreSQL的优势

功能强大:PG是目前功能最强大的开源数据库。PG多年来在GIS领域处于优势地位,因为它有丰富的几何类型,实际上不止几何类型,PG有大量字典、数组、bitmap等数据类型。PG的“无锁定”特性非常突出。PG可以使用函数和条件索引,这使得PG数据库的调优非常灵活,mysql就没有这个功能,条件索引在web应用中很重要。PG有极其强悍的SQL编程能力(9.x图灵完备,支持递归!),有非常丰富的统计函数和统计语法支持,比如分析函数(ORACLE的叫法,PG里叫window函数),还可以用多种语言来写存储过程,对于R的支持也很好。PG不仅仅是SQL数据库,它可以存储array和json,可以在array和json上建索引,甚至还能用表达式索引。为了实现文档数据库的功能,设计了jsonb的存储结构。还有很逆天的fdw功能,就是把友商数据库的表当自己的表用。它自带全文搜索功能(不用费劲再装一个elasticsearch)。实现高效的正则搜索,可以高效处理图结构,轻松实现“朋友的朋友的朋友”这种功能。

稳定可靠:PG是能够做到数据零丢失的金融级开源数据库。Innodb等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步,然而很多MySQL用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的,相比之下,PG数据库这方面要好一些。任何系统都有它的性能极限,在高并发读写,负载逼近极限下,PG的性能指标仍可以维持双曲线甚至对数曲线,到顶峰之后不再下降。

开源省钱:PG是完全开源的、免费的。PG协议友善,采用了宽松的类BSD协议。各种数据库厂商,云厂商出品的“自研/国产数据库”,以及很多“云数据库”大多都是基于PostgreSQL改造的。例如最近HW基于PostgreSQL搞openGaussDB就是一个很明智的选择。PG的协议确实允许这样做,而且这样做也确实让PostgreSQL的生态更加繁荣壮大了。卖PostgreSQL衍生版是一个很成熟的市场:传统企业不差钱且愿意为此付费买单。开源天才之火有商业利益之油浇灌,因而源源不断地释放出旺盛的生命力。

支持广泛:PG得到了开发语言和各种框架的广泛支持。原生PG对Oracle的功能就做到了八九成的兼容性,因此可以看作是开源版的Oracle。是唯一能对Oracle构成直接威胁的开源关系型数据库,PostgreSQL及其衍生版本的技术优势是压倒性的。

社区活跃:PG基本上每三个月推出一个补丁版本,一直在快速升级中。

在ubuntu bionic的安装过程如下:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql postgresql-contrib
sudo systemctl start postgresql

# 查看端口是否绑定上
ss -nlt | grep 5432
sudo -u postgres psql -c "SELECT version();"
# 设置口令
sudo -u postgres psql
# 2次输入确认
\password
\q

安装PostgreSQL数据库之后,默认是只接受本地访问连接。如果想在其他主机上访问PostgreSQL数据库服务器,就需要进行相应的配置。配置远程连接PostgreSQL数据库的步骤很简单,只需要修改pg_hba.conf和postgresql.conf文件。

# 查找配置文件
find / -name pg_hba.conf
sed -i '$ahost  all    all    0.0.0.0/0    scram-sha-256' /etc/postgresql/14/main/pg_hba.conf
echo "listen_addresses = '*'" >> /etc/postgresql/14/main/postgresql.conf
systemctl restart postgresql

以上设置完成后,可以使用DBeaver连接数据库进行测试。配置正确的话,则可以看到以下类似的界面。

 

python有许多库可以访问PG,相对流行的有:

◆Psycopg2

◆pg8000

◆py-postgresql

◆PyGreSQL

◆ocpgdb

◆bpgsql

◆aiopg

目前流行的使用Psycopg2,其安装过程如下:

# 在ubuntu bionic下需要前置安装
sudo apt install python3-dev libpq-dev
pip install psycopg2

数据库操作的示例代码如下:

import psycopg2
import random

# 事先创建后数据库demodb
demodb = psycopg2.connect(database="demodb", user="postgres",
                          password="88488848", host="192.168.0.66", port="5432")

# 创建表
democur = demodb.cursor()
sql_createtable = 'create table demotb(id int primary key not null ,pm25 float not null)'
democur.execute(sql_createtable)

demodb.commit()

# 插入模拟出来的数据。
for id in range(100):
    pm25 = random.uniform(0, 300)
    insertsql = f'insert into demotb(id,pm25) values({id},{pm25})'
    democur.execute(insertsql)

demodb.commit()

democur.execute("SELECT id,pm25 from demotb where id > 49")
rows = democur.fetchall()
for row in rows:
    print(row)

democur.close()
demodb.close()

操作方式与mysql很相似,均是基于游标的底层操作,上述代码只是简单示例。此外需要注意的是,SQL虽然是一个标准化的语言,但每个数据库都有自己的方言,以创建表的语句为例,每个数据库的写法大同小异,有的主键支持自动增加,有的主键就不支持,只能够人工写入。

热门相关:帝少的专属:小甜心,太缠人   重生之至尊千金   名门天后:重生国民千金   夫人,你马甲又掉了!   无限杀路