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
--
http://mail.python.org/mailman/listinfo/python-list
