我有一个如下所示的DataFrame,但其中包含数百万行和约15列:
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
需要将DataFrame导出到SQL数据库中的多个表中。目前我正在使用SQLite3测试功能。这些表应该是:
- main (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, people_id INTEGER, col1_id INTEGER, col2_id INTEGER, total REAL
) - people (
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
)
主表应该看起来像这样:
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
其他的表格,例如“people”表格,如下所示:
id name
8252552 CHARLIE
5699881 JOHN
事实上,我无法找到如何使用pandas中to_sql
方法的schema
属性来实现这一点。在Python中,我会做出如下操作:
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()
那将自动将相应的值添加到表格中(people、col1和col2),创建一个带有期望值和外键的行,并将该行添加到主表中。但是,如果列和行很多,这可能会变得非常缓慢。此外,在处理数据库时我不太确信这是否是“最佳实践”(我对数据库开发还比较新)。
我的问题是:是否有一种方法可以将pandas DataFrame导出到多个SQL表中,并设置规范化规则,就像上面的示例一样?是否有任何方法可以获得相同的结果,同时提高性能?