Jupyter Notebook 中的 Python for 循环不会在列表末尾停止

Question

已解决:这是我放在单元格开头的神奇函数%%timeit。我什至忘记在下面的问题中提及它。我将其删除，一切开始正常工作。

我有一个 CSV 文件列表，我打算循环并将其发送到函数。文件列表名为 1404.csv、1405.csv、.....、1905.csv、1906.csv

当我使用不带函数的 for 循环时，一切正常。

for i in os.listdir("dispensing_csv"):
    db_file = "05.db"
    csv_file = "dispensing_csv/" + i
    month = "01/" + i[2:4] + "/20" + i[0:2]
    # dispensing_csv_to_sql(db_file, csv_file, month)
    print("Period " + month + " written.")

输出变为:

Period 01/04/2014 written.
Period 01/05/2014 written.
....
....
Period 01/05/2019 written.
Period 01/06/2019 written.

但是当我尝试在循环内使用该函数时，循环永远不会结束。

for i in os.listdir("dispensing_csv"):
    db_file = "05.db"
    csv_file = "dispensing_csv/" + i
    month = "01/" + i[2:4] + "/20" + i[0:2]
    dispensing_csv_to_sql(db_file, csv_file, month)
    print("Period " + month + " written.")

输出变为:

Period 01/04/2014 written.
Period 01/05/2014 written.
....
....
Period 01/05/2019 written.
Period 01/06/2019 written.
Period 01/04/2014 written.
Period 01/05/2014 written.
....
....
....

我在 Jupyter Lab 中使用 Python 3.7.3。顾名思义，该函数尝试将一些 CSV 写入 sqlite 文件。

预先感谢您的帮助。

添加dispensing_csv_to_sql()

def dispensing_csv_to_sql(db_file, csv_file, month):
    # Same as in ContractorTypes in SQL db
    ContractorTypes = {"Abated": 1, "Appliance": 2, "Pharmacy": 3}

    # Connecting to the db
    conn = sqlite3.connect(db_file)
    c = conn.cursor()

    # Reading the CSV file in dataframe
    df = pd.read_csv(csv_file)
    df.columns = df.columns.str.strip() # to remove possible whitespaces

    # Checking if period exists in the db, update if not. Return period id
    query_period = c.execute("SELECT 1 FROM Periods WHERE Period = ?", (month, ))
    query_period = list(query_period)
    try:
        query_period = query_period[0][0]
    except:
        c.execute("INSERT INTO Periods (Period) VALUES (?)", (month, ))
    # Getting the period_id to use in DispensingNumbers table
    period_id = c.execute("SELECT PeriodID FROM Periods WHERE Period = ?", (month, ))
    period_id = list(period_id)
    period_id = period_id[0][0] # period_id

    # Now iterating rows in df
    for index, rows in df.iterrows():       
        # Checking if contractor exists in the db, update if not. Return contractor id
        query_contractor = c.execute("SELECT 1 FROM Contractors WHERE ContractorCode = ?", (rows["ContractorCode"], ))
        query_contractor = list(query_contractor)
        try:
            query_contractor = query_contractor[0][0]
        except:
            # Checking if area exists in the db, update if not. Return area id
            query_area = c.execute("SELECT 1 FROM Areas WHERE AreaCode = ?", (rows["AreaCode"], ))
            query_area = list(query_area)
            try: 
                query_area = query_area[0][0]
            except:
                # Inserting non-existing area data
                insert_area = "INSERT INTO Areas (AreaCode, AreaName) VALUES (?, ?)"
                c.execute(insert_area, (rows["AreaCode"], rows["Area"]))
            # Getting the area_id to use in Contractors table
            area_id = c.execute("SELECT AreaID FROM Areas WHERE AreaCode = ?", (rows["AreaCode"], ))
            area_id = list(area_id)
            area_id = area_id[0][0] # area_id         

            # Inserting non-existing Contractor data
            ContractorCode = rows["ContractorCode"]
            ContractorName = rows["ContractorName"]
            ContractorAddress = " ".join(rows[5:9].dropna().tolist())
            ContractorPostcode = rows["Postcode"]
            AreaID = area_id
            ContractorTypeID = ContractorTypes[rows["Contractor Type"].strip()]
            insert_contractor = "INSERT INTO Contractors (ContractorCode, ContractorName, ContractorAddress, \
                ContractorPostcode, AreaID, ContractorTypeID) VALUES (?, ?, ?, ?, ?, ?)"
            contractor_data_to_be_inserted = (ContractorCode, ContractorName, ContractorAddress, ContractorPostcode, AreaID, ContractorTypeID)
            c.execute(insert_contractor, contractor_data_to_be_inserted)            

        # Getting the contractor_id to use in DispensingNumbers table
        contractor_id = c.execute("SELECT ContractorID FROM Contractors WHERE ContractorCode = ?", (rows["ContractorCode"], ))
        contractor_id = list(contractor_id)
        contractor_id = contractor_id[0][0] # contractor_id

        # Inserting dispensing numbers data
        ContractorID = contractor_id
        PeriodID = period_id
        NumberofForms = rows["NumberofForms"] or 0
        NumberofItems = rows["NumberofItems"] or 0
        TotalNofPresPF = rows["TotalnumberofPrescriptions(ProfessionalFees)"] or 0
        NofPresPFStdDis = rows["NumberofPrescriptions(ProfessionalFees)(Standarddiscountrate)"] or 0
        NofPresPFZeroDis = rows["NumberofPrescriptions(ProfessionalFees)(Zerodiscountrate)"] or 0
        NofFormEPS = rows["NumberofformsforElectronicPrescriptionService(EPS)"] or 0
        NofItemEPS = rows["NumberofItemsprocessedviaElectronicPrescriptionService(EPS)"] or 0
        MUR = rows["NumberofMedicineUseReviews(MURs)declared"] or 0
        NMS = rows["NumberofNewMedicineService(NMS)interventionsdeclared"] or 0
        AURHome = rows["NumberofApplianceUseReviews(AURs)conductedinusershomes"] or 0
        AURPremise = rows["NumberofApplianceUseReviews(AURs)conductedatpremises"] or 0
        Stoma = rows["NumberofStomaCustomisationFees"] or 0

        insert_dispensingno = "INSERT INTO DispensingNumbers (ContractorID, PeriodID, NumberofForms, \
            NumberofItems, TotalNofPresPF, NofPresPFStdDis, NofPresPFZeroDis, NofFormEPS, NofItemEPS, MUR, \
            NMS, AURHome, AURPremise, Stoma) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        dispensingno_data_to_be_inserted = (ContractorID, PeriodID, NumberofForms, NumberofItems, TotalNofPresPF, NofPresPFStdDis, 
                                            NofPresPFZeroDis, NofFormEPS, NofItemEPS, MUR, NMS, AURHome, AURPremise, Stoma)

        c.execute(insert_dispensingno, dispensingno_data_to_be_inserted)

    conn.commit()
    conn.close()

Answer 1

我解决了这个问题。这是我放在单元格开头的神奇函数%%timeit。我什至忘记在下面的问题中提及它。我将其删除，一切开始正常工作。

我添加这个答案是为了能够将其标记为已解决。