python - 在python中填充n级PostgreSQL表
问题描述
我无法考虑如何填充 n 级表(在我的情况下是 3 级),我使用 python 从查询中获取数据,但我不确定如何填充我的新表resources
,因为它引用了自己。任何有关该方法的反馈将不胜感激!
在我的 python 文件中运行以下查询后,我得到下表
SELECT distinct c.table_catalog AS "Database", c.table_schema AS "Schema", c.table_name AS "Table"
FROM information_schema.columns c
WHERE c.table_schema != 'information_schema' AND c.table_schema != 'pg_catalog' AND c.table_schema != 'pg_internal' AND c.table_schema not like '% %'
ORDER BY c.table_schema, c.table_name;
Database Schema Table
____________________________________
dev BigBangTheory SomeTable1
dev BigBangTheory SomeTable2
dev BigBangTheory SomeTable3
dev Walle AnotherTable100
dev Walle AnotherTable200
dev StarWars SpaceTablexxx
dev StarWars SpaceTableyyy
stage BigBangTheory SomeTable1
stage BigBangTheory SomeTable2
stage BigBangTheory SomeTable3
stage Walle AnotherTable100
stage Walle AnotherTable200
stage StarWars SpaceTablexxx
stage StarWars SpaceTableyyy
我还有另一个表,我想使用上述结果填充。我要填充的表如下所示:
CREATE TABLE IF NOT EXISTS resources
(
"id" SERIAL NOT NULL PRIMARY KEY,
"type" varchar(100) NOT NULL,
"name" varchar(100) NOT NULL,
"parent" int,
FOREIGN KEY (parent) REFERENCES resources (id)
);
所以我的目标是让表格resources
看起来像这样:
id type name parent
____________________________________________________
1 database dev NULL
2 schema BigBangTheory 1
3 table SomeTable1 2
4 table SomeTable2 2
5 table SomeTable3 2
6 schema Walle 1
7 table AnotherTable100 6
8 table AnotherTable200 6
9 schema StarWars 1
10 table SpaceTablexxx 9
11 table SpaceTableyyy 9
12 database stage NULL
13 schema BigBangTheory 12
14 table SomeTable1 13
15 table SomeTable2 13
16 table SomeTable3 13
17 schema Walle 12
18 table AnotherTable100 17
19 table AnotherTable200 17
20 schema StarWars 12
21 table SpaceTablexxx 20
22 table SpaceTableyyy 20
先感谢您!感谢所有反馈<3
解决方案
作为初学者:您可以直接从中获取所需的信息,information_schema.tables
而不是information_schema.columns
(每个表只有一行,因此需要 for distinct
)。
然后:在 Postgres 中,您可以在单个查询中执行您想要的操作,使用带有returning
子句的级联公用表表达式到insert
Postgres 中的语句。能够
逻辑是首先插入顶部对象(数据库)并返回生成的序列,然后插入模式(使用数据库序列),最后是表。
with
info as (
select c.table_catalog, c.table_schema, c.table_name
from information_schema.tables
where
c.table_schema not in ('information_schema', 'pg_catalog', 'pg_internal')
and c.table_schema not like '% %'
),
dbs as (
insert into resources (type, name)
select distinct 'database', table_catalog
from info
returning id, name
),
schemas as (
insert into resources(type, name, parent)
select distinct 'schema', i.table_schema, d.id
from info i
inner join dbs d on d.name = i.table_catalog
returning id, name, parent
)
insert into resources(type, name, parent)
select 'table', table_name, s.id
from info i
inner join schemas s on s.name = i.table_schema
inner join dbs d on d.id = s.parent and d.name = i.table_catalog
请注意,最后一个insert
连接schemas
和dbs
; 这是为了正确处理存在于不同模式中的“同名”表。
这是一个演示(我使用了一个表格来模拟您的初始查询的结果)。
推荐阅读
- javascript - 运行时崩溃:nativescript APP在真机上崩溃,但在任何模拟器上运行
- python - 有没有办法循环mysql数据并执行它
- python - 如何在 Bokeh 中绘制人物
- symfony - 如何在 Symfony 中同时使用 Assert\File 和 Assert\Image?
- python - 如何将 Kivy TextInput 调用到我的 Python 函数中?
- php - 如何在角度 7 中修复“刚刚评估过以下值”
- javascript - 尝试使用快速 js 路由解析 API 响应时的 HPE_INVALID_HEADER_TOKEN
- python - webdriver 找不到带有 jscontroller 的显示更多链接
- python - 为什么 asyncio 需要一个特殊的信号处理程序?
- asp.net - 用户名或密码不正确