Postgresql之自动化脚本操作

Author：OnceDay Date：2023年3月15日

漫漫长路，才刚刚开始…

1. 概述

本文总结一些最近使用的postgresql脚本使用方法，并非专业数据库维护人员，仅用于入门使用。

下面是官方引导文档：

• PostgreSQL Tutorial - Learn PostgreSQL from Scratch

安装postgresql比较方便，下面以linux为例，对于其他平台，可参考以下文档：

• Getting Started with PostgreSQL (postgresqltutorial.com)

在Ubuntu下，按以下步骤执行命令即可(最基础的安装，不包含指定版本和仓库源操作):

• sudo apt-get update，更新仓库信息。
• sudo apt-get install postgresql，安装postgresql。

然后等待一段时间即可。

一般postgresql不允许使用root账户进行操作，可以创建新账户来使用postgresql，这里使用默认的postgres账号，由postgresql安装时自动创建。

使用下面命令切换到postgres账户：

sudo -i -u postgres

然后就可以使用psql来操作数据库了。

onceday->root:$ psql --help
psql is the PostgreSQL interactive terminal.Usage:psql [OPTION]... [DBNAME [USERNAME]]
.....

通过上面的命令可以查看psql支持的命令。

1.2 基础的postgresql使用

对于非专业数据库操作人员来说，比如偶尔需要使用数据库查看数据的底层开发人员，掌握以下几个基础命令即可。

首先进入命令行：

onceday->root:$ psql
psql (13.3)
Type "help" for help.postgres=# 

查看命令行内的帮助信息：

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms\h for help with SQL commands\? for help with psql commands\g or terminate with semicolon to execute query\q to quit

可以看到，使用\q或ctrl +D可以退出psql命令行。\h查看SQL标准的命令。\?则是查看psql独有的命令。

一般使用\l列出当前的全部数据库，使用\c选择特定的数据库。

postgres=# \lList of databasesName      |    Owner    | Encoding  | Collate | Ctype |   Access privileges   
----------------+-------------+-----------+---------+-------+-----------------------logXXXXXDb   | log_collect | SQL_ASCII | C       | C     | 
......
postgres=# \c logXXXXXDb
You are now connected to database "logXXXXXDb" as user "postgres".

其他有用的命令还有很多。当选择到特定数据库之后，就可以使用标准的sql语句来查询了。

下面直接进入主题。

2. python自动化读写

重点看一下以下几个psql支持的命令：

-c, --command=COMMAND    run only single command (SQL or internal) and exit
-d, --dbname=DBNAME      database name to connect to (default: "postgres")
-f, --file=FILENAME      execute commands from file, then exit
-o, --output=FILENAME    send query results to file (or |pipe)

即可以指定数据库，并且能从命令行和文件中读取指令，然后输出到指定文件中。

下面是一个简单的例子：

onceday:$ psql -d logXXXXDb -c 'select * from "flowXXXX";'timestamp  | value1
------------+------+1677600000 |    1 |1677600000 |    2 |1677600000 |    3 |1677600000 |    4 |

注意，这里使用单引号包裹，因为在postgresql里面带大写的表名需要使用双引号括起来，才能正确识别。

为了避免单引号和双引号冲突，可以根据支持来合理使用。

2.1 从CSV文件中导入数据到数据库里

COPY命令支持从csv导入数据到表中，命令格式如下：

COPY "table_name" (datetime, type, val1, val2)
FROM './data.csv'
DELIMITER ','
CSV HEADER;

• table_name是想导入数据的目标表名，用双引号括起来，防止无法识别大写的情况。
• (datetime, type, val1, val2)是准备导入的四列数据，对应表中的列名。
• FROM './data.csv'是导入的CSV数据所在路径。
• DELIMITER是CSV数据的分割符。
• CSV HEADER，是忽略CSV数据的第一行，为数据标题。

如下的CSV数据便可以导入：

datetime, type, val1, val2
1111, 2, 3, 4
1112, 2, 3, 4
1113, 2, 3, 4
......

一般会使用\copy形式，这表示在psql这边执行，COPY在服务端执行。

\copy "table_name" (datetime, type, val1, val2) from './data.csv' delimiter ',' csv header

两者格式一模一样，没有区别。

下面是一个完整的创建表格+导入数据的sql脚本。

--psql -d logConnectDb -f this_file_path
-- 切换到指定数据库,省略
-- 创建数据表
CREATE TABLE If not exists "table_name"(timestamp bigint,type integer,value1 integer,value2 integer
);
--创建索引
CREATE INDEX "table_name_timestamp_index"
ON "table_name" (timestamp);
CREATE INDEX "table_name_type_index"
ON "table_name" (type);
--导入数据
\copy "table_name" (timestamp, type, value1, value2) from './data.csv' delimiter ',' csv header

在Python里面可以如下编写：

def write_sql_file(table_name):tem_sql = f'''DROP TABLE if exists "{table_name}";CREATE TABLE "{table_name}"(timestamp bigint,type integer,);CREATE INDEX "{table_name}_timestamp_index"ON "{table_name}" (timestamp);CREATE INDEX "{table_name}_type_index"ON "{table_name}" (type);\\copy "{table_name}" (timestamp, type) from './data.csv' delimiter ',' csv header'''with open("./pg.sql", "+w") as f:f.write(tem_sql)

可以使用变量来定义各类名字，组装各种字符，一次性写入sql脚本，然后一起执行。

如下：

write_data_file(...)
write_sql_file(...)
ret = os.popen("psql -d XXXXDb -f ./pg.sql")
print(ret.read())

当写完数据文件和sql脚本文件后，便可以采用psql命令一次性执行它们。然后读取返回结果，这里也可以使用-o选项将结果直接输出到文件中。

这个方法主要用于底层开发人员往数据库写入测试数据，然后提供给web开发人员测试，不适用于专业数据库维护场景。