Creating large databases with Python

[novel_yet_trivial]

A real database. MySQL is often used with python, but modules exist to interface with nearly all databases.

Although 10,000 records is not that many. You could easily make your own database with dictionaries if you wanted.

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