Question

In this assignment, you will write a Python script to increase the population of each city in the world_x database by 10% (rounded).

First, add a new column to the "world_x" city table using the following MySQL command:

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

The existing population data are stored as JSON datatype in the city table in a field named Info. You learned about JSON data types in Module 2. To obtain the population data from the Info column in the city table, run the following MySQL command:

• select info-'$.Population' from city

Your Python script should do the following:

• Copy the population data from the "Info" column into the "Population" column,
• Increase the population by 10% in the "Population" column, and
• List the population before the increase and the population after it was increased.

Submit your Python script and Python script output as two separate text files included in a zip file. Submit your zip file to the Module 6 folder. Provide a detailed technical report on the steps needed to integrate Python and MySQL. In addition, provide details on how you iterate through a JSON field in a Python scrip

Answer 1

For indundation %

To answer the above question, I am dividing it into three parts :
Python script, which does the above mentioned tasks
Steps needed to integrate Python and MySQL
Iterating through a json field in a python script
Now elaborating every point one by one:
1.Python script, which does the above mentioned tasks Assumptions :
MySQL DB is set up on the local DB and you know the credentials.
MySQL db has a DB named "SampleDB" with a table named which has fields of (json type) and population (of INT type)
info field looks something like {'population':500}
Python script:
import mysql.connector mydb=mysql.connector.connect(host='localhost',database='Sampledb', user='root', password ='123456') ;
mycursor = mydb.cursor()
copy data from info colulmn to population column mycursor.execute("update city set population = JSON_EXTRACT(info, '$.population')") ;

mydb.commit()
Population before increase
print('Population before increase')
mycursor.execute("SELECT city_id,population FROM city") myresult = mycursor.fetchall()
for x in myresult:
city_id=x[0]
population=x[1]
print(city_id,population)
Increase the population by 10% in the Population column mycursor.execute("update city set population = population + population *0.10")

mydb.commit()
Population after increase
print('Population after increase')
mycursor.execute("SELECT city_id,population FROM city") myresult = mycursor.fetchall()
for x in myresult:
city_id=x[0]
population=x[1]
print(city_id,population)
2.Steps needed to integrate Python and MySQL
Installation on machine : Python 2.7 and MySQL should be installed on the system
Python packages : mysql-connector-python [using pip install MySQL-connector-python]

Step 1:
import mysql.connector
Step 2 :
Make MySQL DB connection using mydb=mysql.connector.connect(host='Iocalhost',database='SampleD This will validate that the credentials and other DB information is corr Step 3:
Execeute queries (like we did in the above part)
3.Iterating through a json field in a python script For iterating a json mysql field in python, we use
JSON_EXTRACT(info, '.population')
where info is the name of the column and population is one of the feel extract the value of any field in json.
Another approach would have been to extract whole json data from a json module.
Example :
import json
mycursor.execute("SELECT info FROM city") myresult = mycursor.fetchall()
for x in myresult:
json_info=json.loads(x)
print(json_info['population'])