python 存list資料只重複存到最後一筆

python 存入sql 重複資料 sql phpmyadmin

hcc1108 2022-06-22 17:12:32 ‧ 1424 瀏覽

分享至

已經將我所需要的資料存入list裡
也將資料庫跟資料表建立好了
但是要將list資料存入資料表的時候只會重複存到最後一筆資料
原資料大約700多筆但不曉得為甚麼資料庫回存入到三千多筆
爬了很多文還是不曉得怎麼修改
想問一下版上的大大怎麼解決這個問題TT
以下是程式碼:

import requests,json,pymysql
#讀取資料
response = requests.get("http://tbike-data.tainan.gov.tw/Service/TopTenStation/Json")
if response.status_code == 200:
    tmp = json.loads(response.content.decode('utf-8'))
    totallist = []
    for items in tmp:
        details = {}
        details['Year'] = items['Year']
        details['Month'] = items['Month']
        details['Rank'] = items['Rank']
        details['StationName'] = items['StationName']
        details['Count'] = items['Count']
        totallist.append(details)
print(totallist)
#連接到資料庫
conn = pymysql.connect(host='*********',port=3306,user='*****',passwd='*****',db='ic',charset='utf8')
cursor = conn.cursor()
#print("ok")
#建立資料表
sql = "CREATE TABLE `ic`.`tbike3` ( `Year` INT(20) NOT NULL , `Month` INT(20) NOT NULL , `Rank` INT(20) NOT NULL , `StationName` VARCHAR(40) NOT NULL ,`Count` VARCHAR(40) NOT NULL ) ENGINE = InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;"
cursor.execute(sql)
conn.commit()
#將資料寫入資料庫
for i in range(len(totallist)):
    print(i)
    for j in totallist[i]:
        print(j)
        into = "INSERT INTO `tbike3` (`Year`, `Month`, `Rank`, `StationName` , `Count`) VALUES (%s,%s, %s, %s,%s)"
        values = (items['Year'],items['Month'],items['Rank'],items['StationName'],items['Count'])
        cursor.execute(into, values)
conn.commit()
cursor.close()
conn.close()

資料庫畫面:

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

jasper0047

iT邦新手 5 級 ‧ 2022-06-22 17:46:27

最後的迴圈items都是讀上面迴圈items的最後一筆，所以資料庫全部都一樣，變數要分清楚

for i in totallist:

    into = "INSERT INTO `tbike3` (`Year`, `Month`, `Rank`, `StationName` , `Count`) VALUES (%s,%s, %s, %s,%s)"
    values = (i['Year'],i['Month'],i['Rank'],i['StationName'],i['Count'])
    cursor.execute(into, values)
conn.commit()
cursor.close()
conn.close()

回應
分享
檢舉

登入發表回應

phes11434

iT邦新手 2 級 ‧ 2022-06-22 17:57:51

幫你修正了，可以寫入全部資料主要是迴圈問題，寫入的資料不對

import requests,json,pymysql
#讀取資料
response = requests.get("http://tbike-data.tainan.gov.tw/Service/TopTenStation/Json")
if response.status_code == 200:
    tmp = json.loads(response.content.decode('utf-8'))
    totallist = []
    for items in tmp:
        details = {}
        details['Year'] = items['Year']
        details['Month'] = items['Month']
        details['Rank'] = items['Rank']
        details['StationName'] = items['StationName']
        details['Count'] = items['Count']
        totallist.append(details)
#print(totallist)
#連接到資料庫
conn = pymysql.connect(host="localhost",port=3306,user='root',passwd='',db='ic',charset='utf8')
cursor = conn.cursor()
print("ok")
#建立資料表
sql = "CREATE TABLE if not exists `ic`.`tbike3` ( `Year` INT(20) NOT NULL , `Month` INT(20) NOT NULL , `Rank` INT(20) NOT NULL , `StationName` VARCHAR(40) NOT NULL ,`Count` VARCHAR(40) NOT NULL ) ENGINE = InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;"
cursor.execute(sql)
conn.commit()
#將資料寫入資料庫
for i in range(len(totallist)):
    #print(i)
    into = "INSERT INTO `tbike3` (`Year`, `Month`, `Rank`, `StationName` , `Count`) VALUES (%s,%s, %s, %s,%s)"
    values = (totallist[i]['Year'],totallist[i]['Month'],totallist[i]['Rank'],totallist[i]['StationName'],totallist[i]['Count'])
    cursor.execute(into, values)
conn.commit()
cursor.close()
conn.close()