pandas - 将数据附加到具有自动增量的表时，通过外键维护关系

Question

假设我连续得到两个 pandas 数据帧，df_base并且df_referencing. 两者都有带值的标准数字索引0,1,2,...。df_referencing有一列base_id引用df_base--在数据库术语中的索引，这将是外键。

每次我得到这些新的数据帧时，我都想将它们附加到相应的 sqlite 表中

CREATE TABLE base_table (
  "ID" INTEGER PRIMARY KEY AUTOINCREMENT,
  "COL1" FLOAT,
  "COL2" FLOAT);

CREATE TABLE referencing_table (
  "ID" INTEGER PRIMARY KEY AUTOINCREMENT,
  "COL1" FLOAT,
  "COL2" FLOAT,
  "BASE_ID" INTEGER,
  FOREIGN KEY(BASE_ID) REFERENCES base_table(ID));

我有以下两个问题：

• 如何插入两者df_base，df_referencing以使主键从现有记录继续？我无法让它与 pandas 方法一起使用to_sql。我是否必须编写自己的 sql 命令，例如INSERT INTO base_table (COL1, COL2) VALUES ('0.736252', '0.709897');哪个会ID自动创建值？[在编辑1中自己回答了这个问题]
• 如何确保referencing_table使用插入表时创建的主键更新表中的外键base_table？

我的环境：Windows 10、python 3.7.0、pandas 0.24.0、sqlalchemy 1.2.16

也许它有助于查看示例数据框

import pandas as pd
import numpy as np
df_base = pd.DataFrame(np.random.rand(5,2), columns = ['COL1', 'COL2'])
df_referencing = pd.DataFrame(np.random.rand(10,2), columns = ['COL1', 'COL2'])
df_referencing['base_id'] = [0,0,1,1,2,2,3,3,4,4]
print(df_base)
print(df_referencing)

# df_base
       COL1      COL2
0  0.736252  0.709897
1  0.846635  0.372465
2  0.360253  0.302076
3  0.695931  0.407356
4  0.800489  0.070650

# df_referencing
       COL1      COL2  base_id
0  0.529504  0.951910        0
1  0.407145  0.321576        0
2  0.685721  0.488690        1
3  0.943426  0.961819        1
4  0.355398  0.299547        2
5  0.118759  0.234632        2
6  0.243048  0.585558        3
7  0.117997  0.957484        3
8  0.792215  0.596002        4
9  0.265951  0.656986        4

编辑1：

我必须纠正自己：如果我通过to_sql了，就尊重AUTOINCREMENT财产index=False。

import pandas as pd
import numpy as np
import sqlalchemy
import os

# create sample data
df_base = pd.DataFrame(np.random.rand(5,2), columns = ['COL1', 'COL2'])
df_referencing = pd.DataFrame(np.random.rand(10,2), columns = ['COL1', 'COL2'])
df_referencing['base_id'] = [0,0,1,1,2,2,3,3,4,4]
print(df_base)
print(df_referencing)

# establish connection
engine = sqlalchemy.create_engine('sqlite:////'+os.getcwd()+'test.db')

# write to db
df_base.to_sql('base_table', con=engine, if_exists='append', index=False)
df_base_read = pd.read_sql_table('base_table', con=engine)
print(df_base_read)

标签： pandassqlitesqlalchemy