SQL, lite lite lite

[Aaron Brady]

Hi all,

About a year ago, I posted an idea I was having about thread
synchronization to the newsgroup. However, I did not explain it well,
and I really erred on the side of brevity. (After some finagling, Mr.
Bieber and I decided it wasn't exactly anything groundbreaking.) But
I think the brevity cost me some readers, who might have had more
interest. The affair was on the whole discouraging. So, I'm going to
try another idea, and assume that readers have some time, and will
spend it on it.

I don't think relational data can be read and written very easily in
Python. 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'. To me, 'lite' is something you could learn (even make!) in an
afternoon, not a semester; 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.

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, ...

**warning, spoiler! here's what I thought of already.**

**repeat! spoiler! here's what I thought of already.**

#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""555-1234"

#Create table syntax (a-whole-nother beast in itself):
#create table 'classes' ( 'key', 'class_', 'person' ) unique
( 'class_', 'person' )
Hoping-"good critic"-is-self-consistent-ly, hoping-to-hear-it's-too-
complicated-already-ly,
A. Brady

 

[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""555-1234"

#Create table syntax (a-whole-nother beast in itself):
#create table 'classes' ( 'key', 'class_', 'person' ) unique
( 'class_', 'person' )

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[]

# Create a new Poll.
# Save the object into the database. You have to call save() explicitly.
# 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.1

# Access database columns via Python attributes.datetime.datetime(2007, 7, 15, 12, 00, 53)

# Change values by changing the attributes, then calling save().
# objects.all() displays all the polls in the database.[<Poll: What's up?>]
# Django provides a rich database lookup API that's entirely driven by
# keyword arguments.

>>> Poll.objects.filter(id=1)

>>> 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.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: What's up?>

# Make sure our custom method worked.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.
# Choice objects have API access to their related Poll objects.<Poll: What's up?>

# And vice versa: Poll objects get access to Choice objects.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.

>>> c = p.choice_set.filter(choice__startswith='Just hacking')
>>> c.delete()

[/QUOTE]


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

HTH

 

[Philip Semanchuk]

I don't think relational data can be read and written very easily in
Python. 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'. To me, 'lite' is something you could learn (even make!) in an
afternoon, not a semester; something the size of an ActiveState
recipe, or a little bigger, maybe a file or two.

Hi Aaron,
The "lite" part of SQLite refers to its implementation more than its
feature set. In other words, SQLite doesn't promise to make SQL
easier, it promises many of the features of a big, heavy relational
database (e.g. Postgres, MySQL, Oracle, etc.) but in a small, light
package. I can see why you'd be disappointed if you were expecting the
former. IMHO it does quite well at the latter.

After a look at the syntax you're proposing, I wonder how you feel it
differs from ORMs like SQLAlchemy (for instance).


Cheers
Philip

 

[Ned Deily]

I don't think relational data can be read and written very easily in
Python. 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'. To me, 'lite' is something you could learn (even make!) in an
afternoon, not a semester; something the size of an ActiveState
recipe, or a little bigger, maybe a file or two.

[...]
After a look at the syntax you're proposing, I wonder how you feel it
differs from ORMs like SQLAlchemy (for instance).

.... and Elixir, a declarative layer on top of SQLAlchemy:

<http://elixir.ematia.de/trac/wiki/TutorialDivingIn>

 

[Pierre Quentel]

Hi all,

About a year ago, I posted an idea I was having about thread
synchronization to the newsgroup.  However, I did not explain it well,
and I really erred on the side of brevity.  (After some finagling, Mr.
Bieber and I decided it wasn't exactly anything groundbreaking.)  But
I think the brevity cost me some readers, who might have had more
interest.  The affair was on the whole discouraging.  So, I'm going to
try another idea, and assume that readers have some time, and will
spend it on it.

I don't think relational data can be read and written very easily in
Python.  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'.  To me, 'lite' is something you could learn (even make!) in an
afternoon, not a semester; 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.

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, ...

**warning, spoiler!  here's what I thought of already.**

**repeat!  spoiler!  here's what I thought of already.**

#Just the select and update syntax:

#select 'key' from 'people' where 'firstname'== 'joe'>>> a

[Entry2864, Entry3076, Entry3172]>>> entry1= a[ 0 ]
#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):

#create table 'classes' ( 'key', 'class_', 'person' ) unique
( 'class_', 'person' )

Hoping-"good critic"-is-self-consistent-ly, hoping-to-hear-it's-too-
complicated-already-ly,
A. Brady

Hi,

PyDbLite (http://pydblite.sourceforge.net/) is not far from what you
describe. The basic version stores data in cPickle format, and there
are interfaces to use the same Pythonic syntax with SQLite and MySQL
backends

Regards,
Pierre

 

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Python. 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'.

Have you compared the compiled size of SQLite against other things? For
example on my machine the size of MySQL client library, whose sole
purpose is to transport queries and results across the network is the
same size as the entirety of SQLite! You can prune SQLite back even
further as documented in http://www.sqlite.org/compile.html

It is even possible to omit the SQL front end. Queries are stored
already processed in the database. This functionality is used by mp3
manufacturers and similar constrained embedded environments.

To me, 'lite' is something you could learn (even make!) in an
afternoon,

If you just want to treat the database as a glorified spreadsheet then
SQL is "lite", although perhaps a little verbose of a dbm style interface.

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.

The basics of SQL are about expressing the relational model
http://en.wikipedia.org/wiki/Relational_model which has stood the test
of time. (That doesn't mean it is superior just that it is good enough
like the "qwerty" keyboard layout.) There have been attempts at
alternatives like http://en.wikipedia.org/wiki/The_Third_Manifesto but
that doesn't seem to have caught on.

It seems your basic complaint is the complexity of doing database stuff.
Ultimately this will be the case if some data is related to other bits
of data. As other posters have pointed out, there are various ORM type
wrappers for Python that try to wrap this up in syntactic sugar

For something completely different have a look at CouchDB
http://couchdb.apache.org/ which operates on "documents" (basically
something with an id and an arbitrary updateable list of properties).
It does take a bit to get your head wrapped around it - try this posting
for an example http://www.cmlenz.net/archives/2007/10/couchdb-joins

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAklZRp0ACgkQmOOfHg372QQ4RQCgzSmgEhvG2DQlWYb68U8BZNOo
wOAAnip4GIvKiskuwwWJwaepmJwHLjXJ
=0UiA
-----END PGP SIGNATURE-----

 

[Johannes Bauer]

This is basically an invitation for everyone to brainstorm. (No
hijackings, good humor & digression ok.) Lastly, ...

Even if it took (as you mentioned) a semester of SQL studies - which it
does not - why do you think your syntax is easier? The only person your
proposed syntax is easier for is you. Get over it, learn SQL, and enjoy
the benefits of one unified standard - not everyone cooking their own
soup. You'll be able to learn PostgreSQL, Oracle SQL, mySQL, SQlite all
at once!

Regards,
Johannes

 

[Bruno Desthuilliers]

Johannes Bauer a écrit :
(snip)

Even if it took (as you mentioned) a semester of SQL studies - which it
does not - why do you think your syntax is easier? The only person your
proposed syntax is easier for is you. Get over it, learn SQL, and enjoy
the benefits of one unified standard - not everyone cooking their own
soup. You'll be able to learn PostgreSQL, Oracle SQL, mySQL, SQlite all
at once!

Oh what a beautiful dream... We all wish we'd live in such a perfect
world. Sadly, it's not the case. First because each SQL vendor cooks its
own soup, so you still have to learn each SQL implementation,
limitations and gory details. Also, because handling SQL queries as raw
strings is more than painfull. It just doesn't play well with the host
language. So any non trivial project ends up reinventing its own
half-backed abstraction layer, that you have to learn too.

Now I don't mean that learning SQL (and the relational theory behind
SQL) is a waste of time. You obviously need a good understanding of SQL
to use a SQL database - directly or thru any abstraction layer. We
definitively agree on this. But a smart abstraction layer (like
SQLAlchemy, or even the less powerfull but still pretty good Django ORM)
at least avoids reinventing a new one for each project.

My 2 cents...

 

[pruebauno]

Hi all,

About a year ago, I posted an idea I was having about thread
synchronization to the newsgroup.  However, I did not explain it well,
and I really erred on the side of brevity.  (After some finagling, Mr.
Bieber and I decided it wasn't exactly anything groundbreaking.)  But
I think the brevity cost me some readers, who might have had more
interest.  The affair was on the whole discouraging.  So, I'm going to
try another idea, and assume that readers have some time, and will
spend it on it.

I don't think relational data can be read and written very easily in
Python.  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'.  To me, 'lite' is something you could learn (even make!) in an
afternoon, not a semester; 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.

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, ...

**warning, spoiler!  here's what I thought of already.**

**repeat!  spoiler!  here's what I thought of already.**

#Just the select and update syntax:

#select 'key' from 'people' where 'firstname'== 'joe'>>> a

[Entry2864, Entry3076, Entry3172]>>> entry1= a[ 0 ]
#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):

#create table 'classes' ( 'key', 'class_', 'person' ) unique
( 'class_', 'person' )

Hoping-"good critic"-is-self-consistent-ly, hoping-to-hear-it's-too-
complicated-already-ly,
A. Brady

You really do like to reinvent the wheels do you? Nothing wrong
with that. Just be aware that most people that really need what you
are proposing are probably already using mature feature rich libraries
for that.

http://wiki.python.org/moin/HigherLevelDatabaseProgramming

 

[Aaron Brady]

snip
You really do like to reinvent the wheels do you? Nothing wrong
with that. Just be aware that most people that really need what you
are proposing are probably already using mature feature rich libraries
for that.

http://wiki.python.org/moin/HigherLevelDatabaseProgramming

Look at these options! Who invents the wheel? Naturally, I've had
time to master every one.

The page on Dee reminded me that there was some relational algebra
early on in the databases course. I'd forgotten. Maybe I wouldn't
feel so sheepish if I'd tried starting there, to see exactly what I
need.

It's possible that if I had a real goal, it could drive the
requirements for my Relation class. I don't. What I have is very
vague, somewhat second-hand, and also inspired by a tangent in a
recent thread here on c-l-databases, er, -py.

You remember the days of parallel arrays before records were
invented. The bare minimum I can think of for a relation is just a
set of tuples.

TakingClass( Arthur, Science )
#TakingClass.add( ( Arthur, Science ) )

It's just that 'what classes is Arthur taking?' is an O( total ) op.
Next is a parallel mapping.

TakingClass( Arthur, Science )
# science.add( arthur )
# arthur.add( science )

O( key ) read and O( 1 ) add, but it's repetitious. I feel it's
really error prone, but maybe it's the best balance. The actual
abstraction is something more like this:

# Emps( Name, EmpID, Department )
Emps( KnightsOfNi, NiNiNi, Gardening )
# tupA= EmpsTuple( KnightsOfNi, NiNiNi, Gardening )
# KnightsOfNi.add( tupA )
# NiNiNi.add( tupA )
# Gardening.add( tupA )

(If KnightsOfNi will appear in more than one relation, it will need to
distinguish: 'KnightsOfNi.emps.add( tupA )'.)

A single predicate is just a set. A relation is just a set of tuples.

Dee lets you write lambda expressions for filters/selects. Django
lets you chain selects with a double underscore.

 

[Bruno Desthuilliers]

Gerhard Häring a écrit :

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 ?
[...]

Using an ORM when you don't grasp the relational model and/or the SQL
query language is futile.

Yes, indeed. And ? Aaron's post was mostly about a better integration of
the relational model in Python - which obviously requires some knowledge
of the topic.

 

[Bruno Desthuilliers]

Aaron Brady a écrit :

On Dec 30, 11:16 am, (e-mail address removed) wrote: (snip)

Look at these options! Who invents the wheel? Naturally, I've had
time to master every one.

Oh, so that's why you propose to add yet another item to the list ?

 

[Gerhard Häring]

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 ?
[...]

Using an ORM when you don't grasp the relational model and/or the SQL
query language is futile.

That's probably the case for many other abstraction layers, too.

-- Gerhard

 

[Aaron Brady]

Aaron Brady a écrit :



Oh, so that's why you propose to add yet another item to the list ?

Aha, so it is. Your criticism might seem harsh, but it has an
advantage. It made me get the lead out, that is, think.

Assuming I do want my Relation class to be backed by a full-fledged
database, all I would need is an adapter to a Dee or Django object.
The only restriction is, records have to be uniform types. (In many
cases, this can be good, since relations are statically typed.)

I recognize that on average, I'm only saving a little syntax. If I
have a Student object and I want the classes s/he is in, the SQL isn't
horrible.

SELECT objectrep FROM Classes WHERE Student IS studentA
//'IS' is not supported in SQL

It returns a list of Class instances. The Python is better.

studentA.getClasses()

To make it fully dynamic,

studentA.get( 'Classes' ) or
studentA.get( Classes ) or
studentA.cross( Classes )

would be closer to what I have in mind. If readers will permit a
brainstorm, maybe one of the options will spark an idea.

studentA and Classes
studentA.intersection( Classes )

However, I want a list of classes, not a list of students, so I am not
wanting a pure 'intersection' relation, that is, not a commutative
one.

Classes.get( studentA )
Classes and studentA #breaks commutativity of 'and'
Classes.intersection( studentA )

The method will have to determine what field 'studentA' is supposed to
match.

 

[Bruno Desthuilliers]

Aaron Brady a écrit :

Aha, so it is. Your criticism might seem harsh,

This was mostly a tentative to make you notice that it might be better
to check out the existing solutions and possibly contribute to one of
them instead of inventing yet another one...

but it has an
advantage. It made me get the lead out, that is, think.

(snip)

.... but it obviously failed.

 

[Aaron Brady]

Aaron Brady a écrit :





This was mostly a tentative to make you notice that it might be better
to check out the existing solutions and possibly contribute to one of
them instead of inventing yet another one...


(snip)

... but it obviously failed.

You have to admit that mine is simpler than a Django statement. If
they want to use it, that's fine with me, but I have to invent it
first. That's the reason I'm sharing on a newsgroup. Recap: An
ADAPTER from a Django class into something simpler and less functional.