any complete and actual pysqlite example?

[Philip Semanchuk]

On Apr 18, 2009, at 8:59 AM, oyster wrote:

> I mean not a code to do seperated functions, but a real project
>
> I have read the doc of pysqlite, but since I know little about
> database, I found it is still very hard for me to cook a real database
> app from the scratch with the help of codelet in pysqlite doc. here is
> the simplified details:
> [TABLE 1]
> bookName text
> price num
> author <-- this is actually linked with the [TABLE 2], for one author
> maybe write many books, I think it is better to do an external link
> [/TABLE 1]
>
> [TABLE 2]
> authorName text
> [/TABLE 2]
>
> Then
> [record  for TABLE 1]
> id  bookName                          price authoridx
> 1   The Definitive Guide to SQLite      30      1
> 2   Core Python                         40      2
> 3   Dive Into Python                    35      3
> [/record  for TABLE 1]
>
> [record  for TABLE 2]
> id  authorName
> 1   Michael Owens
> 2   Wesley J. Chun
> 3   Mark Pilgrim
> [/record  for TABLE 2]
>
> what I need
> 1. of cause, create the TABLE, and add real data into it. I don't know
> how to deal with the author item in [TABLE 1]
>
> 2. list the data. A question is, when I list the record in [TABLE 1],
> can I get something like following?
> "The Definitive Guide to SQLite", 30.0, "Michael Owens"
> I mean, the authorName is returned automatically, but not only the  
> authoridx
>
> 3. what if I del the 1st record in [TABLE 2] since a record in  
> [TABLE 1] use it?
>
> 4.I want to do some group, for example, group the record in [TABLE 1]
> according to their price, so that
> [30]
>   Book1Name
>   Book2Name
> [40]
>   Book3Name
> [50]
>   Book4Name
>   Book4Name
> does sqlite support this? or I have to do this in python?

Hi Oyster,
It sounds like you're trying to learn several things at once. You  
might find it easier to separate them so you can learn one thing at a  
time.

Your questions are mostly about SQL and relational databases, not  
Python or Python's interface to SQLite. Once you have a better  
understanding of SQL and SQLite, the Python interface will make more  
sense to you.

In order to learn SQLite, I suggest that you install it and play with  
it on its own. There are probably precompiled binaries for your  
platform:
http://www.sqlite.org/download.html

SQLite comes with a command line client similar to Python's  
interactive shell, so you can enter database commands interactively  
and see their output, like so:

$ sqlite3
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> CREATE TABLE books
    ...>     (
    ...>         id              INTEGER NOT NULL PRIMARY KEY  
AUTOINCREMENT,
    ...>         name            TEXT,
    ...>         price           REAL,
    ...>         author_id       INTEGER
    ...>     )
    ...> ;
sqlite> CREATE TABLE authors
    ...>     (
    ...>         id              INTEGER NOT NULL PRIMARY KEY  
AUTOINCREMENT,
    ...>         name            TEXT
    ...>     )
    ...> ;
sqlite>
sqlite> INSERT INTO
    ...>     authors (name)
    ...> VALUES
    ...>     ('Mrs. Premise');
sqlite>
sqlite> INSERT INTO
    ...>     authors (name)
    ...> VALUES
    ...>     ('Mrs. Conclusion');
sqlite>
sqlite> SELECT
    ...>     *
    ...> FROM
    ...>     authors;
id          name
----------  ------------
1           Mrs. Premise
2           Mrs. Conclus
sqlite>

To answer your specific questions:
> what I need
> 1. of cause, create the TABLE, and add real data into it. I don't know
> how to deal with the author item in [TABLE 1]

There's sample create table statements above. The author_id in the  
books table in my example is called a "foreign key". You should read  
more about them.


> 2. list the data. A question is, when I list the record in [TABLE 1],
> can I get something like following?
> "The Definitive Guide to SQLite", 30.0, "Michael Owens"
> I mean, the authorName is returned automatically, but not only the  
> authoridx

Yes, using something called a JOIN:

sqlite> INSERT INTO
    ...>     books (name, price, author_id)
    ...> VALUES
    ...>     ('The Big Book of Nonsense', 30.00, 1);
sqlite> INSERT INTO
    ...>     books (name, price, author_id)
    ...> VALUES
    ...>     ('Jean Paul Satre Goes to the Chemist', 25.00, 2);
sqlite>
sqlite> SELECT
    ...>     books.name, books.price, authors.name
    ...> FROM
    ...>     books, authors
    ...> WHERE
    ...>     books.author_id = authors.id;
name                      price       name
------------------------  ----------  ------------
The Big Book of Nonsense  30.0        Mrs. Premise
Jean Paul Satre Goes to   25.0        Mrs. Conclus
sqlite>

> 3. what if I del the 1st record in [TABLE 2] since a record in  
> [TABLE 1] use it?

That's a problem! =) In a relational database you want to preserve  
something called "referential integrity". That means that if one table  
refers to records in another table, you want to make sure that the  
thing being referred to doesn't disappear. The SQL standard helps you  
to defend against this problem with something called a "foreign key  
constraint". When you applied correctly, the database won't let you  
delete a record if something is referring to it.

Unfortunately, this is one of the few SQL features that SQLite doesn't  
support:
http://www.sqlite.org/omitted.html

You'll have to enforce referential integrity through your application.  
(The SQLite doc points out that "the equivalent constraint enforcement  
can be achieved using triggers" but that's pretty advanced for someone  
who is just starting out with databases.)


> 4.I want to do some group, for example, group the record in [TABLE 1]
> according to their price

sqlite> INSERT INTO
    ...>     books (name, price, author_id)
    ...> VALUES
    ...>     ('Jean Paul Satre and What the Python Saw', 30.00, 2);
sqlite>
sqlite> SELECT
    ...>     name, price
    ...> FROM
    ...>     books
    ...> ORDER BY
    ...>     price;
name                                 price
-----------------------------------  ----------
Jean Paul Satre Goes to the Chemist  25.0
The Big Book of Nonsense             30.0
Jean Paul Satre and What the Python  30.0



I hope this helps. SQLite has some limitations, but in general it's a  
wonderful little database, especially for experimentation like you're  
doing. Remember, if things get really screwed up you can always delete  
the database file and start over! =)

Have fun and good luck
Philip