Data deleting while I migrate data from one db to another. I using PostgreSQL and python psycopg 3

Question

I try to migrate data from on db "zno_db" to another db "zno_db_norm". Both of them are on same serve.

When I create tables on "zno_db_norm", data from "zno_db" dropping.

Let me explain whole program and the code.

main.py

from table import table
from insert import insert_data, migrate_data
from result import get_result



if __name__ == "__main__":
     table("sql/1_CREATE_TABLE.sql", "config/zno.env", "Create table zno\n")
     insert_data()
     get_result()

     table("sql/2_NORMAL_TABLE.sql", "config/zno.env", "Norming tables\n")
     table("sql/3_MIGRATION.sql", "config/zno.env", "Migrations\n")
     table("sql/2_NORMAL_TABLE.sql", "config/zno_norm.env", "Creating second bd\n")

Function "table" connecting to db and execute in db SQL code. "table" get path to the SQL file (SQL code I put later), path to the .env file where I save environment variables, which need to connect to the db. Also table get messege, but it just print to console for comfort.

table.py

from connect import get_conn

def table(filepath, envpath, message):
     try:
          conn = get_conn(envpath)
          cur = conn.cursor()
          print(filepath, envpath)
          #cur.execute()
          with open(filepath, "r") as sql_file:
               sql_code = sql_file.read()
               cur.execute(sql_code)


          conn.commit()
          cur.close()
          conn.close()
          print(message)
     except:
          table(filepath, envpath, message)

Maybe problem can be in connect file

connect.py

import psycopg
import time
from dotenv import load_dotenv
import os


def get_conn(envpath="config/zno.env"):
     load_dotenv(envpath)

     username = os.getenv("DB_USERNAME")
     password = os.getenv("DB_PASSWORD")
     database = os.getenv("DB_DATABASE")
     host = os.getenv("DB_HOST")
     port = os.getenv("DB_PORT")

     try:
          conn = psycopg.connect(user=username, password=password, dbname=database, host=host, port=port)
          return conn
     except:
          print("Reconnecting...")
          time.sleep(5)
          get_conn()

1. So I create table "zno" in zno_db from csv file (it works absolutely correct)

2. Then I norming table zno to 3rd normal form and creating few tables. (SQL code I put later)

3. After that I make migration from table "zno" to other tables educate_organisation, participants, loc, test, participants_eo

All this things I do in only one database "zno_db".

All this steps are works correctly.

Problems start here.

4. I create new database "zno_db_norm" in pgAdmin. Then I create on "zno_db_norm" tables with the same names educate_organisation, participants, loc, test, participants_eo.

And what happens! On this moment program delete all data from tables on "zno_db"! I don't get why!

How you can see in main, from filepath in the "table" I use only 3 SQL files. "1_CREATE_TABLE.sql" I use only once in step "1". It works correctly so putting its code is unnecessary.

