Re: Create multiple sqlite tables, many-to-many design
Chris Angelico wrote: > On Thu, Aug 15, 2019 at 7:41 PM Gregory Ewing > wrote: >> >> Chris Angelico wrote: >> > I prefer to say "Trails" for the table, and "Trail" would then refer >> > to a single row from that table. >> >> That makes sense for a data structure in your program that contains a >> collection of rows. But I've come to the view that SQL tends to read >> better if the names of the database tables are singular, because in SQL >> you're writing assertions about individual rows, not the tables in >> their entirety. >> >> So I would write something like >> >> select T.name, T.id from Trail T >> >> but I would assign the resulting list of rows to a variable named >> "trails" in my program. >> > > You're selecting from a collection of trails. I don't see a conflict > here. It's the same as taking a list of values and then filtering it - > even though assertions are made about individuals, you are filtering > the entire list: > > early_things = [thing for thing in things if thing.name < 'M'] If list comprehensions were like sql queries then the above would be early_things = [things.* from things where things.name < "M"] That doesn't read well as the condition works on one thing at a time. However, I'm so used to select things.* from things where things.name < "M" in the sql context that I still prefer it. Also, in simple queries you can omit the table name. -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On Thu, Aug 15, 2019 at 7:41 PM Gregory Ewing wrote: > > Chris Angelico wrote: > > I prefer to say "Trails" for the table, and "Trail" would then refer > > to a single row from that table. > > That makes sense for a data structure in your program that contains a > collection of rows. But I've come to the view that SQL tends to read > better if the names of the database tables are singular, because in SQL > you're writing assertions about individual rows, not the tables in > their entirety. > > So I would write something like > > select T.name, T.id from Trail T > > but I would assign the resulting list of rows to a variable named > "trails" in my program. > You're selecting from a collection of trails. I don't see a conflict here. It's the same as taking a list of values and then filtering it - even though assertions are made about individuals, you are filtering the entire list: early_things = [thing for thing in things if thing.name < 'M'] ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
Chris Angelico wrote: I prefer to say "Trails" for the table, and "Trail" would then refer to a single row from that table. That makes sense for a data structure in your program that contains a collection of rows. But I've come to the view that SQL tends to read better if the names of the database tables are singular, because in SQL you're writing assertions about individual rows, not the tables in their entirety. So I would write something like select T.name, T.id from Trail T but I would assign the resulting list of rows to a variable named "trails" in my program. -- Greg -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On Wed, 14 Aug 2019, Chris Angelico wrote: I prefer to say "Trails" for the table, and "Trail" would then refer to a single row from that table. +1 Rich -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On Wed, Aug 14, 2019 at 9:06 PM Gregory Ewing wrote: > > MRAB wrote: > > Another thing you might want to avoid is naming something with what it > > is, e.g. "Trails_Table" (why not just "Trails"). > > Or possibly just "Trail", since any table potentially contains > multiple rows, so making all your table names plural doesn't > add any information. > I prefer to say "Trails" for the table, and "Trail" would then refer to a single row from that table. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
MRAB wrote: Another thing you might want to avoid is naming something with what it is, e.g. "Trails_Table" (why not just "Trails"). Or possibly just "Trail", since any table potentially contains multiple rows, so making all your table names plural doesn't add any information. -- Greg -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On Wed, 14 Aug 2019, MRAB wrote: The actual code is different. htbl, ttbl, jtbl, etc. Too short? Definitely! :-) Another thing you might want to avoid is naming something with what it is, e.g. "Trails_Table" (why not just "Trails"). Another reason to read Celko's 'SQL Programming Style.' In the 1970s I was writing FORTRAN ecosystem models punched on 80-column Hollerith cards using an IBM 029 keypunch. Everything was as short as possible, especially variable names. There were two valuable lessons I learned (in addition to marking a Sharpie pen line diagonally across the card deck to make it easier to put back in sequence when the box was dropped): 1. Make variable names fully descriptive. 2. Write many comments to explain what things are and why you're doing things the way you are. The reason is that within 6 months (or less) you will look at your schema and code and have no idea what's going on or why you wrote it like that. Table names are commonly written with initial uppercase letters; variables in all lowercase. Leave CamelCase in the desert. Use underscores between words, e.g., hike_id, trail_nbr, not hyphens. And, you might not be the only person looking at the schema and code needing to understand what's going on. Rich -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On 2019-08-13 22:11, Dave via Python-list wrote: On 8/13/19 4:45 PM, MRAB wrote: On 2019-08-13 19:59, Chris Angelico wrote: On Wed, Aug 14, 2019 at 4:50 AM Dave via Python-list 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 [snip] Might I also suggest dropping unnecessary prefixes from the field names. For example, "hike_comments" in "Hiking_Table" can be called just "comments" because it's clear from the context that a field called "comments" in the hiking table will contain comments about hiking, if you see what I mean. I do indeed. I did that so it was easy for everyone to follow. Having started with assm. and C, I have to remind myself to be more explanatory in naming. Guess I over-did it. The actual code is different. htbl, ttbl, jtbl, etc. Too short? Definitely! :-) Another thing you might want to avoid is naming something with what it is, e.g. "Trails_Table" (why not just "Trails"). -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On 8/13/19 5:46 PM, Rich Shepard wrote: On Tue, 13 Aug 2019, Rich Shepard wrote: Read Joe Celko's books, starting with his SQL Programming Guide, then SQL That should be SQL Programming Style Rich Rich, On my next trip to BN I'll see if they have them. That is long term though. Right now I just need to know how to populate the join table and anything else that has escaped me. SQL is cool. SQL + Python (or C or C++ or Java) is more cool. Lot easier to understand than pointer math in C. Dave, -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On Tue, 13 Aug 2019, Rich Shepard wrote: Read Joe Celko's books, starting with his SQL Programming Guide, then SQL That should be SQL Programming Style Rich -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On Wed, Aug 14, 2019 at 7:26 AM Dave via Python-list wrote: > 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 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. """ > Gotcha! Some database engines (including PostgreSQL) allow you to add a RETURNING clause to your INSERT statement, which will then turn it into a combined "insert, then select from the newly-inserted rows". SQLite3 does not have this, but there is a special attribute on the cursor (which you haven't shown in the cut-down example here, but I presume you know what I'm talking about) to retrieve the ID of the newly-inserted row: https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.lastrowid So after inserting into the hike table, you can grab the ID of that row off the cursor, and then same after the trail. Then you just insert into the join table using those IDs. A proper RETURNING clause is far more flexible (it can handle multiple rows, it can be chained into other queries, etc), but this should be sufficient for what you're doing here. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On Tue, 13 Aug 2019, Dave via Python-list wrote: I do indeed. I did that so it was easy for everyone to follow. Having started with assm. and C, I have to remind myself to be more explanatory in naming. Guess I over-did it. The actual code is different. htbl, ttbl, jtbl, etc. Too short? Dave, I encourage you to step back and approach your project from a different side. Databases, especially relational ones using SQL, are a very different world from Assembly, C, Python, and other procedural/functional/whatever languages. Read Joe Celko's books, starting with his SQL Programming Guide, then SQL for Smarties. A book on relational database design (other than these) would help. One I've used is Van der Laans' 'Introductory SQL'. SQL is a set language and thinking in sets is different from thinking in step-wise procedures. With SQL to tell the engine what you want, not how to do it. The engine decides the optimal way of getting the results you want from the tables. There are three components of SQL; most of us use only two of them: DDL (Data Definition Language) to define tables and relationships and DML (Data Manipulation Language) which we use to write queries. When you get your head around all this consider using Python and SQLAlchemy with SQLite3, postgresql, or whatever you want for the database back end. Good luck! Rich -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On 8/13/19 2:59 PM, Chris Angelico wrote: On Wed, Aug 14, 2019 at 4:50 AM Dave via Python-list wrote: Some of the tables are related. For example: Hiking_Table Trails_TableJoining_Table -- hike_id PK trail_id PKhike_id FK hike_date TEXT trail_name TEXTtrail_id FK hike_destination TEXTtrail_rating REAL hike_rating REALtrail_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
Re: Create multiple sqlite tables, many-to-many design
On 8/13/19 4:45 PM, MRAB wrote: On 2019-08-13 19:59, Chris Angelico wrote: On Wed, Aug 14, 2019 at 4:50 AM Dave via Python-list 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. Might I also suggest dropping unnecessary prefixes from the field names. For example, "hike_comments" in "Hiking_Table" can be called just "comments" because it's clear from the context that a field called "comments" in the hiking table will contain comments about hiking, if you see what I mean. I do indeed. I did that so it was easy for everyone to follow. Having started with assm. and C, I have to remind myself to be more explanatory in naming. Guess I over-did it. The actual code is different. htbl, ttbl, jtbl, etc. Too short? Dave, -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On 2019-08-13 19:59, Chris Angelico wrote: On Wed, Aug 14, 2019 at 4:50 AM Dave via Python-list wrote: Some of the tables are related. For example: Hiking_Table Trails_TableJoining_Table -- hike_id PK trail_id PKhike_id FK hike_date TEXT trail_name TEXTtrail_id FK hike_destination TEXTtrail_rating REAL hike_rating REALtrail_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. Might I also suggest dropping unnecessary prefixes from the field names. For example, "hike_comments" in "Hiking_Table" can be called just "comments" because it's clear from the context that a field called "comments" in the hiking table will contain comments about hiking, if you see what I mean. -- https://mail.python.org/mailman/listinfo/python-list
Re: Create multiple sqlite tables, many-to-many design
On Wed, Aug 14, 2019 at 4:50 AM Dave via Python-list wrote: > Some of the tables are related. For example: > > Hiking_Table Trails_TableJoining_Table > -- > hike_id PK trail_id PKhike_id FK > hike_date TEXT trail_name TEXTtrail_id FK > hike_destination TEXTtrail_rating REAL > hike_rating REALtrail_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 -- https://mail.python.org/mailman/listinfo/python-list
