On Thu, November 5, 2009 05:12, jim clark wrote:
> ... expressing one2many and many2many relationships.

Jim,

I remember feeling a tad uncertain myself when I first was exposed to Python
object databases in that I kept thinking about typical SQL relational
concepts. Looking back now I can't believe it was so difficult for me to make
that transition.

It will sound too-simple but the best advice is to forget all about terms
like one-to-many or de-normalization or 1NF or ...

A "database" of Python objects can be as simple as a sequence like a list()
or a tuple() or a mapping like a dict(). Here is a database of numbers:

    numbers = [1,2,3,66,123,14]

Pretty boring, but you can query it with a syntax that looks somewhat
familiar to SQL:

    print [num for num in numbers if num >= 14]

The equivalent SQL?

    select num from numtable where num >= 14;

The Python list comprehension is pretty similar, no?

Here is a database of people:

    people = dict(
        David='David Binger',
        Jim='Jim Clark',
        Mike='Michael Watkins')

And we can query that too:

    print people.get('Jim', None)
    print people.get('James', None)

SQL:

    select name from people p where p.nickname = 'Jim';

Hmnn, `people.get('Jim')` is pretty concise, albeit quite different from a SQL
query.

Now the truly neat thing about a Python object database is that we are not
constrained to such basic types as "varchar" or int or long. You can have a
database of Notes (a mapping (dict) keyed on a "title" perhaps) or a database
of People (a mapping keyed on a user name or email address).

At the risk of confusing things further I've attached a python source file
with an example Products, Customers, Orders - a situation where in SQL you
have one to many and many to many relationships. There are no Durus concepts
in the file rather intentionally. I started writing this as a multi-part
series a while ago in response to a question from a colleague but never
finished the series; I shall try to find the time to do that. Next
instalment: something more "databasey" looking.
"""
This file is a first in a series of object-oriented database design examples.

In oodb_part_one.py we'll look at an ultra-simplistic example showing Customers,
Orders, and Products which can be placed on order.

Often long-time RDBMS users have to cross a mental bridge in order leave behind
certain concepts that make sense in the relational world but less sense in an
object oriented world.

In this installment we shall show "one to many" and "many to many"
relationships. Bear with the example - these are contrived and less interesting
than how one would structure a solution in real life, coming up in part two.

Note that no Durus concepts have been introduced as yet; the object "storage" is
in
standard Python stuctures including lists, sets, and dicts.
"""

from datetime import datetime

# In part one our "tables" will be simple Python dictionaries (mappings) or
lists
customers = {}
orders = []
products = {}


class Product(object):

    def __init__(self, name, price):
        self.name = name
        self.price = price

    # repr methods to pretty-up the demo output
    def __repr__(self):
        return '<%s : %-10s : %.2f>' % (self.__class__.__name__,
                                        self.name, self.price)


class Customer(object):

    def __init__(self, name):
        self.orders = set()
        self.name = name

    def __repr__(self):
        return '<%s : %s>' % (self.__class__.__name__, self.name)


class Order(object):

    def __init__(self, customer):
        self.lines = []
        self.timestamp = datetime.now()
        self.customer = customer
        self.customer.orders.add(self)

    def add_line(self, product, qty):
        self.lines.append((product, qty))

    def __repr__(self):
        return '<%s : for %s : %s>' % (self.__class__.__name__,
                                       self.customer.name,
                                       self.timestamp.isoformat())


def print_order(order):
    print '-' * 30
    print order
    for product, qty in order.lines:
        print '  %s %s each @ %.2f' % (product.name, qty, product.price)
    print '-' * 30
    print


if __name__ == '__main__':

    # first, create products, customers and some orders ###########

    # create a "database" of Products - just a mapping (dict)
    for n, p in [('Hair Spray', 3.99),
                 ('Ultra-Gel', 12.99),
                 ('Scissors', 22.49)]:
        products[n] = Product(n, p)

    # add a customer to the db
    cust = Customer('Hair Plus')
    customers[cust.name] = cust

    # create an order for the customer
    neworder = Order(cust)
    neworder.add_line(products.get('Ultra-Gel'), 10)

    # finished order, add to the orders "database"
    orders.append(neworder)

    # let's add another customer and another order quickly
    # this one wants one of everything we have to offer
    cust = Customer('Georgios')
    customers[cust.name] = cust
    neworder = Order(cust)
    for product in products.values():
        neworder.add_line(product, 1)
    orders.append(neworder)
    # plus a second order for scissors alone
    neworder = Order(cust)
    neworder.add_line(products.get('Scissors'), 42)

    # and another customer without orders
    customers["Gillian's Place"] = Customer("Gillian's Place")

    # now, some queries ###########################################
    print "Customers whose name starts with the letter 'G'"
    print [c for c in customers.values() if c.name.startswith('G')]
    print

    print "Report: All products on file:"
    for p in products.values():
        print repr(p)
    print

    print "Report: All orders on file:"
    for order in orders:
        print_order(order)

    print "Report: Orders containing Ultra-Gel:"
    ultra_gel_orders = [order for order in orders if
                        products.get('Ultra-Gel') in order.lines]
    for order in ultra_gel_orders:
        print_order(order)

    print "Orders a specific customer has (Georgios):"
    cust = customers.get('Georgios')
    for order in cust.orders:
        print_order(order)