pandas to_sql保存数据到数据库后，添加自增长的主键ID(PRIMARY KEY)

1. 构造dataframe

import pandas as pd
import numpy as np

df = pd.DataFrame(data=np.random.randint(0, 20, size=(1000, 3)))
df.columns = ['col_1', 'col_2', 'col_3']

2. 构造数据库

from sqlalchemy import create_engine

db_name = "test_db"  # 要保存的数据库名
table_name = "my_item_table"  # 要保存的表名
# engine = create_engine("mysql+pymysql://用户名:密码@127.0.0.1:3306/数据库名")
engine = create_engine('mysql+pymysql://testuser:testpassword@localhost:3306/{}'.format(db_name), encoding='utf8')
engine.connect()

3. 然后使用to_sql()先保存dataframe

df.to_sql(table_name, engine, if_exists='replace', index=False)

4. 使用sql语句在第一列插入主键ID

with engine.connect() as con:
    con.execute("""ALTER TABLE `{}`.`{}` \
            ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST, \
            ADD PRIMARY KEY (`id`);"""
                .format(db_name, table_name))

对应的SQL其实是：

ALTER TABLE `test_db`.`my_item_table`
	ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,
	ADD PRIMARY KEY (`id`);

然后就可以看到结果：

5. 验证

在mysql workbench中可以通过：

SELECT * FROM test_db.my_item_table;

得到结果：

完整代码

import pandas as pd
import numpy as np

df = pd.DataFrame(data=np.random.randint(0, 20, size=(1000, 3)))
df.columns = ['col_1', 'col_2', 'col_3']
# 连接数据库
from sqlalchemy import create_engine

db_name = "test_db"  # 要保存的数据库名
table_name = "my_item_table"  # 要保存的表名
# engine = create_engine("mysql+pymysql://用户名:密码@127.0.0.1:3306/数据库名")
engine = create_engine('mysql+pymysql://testuser:testpassword@localhost:3306/{}'.format(db_name), encoding='utf8')
engine.connect()
# save to database
df.to_sql(table_name, engine, if_exists='replace', index=False)
# 增加主键
with engine.connect() as con:
    con.execute("""ALTER TABLE `{}`.`{}` \
            ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST, \
            ADD PRIMARY KEY (`id`);"""
                .format(db_name, table_name))