Postgresql之(1)自动化脚本

news/2024/7/9 20:26:26 标签: postgresql, 自动化

Postgresql之自动化脚本操作

Author：OnceDay Date：2023年3月15日

漫漫长路，才刚刚开始…

1. 概述

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

下面是官方引导文档：

  • PostgreSQL Tutorial - Learn PostgreSQL from Scratch

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

  • Getting Started with PostgreSQL (postgresqltutorial.com)

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

然后等待一段时间即可。

一般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支持的命令。

postgresql_46">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=# \l
                                 List of databases
      Name      |    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开发人员测试，不适用于专业数据库维护场景。


http://www.niftyadmin.cn/n/153186.html

相关文章

微信小程序5，自定义组件，组件中的数据、属性、方法，组件的生命周期函数，组件通信

微信小程序5，自定义组件，组件中的数据、属性、方法，组件的生命周期函数，组件通信

目录组件组件的引用方式样式隔离自定义组件的样式隔离修改组件的样式隔离选项组件中的数据、属性、方法propertiesdata和properties的区别使用setData修改properties的值数据监听器纯数据字段使用规则组件的生命周期函数组件所在页面的生命周期函数组件通信插槽父子组件之间通信…
阅读更多...
图数据库中的“分布式”和“数据切分”（切图）

图数据库中的“分布式”和“数据切分”（切图）

今天，我试着简要综述几类不同的图数据库的分布式与切图的设计，希望可以帮助大家了解不同项目、产品的设计差异。如果有理解不对的地方，欢迎留言讨论。 什么是分布式系统 一般来说，分布式系统是一组计算机程序的集合，…
阅读更多...
Python抖音弹幕

Python抖音弹幕

一：介绍抖音是一款短视频应用程序，用户可以在其中观看和分享短视频内容。在抖音中，用户可以通过发送弹幕来与视频内容进行互动。弹幕是指在视频播放过程中，用户可以发送文字、表情等内容，这些内容会在视频画面上以滚动…
阅读更多...
CCNP300-410学习笔记（1-50）

CCNP300-410学习笔记（1-50）

1、Refer to the exhibit. Users in the branch network of 2001:db8:0:4::/64 report that they cannot access the Internet. Which command is issued in IPv6 router EIGRP 100 configuration mode to solve this issue? A. Issue the eigrp stub command on R1.B. Issue …
阅读更多...
YOLOv8 网络详解

YOLOv8 网络详解

YOLOv8 是 ultralytics 公司在 2023 年 1月 10 号开源的 YOLOv5 的下一个重大更新版本，目前支持图像分类、物体检测和实例分割任务，在还没有开源时就收到了用户的广泛关注。 官方开源地址： https://github.com/ultralytics/ultralytics MMYOLO 开源地址： https://github.c…
阅读更多...
Physical adversarial attack 入门

Physical adversarial attack 入门

1.《ADVERSARIAL EXAMPLES IN THE PHYSICAL WORLD》 说明打印出来的通过digital attack的图片也可以欺骗 边缘端设备 2.《No need to worry about adversarial examples in object detection in autonomous vehicles》 证明了digital attack在物理世界视角等变化下是无效的 3…
阅读更多...
QT串口助手开发2之串口程序编写

QT串口助手开发2之串口程序编写

系列文章目录 QT串口助手开发1之绘制界面 QT串口助手开发2系列文章目录一、前期准备工作1.1给选择框添加子选项1.2给控件命名ID二、自动识别系统端口程序三、响应打开函数一、前期准备工作 1.1给选择框添加子选项 书接上文 绘制好界面后，需要给每个控制命个名&…
阅读更多...
2023年小哥哥都说好的CDGA数据治理认证

2023年小哥哥都说好的CDGA数据治理认证

弘博创新是DAMA中国授权的数据治理人才培养基地，贴合市场需求定制教学体系，采用行业资深名师授课，理论与实践案例相结合，快速全面提升个人/企业数据治理专业知识与实践经验，通过考试还能获得数据专业领域证书。 DAMA认…
阅读更多...
最新文章

Copyright @ 2022~2023