Truncating table and then inserting data into MYSQL table with Python only inserts last record

Question

Whenever I run my Python code, I want it to truncate the table in MYSQL and then insert all the rows. I tried doing a TRUNCATE statement, followed by an INSERT INTO statement, but once the code is finished executing, I only see the last record in there. This is what my code looks like.

import pandas as pd
from sodapy import Socrata
import requests
import json
import pymysql

client = Socrata("data.cdc.gov", None)
results = client.get("9mfq-cb36", limit=100)

con = pymysql.connect(host = 'x.x.x.x', user = 'x', passwd = 'xxx', db = 'xxx')
cursor = con.cursor()

# for every column, get the value of the cell as it goes through each row
for val in results: 
    try:
        submission_date = val['submission_date']
    except(KeyError):
        submission_date = None

    try:
        state = val['state']
    except(KeyError):
        state = None 

     # continues getting vals for each column with try statements

     cursor.execute("TRUNCATE TABLE covid19_cases_t")
     cursor.execute("INSERT INTO covid19_cases_t (`submission_date`, `state`, `tot_cases`, `conf_cases`, `prob_cases`, `new_case`, `pnew_case`, `tot_death`, `conf_death`, `prob_death`, `new_death`, `pnew_death`, `created_at`, `consent_cases`, `consent_deaths`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (submission_date, state, tot_cases, conf_cases, prob_cases, new_case, pnew_case, tot_death, conf_death, prob_death, new_death, pnew_death, created_at, consent_cases, consent_deaths))

     con.commit()
     con.close()

I would like it so that once I execute the code, it truncates any data that's already in the table once, then inserts all rows again. The insert into statement works just fine, and if I don't have the truncate statement there, it executes all the rows. If I do have the truncate statement there, it truncates every record that the Insert Into statement inserts except for the very last record. I saw this StackOverflow post, but it doesn't deal with Python. The answer recommends to insert all statements in one go rather than one by one, but I don't know how to do that within Python. For anyone who has conceptual suggestions, please assist with coding that.

Am I supposed to make my insert statement different? Move stuff around in the code? Create a loop? Please help and thanks.

use truncate table query before for loop also con should be close after for loop — Nero, Mar 24 '21 at 15:32
@Nero Thanks! Your comment and the comment from the other user solved my problem. — coda, Mar 24 '21 at 15:38

score 1 · Accepted Answer · answered Mar 24 '21 at 15:14

1

Your cursor.execute statement is after the for loop, this is why all the variables stay at their last iteration values. You should first build a list and then run a for loop on that list to execute inserts.

answered Mar 24 '21 at 15:14

Zohaib Shah

49
2

Hi, I went ahead and fixed the cursor statements so that they're not after the for loop. When I execute it as is, I have the same result in my SQL table where it's just the last record. I'm not exactly sure how to build this list that you mentioned and what the for loop is supposed to look like, if you could please help. – coda Mar 24 '21 at 15:21
Oh, just had to move the truncate statement. All good now thanks! – coda Mar 24 '21 at 15:37

Truncating table and then inserting data into MYSQL table with Python only inserts last record

1 Answers1