我一直在编写一个python脚本,它从一个网站中提取和组织数据,然后尝试在mysql表中重现这些数据。出于这个问题的目的,这个表的名称是“电容器”,但我打算最终有更多的表用于更多的类别。
有一件事我似乎绕不开,那就是将数据插入mysql表。我的脚本正常运行(至少我这么认为),直到sql execute语句只向表中添加一行。它应该根据脚本顶部的for循环总共添加5行。edit:what i我在这里真正寻找的是能够用网站上的新信息更新现有的行,以及添加全新的行。
我能给你一些建议吗,我在这里遗漏了什么?让我知道如果你需要什么以外的脚本,这是下面。谢谢!!
import stock_checker, json, urllib.request, pprint, os, csv, re, time
import mysql.connector as my
# Create list of parts that will be looked up in Octopart
parts=[]
for k in range(len(stock_checker.d)):
parts.append((stock_checker.d[k]["product_id"]))
# THIS NEXT PART NEEDS TO HAPPEN FOR EACH PIECE
for i in range(5):
print ("Generating detailed part information on: %s" % parts[i])
url = 'http://octopart.com/api/v3/parts/match?'
url += '&queries=[{"mpn":"' + parts[i] + '"}]'
url += '&apikey=1b0d5753'
url += '&include[]=specs'
data = urllib.request.urlopen(url).read()
response = json.loads(data)
#print request time (in milliseconds)
# print response['msec']
debug=False
if debug:
pprint.pprint(response)
# print mpn's
mylisth= ['part_number'] #list of headers
mylistv= [parts[0]] #list of values
mydict = {} #dictionary of pairs
headers = []
for result in response['results']:
for item in result['items']:
for key,value in item['specs'].items():
try:
mydict.update({key:value['display_value']})
mylisth.append(key)
mylistv.append(value['display_value'])
print ("%s: %s" % (key, value['display_value']))
#print("%s" % (value['display_value']))
except KeyError as e:
print ("WARNING: Could not find Key for %s in key \"%s\". Value is %s" % (e, key, value))
print ("")
#vector of what all the headers should look like
try:
if mylisth[1] == 'capacitance':
cap_headers = ['part_number','capacitance','capacitance_tolerance','case_package','case_package_si','dielectric_characteristic',
'dielectric_material', 'lead_free_status','lifecycle_status','mounting_style','operating_temperature','packaging','pin_count',
'reach_svhc_compliance','rohs_status','size_height','size_length','size_thickness','size_width','voltage_rating_dc']
missing = list(set(cap_headers)-set(mylisth))
for i in range(len(missing)):
mylisth.insert(cap_headers.index(missing[i]), missing[i])
mylistv.insert(cap_headers.index(missing[i]), None)
if mylisth[2] == 'inductance':
ind_headers = ['part_number','case_package','inductance','lead_free_status','mounting_style','packaging',
'pin_count','rohs_status']
except:
print("ERROR: BYE! HAVE A GOOD TIME! ;)")
#Remove Units
#if mylisth[1]=='capacitance':
#mylistv[2]=re.sub("[^0-9]","",mylistv[2])
time.sleep(1)
#######################################################################################################
#connect to database
cnx = my.connect(host='localhost',user='root',password='',database='mydb')
cursor = cnx.cursor()
#create string input of mylisth
heads = ','.join(str(i) for i in mylisth)
#create string input of %s corresponding to number of entries in mylisth
placelst = ['%s' for i in mylisth]
placestr = ','.join(str(i) for i in placelst)
#add_capacitor -> SQL query line // data_capacitor -> values to be inserted using query line
add_capacitor = ("INSERT IGNORE INTO capacitors ("+heads+") VALUES ("+placestr+")")
data_capacitor = mylistv
cursor.execute(add_capacitor, data_capacitor)
emp_no = cursor.lastrowid
cnx.commit()
cursor.close()
cnx.close()
暂无答案!
目前还没有任何答案,快来回答吧!