In other steps I use two SQL files (you can see order of executing in "main.py" code

This code I execute in both db "zno_db" and "zno_db_norm". I think problem in this code in DROP TABLE string. Seems it conflict happens, because both db have same tables and exist on one server. But I'm not sure about it and I don't know how to fix that if it is true.

2_NORMAL_TABLE.sql

DROP TABLE IF EXISTS educate_organisation, participants, loc, test, participants_eo;

CREATE TABLE loc (
    locid SERIAL PRIMARY KEY,
    locname varchar,
    locregname varchar,
    locareaname varchar,
    loctername varchar,
    UNIQUE(locname, locregname, loctername)
);


CREATE TABLE educate_organisation (
    eoid SERIAL PRIMARY KEY,
    eoname varchar,
    eoregname varchar,
    eoareaname varchar,
    eotername varchar,
    eoparent varchar,
    eotypename varchar,
    locid INTEGER,
    FOREIGN KEY (locid) REFERENCES loc (locid),
    UNIQUE(eoname, eotypename, eoparent)
);

CREATE TABLE participants (
    outid varchar PRIMARY KEY,
    birth numeric,
    sextypename varchar,
    regtypename varchar,
    classprofilename varchar,
    classlangname varchar,
    regname varchar,
    areaname varchar,
    tername varchar,
    tertypename varchar,
    zno_year INTEGER,
    locid INTEGER,
    FOREIGN KEY (locid) REFERENCES loc (locid)
);


CREATE TABLE test (
    testid SERIAL PRIMARY KEY,
    outid varchar,
    test varchar,
    testlang varchar,
    teststatus varchar,
    ball100 decimal,
    ball12 numeric,
    ball numeric,
    adaptscale numeric,
    dpalevel varchar,
    locid INTEGER,
    FOREIGN KEY (locid) REFERENCES loc (locid),
    FOREIGN KEY (outid) REFERENCES participants (outid),
    UNIQUE(outid, test)
);

CREATE TABLE participants_eo (
    outid varchar,
    eoid INTEGER,
    FOREIGN KEY (outid) REFERENCES participants (outid),
    FOREIGN KEY (eoid) REFERENCES educate_organisation (eoid),
    UNIQUE(outid, eoid)
);

This SQL code I apply only to "zno_bd", but maybe problem is here.

3_MIGRATION.sql


INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    ukrptname,
    ukrptregname,
    ukrptareaname,
    ukrpttername
FROM zno
WHERE ukrptname IS NOT NULL
ON CONFLICT DO NOTHING;

INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    histptname,
    histptregname,
    histptareaname,
    histpttername
FROM zno
WHERE histptname IS NOT NULL
ON CONFLICT DO NOTHING;


INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    mathptname,
    mathptregname,
    mathptareaname,
    mathpttername
FROM zno
WHERE mathptname IS NOT NULL
ON CONFLICT DO NOTHING;

INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    physptname,
    physptregname,
    physptareaname,
    physpttername
FROM zno
WHERE physptname IS NOT NULL
ON CONFLICT DO NOTHING;

INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    bioptname,
    bioptregname,
    bioptareaname,
    biopttername
FROM zno
WHERE bioptname IS NOT NULL
ON CONFLICT DO NOTHING;

INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    chemptname,
    chemptregname,
    chemptareaname,
    chempttername
FROM zno
WHERE chemptname IS NOT NULL
ON CONFLICT DO NOTHING;

INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    geoptname,
    geoptregname,
    geoptareaname,
    geopttername
FROM zno
WHERE geoptname IS NOT NULL
ON CONFLICT DO NOTHING;


INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    engptname,
    engptregname,
    engptareaname,
    engpttername
FROM zno
WHERE engptname IS NOT NULL
ON CONFLICT DO NOTHING;


INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    fraptname,
    fraptregname,
    fraptareaname,
    frapttername
FROM zno
WHERE fraptname IS NOT NULL
ON CONFLICT DO NOTHING;


INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    spaptname,
    spaptregname,
    spaptareaname,
    spapttername
FROM zno
WHERE spaptname IS NOT NULL
ON CONFLICT DO NOTHING;



INSERT INTO loc (locname, locregname, locareaname, loctername)
SELECT 
    deuptname,
    deuptregname,
    deuptareaname,
    deupttername
FROM zno
WHERE deuptname IS NOT NULL
ON CONFLICT DO NOTHING;








INSERT INTO educate_organisation (eoname, eoregname, eoareaname, eotername, eoparent, eotypename, locid)
SELECT DISTINCT
    eoname,
    eoregname,
    eoareaname,
    eotername,
    eoparent,
    eotypename,
    (SELECT locid
     FROM loc
     WHERE 
     locregname = zno.eoregname AND
     locareaname = zno.eoareaname AND
     loctername = zno.eotername
     LIMIT 1)
FROM zno
WHERE eoname IS NOT NULL
ON CONFLICT DO NOTHING;






INSERT INTO participants (outid, birth, sextypename, regtypename, classprofilename, classlangname, regname, areaname, tername, tertypename, zno_year, locid)
SELECT DISTINCT
    outid,
    birth,
    sextypename,
    regtypename,
    classprofilename,
    classlangname,
    regname,
    areaname,
    tername,
    tertypename,
    zno_year,
    (SELECT locid
     FROM loc
     WHERE 
     locregname = zno.regname AND
     locareaname = zno.areaname AND
     loctername = zno.tername
     LIMIT 1)
FROM zno
WHERE outid IS NOT NULL
ON CONFLICT DO NOTHING;







INSERT INTO participants_eo(eoid, outid)
SELECT DISTINCT
    (SELECT eoid FROM educate_organisation WHERE
    eoname = zno.eoname AND
    eotypename = zno.eotypename AND
    eoparent = zno.eoparent
    LIMIT 1), outid
FROM zno
WHERE eoname IS NOT NULL
ON CONFLICT DO NOTHING;







INSERT INTO test (test, teststatus, ball100, ball12, ball, adaptscale, locid)
SELECT
    ukrtest,
    ukrteststatus,
    ukrball100,
    ukrball12,
    ukrball,
    ukradaptscale,
    (SELECT locid FROM loc WHERE
    locregname = zno.ukrptregname AND
    locareaname = zno.ukrptareaname AND
    loctername = zno.ukrpttername
    LIMIT 1)
FROM zno
WHERE ukrtest IS NOT NULL
ON CONFLICT DO NOTHING;


INSERT INTO test (test, testlang, teststatus, ball100, ball12, ball, locid)
SELECT
    histtest,
    histlang,
    histteststatus,
    histball100,
    histball12,
    histball,
    (SELECT locid FROM loc WHERE
    locregname = zno.histptregname AND
    locareaname = zno.histptareaname AND
    loctername = zno.histpttername
    LIMIT 1)
FROM zno
WHERE histtest IS NOT NULL
ON CONFLICT DO NOTHING;


INSERT INTO test (test, testlang, teststatus, ball100, ball12, ball, locid)
SELECT
    mathtest,
    mathlang,
    mathteststatus,
    mathball100,
    mathball12,
    mathball,
    (SELECT locid FROM loc WHERE
    locregname = zno.mathptregname AND
    locareaname = zno.mathptareaname AND
    loctername = zno.mathpttername
    LIMIT 1)
FROM zno
WHERE mathtest IS NOT NULL
ON CONFLICT DO NOTHING;



INSERT INTO test (test, testlang, teststatus, ball100, ball12, ball, locid)
SELECT
    phystest,
    physlang,
    physteststatus,
    physball100,
    physball12,
    physball,
    (SELECT locid FROM loc WHERE
    locregname = zno.physptregname AND
    locareaname = zno.physptareaname AND
    loctername = zno.physpttername
    LIMIT 1)
FROM zno
WHERE phystest IS NOT NULL
ON CONFLICT DO NOTHING;




INSERT INTO test (test, testlang, teststatus, ball100, ball12, ball, locid)
SELECT
    chemtest,
    chemlang,
    chemteststatus,
    chemball100,
    chemball12,
    chemball,
    (SELECT locid FROM loc WHERE
    locregname = zno.chemptregname AND
    locareaname = zno.chemptareaname AND
    loctername = zno.chempttername
    LIMIT 1)
FROM zno
WHERE chemtest IS NOT NULL
ON CONFLICT DO NOTHING;



INSERT INTO test (test, testlang, teststatus, ball100, ball12, ball, locid)
SELECT
    biotest,
    biolang,
    bioteststatus,
    bioball100,
    bioball12,
    bioball,
    (SELECT locid FROM loc WHERE
    locregname = zno.bioptregname AND
    locareaname = zno.bioptareaname AND
    loctername = zno.biopttername
    LIMIT 1)
FROM zno
WHERE biotest IS NOT NULL
ON CONFLICT DO NOTHING;



INSERT INTO test (test, testlang, teststatus, ball100, ball12, ball, locid)
SELECT
    geotest,
    geolang,
    geoteststatus,
    geoball100,
    geoball12,
    geoball,
    (SELECT locid FROM loc WHERE
    locregname = zno.geoptregname AND
    locareaname = zno.geoptareaname AND
    loctername = zno.geopttername
    LIMIT 1)
FROM zno
WHERE geotest IS NOT NULL
ON CONFLICT DO NOTHING;



INSERT INTO test (test, teststatus, ball100, ball12, dpalevel, ball, locid)
SELECT
    engtest,
    engteststatus,
    engball100,
    engball12,
    engdpalevel,
    engball,
    (SELECT locid FROM loc WHERE
    locregname = zno.engptregname AND
    locareaname = zno.engptareaname AND
    loctername = zno.engpttername
    LIMIT 1)
FROM zno
WHERE engtest IS NOT NULL
ON CONFLICT DO NOTHING;




INSERT INTO test (test, teststatus, ball100, ball12, dpalevel, ball, locid)
SELECT
    fratest,
    frateststatus,
    fraball100,
    fraball12,
    fradpalevel,
    fraball,
    (SELECT locid FROM loc WHERE
    locregname = zno.fraptregname AND
    locareaname = zno.fraptareaname AND
    loctername = zno.frapttername
    LIMIT 1)
FROM zno
WHERE fratest IS NOT NULL
ON CONFLICT DO NOTHING;



INSERT INTO test (test, teststatus, ball100, ball12, dpalevel, ball, locid)
SELECT
    spatest,
    spateststatus,
    spaball100,
    spaball12,
    spadpalevel,
    spaball,
    (SELECT locid FROM loc WHERE
    locregname = zno.spaptregname AND
    locareaname = zno.spaptareaname AND
    loctername = zno.spapttername
    LIMIT 1)
FROM zno
WHERE spatest IS NOT NULL
ON CONFLICT DO NOTHING;



INSERT INTO test (test, teststatus, ball100, ball12, dpalevel, ball, locid)
SELECT
    deutest,
    deuteststatus,
    deuball100,
    deuball12,
    deudpalevel,
    deuball,
    (SELECT locid FROM loc WHERE
    locregname = zno.deuptregname AND
    locareaname = zno.deuptareaname AND
    loctername = zno.deupttername
    LIMIT 1)
FROM zno
WHERE deutest IS NOT NULL
ON CONFLICT DO NOTHING;

Thanks for any help. I few days can't get what is wrong in this code.

I expecting, that anybody will tell where is the problem. Why data from first db dropping when I creating tables in second db. Maybe someone even know, how to fix that. It's will be perfect.

Answer 1

I'm 99% sure that the problem is with your ".env" files.

Dropping "other database tables" when you create tables with the same name on a "new database". This behavior is only possible if Python code is applying those queries to the same database instead of 2 (even though you specified filepath for 2 separate configs, content of that config file might be the same)

SHORT ANSWER: Check your "zno.env" and "zno_norm.env" files.