Integrating MySQL Table Data with a Python Script In this assignment, you will w

Question

Integrating MySQL Table Data with a Python Script

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 Populationcolumn,
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 script.

The Table and Column Names used in the Database are:

City: CountryCode, CityName, Population, Info

Country: CountryCode, CountyName

CountryLanguage: CountryCode, Language, Percentage

Explanation / Answer

Answer;

Here, I have following script using python code as shown below. May this will useful for you

Script:

import sqlite3

connection = sqlite3.connect("myTable.db")

con = connection.cursor()

#To create a table

sqlTable1 = """CREATE TABLE City(

CountryCode Number(20),

CityName varchar(10),

Info varchar(20)

);"""

sqlTable1 = """CREATE TABLE Country(

CountryCode Number(10),

CountryName varchar2(20)

);"""

# To create a table as CountryLanguage

sqlTable1 = """CREATE TABLE CountryLanguage(

CountryCode Number(10),

language varchar(20),

Percentage Number(10)

);"""

# execute the statement

con.execute(sqlTable1)

#To insert value

con.execute("""INSERT INTO City VALUES(2000,"ABC", "hahaha");""")

con.execute("""insert into City values(3400,"XXYY", "yummmy");""")

# To make an alter in the table

sqlTable1 = """ALTER TABLE `City` ADD COLUMN `Population` DOUBLE NULL DEFAULT NULL;"""

con.execute(sqlTable1)

con.execute("select info from City")

Population= con.fetchall()

# for inserting into table

con.execute("INSERT INTO City (Population) SELECT info from City")

#to make an update into table

con.execute("UPDATE City SET Population = (Population +(Population*10/100));")

con = connection.cursor()

connection.commit()

# select/display from the table

con.execute("SELECT info,population FROM City")

a= con.fetchall()

for j in a:

print(j)

connection.close()

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.