Extract String From Enclosing Tuple

[Ben Finney]

rshepard at nospam.appl-ecosys.com 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