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的安装过程如下:

1. sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
2. wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
3. sudo apt-get update
4. sudo apt-get -y install postgresql postgresql-contrib
5. sudo systemctl start postgresql
7. # 查看端口是否绑定上
8. ss -nlt | grep 5432
9. sudo -u postgres psql -c "SELECT version();"
10. # 设置口令
11. sudo -u postgres psql
12. # 2次输入确认
13. \password
14. \q

复制代码

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

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

复制代码

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

 
python有许多库可以访问PG，相对流行的有：
◆Psycopg2
◆pg8000
◆py-postgresql
◆PyGreSQL
◆ocpgdb
◆bpgsql
◆aiopg
目前流行的使用Psycopg2，其安装过程如下:

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

复制代码

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

1. import psycopg2
2. import random
4. # 事先创建后数据库demodb
5. demodb = psycopg2.connect(database="demodb", user="postgres",
6.                           password="88488848", host="192.168.0.66", port="5432")
8. # 创建表
9. democur = demodb.cursor()
10. sql_createtable = 'create table demotb(id int primary key not null ,pm25 float not null)'
11. democur.execute(sql_createtable)
13. demodb.commit()
15. # 插入模拟出来的数据。
16. for id in range(100):
17.     pm25 = random.uniform(0, 300)
18.     insertsql = f'insert into demotb(id,pm25) values({id},{pm25})'
19.     democur.execute(insertsql)
21. demodb.commit()
23. democur.execute("SELECT id,pm25 from demotb where id > 49")
24. rows = democur.fetchall()
25. for row in rows:
26.     print(row)
28. democur.close()
29. demodb.close()

复制代码

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

来源:https://www.cnblogs.com/shanxihualu/p/17380774.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！