问题描述
我有一个像这样的DataFrame,但是有数百万行和大约15列:
I have a DataFrame like this, but with millions of rows and about 15 columns:
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来测试功能。这些表将是:
The DataFrame needs to be exported to a SQL database, in several tables. I'm currently using SQLite3, to test the functionality. The tables would be:
- main(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,people_id INTEGER,col1_id INTEGER,col2_id INTEGER,总计)实型
) - people(
id INTEGER NOT NULL PRIMARY KEY UNIQUE,name TEXT UNIQUE
) - col1(
id整数非空主键自动增值唯一,名称为TEXT UNIQUE
) - col2(
id整数非空主键自动输入的唯一性,名称为文本唯一性
)
- 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,像这样:
Other tables, like "people", like this:
id name
8252552 CHARLIE
5699881 JOHN
问题是,我找不到如何实现该usi在熊猫中使用 to_sql
方法的 schema
属性。使用Python,我会执行以下操作:
Thing is, I can't find how to achieve that using the schema
attribute of the to_sql
method in pandas. Using Python, I'd do something like this:
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()
将相应的值添加到表(人员,col1和col2),创建一个包含期望值和外键的行,并将该行添加到主表中。但是,有很多列和行,这可能会变得很慢。另外,我对这是最佳做法处理数据库时(我对数据库开发还不是很熟悉)
That would automatically add the corresponding values to the tables (people, col1 and col2), create a row with the desire values and foreign keys, and add that row to the primary table. However, there are a lot of columns and rows, and this might get very slow. Plus, I don't feel very confident that this is a "best practice" when dealing with databases (I'm fairly new to database development)
我的问题是:是否可以将pandas DataFrame导出到多个SQL表,并设置规范化规则,如上面的例子?有什么方法可以得到相同的结果,并且性能得到改善?
My question is: Is there a way to export a pandas DataFrame to multiple SQL Tables, setting the normalization rules, as in the above example? Is there any way to get the same result, with improved performance?
推荐答案
您可以先将Pandas数据帧拆分为几个子对象吗?数据帧根据数据库表,然后在每个子数据帧上应用 to_sql()
方法?
Could you first split your Pandas data frame into several sub data frames according to the database tables, then apply the to_sql()
method on each sub data frames?
这篇关于将单个 pandas 数据框导出到多个SQL表(自动归一化)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!