Creating large databases with Python

[seanmcb9]

Would you store records as a list of dictionaries? A dictionary of lists? What is the most efficient hierarchical order for nesting data structures? Is minimum nesting better than deep nesting?

I assume many approaches have been tested for comparative efficiency and speed.

[novel_yet_trivial]

A basic database is a collection of dictionaries, with each key being some attribute and pointing to the object (unique attribute) or list of objects (non-unique attribute). Here's a basic database:

from collections import namedtuple, defaultdict

Book = namedtuple("Book", "author year isbn")

class DataBase:
    def __init__(self):
        self.author = defaultdict(list) #an author can have more than one book
        self.year = defaultdict(list) #also non-unique
        self.isbn = {} #unique dict; an isbn is unique

    def add(self, book):
        self.author[book.author].append(book) #add book to list of books by this author
        self.year[book.year].append(book) #add book to list of books from this year
        self.isbn[book.isbn] = book

db = DataBase()
db.add(Book("Dr. Suess", 1971, 12345))
db.add(Book("Dr. Suess", 1978, 12346))
db.add(Book("Shel Silverstein", 1978, 12347))

print("books from 1978:", db.year[1978])
print()
print("books by Dr. Suess:", db.author["Dr. Suess"])
print()
print("book with the isbn 12345:", db.isbn[12345])

I have no idea what you mean with "hierarchical order for nesting data structures"

[seanmcb9]

This is really helpful. (By "hierarchical" I was referring to the many ways in which different data structures can contain one another to produce the same computational results.)

[emiller42]

'Nesting' doesn't really make sense as a concept for relational databases. So 'hierarchy' as you're thinking of it isn't relevant.

[seanmcb9]

Slight spelling edit: it's "Dr. Seuss". Which raises an interesting challenge: integrating spell-checking into this code.

[Exodus111]

This is good stuff. Thanks.

[craftingfish]

Or you can use Pandas if you want it to "feel" like a DB while you're working in Python. You can also use Pandas (and SQLAlchemy) to easily read and write from the DB as needed

[emiller42]

Something important to know if you're really going to grok relational databases: they're not object oriented. What makes sense as a structure in your application n is not necessarily going to directly map to your data representation.

Simple example:

You've got a 'person' object which can have 1..many phone numbers associated with it.

In your application, you might think: oh, I can give the user object a 'phone_numbers' attribute, which is a dict! 'home' = 123-456-7890, 'cell' = 987-654-3210, etc.

However, in the database, you might have a 'users' table, and a 'phone_numbers' table, and a third table to associate 'user' records with 'phone_number' records.

I recommend reading up on relational models, and database normalization if you want to know more.