1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
# -*- coding: utf-8 -*-
from __future__ import print_function
from __future__ import unicode_literals
from builtins import str, bytes, dict, int
import os
import sys
sys.path.insert(0, os.path.join(os.path.dirname(__file__), "..", ".."))
from pattern.db import Database, SQLITE, MYSQL
from pattern.db import field, pk, STRING, INTEGER, DATE, NOW
from pattern.db import assoc
from pattern.db import rel
from pattern.db import pd # pd() = parent directory of current script.
# In this example, we'll build a mini-store:
# with products, customers and orders.
# We can combine the data from the three tables in an invoice query.
# Create a new database.
# Once it is created, you can use Database(name) to access it.
# SQLite will create the database file in the current folder.
# MySQL databases require a username and a password.
# MySQL also requires that you install MySQLdb, see the installation instructions at:
# http://www.clips.ua.ac.be/pages/pattern-db
db = Database(pd("store.db"), type=SQLITE)
#db._delete()
# PRODUCTS
# Create the products table if it doesn't exist yet.
# An error will be raised if the table already exists.
# Add sample data.
if "products" not in db:
# Note: in SQLite, the STRING type is mapped to TEXT (unlimited length).
# In MySQL, the length matters. Smaller fields have faster lookup.
schema = (
pk(), # Auto-incremental id.
field("description", STRING(50)),
field("price", INTEGER)
)
db.create("products", schema)
db.products.append(description="pizza", price=15)
db.products.append(description="garlic bread", price=3)
#db.products.append({"description": "garlic bread", "price": 3})
# CUSTOMERS
# Create the customers table and add data.
if "customers" not in db:
schema = (
pk(),
field("name", STRING(50)),
field("address", STRING(200))
)
db.create("customers", schema)
db.customers.append(name="Schrödinger") # Unicode is supported.
db.customers.append(name="Hofstadter")
# ORDERS
# Create the orders table if it doesn't exist yet and add data.
if "orders" not in db:
schema = (
pk(),
field("product_id", INTEGER),
field("customer_id", INTEGER),
field("date", DATE, default=NOW) # By default, current date/time.
)
db.create("orders", schema)
db.orders.append(product_id=1, customer_id=2) # Hofstadter orders pizza.
# Show all the products in the database.
# The assoc() iterator yields each row as a dictionary.
print("There are %s products available:" % len(db.products))
for row in assoc(db.products):
print(row)
# Note how the orders table only contains integer id's.
# This is much more efficient than storing entire strings (e.g., customer address).
# To get the related data, we can create a query with relations between the tables.
q = db.orders.search(
fields = (
"products.description",
"products.price",
"customers.name",
"date"
),
relations = (
rel("product_id", "products.id", "products"),
rel("customer_id", "customers.id", "customers")
))
print("")
print("Invoices:")
for row in assoc(q):
print(row) # (product description, product price, customer name, date created)
print("")
print("Invoice query SQL syntax:")
print(q)
print("")
print("Invoice query XML:")
print(q.xml)
# The XML can be passed to Database.create() to create a new table (with data).
# This is explained in the online documentation.
|
