Question

Using python and SQLite, connect to the database called "world.db".

a. Input the data into table 'city' and print

b. Using python, print out the country Name, Continent, Region and the country's Population of all the countries, whose country population is larger than 1,000,000,000. Print out all the fields for the cities in the U.S. whose city population is larger than 1,000,000.

Answer 1

Full working python code:

#Firstly importing sqlite3 module from python library
import sqlite3
from sqlite3 import Error

def sqlite_connection():
    try:
        conn = sqlite3.connect('world.db') #Creates the database world.db in the system memory
        return conn                        #if the connection made then return the connection
    except Error:
        print(Error)                      #if connection failed to make then throw error
        
def sqlite_table(conn):
    #cursor is method of connection object to execute SQlite statements in Python
    cursorObj = conn.cursor()
    #execute method executes the sqlite commands
    cursorObj.execute(
        "CREATE TABLE city(id integer PRIMARY KEY, country_name text, continent text, region text, population real)")
    #commit method is used to save changed done in the database with execute statement
    conn.commit()

#method to insert rows into the table
def sqlite_insert(conn, row):
    #again define the cursor. Everytime we need to excute sql query we need to define cursor for that
    cursorObj = conn.cursor()
    #? represent that it is expected to get its value from tuple row
    cursorObj.execute(
        'INSERT INTO city(id, country_name, continent, region, population) VALUES(?, ?, ?, ?, ?)', row)
    conn.commit()

#method to select or display desired rows for question part 1
def sqlite_select1(conn):
    cursorObj = conn.cursor()
    #Countries with population > 1000000000 will be returned only
    cursorObj.execute('SELECT country_name, continent, region, population FROM city WHERE population > 1000000000')
    #Fetch all method is uded to get all the values from the cursor and storing it into a variable
    rows = cursorObj.fetchall()
    #Now iterate through that variable to display the desired rows
    for row in rows:
        print(row)
        
#function to get all records for question part 2. Same as above just the select query is being changed
def sqlite_select2(conn):
    cursorObj = conn.cursor()
    cursorObj.execute('SELECT * FROM city WHERE continent = "United States" AND population > 1000000')
    rows = cursorObj.fetchall()
    for row in rows:
        print(row)

conn = sqlite_connection()  #Try to make connection with sqlite
sqlite_table(conn)          #If connection made the create sqlite table with that connection
row1 = (1, 'India', 'Asia', 'Peninsular', 30000000000)    #Define row 1 
sqlite_insert(conn,row1)    #Insert row 1
row2 = (2, 'Australia', 'Australia', 'Vascular', 20000000)
sqlite_insert(conn,row2)    #Insert row 2
row3 = (3, 'Chicago', 'United States', 'A', 2000000)
sqlite_insert(conn,row3)    #Insert row 3
row4 = (4, 'New Mexico', 'United States', 'B', 3400000)
sqlite_insert(conn,row4)    #Insert row 4
row5 = (5, 'Washington', 'United States', 'C', 100000)
sqlite_insert(conn,row5)    #Insert row 5
row6 = (6, 'Sri Lanka', 'Asia', 'Peninsular', 200000)
sqlite_insert(conn,row6)    #Insert row 6
row7 = (7, 'Russia', 'Russia', 'X', 35000000)
sqlite_insert(conn,row7)    #Insert row 7
print('Solution 1:')
sqlite_select1(conn)        #Displays the data for question part 1
print('Solution 2:')
sqlite_select2(conn)        #Displays records for part 2 question

Screenshots;

Output:

Directory with .py file and database file: