python – Algorithm for populating tables in PostgresSQL

Question:

I'm writing an algorithm to populate all tables in a database in Postgres. I can already populate all tables that have no relationship , as follows:

  1. I get all tables with the following query:

     SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')
  2. For the list of obtained tables, I make a loop, and for each table I get its properties (column name, data type, if null or not, maximum characters):

     SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'NOME_DA_TABELA'
  3. With this list of properties I dynamically mount the INSERT

     INSERT INTO NOME_TABELA(prop1, prop2, prop3) VALUES(value1, value2, value3)

    Each value is randomly generated based on the property type, for example:

    • Integer: 65422
    • Character: "Lorem ipsum…"
    • Date: 2016-12-12 20:00

And so on for each type of data accepted by the algorithm. If it finds some type of data that is not accepted, the application terminates (which is to be expected).

  1. And finally, with the INSERT mounted, I run the query in the database

The steps of this algorithm work perfectly for tables where there are no relationships .


For tables that have relationship (1:N, 1:1, N:N), I would first need to find all the foreign keys and understand which tables they come from, to insert the data into this "Parent" table, save the IDs and then insert in the "Children" tables thus associating their foreign keys. But how to do this in a simple way?

It's interesting to note that I can already get the list of all foreign keys using the following query:

SELECT conrelid::regclass AS table_from
      ,conname
      ,pg_get_constraintdef(c.oid)
FROM   pg_constraint c
JOIN   pg_namespace n ON n.oid = c.connamespace
WHERE  contype IN ('f', 'p ')
AND    n.nspname = 'public' -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;

I am grateful for the suggestion.

Note: The algorithm is being developed in Python.

Answer:

You already have everything you need. Just implement a recursive function that inserts a row into the FK table before inserting the current table row. It's basically doing a tree search.

Here is an example:

procedimento obter_fks(tabela)
  # Retorna um array com o nome das tabelas
  # Você disse que já tem sabe como obter essa informação
fim procedimento

procedimento inserir_linha(tabela)
  tabelas_ligadas = obter_fks(tabela)
  chaves_tabelas_ligadas = dicionário zerado

  para cada tabela_fk em tabelas_ligadas
    chave = inserir_linha(tabela_fk)
    chaves_tabelas_ligadas[tabela] = chave
  fim para

  # Você disse que sua rotina que já é funcional
  # Use a chaves_tabelas_ligadas para popular as FKs

  retornar a PK da linha inserida
fim procedimento
Scroll to Top