import pymysql
import csv
from pymysql.cursors import Cursor
# utf-8-sig ---> 带字节序签名的utf-8(utf-8 with BOM)
f = open('2018年北京积分落户数据.csv', 'r', encoding='utf-8-sig', newline='')
reader = csv.reader(f)
# 1. 创建连接
conn = pymysql.connect(host='localhost', port=3306,
user='guest', password='guest.618',
database='hrs', charset='utf8mb4')
try:
next(reader)
# 2. 获取游标对象
with conn.cursor() as cursor: # type: Cursor
# 3. 通过游标对象执行SQL语句
cursor.execute(
'create table if not exists tb_houses'
'('
'id int unsigned not null,'
'name varchar(10) not null,'
'birthday date not null,'
'company varchar(50) not null,'
'score decimal(5,2) not null,'
'primary key (id)'
')engine=innodb comment "北京积分落户数据"'
)
params = []
count = 0
for row in reader:
row[2] = row[2] + '-01'
params.append(row)
count += 1
if count % 1000 == 0:
# insert批处理,将数据批量写入数据库
cursor.executemany(
'insert into tb_houses (id, name, birthday, company, score) '
'values (%s, %s, %s, %s, %s)',
params
)
params = []
cursor.executemany(
'insert into tb_houses (id, name, birthday, company, score) '
'values (%s, %s, %s, %s, %s)',
params)
# 4. 手动提交(让之前的操作生效)
conn.commit()
except pymysql.MySQLError as err:
# 4. 手动回滚(撤销之前的操作)
conn.rollback()
print(err)
finally:
# 5. 关闭连接
conn.close()
f.close()
【作业】2022.5.28 将CSV写入数据库
最新推荐文章于 2024-09-01 13:47:06 发布