需要创建表,在创建表之前我们需要将每个列指定一下格式:
column = ''for dd in devide:
column = column + dd + ' varchar(255),'
拼接好后我们需要将最后一个列的逗号去掉
col = column.rstrip(',')
创建表:
table_name = "TBexport"
create_table_sql = 'create table if not exists {} ({}) DEFAULT CHARSET=utf8'\
.format(table_name, col)
cur.execute(create_table_sql)
插入数据的语句:
file_path = "export.csv"
data = 'LOAD DATA LOCAL INFILE \'' + file_path \
+ '\'REPLACE INTO TABLE ' \
+ table_name \
+ 'CHARACTER SET UTF8 FIELDS TERMINATED BY \',' \
'\' ENCLOSED BY \'\"\' ' \
'LINES TERMINATED BY \'\n\' IGNORE 1 LINES;'
cur.execute(data.encode('utf8'))
con.commit() # 提交事务
关闭游标和数据库连接。
cur.close()
con.close()
csv数据导入样式:
mysql支持csv数据的导入,以下是sql的语法:
LOAD DATA INFILE '文件名'
REPLACE INTO TABLE 表名
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
完整脚本:
import pymysql
# file_path = "exam.csv"# table_name = 'update_time_table'
file_path = "export.csv"
table_name = "TBexport"try:
con = pymysql.connect(user="root",
passwd="root",
db="test",
host="47.95.20x.xxx",
local_infile=1)
con.set_charset('utf8')
cur = con.cursor()
cur.execute("set names utf8")
cur.execute("SET character_set_connection=utf8;")
withopen(file_path, 'r', encoding='utf8') as f:
reader = f.readline()
print(reader)
devide = reader.split(',') # 做成列表
devide[-1] = devide[-1].rstrip('\n') # 去除最后的换行符print(devide)
column = ''for dd in devide:
#如果标题过长,只能存成text格式if dd == "标题":
column = column + dd + ' TEXT,'else:
column = column + dd + ' varchar(255),'
col = column.rstrip(',') # 去除最后一个多余的,# print(column[:-1])
create_table_sql = 'create table if not exists {} ({}) DEFAULT CHARSET=utf8'.format(table_name, col)
print(create_table_sql)
data = 'LOAD DATA LOCAL INFILE \'' + file_path + '\'REPLACE INTO TABLE ' + table_name + ' CHARACTER SET UTF8 FIELDS TERMINATED BY \',\' ENCLOSED BY \'\"\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES;'
cur.execute(create_table_sql)
cur.execute(data.encode('utf8'))
print(cur.rowcount)
con.commit()
except:
print("发生错误")
con.rollback()
finally:
cur.close()
con.close()
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步