当我将数据从一个数据库迁移到另一个数据库时删除数据,我使用PostgreSQL和python psycopg 3

monwx1rj  于 2024-01-07  发布在  PostgreSQL
关注(0)|答案(1)|浏览(186)

我尝试将数据从数据库“zno_db”迁移到另一个数据库“zno_db_norm”。这两个数据库都在同一个服务器上。
当我在“zno_db_norm”上创建表时，来自“zno_db”的数据正在下降。

让我解释整个程序和代码。

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")

字符串
函数“table”连接到数据库并在数据库SQL代码中执行。“table”获取SQL文件的路径（我后面放的SQL代码），保存保存环境变量的.env文件的路径，这些变量需要连接到数据库。此外，表获取消息，但它只是打印到控制台以方便。
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)

型
可能问题出在连接文件中
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.所以我从csv文件中创建了zno_db中的表“zno”（它绝对正确）
1.然后我规范表zno到第三范式，并创建几个表。（SQL代码我放在后面）
1.之后，我从表“zno”迁移到其他表educate_organization，participants，loc，test，participants_eo
所有这些事情我做的只有一个数据库“zno_db”。
所有这些步骤都是正确的。

问题从这里开始

1.我在pgAdmin中创建了一个新的数据库“zno_db_norm”，然后在“zno_db_norm”上创建了一些表，这些表的名字分别是educate_organization，participants，loc，test，participants_eo。
发生了什么！在这一刻程序删除所有数据从表中的“zno_db”！我不明白为什么！
你可以在main中看到，从“table”中的filepath中，我只使用了3个SQL文件。“1_CREATE_TABLE.sql”我只在步骤“1”中使用了一次。它工作正常，所以不需要放入它的代码。
在其他步骤中，我使用了两个SQL文件（您可以在“main.py“代码中看到执行顺序
这段代码我在数据库“zno_db”和“zno_db_norm”中执行。我认为DROP TABLE字符串中的这段代码有问题。似乎发生了冲突，因为两个数据库都有相同的表，并且存在于一个服务器上。但我不确定这一点，如果它是真的，我不知道如何修复。
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)
);

型
这个SQL代码我只适用于“zno_bd”，但可能问题就在这里。

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;

型
感谢任何帮助.我几天不能得到什么是错误的，在这个代码.
我期待着，任何人都能告诉我问题在哪里。为什么当我在第二个数据库中创建表时，第一个数据库中的数据会下降。也许有人甚至知道，如何修复。这将是完美的。

postgresql

1条答案

按热度按时间
u5i3ibmn

u5i3ibmn1#

我99%确定问题出在你的“.env”文件上。
在“新数据库”上创建同名表时删除“其他数据库表”。只有当Python代码将这些查询应用于同一个数据库而不是两个数据库时，此行为才可能发生（即使您为两个单独的数据库指定了filepath，该配置文件的内容可能相同）
简短回答：检查你的“zno.env”和“zno_norm.env”文件。

赞(0）分享  回复(0）举报  2024-01-07
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com