sql—在python中填充n级postgresql表

whlutmcx 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(283)

我在思考如何填充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)
);

所以我的目标是table 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

sql postgresql python database-design sql-insert

来源：https://stackoverflow.com/questions/62436365/populating-n-level-postgresql-table-in-python

1条答案

按热度按时间

yshpjwxd1#

作为初学者：你可以直接从 information_schema.tables 而不是 information_schema.columns （每个表只有一行，因此需要 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 ; 这意味着要正确处理位于不同模式中的“同音异义”表。
下面是一个演示（我使用了一个表来模拟初始查询的结果）。

赞(0）回复(0）举报 2021-07-26

我来回答

sql—在python中填充n级postgresql表

1条答案

相关问题

热门标签

最新问答