Create multiple sqlite tables, many-to-many design

[Chris Angelico]

On Wed, Aug 14, 2019 at 7:26 AM Dave via Python-list
<python-list at python.org> 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