How to print row that includes key with the smallest value-CodePudding

this is another question about my cities program. I am trying to print the city with the smallest population. I got it to print the smallest population, but I would like it to print the entire row with that population. Here is my code:

import sqlite3
import os 

# Remove Database file if it exists:

os.remove('cities.db')

connection = sqlite3.connect("cities.db")
cursor = connection.cursor()

cursor.execute("create table if not exists cities(city_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, cities_name TEXT, city_population INTEGER)")
    
cities_list = [('Minneapolis', 425336),
    ('St. Paul', 307193),
    ('Dallas', 1288000),
    ('Memphis', 628127),
    ('San Francisco', 815201),
    ('Milwaukee', 569330),
    ('Denver', 711463),
    ('Phoenix', 1625000),
    ('Chicago', 2697000),
    ('New York', 8468000)]

cursor.executemany("insert into cities(cities_name, city_population) values (?, ?)", cities_list)
connection.commit()

# Print entire table: 
for row in cursor.execute("select * from cities"):
    print(row)

# Print cities in alphabetical order:

cursor.execute("select cities_name from cities")
result = sorted(cursor.fetchall())
print(result)

# Print average:

cursor.execute("select city_population from cities")
result = list(cursor.fetchall())
average = sum(list(map(sum, list(result)))) / len(result)
print(average)

# Print city with the smallest population:

cursor.execute("select city_population from cities")
result = list(cursor.fetchall())
result.sort()
print('Smallest population is: ', result[0])

connection.commit()
connection.close()

I tried using the output of the smallest population and plugging it into some code the prints a row based that includes a specified value. Unfortunately, it didn't work.

CodePudding user response：

You can get sqlite to sort the cities for you. Replace

cursor.execute("select city_population from cities")

with

cursor.execute("select cities_name, city_population from cities order by city_population asc limit 1")

After that, the result variable should contain the name and population of the city with the smallest population.

You also don't need the result.sort() line after this change, as the sorting is now happening on the database side.

CodePudding user response：

While you can definitely do it with Python, this is probably better done using SQL.

Based on the sample schema that you provided, the following query should return what you're looking for:

SELECT * FROM cities
WHERE city_population = (
  SELECT MIN(city_population) FROM cities
)

The query gets every row that has the minimum population and returns this. This means that if two cities have the same minimum population, your query will return both.

In python, this will look like cursor.execute("select * from cities WHERE city_population = ( SELECT MIN(city_population) FROM cities )").

Here's the SQLFiddle that shows the query.

CodePudding user response：

Instead of sorting the list using python, you can let the sql query take care of sorted data retrieval from database.

Also add a check condition before removing the database file.

main.py :

import sqlite3
import os 

# Remove Database file if it exists:

if(os.path.isfile('cities.db')):
  os.remove('cities.db')


connection = sqlite3.connect("cities.db")
cursor = connection.cursor()

cursor.execute("create table if not exists cities(city_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, cities_name TEXT, city_population INTEGER)")
    
cities_list = [('Minneapolis', 425336),
    ('St. Paul', 307193),
    ('Dallas', 1288000),
    ('Memphis', 628127),
    ('San Francisco', 815201),
    ('Milwaukee', 569330),
    ('Denver', 711463),
    ('Phoenix', 1625000),
    ('Chicago', 2697000),
    ('New York', 8468000)]

cursor.executemany("insert into cities(cities_name, city_population) values (?, ?)", cities_list)
connection.commit()

# Print city with the smallest population:

cursor.execute("select cities_name, city_population from cities order by city_population")
result = list(cursor.fetchall())
print('Smallest population is: ', result[0])

connection.commit()
connection.close()

CodePudding user response：

You can solve every number in sql , so you donÄ't need pull so many information from the database, which makes the script faster

# Print average:
cursor.execute("select AVG(city_population) from cities")
result = cursor.fetchone()[0]
print(result)

# Print city with the smallest population:

cursor.execute("select city_population from cities ORDER BY city_population ASC LIMIT 1")
result = cursor.fetchone()[0]
print('Smallest population is: ', result)