下载CSV文件，然后使用Python上传到MySQL

b09cbbtk 于 2023-05-16 发布在 Mysql

关注(0)|答案(2)|浏览(137)

我尝试构建简单的python脚本来从USGS站点收集“实时”地震数据，这些数据以CSV文件的形式提供。我希望它能用Python获取数据，然后让它转过来更新我构建的MySQL服务器。

C:\Python27\python.exe C:/Users/XXXX/PycharmProjects/QuakeUpload/TestSQLOpen2.py
Traceback (most recent call last):
  File "C:/Users/XXXX/PycharmProjects/QuakeUpload/TestSQLOpen2.py", line 19, in <module>
    with connection.cursor() as cursor:
  File "C:\Python27\lib\site-packages\pymysql\connections.py", line 745, in cursor
    return self.cursorclass(self)
TypeError: 'str' object is not callable

Process finished with exit code 1

import pymysql
import codecs
import csv
import urllib2
import pymysql.cursors

# Get URL Data
url = "http://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.csv"
URLstream = urllib2.urlopen(url)
csvfile = csv.reader(codecs.iterdecode(URLstream, 'utf-8'))
# for rows in csvfile:
#    print(rows)

# Connect to the database

connection = pymysql.connect(host='b8con.no-ip.org', user='sal', password='XXXXXX', db='EarthQuake', charset='utf8mb4', cursorclass="pymysql.cursors.DictCursor")

try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO quakedata(time, latitude, longitude, depth, mag, magType, nst, gap, dmin, rms, net, id, updated, place, type) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        next(csvfile, None)
        for line in csvfile():
            cursor.execute(sql, (line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], line[8], line[9], line[10], line[11], line[12], line[13], line[14]))

finally:
    connection.close()


'''
next(csvfile, None)
for line in csvfile:
    print(line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], line[8], line[9], line[10], line[11], line[12], line[13], line[14])
    sql = "INSERT INTO quakedata(time, latitude, longitude, depth, mag, magType, nst, gap, dmin, rms, net, id, updated, place, type) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], line[8], line[9], line[10], line[11], line[12], line[13], line[14]))

connection.commit()

connection.close()
'''

mysql

来源：https://stackoverflow.com/questions/32107136/download-a-csv-file-then-upload-to-mysql-using-python

2条答案

按热度按时间

wtlkbnrh1#

有趣的是，追溯错误消息并没有显示导致错误的行。导致错误的行出现在对connection.cursor()的调用之前（回溯将该行作为问题的来源）。
该错误是由以下原因引起的

connection = pymysql.connect(host='b8con.no-ip.org', user='sal', 
                 password='bigsal71', db='EarthQuake', charset='utf8mb4', 
                 cursorclass="pymysql.cursors.DictCursor")

这可以通过删除cursorclass周围的双引号来修复：

connection = pymysql.connect(..., cursorclass=pymysql.cursors.DictCursor)

注意pymysql.cursors.DictCursor是一个类，而"pymysql.cursors.DictCursor"是一个字符串。如果self.cursorclass被设置为pymysql.cursors.DictCursor（通过cursorclass参数），则行

return self.cursorclass(self)

返回DictCursor类的示例。如果self.cursorclass是字符串"pymysql.cursors.DictCursor"，则调用该字符串将引发

TypeError: 'str' object is not callable

因为字符串不可调用。
另外，正如夜客指出的

for line in csvfile():

应该是

for line in csvfile:

（不带括号），因为csvfile本身就是可迭代的。

赞(0）回复(0）举报 2023-05-16

jckbn6z72#

谢谢你们！我能让它工作。这是我的最终代码。

import pymysql
import codecs
import csv
import urllib2
import pymysql.cursors

# Get URL Data
url = "http://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.csv"
URLstream = urllib2.urlopen(url)
csvfile = csv.reader(codecs.iterdecode(URLstream, 'utf-8'))

# Connect to the database

connection = pymysql.connect(host='b8con.no-ip.org', user='HanSolo', password='password', db='EarthQuake', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO quakedata(time, latitude, longitude, depth, mag, magType, nst, gap, dmin, rms, net, id, updated, place, type) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        next(csvfile, None)
        for line in csvfile:
            cursor.execute(sql, (line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], line[8], line[9], line[10], line[11], line[12], line[13], line[14]))

finally:
    connection.commit()
    connection.close()

赞(0）回复(0）举报 2023-05-16

我来回答

下载CSV文件，然后使用Python上传到MySQL

2条答案

相关问题

热门标签

最新问答