Access list of dictionaries

[Pedroski55]

I don't understand why people want to create an sql database from Python. It can be done with Python, but it's a one-off thing. Just create the database cars in phpMyAdmin.

After making the database, and creating the table or tables you want within that database, then you can add columns to a particular table, manipulate, retrieve and display sets of data from the database tables.

In this particular case, I would create a database cars and a table car_details. The table car_details should have a unique key, best as column 1 called id. The next column should contain the names of the manufacturers, call that column make. Then a column model, the name of the vehicle. Then as many other columns as you wish, such as wheels, colour, doors, engine, price, whatever. You can always add more columns to a table, or INSERT a new make of vehicle plus details.

I use pymysql, not sqllite3, but getting data from the database is pretty much the same:

def mysqlRemoteCW(num_wheels, colour): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.123', 
        user='myuser',  
        password = 'topsecret', 
        db='cars', 
        )   
    # results as tuple of tuples    
    #cur = conn.cursor()
    # results as list of dictionaries format column_name, value
    cur = conn.cursor(pymysql.cursors.DictCursor)
    # Example Select query 
    sql = f"SELECT make, name, wheels, colour FROM car_details WHERE  wheels < {num_wheels} AND colour = %s
    cur.execute(sql, (colour,))
    output = cur.fetchall() 
    # To close the connection 
    conn.close()
    return output

The dictionary output is unnecessary and repetative, because you should know the column names you want to get. I would be happy with a tuple of tuples!

If you set num_wheels = 1 you may not find many cars to suit that criterion!