python读取文件中的数据插入到mysql

84 阅读 0 评论 56 点赞

我是靠谱客的博主年轻小松鼠，这篇文章主要介绍python读取文件中的数据插入到mysql，现在分享给大家，希望可以做个参考。

使用Python读取文件中的数据然后插入到mysql表中
在python中存在许多的第三方库操作MySQL，比如MySQLdb、pymysql等
MySQLdb是针对与python2的模块，还未曾支持python3
主要讲解一下pymysql这个模块

pymysql

安装模块

复制代码

1
2
pip3 install pyMySQL

如果是windows下的Anaconda用户，可以打开Anaconda Prompt后使用命令

复制代码

1
2
conda install PyMySQL

连接数据库

获取一个连接：connect()

复制代码

1
2
connect = pymysql.connect(host,username,password,db_name)

获取游标对象：cursor()

复制代码

1
2
cursor = connect.cursor()

执行sql语句：execute()

复制代码

1
2
cursor.execute("show tables")

关闭资源：close()

复制代码

1
2
3
cursor.close()
connect.close()

以上就是使用python对mysql的操作，基本流程就是使用这几个方法，关于查询或者创建等需求，只需要编写不同的sql语句即可

读取文件写入mysql

首先知道需要使用的方法
1、加载文件:open()

复制代码

1
2
f = open("F:\Done\dic\part-r-00000.txt", "r")

2、读取一行文件：readline()

复制代码

1
2
line = f.readline()

3、切割字符：strip()、split()

复制代码

1
2
3
4
5
line = line.strip('n')
#取出每行首尾的空格回车
line = line.split(",")
#按照“，”进行分割字符

编写代码

复制代码

import pymysql
from time import time
host="localhost"
port=3306
username="root"
password="000000"
db_name="AIS202002"
conn = pymysql.connect(host=host,port=port,user=username,passwd=password,db=db_name)
cur = conn.cursor()
f = open("F:\Done\dic\part-r-00000.txt", "r")
start_time = time()
while True:
line = f.readline()
if line:
#处理每行n
line = line.strip('n')
line = line.split(",")
MMSI = line[0]
shipType = line[1]
nacStatusEN = line[2]
draught = line[3]
heading = line[4]
course = line[5]
speed = line[6]
dest = line[7]
unixTime = line[8]
lon_d = line[9]
lat_d = line[10]
seaRange = line[11]
try:
cur.execute("insert into CargoShip(MMSI,shipType,nacStatusEN,draught,heading,course,speed,dest,unixTime,lon_d,lat_d,seaRange)"
"values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
[MMSI,shipType,nacStatusEN,draught,heading,course,speed,dest,unixTime,lon_d,lat_d,seaRange])
print("成功插入一条数据")
except Exception as e:
conn.rollback()
print("there is a error!")
else:
break
f.close()
cur.close()
conn.commit()
conn.close()
end_time = time()
print("总共执行了 {} 秒！".format(end_time - start_time))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
import pymysql
from time import time
host="localhost"
port=3306
username="root"
password="000000"
db_name="AIS202002"
conn = pymysql.connect(host=host,port=port,user=username,passwd=password,db=db_name)
cur = conn.cursor()
f = open("F:\Done\dic\part-r-00000.txt", "r")
start_time = time()
while True:
line = f.readline()
if line:
#处理每行n
line = line.strip('n')
line = line.split(",")
MMSI = line[0]
shipType = line[1]
nacStatusEN = line[2]
draught = line[3]
heading = line[4]
course = line[5]
speed = line[6]
dest = line[7]
unixTime = line[8]
lon_d = line[9]
lat_d = line[10]
seaRange = line[11]
try:
cur.execute("insert into CargoShip(MMSI,shipType,nacStatusEN,draught,heading,course,speed,dest,unixTime,lon_d,lat_d,seaRange)"
"values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
[MMSI,shipType,nacStatusEN,draught,heading,course,speed,dest,unixTime,lon_d,lat_d,seaRange])
print("成功插入一条数据")
except Exception as e:
conn.rollback()
print("there is a error!")
else:
break
f.close()
cur.close()
conn.commit()
conn.close()
end_time = time()
print("总共执行了 {} 秒！".format(end_time - start_time))