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 datetime

mydb=mysql.connector.connect(
    host="localhost",
    user="usernamevalue",
    password="passwordvalue",
    database="dbnamevalue"
)

myCursor=mydb.cursor()

batch_size=100000
total_records=20000001

sql="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)