任务:将招聘信息CSV文件数据导入数据库。
首先导入库:
import pandas as pd
import pymysql
import csv
import codecs
读取csv文件:
file_path = "E:/python/jobs/templates/static/manual.csv"
data = pd.read_csv(file_path, encoding='gbk' )
连接数据库:
def get_conn():
db = pymysql.connect(host='localhost',
user='root',
password='123',
database='jobs',
charset='gbk')
return db
自己在数据库里面创建一个 tb_csv 表单(注意格式 utf-8 / gbk ):
向表单中插入sql语句:
def insert(cur, sql, args):
try:
cur.execute(sql, args)
except Exception as e:
print(e)
读取csv文件并导入数据库:
def read_csv_to_mysql(filename):
'''
csv文件->数据库
:param filename:
:return:
'''
with codecs.open(filename=filename, mode='r', encoding='gbk') as f:
reader = csv.reader(f)
head = next(reader)
# print(head)
conn = get_conn()
cur = conn.cursor()
sql = 'insert into tb_csv values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
for item in reader:
# if item[1] is None or item[1] == '': # item[1]作为唯一键,不能为null
# continue
args = tuple(item)
# print(args)
insert(cur, sql=sql, args=args)
conn.commit()
cur.close()
conn.close()
就导入啦: