Re: Extract String From Enclosing Tuple
- From: Ben Finney <bignose+hates-spam@xxxxxxxxxxxxxxx>
- Date: Thu, 01 Mar 2007 09:13:40 +1100
rshepard@xxxxxxxxxxxxxxxxxxxxxx writes:
Data are assembled for writing to a database table. A
representative tuple looks like this:
('eco', "(u'Roads',)", 0.073969887301348305)
You refer to the second item as "a tuple" later, but it's not; it's
now just a string (not even a unicode string). Whatever has assembled
these has effectively lost the structure of the data and you are now
left with three items in a tuple: string, string, float.
Some RDBMSs can store a structure of multiple values in one value;
SQLite cannot. The usual solution for this limitation is to take these
structural values and store the component values as separate rows of a
different table, and have each of those rows refer back to the
identifying key of the original table so they can be joined easily.
E.g., I might conceptually think of order records as the following
tuples, with further tuples-of-tuples for the items on each order:
orders = (
# fields: id, cust_code, date, order_items
(1, "cust1234", "2007-02-15", (("item002", 1), ("item005", 3), ("item007", 1))),
(2, "cust4567", "2007-02-19", (("item001", 5), ("item005", 2))),
)
Since I can't store those as-is in SQLite, I would need to restructure
the tables: separate the "order items" to separate rows in a dedicated
table, and refer to the "order" table key in each "order item" row.
orders = (
# fields: id, cust_code, date
(1, "cust1234", "2007-02-15"),
(2, "cust4567", "2007-02-19"),
)
order_items = (
# fields: id, order_id, item_code, quantity
(1, 1, "item002", 1),
(2, 1, "item005", 3),
(3, 1, "item007", 1),
(4, 2, "item001", 5),
(5, 2, "item005", 2),
)
Then you can use SQL JOIN clauses as necessary, with the
order_item.order_id field a foreign key into the order table.
--
\ "I moved into an all-electric house. I forgot and left the |
`\ porch light on all day. When I got home the front door wouldn't |
_o__) open." -- Steven Wright |
Ben Finney
.
- References:
- Extract String From Enclosing Tuple
- From: rshepard
- Extract String From Enclosing Tuple
- Prev by Date: Re: Python Source Code Beautifier
- Previous by thread: Re: Extract String From Enclosing Tuple
- Next by thread: New to Tkinter GUI building
- Index(es):
Relevant Pages
|
