CODEWITHSUNDEEP
pythonmysqlsqlsql-updatesql-insert
Faisal Malik
Faisal Malik

Reputation: 77

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

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!

Upvotes: 1

Views: 433

Answers (1)

GMB
GMB

Reputation: 222512

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;

Upvotes: 1

Related Questions