Using two columns in an existing SQLite database to create a third column using Python

Not sure why it isn't working. Have you tried just doing it all in SQL?

conn = sqlite3.connect('nuclear_builds.sqlite')
cur = conn.cursor()

cur.execute('''ALTER TABLE Construction
    ADD COLUMN Dollar_per_KWh INTEGER;''')
cur.execute('''UPDATE Construction SET Dollar_per_KWh = cast((cost/MWe)*1000 as integer);''')

It's a lot simpler just doing the calculation in SQL than pulling data to Python, manipulating it, and pushing it back to the database.

If you need to do this in Python for some reason, testing whether this works will at least give you some hints as to what is going wrong with your current code.

Update: I see a few more problems now. First I see you are creating an empty dictionary dol_pr_kW before the for loop. This isn't necessary as you are re-defining it as a list later anyway.

Then you are trying to create the list dol_pr_kW inside the for loop. This has the effect of over-writing it for each row in data.

I'll give a few different ways to solve it. It looks like you were trying a few different things at once (using dict and list, building two lists and zipping into a third list, etc.) that is adding to your trouble, so I am simplifying the code to make it easier to understand. In each solution I will create a list called data_to_insert. That is what you will pass at the end to the executemany function.

First option is to create your list before the for loop, then append it for each row.

dol_pr_kW = list()

for row in data:
    id = row[0]
    cost = row[1]
    MWe = row[2]
    val = int((cost*10**6)/(MWe*10**3))
    dol_pr_kW.append(id,val)

#you can do this or instead change above step to dol_pr_kW.append(val,id).
data_to_insert = [(r[1],r[0]) for r in dol_pr_kW]

The second way would be to zip the key and value lists AFTER the for loop.

key = list()
value = list()

for row in data:
    id = row[0]
    cost = row[1]
    MWe = row[2]
    value.append(int((cost*10**6)/(MWe*10**3)))
    key.append(id)

dol_pr_kW = list(zip(key,value))
#you can do this or instead change above step to dol_pr_kW=list(zip(value,key))
data_to_insert = [(r[1],r[0]) for r in dol_pr_kW]

Third, if you would rather keep it as an actual dict you can do this.

dol_pr_kW = dict()

for row in data:
    id = row[0]
    cost = row[1]
    MWe = row[2]
    val = int((cost*10**6)/(MWe*10**3))
    dol_pr_kW[id] = val

# convert to list 
data_to_insert = [(dol_pr_kW[id], id) for id in dol_per_kW]

Then to execute call

cur.executemany('''UPDATE Construction SET Dollar_per_KWh = ? WHERE _rowid_ = ?''', data_to_insert)
cur.commit()

I prefer the first option since it's easiest for me to understand what's happening at a glance. Each iteration of the for loop just adds a (id, val) to the end of the list. It's a little more cumbersome to build two lists independently and zip them together to get a third list.

Also note that if the dol_pr_kW list had been created correctly, passing (dol_pr_kW[1],dol_pr_kW[0]) to executemany would pass the first two rows in the list instead of reversing (key,value) to (value,key). You need to do a list comprehension to accomplish the swap in one line of code. I just did this as a separate line and assigned it to variable data_to_insert for readability.