Python access mysql and insert data batch by batch

pip install mysql-connector
or

pip install mysql-connector-python -i https://pypi.tuna.tsinghua.edu.cn/simple/

create table t2(id bigint primary key auto_increment,firstname varchar(100) not null default '',lastname varchar(100) not null default '',
index fn_idx(firstname)) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

import mysql.connector
from datetime import datetimemydb=mysql.connector.connect(host="localhost",user="usernamevalue",password="passwordvalue",database="dbnamevalue"
)myCursor=mydb.cursor()batch_size=100000
total_records=20000001sql="insert into t2(firstname,lastname) values(%s,%s)"
print(f"Starting insertion at {datetime.now()}")for start in range(0,total_records,batch_size):timeStart=datetime.now()end=min(start+batch_size,total_records)values=[]for a in range(start+1,end+1):values.append((f"fn_{a}",f"ln_{a}"))myCursor.executemany(sql,values)mydb.commit()    cost=f"{(datetime.now()-timeStart).total_seconds():.2f} seconds"print(f"Commited:{end},cost:{cost} at {datetime.now()}")print(f"Finished insertion at {datetime.now()}")myCursor.execute("select count(firstname) from customers")
for x in myCursor:print(x)