[Tutor] sqlite: don't understand a code snippet

[Rich Lovely]

2009/7/25 David <ldl08 at gmx.net>:
> Dear tutors,
>
> I am trying to teach myself the way Python's works with databases.
> I decided to start with SQLite, and am looking at Summerfield's
> 'Programming in Python 3'.
> I got a code snippet that I don't fully understand (the comments are mine):
>
>
> def get_and_set_director(db, director):
>    # try to fetch existing director ID from db
>    director_id = get_director_id(db, director)
>    # if director ID was found in db, return ID
>    if director_id is not None:
>    return director_id
> cursor = db.cursor()
> # insert new director record
> cursor.execute("INSERT INTO directors (name) VALUES (?)",
>               (director,))
> db.commit()
> # retrieve and return new director ID from db
> return get_director_id(db, director)
>
> Here is what I think is going on:
>
> The function get_and_set_director() gets the director ID from the db
> by calling the function get_director-id() and returns its value.
>
> If the director ID is not in the db then, from outside the
> get_and_set_director() function, the ID gets inserted to the db via
> the commit() method. Finally, the director ID is returned (from the db)
> by once again calling the get_director_id() function.
>
> Question: where does a new the director ID come from?
>
> Thanks for your directions!
>
> David
>
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> http://mail.python.org/mailman/listinfo/tutor
>

It sounds as if the directors table has an AUTO_INCREMENT column,
which will automatically assign itself the next value sequentially
when you insert an entry into the table.  i.e. The first inserted
entry gets an ID of 1, the next 2, and so on.  This is stored in a
column in the table.  The get_director_id(...) function will do
something like the following query:

cursor.execute("SELECT id FROM directors WHERE name == \"%s\"", (name,))

I don't know how well I've explained it, but this is the normal
technique for generating unique ids for rows in a database.

-- 
Rich "Roadie Rich" Lovely
There are 10 types of people in the world: those who know binary,
those who do not, and those who are off by one.