Exportar dataframe único do pandas para várias tabelas SQL (normalização automática)

Question

Tenho um DataFrame como este, mas com milhões de linhas e cerca de 15 colunas:

       id    name  col1   col2  total
0 8252552 CHARLIE DESC1 VALUE1   5.99
1 8252552 CHARLIE DESC1 VALUE2  20.00
2 5699881    JOHN DESC1 VALUE1  39.00
2 5699881    JOHN DESC2 VALUE3  -3.99

O DataFrame precisa ser exportado para um banco de dados SQL, em várias tabelas. No momento, estou usando o SQLite3 para testar a funcionalidade. As tabelas seriam:

• principal ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, people_id INTEGER, col1_id INTEGER, col2_id INTEGER, total REAL)
• pessoas ( id INTEGER NOT NULL PRIMARY KEY UNIQUE, name TEXT UNIQUE)
• col1 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT UNIQUE)
• col2 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT UNIQUE)

A tabela principal deve ser semelhante a esta:

  people_id col1_id col2_id  total
0   8252552       1       1   5.99
1   8252552       1       2  20.00
2   5699881       1       1  39.00
3   5699881       2       3  -3.99

Outras tabelas, como "pessoas", como esta:

     id    name
8252552 CHARLIE
5699881    JOHN

Acontece que não consigo descobrir como fazer isso usando o schemaatributo do to_sqlmétodo em pandas. Usando Python, eu faria algo assim:

conn = sqlite3.connect("main.db")
cur = conn.cursor()
for row in dataframe:
    id = row["ID"]
    name = row["Name"]
    col1 = row["col1"]
    col2 = row["col2"]
    total = row["total"]
    cur.execute("INSERT OR IGNORE INTO people (id, name) VALUES (?, ?)", (id, name))
    people_id = cur.fetchone()[0]
    cur.execute("INSERT OR IGNORE INTO col1 (col1) VALUES (?)", (col1, ))
    col1_id = cur.fetchone()[0]
    cur.execute("INSERT OR IGNORE INTO col1 (col2) VALUES (?)", (col2, ))
    col2_id = cur.fetchone()[0]
    cur.execute("INSERT OR REPLACE INTO main (people_id, col1_id, col2_id, total) VALUES (?, ?, ?, ?)", (people_id, col1_id, col2_id, total ))
conn.commit()

Isso adicionaria automaticamente os valores correspondentes às tabelas (pessoas, col1 e col2), criaria uma linha com os valores desejados e as chaves estrangeiras e adicionaria essa linha à tabela primária. No entanto, há muitas colunas e linhas e isso pode ficar muito lento. Além disso, não me sinto muito confiante de que esta seja uma "prática recomendada" ao lidar com bancos de dados (sou bastante novo no desenvolvimento de banco de dados)

Minha pergunta é: Existe uma maneira de exportar um DataFrame pandas para várias tabelas SQL, definindo as regras de normalização, como no exemplo acima? Existe alguma maneira de obter o mesmo resultado, com melhor desempenho?

Answer 1

Você poderia primeiro dividir seu quadro de dados do Pandas em vários subquadros de dados de acordo com as tabelas do banco de dados e, em seguida, aplicar o to_sql()método em cada subquadro de dados?