I am working on a Python script that processes a CSV file to update an SQLite database. The script reads various address-related fields from a CSV file and updates corresponding entries in the database based on a unique series_id. The data includes postal codes, street names, and city information which should be updated if previously marked as ‘downdate’.

The script is supposed to save the old records in a dictionary (downdate_records) during the ‘downdate’ operations and then use these records to update the database during the ‘update’ operations.

However, when I try to execute the update operation, it either doesn’t update the records as expected or throws an error, specifically when executing the nested SQL statement for the CITY_ID.

Here is the relevant part of the script:

import csv
import sqlite3
import logging

# Configure logging
def process_csv_file(file_path):
    print(f"Opening database connection to 'postcode.db'.")
    conn = sqlite3.connect('postcode.db')
    cursor = conn.cursor()
    print(f"Opening file {file_path}.")
    downdate_records = {}

    try:
        with open(file_path, mode='r', encoding='utf-8') as file:
            reader = csv.reader(file)
            next(reader)  # Skip the header
            print("Processing CSV file started.")

            for row in reader:
                operation_type = row[0]
                series_id = row[1]
                postcode_number = row[2]
                postcode_letter = row[3]
                house_nr_from = int(row[4])
                house_nr_to = int(row[5])
                series_indicator = row[6]
                street_name = row[7]
                street_name_nen = row[8]
                street_name_utf8 = row[9]
                street_name_utf8_nen = row[10]
                city_name = row[11]
                city_name_utf8 = row[12]
                city_name_utf8_nen = row[13]
                municipality_name = row[14]
                municipality_name_utf8 = row[15]
                municipality_name_utf8_nen = row[16]
                province_name = row[17]

                full_area_code = f"{postcode_number} {postcode_letter}"

                print(f"Processing operation {operation_type} for series_id {series_id}.")

                if operation_type == 'downdate':
                    downdate_records[series_id] = {
                        'full_area_code': full_area_code,
                        'house_nr_from': house_nr_from,
                        'house_nr_to': house_nr_to,
                        'street_name': street_name,
                        'street_name_nen': street_name_nen,
                        'street_name_utf8': street_name_utf8,
                        'street_name_utf8_nen': street_name_utf8_nen,
                        'city_name': city_name,
                        'municipality_name': municipality_name,
                        'province_name': province_name
                    }
                    print(f"Downdate record saved for series_id {series_id}.")
                
                elif operation_type == 'update':
                    orig = downdate_records.get(series_id)
                    if orig:
                        try:
                            cursor.execute('''
                            UPDATE STREET
                            SET AREA_CODE = ?,
                                FROM = ?,
                                TO = ?,
                                NAME = ?,
                                ALTNAME1 = ?,
                                ALTNAME2 = ?,
                                ALTNAME3 = ?,
                                CITY_ID = (SELECT CITY_ID FROM CITY WHERE NAME = ?)
                            WHERE STREET_ID = ?
                        ''', (full_area_code, house_nr_from, house_nr_to, street_name, street_name_nen, street_name_utf8, street_name_utf8_nen, city_name, series_id))
                            print(f"Update performed for series_id {series_id}.")
                        except sqlite3.Error as e:
                            logging.error(f"Error executing update for series_id {series_id}: {e}")

How can I ensure that the updates are correctly applied to the database without errors? Also, is there a more efficient way to handle transactions in this context to avoid partial updates?