Access list of dictionaries

[britesc]

I have used sqlitedict to save a number of dictionaries into a database.
eg
ford = {'make': 'Ford', 'wheels': '4', 'colour': 'black'}
mahindra = {'make': 'Mercedes', 'wheels': '4', 'colour': 'white'}
reliant = {'make': 'Robin', 'wheels': '3', 'colour': 'rusty yellow'}

I can retrieve all the dictionaries data with a for items loop.
What I want to do is make list of just the keys from the first dictionary so that I can use it as column headers. I do not want to hardcode the column headers in case I decide to upgrade the dictionaries at a later date.

I do not have sufficient knowledge yet to be able to do this.
I have tried various methods for the last few days but cannot seem to recover just the keys.

Any help really appreciated. Thank you.
jB

[deanhystad]

Next time try reading the documentation. Python documentation is pretty good, and I don't think it is referenced as often as it should.

https://docs.python.org/3/tutorial/datas...ctionaries

Performing list(d) on a dictionary returns a list of all the keys used in the dictionary, in insertion order (if you want it sorted, just use sorted(d) instead). To check whether a single key is in the dictionary, use the in keyword.

columns = list(ford)

Even if you didn't read that particular document, I'm having a tough time thinking up how you could try "various methods" and fail. Especially after you mentioned this: "I can retrieve all the dictionaries data with a for items loop.". Here are methods that use a loop to extract the dictionary keys.

ford = {"make": "Ford", "wheels": "4", "colour": "black"}

columns = []
for item in ford.items():
    columns.append(item[0])
print(columns)

columns = []
for key, value in ford.items():
    columns.append(key)
print(columns)

columns = []
for key in ford.keys():
    columns.append(key)
print(columns)

columns = []
for key in ford:
    columns.append(key)
print(columns)

columns = [key for key in ford.keys()]
columns = [key for key in ford]
print(columns)

columns = list(ford)
print(columns)

Did I misread your post?

You should look at using Pandas. Pandas can create a table from your dictionaries and write the table to a database.

import pandas as pd
import sqlite3 as sql

cars = [
    {"make": "Ford", "wheels": "4", "colour": "black"},
    {"make": "Mercedes", "wheels": "4", "colour": "white"},
    {"make": "Robin", "wheels": "3", "colour": "rusty yellow"},
]

cars = pd.DataFrame(cars)
print(cars.columns)

conn = sql.connect("cars.db")
cars.to_sql("makes", conn, if_exists="replace")
conn.close

[britesc]

Thank you for your swift reply.
Actually what I need most help with is finding out what the first dictionary in the list is called so I can use your formluae.
It may be that someone has deleted ford or added audi. Which is acceptable if their follow the structure. So the problem starts with getting the name of the first dictionary in the database, so ford.keys() may not work if ford is not in the list.

So can I access the first dictionary using a numerical value like [0] or ["0"]
I could possibly create a dummy car called AAAA with dummy values but correct keys, but that seems a bit self defeating.
Why make life easier for the computer when it should provide what I want???

If sqllitedict is a wrapper could I just use sqlite to find the first rows dictionary name and the using that revert to sqlitedict to retrieve the dictionary associated with it. Probably over kill, I suspect.

Thanks again,

[deanhystad]

I don't understand your posts. Are you making or querying a database? Maybe you should post some more code to provide context.

If you are querying a database, the query returns the columns as part of the description. Using the cars database created by my previous post:

import sqlite3 as sql

conn = sql.connect("cars.db")
cars = conn.cursor().execute("SELECT * FROM makes")
columns = [car[0] for car in cars.description]
print(columns)
print(*list(cars), sep="\n")
conn.close

Output:
['index', 'make', 'wheels', 'colour']
(0, 'Ford', '4', 'black')
(1, 'Mercedes', '4', 'white')
(2, 'Robin', '3', 'rusty yellow')

If you are creating a database, you should not use individual variables to represent a collection. Your code should not have ford or reliant or audi. You should have a list of cars, or better yet you should read a table of cars from a CSV file or have a GUI that allows entering cars. As far as the program is concerned, all cars are the same.

[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!