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

我试图建立简单的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='bigsal71', 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()
'''

最佳答案

有趣的是，回溯错误消息没有显示导致错误的行。导致错误的行发生在调用connection.cursor()之前（该行以回溯为问题源）。

错误是由于

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

可以通过固定
removing the double quotes from around the cursorclass：

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

请注意，pymysql.cursors.DictCursor是一个
class，而"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

因为字符串是不可调用的。



另外，作为nightuser points out

for line in csvfile():

应该

for line in csvfile:

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