Question

Write a program called popchg.py that enables DBVac to update the state populations for states in the databsae. Your program should: • Include a comment in the first line with your name. • Include comments describing each major section of code. • Connect to your personal version of the DBVac database. • Ask the user to input a two-letter state abbreviation. o 1 point Extra Credit: Validate that what the user entered was two characters long. If not, then have the user re-enter the abbreviation until it’s two characters long. • Ask the user to input the population of the new state. o 1 point Extra Credit: Validate that the value entered was an integer above 0. If not, keep asking the user to enter a value until they enter an integer above 0. • Update the database to change the population for the state entered to the new population value entered.

Answer 1

I have given the simplest possible code below with the necessary explanations in the comments followed by the #.

#import the mysql connector and system modules
#The code below assumes a MySQL database
#However it can easily modified for any other database
import mysql.connector
import sys

#Connect to the appropriate host and database
#Replace localhost with the host name or IP address,
#user with username used to connect to database
#password with the password used for database connection
#mention the database name accurately

mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="dbVac"
)

#Capture the State Abbreviation from the user
#Initialize the variable first
inputState = ""

#Enter into a loop for validating the length of the input
#Validation can be accomplished in various ways
#Below is the simplest way however it can also be done
#with try and except statements as in the next code unit below
while len(inputState) != 2:
inputState = input("Enter two character State abbreviation: ")

#Capture the State population and validate the input
#Using the try and except block
while True:
inputPop = input("Enter the state population: ")
try:
value = int(inputPop)
if value > 0:
break
else:
print("Population should be greater than zero, try again")
except ValueError:
print("Population must be a number, try again")

#Create a cursor object of the database
mycursor = mydb.cursor()

#Capture the SQL statement used to update the Database as instructed
#Use the appropriate table name and column names in place of
#state_info, state_population and state_id (as per your personal database dbVac)
#Use %s to pass the variables captured through input
# This is to prevent SQL injection and is considered as a good practice
#You can read about this on Google
sql = "UPDATE state_info SET state_population = %s WHERE state_id = %s"
val = (inputState, inputPop)

#Execute the SQL statement and commit the query
#Print the results to confirm that the appropriate table is updated
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")