SQL, lite lite lite

[Bruno Desthuilliers]

Aaron Brady a écrit :
> Hi all,
> 
(snip)
 >
> I don't think relational data can be read and written very easily in
> Python. 

Did you try SQLAlchemy or Django's ORM ?

> There are some options, such as 'sqllite3', but they are not
> easy.  'sqllite3' statements are valid SQL expressions, which afford
> the entire power of SQL, but contrary to its name, it is not that
> 'lite'.

sqlite is a Python-independant library providing a lightweight SQL 
embedded (ie : no server) database system. It is "light" wrt/ Oracle, 
Postgres etc.

>  To me, 'lite' is something you could learn (even make!) in an
> afternoon, not a semester;

No one in it's own mind would hope to learn the relational theory and 
algebra in an afternoon, whatever the implementation.

> something the size of an ActiveState
> recipe, or a little bigger, maybe a file or two.  If you think SQL is
> a breeze, you probably won't find my idea exciting.  I assume that the
> basics of SQL are creating tables, selecting records, and updating
> records.

There's much more than this.

> My idea is to create a 'Relation' class.  The details are basically
> open, such as whether to back it with 'sqllite3', 'shelve', 'mmap', or
> just mapping and sequence objects; what the simplest syntax is that
> can capture and permit all the basics, and how much and what else can
> fit in at that level; how and whether it can include arbitrary Python
> objects, and what constraints there are on them if not; how and
> whether to permit transactions; and what the simplest and coolest
> thing you can do with a little Python syntax is.
> 
> This is basically an invitation for everyone to brainstorm.  (No
> hijackings, good humor & digression ok.)  Lastly, ...
> 
> 
> #Just the select and update syntax:
> 
>>>> a= people._select( "firstname== 'Joe'" )
> #select 'key' from 'people' where 'firstname'== 'joe'
>>>> a
> [Entry2864, Entry3076, Entry3172]
>>>> entry1= a[ 0 ]
>>>> entry1.phone
> #select 'phone' from 'people' where 'key'==self.key
> "555-2413"
>>>> entry1.phone= "555-1234"
> #update 'people' set 'phone'= '555-1234' where 'key'==self.key
>>>> entry1.phone
> "555-1234"
> 
> #Create table syntax (a-whole-nother beast in itself):
> 
>>>> classes= db.Relation( 'class_', 'person', Unique( 'class_', 'person' ) )
> #create table 'classes' ( 'key', 'class_', 'person' ) unique
> ( 'class_', 'person' )
>>>> classes._unique( 'class_', 'person' )
>>>> classes.class_.noneok= False #'class_' cannot be null
>>>> classes.person.noneok= False
>>>> classes._insert( 'Physics', 'Dan' )
>>>> classes._insert( 'Chem', 'Tim' )

 From django's tutorial, part 1:

# polls/models.py
import datetime
from django.db import models

class Poll(models.Model):
     question = models.CharField(max_length=200)
     pub_date = models.DateTimeField('date published')

     def __unicode__(self):
         return self.question

     def was_published_today(self):
         return self.pub_date.date() == datetime.date.today()

class Choice(models.Model):
     poll = models.ForeignKey(Poll)
     choice = models.CharField(max_length=200)
     votes = models.IntegerField()

     def __unicode__(self):
         return self.choice

# in the interactive shell
 >>> from mysite.polls.models import Poll, Choice
 >>> Poll.objects.all()
[]

# Create a new Poll.
 >>> import datetime
 >>> p = Poll(question="What's up?", pub_date=datetime.datetime.now())

# Save the object into the database. You have to call save() explicitly.
 >>> p.save()

# Now it has an ID. Note that this might say "1L" instead of "1", depending
# on which database you're using. That's no biggie; it just means your
# database backend prefers to return integers as Python long integer
# objects.
 >>> p.id
1

# Access database columns via Python attributes.
 >>> p.question
"What's up?"
 >>> p.pub_date
datetime.datetime(2007, 7, 15, 12, 00, 53)

# Change values by changing the attributes, then calling save().
 >>> p.pub_date = datetime.datetime(2007, 4, 1, 0, 0)
 >>> p.save()

# objects.all() displays all the polls in the database.
 >>> Poll.objects.all()
[<Poll: What's up?>]
# Django provides a rich database lookup API that's entirely driven by
# keyword arguments.
 >>> Poll.objects.filter(id=1)
[<Poll: What's up?>]
 >>> Poll.objects.filter(question__startswith='What')
[<Poll: What's up?>]

# Get the poll whose year is 2007. Of course, if you're going through this
# tutorial in another year, change as appropriate.
 >>> Poll.objects.get(pub_date__year=2007)
<Poll: What's up?>

 >>> Poll.objects.get(id=2)
Traceback (most recent call last):
     ...
DoesNotExist: Poll matching query does not exist.

# Lookup by a primary key is the most common case, so Django provides a
# shortcut for primary-key exact lookups.
# The following is identical to Poll.objects.get(id=1).
 >>> Poll.objects.get(pk=1)
<Poll: What's up?>

# Make sure our custom method worked.
 >>> p = Poll.objects.get(pk=1)
 >>> p.was_published_today()
False

# Give the Poll a couple of Choices. The create call constructs a new
# choice object, does the INSERT statement, adds the choice to the set
# of available choices and returns the new Choice object.
 >>> p = Poll.objects.get(pk=1)
 >>> p.choice_set.create(choice='Not much', votes=0)
<Choice: Not much>
 >>> p.choice_set.create(choice='The sky', votes=0)
<Choice: The sky>
 >>> c = p.choice_set.create(choice='Just hacking again', votes=0)

# Choice objects have API access to their related Poll objects.
 >>> c.poll
<Poll: What's up?>

# And vice versa: Poll objects get access to Choice objects.
 >>> p.choice_set.all()
[<Choice: Not much>, <Choice: The sky>, <Choice: Just hacking again>]
 >>> p.choice_set.count()
3

# The API automatically follows relationships as far as you need.
# Use double underscores to separate relationships.
# This works as many levels deep as you want; there's no limit.
# Find all Choices for any poll whose pub_date is in 2007.
 >>> Choice.objects.filter(poll__pub_date__year=2007)
[<Choice: Not much>, <Choice: The sky>, <Choice: Just hacking again>]

# Let's delete one of the choices. Use delete() for that.
 >>> c = p.choice_set.filter(choice__startswith='Just hacking')
 >>> c.delete()


NB : works with sqlite, MySQL and Postgres, and supports transactions if 
the underlying SQL engine supports them.

HTH