How to populate a new column in database using python script?

Question

I have a table in my database (world_x) called 'city':

| ID | Name           | CountryCode | District      | Info                    | Population |
+----+----------------+-------------+---------------+-------------------------+------------+
|  1 | Kabul          | AFG         | Kabol         | {"Population": 1780000} |       NULL |
|  2 | Qandahar       | AFG         | Qandahar      | {"Population": 237500}  |       NULL |
|  3 | Herat          | AFG         | Herat         | {"Population": 186800}  |       NULL |
|  4 | Mazar-e-Sharif | AFG         | Balkh         | {"Population": 127800}  |       NULL |
|  5 | Amsterdam      | NLD         | Noord-Holland | {"Population": 731200}  |       NULL |

I created the Population field using :

ALTER TABLE `world_x`.`city` 
ADD COLUMN `Population` DOUBLE NULL DEFAULT NULL AFTER `Info`;

I used the following script to try and load the data but failed:

import mysql.connector
import json

mydb = mysql.connector.connect(host='localhost', user='****',password='********')

mycursor = mydb.cursor()

mycursor.execute("use world_x")
# This is for 10 rows but I will need all the rows for actual insert
mycursor.execute("SELECT * FROM city LIMIT 10")

new_column_data = []
for i in mycursor:
   s = json.loads(i[4])
   pop_before = s["Population"]
   pop_after_increase = int(round(pop_before * 1.1,0))
   new_column_data.append('Before: {}. After: {}'.format(pop_before, pop_after_increase))

output: of print(new_column_data):
['Before: 1780000. After: 1958000',
'Before: 237500. After: 261250',
'Before: 186800. After: 205480',
'Before: 127800. After: 140580',
'Before: 731200. After: 804320',
'Before: 593321. After: 652653',
'Before: 440900. After: 484990',
'Before: 234323. After: 257755',
'Before: 201843. After: 222027',
'Before: 193238. After: 212562']

sql = "INSERT INTO world_x.city Population VALUES (%s)"

mycursor.executemany(sql, new_column_data)

My goal is to fill the Population column with ["Before:, After:"] data <- This is one column. Any help would be greatly appreciated!

You should never copy data between columns. Each value should only be stored once! — jarlh
– jarlh, Commented Nov 16, 2020 at 20:50
but failed...Please provide specifics to your issue. Errors or undesired results? Do you really want to INSERT or UPDATE? — Parfait
– Parfait, Commented Nov 16, 2020 at 22:49
I think I should update as I managed to insert, and it populated rows starting at the bottom of Population column. — Faisal Malik
– Faisal Malik, Commented Nov 17, 2020 at 17:03

GMB · Accepted Answer · 2020-11-16 20:50:56Z

1

If I follow you correctly, you can do the whole process in the database with a single update query:

update city
set population = (info ->> '$.Population') * 1.1

Note that you should not be storing such derived information. You can use a computed column instead:

alter table city
    add column population double
    generated always as ((info ->> '$.Population') * 1.1) stored;

answered Nov 16, 2020 at 20:50

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Faisal Malik Over a year ago

My assignment requires me to use a python script to do it.

Collectives™ on Stack Overflow

How to populate a new column in database using python script?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related