CODEWITHSUNDEEP
pythonsqlite
Kyle C
Kyle C

Reputation: 121

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

I have created a database with multiple columns and am wanting to use the data stored in two of the columns (named 'cost' and 'Mwe') to create a new column 'Dollar_per_KWh'. I have created two lists, one contains the rowid and the other contains the new value that I want to populate the new Dollar_per_KWh column. As it iterates through all the rows, the two lists are zipped together into a dictionary containing tuples. I then try to populate the new sqlite column. The code runs and I do not receive any errors. When I print out the dictionary it looks correct.

Issue: the new column in my database is not being updated with the new data and I am not sure why. The values in the new column are showing 'NULL'

Thank you for your help. Here is my code:

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

cur.execute('''ALTER TABLE Construction
    ADD COLUMN Dollar_per_KWh INTEGER''')

cur.execute('SELECT _rowid_, cost, Mwe FROM Construction')
data = cur.fetchall()

dol_pr_kW = dict()
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))

cur.executemany('''UPDATE Construction SET Dollar_per_KWh = ? WHERE _rowid_ = ?''', (dol_pr_kW[1], dol_pr_kW[0]))
conn.commit()

Upvotes: 3

Views: 1313

Answers (1)

N. Lombardo
N. Lombardo

Reputation: 76

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.

Upvotes: 2

Related Questions