On 8/13/19 2:59 PM, Chris Angelico wrote:
On Wed, Aug 14, 2019 at 4:50 AM Dave via Python-list
<python-list@python.org> wrote:
Some of the tables are related. For example:
Hiking_Table Trails_Table Joining_Table
----------------- -------------------- -----------------
hike_id PK trail_id PK hike_id FK
hike_date TEXT trail_name TEXT trail_id FK
hike_destination TEXT trail_rating REAL
hike_rating REAL trail_comments TEXT
hike_comments TEXT
So far, so good. I know how to create the tables. What I am struggling
with is how do I insert data into the joining table or don"t I? If so,
do I need to query the other two tables to get the auto-number ID's?
Some things I have read suggest that the joining table just contains
references, so there is no actual insert. A pointer to information how
to do this would be appreciated. As for queries, I think I use joins,
but a pointer on how to do this would also be appreciated.
The joining table is a real thing, and will have real inserts. It
might be easier to think of this as two separate one-to-many
relationships; for the sake of demonstration, I'm going to add another
column to your joining table.
hike_sections ==> hike_id references hikes, trail_id references
trails, companion_name
You've decided to have someone with you for some sections of your
hike. As such, what we have is a number of "mini-hikes" that make up a
single hike (that's a one-to-many relationship between hikes and
sections), and also a single trail can be a section of any number of
hikes (so, another one-to-many relationship between trails and
sections). For any given section, there is exactly one companion.
Does this make the many-to-many relationship a bit easier to
understand? It'll work exactly the same way even if you have no
ancillary information in that joining table.
ChrisA
Chris,
Thanks for the note. I get the theory of MTM and the join table. It is
the implementation I don't get. Let me see if I can demonstrate my
confusion using pseudo code.
def dbTables_create (dbpath):
sql_HikeTable = """ CREATE TABLE IF NOT EXISTS hike (
hike_id INTEGER AUTO_INCREMENT PRIMARY KEY,
hike_date TEXT,
hike_destination TEXT,
hike_rating REAL,
hike_comments TEXT ) """
sql_TrailTable = """ CREATE TABLE IF NOT EXISTS trail (
trail_id INTEGER AUTO_INCREMENT PRIMARY KEY,
trail_name TEXT,
trail_rating REAL,
trail_comment TEXT ) """
sql_JoiningTable = """ CREATE TABLE IF NOT EXISTS hike_trail (
hike_id INTEGER
trail_id INTEGER ) """
# Some more code to open connection, create cursor, execute SQL.
def getUserInput ():
# Code to get the user input.
# The user input is:
hdate = "2019-05-28"
hdestination = "Top of White Face Mountain, NY."
hrating = 5.0 # Rating scale 1.0 (bad) to 5.0 (perfect).
hcomments "Got to do again. Better shoes needed."
tname1 = "Brookside"
trating1 = 4.5
tcomments1 = "Easy"
tname2 = "Wilmington Trail"
trating2 = 4.9
tcomments2 = "Awesome!!"
def dbDataInsert():
sql_HikeInsert = """ INSERT INTO hike (
hike_date,
hike_destination,
hike_rating,
hike_comments )
VALUES (
hdate,
hdestination,
hrating,
hcomments ) """
sql_TrailInsert = """ NSERT INTO trail (
trail_name,
trail_rating,
trail_comment )
VALUES (
tname1,
trating1,
tcomments1 ) """
sql_TrailInsert = """ NSERT INTO trail (
trail_name,
trail_rating,
trail_comment )
VALUES (
tname2,
trating2,
tcomments2 ) """
""" ---> Now what? I need to populate the join (hike_trail) table.
Do I query the tables to get the id's? Is there another
way? This is the part I really don't get. """
Dave,
--
https://mail.python.org/mailman/listinfo/python-list
