[Edu-sig] Simple Python database application for self-study and training

[Kirby Urner]

At 07:32 AM 5/13/2003 -0700, you wrote:

>Is there anyone who knows about such a system, or would make such a system
>available?
>
>Thomas

Hi Thomas --

I don't know of such a setup to offer.  However, as one who is learning more
about using Python with both MySQL and PostgreSQL, as well as wxPython, I'd
welcome news of what you find out.

For me, the most useful thing is to write simple Python methods to create
tables and manage inserting, updating, deleting and selecting.  The wxPython
GUI would come later, after the basic functionality is in place.

I'm currently using PostgreSQL to manage a list of Python-related URLs,
categorized by key words (one or more).  Instead of wxPython, I'm currently
using Plone for a front end (making data management doable through a web
browser).

For wxPython development, you might want to check out PythonCard, which 
provides
a framework for wxPython applications, with a lot of pre-built functionality.

Kirby

FYI, here's a basic script in Python designed to create the guts of my
database of Python-related URLs.  This is for PostgreSQL, but I'm sure the
MySQL script would look very similar (MySQL uses AUTOINCREMENT in place
of sequences etc.).  PostgreSQL runs in cygwin under Windows, though a
native Windows version is in the pipeline.  Development in Linux would be
easier.

===================

import sys

from pg import DB

conn = DB('test')

# this is a "start from the beginning" type script, i.e. if any objects
# are already in the database, they're deleted and recreated from scratch
# (meaning we have to check first -- to see if they need to be DROPped).

mytables = conn.query("""
         SELECT relname FROM pg_stat_user_tables
""").getresult()

mytables = [item[0] for item in mytables]

myseqs = conn.query("""
         SELECT relname FROM pg_statio_user_sequences
""").getresult()

myseqs = [item[0] for item in myseqs]


if "edulinks_seq" in myseqs:
         conn.query("DROP SEQUENCE edulinks_seq")

conn.query("""
CREATE SEQUENCE edulinks_seq
""")

if "categories_seq" in myseqs:
         conn.query("DROP SEQUENCE categories_seq")

conn.query("""
CREATE SEQUENCE categories_seq
""")

if "edulinks" in mytables:
         conn.query("DROP TABLE edulinks CASCADE")

conn.query("""
CREATE TABLE edulinks (
         id              INTEGER DEFAULT nextval('edulinks_seq'),
         url             VARCHAR(95),
         descript        VARCHAR(101),
         notes           TEXT,
         status          INT2,
                         PRIMARY KEY(id)
)""")

if "categories" in mytables:
         conn.query("DROP TABLE categories CASCADE")

conn.query("""
CREATE TABLE categories (
         catid           INTEGER DEFAULT nextval('categories_seq') PRIMARY KEY,
         category        VARCHAR(30)
)""")

if "edusort" in mytables:
         conn.query("DROP TABLE edusort CASCADE")

conn.query("""
CREATE TABLE edusort (
         id              INTEGER REFERENCES edulinks
                                 ON UPDATE CASCADE
                                 ON DELETE CASCADE,
         catid           INTEGER REFERENCES categories
                                 ON UPDATE CASCADE
                                 ON DELETE CASCADE
)""")


# populate tables with data from external text files
conn.query("COPY edulinks FROM 
'/cygdrive/d/cygwin/home/urnerk/pgedulinks.txt' DELIMITERS '|'")
conn.query("COPY categories FROM 
'/cygdrive/d/cygwin/home/urnerk/pgcategories.txt' DELIMITERS '|'")
conn.query("COPY edusort FROM 
'/cygdrive/d/cygwin/home/urnerk/pgedusort.txt' DELIMITERS '|'")

# a liberal access policy - :-D
conn.query("GRANT ALL ON edulinks TO PUBLIC")
conn.query("GRANT ALL ON categories TO PUBLIC")
conn.query("GRANT ALL ON edusort TO PUBLIC")

conn.close()