Introduction of AmiDB

I had used SQLObject for Orangoo Feed Reader, Skeletonz and some internal projects. I had also used ZODB for the first version of Orangoo Feed Reader. My conclusion:

• Using only object-relational mapping sucks BADLY. Using object storage sucks even more than that.

They basically have a lot of limitations and those that bugged me most was expressiveness and lack of control over queries. ZODB performance was really awful.

To solve this I have coded AmiDB. I tried to create a SQL libary that has:

• The expressiveness and power of pure SQL
• The smartness and easiness of object-relational mapping

If you ask me then I have mostly succeeded... But maybe a lot of people don't have the same taste like me. I use this library in the newest version of Skeletonz and it works very fine (a lot better than SQLObject). Here comes a little tutorial showing the stuff that AmiDB has to offer.

Setting up a table

Set up following table:

CREATE TABLE person (
  id int(11) NOT NULL auto_increment,
  name varchar(255),
  email varchar(255) DEFAULT '',
  website varchar(255) default '',
  age int(11) default 0,
  PRIMARY KEY (id)
);

Upcoming project of mine will probably be managing the creation and updates of SQL databases. Right now you must use the old way ;)

Importing AmiDB and setting up a connection pool

AmiDB features own connection pool (yay). It's pretty trivial to set up:

import amidb
amidb.user = ""
amidb.password = ""
amidb.db = ""
amidb.host = ""

connection_pool = amidb.ConnectionPool()
db = connection_pool.getConnection()

Now you can use db to insert, query, update etc. the database.

After you are the done with the connection, it is cool to return back the connection to the pool:

connection_pool.releaseConnection(db)

Inserting Amir and Robocop

If you have a person table and can connect to your database, then you are ready to insert a couple of persons:

amir_id = db.insert("person", name="Amir Salihefendic", age=20)
robocop_id = db.insert("person", name="Alex J. Murphy", age=33)

Notice that db.insert is a function that can take arbitrary parameters... Most of the functions one uses in AmiDB can do this. Python ROCKS.

Selecting Amir

To select you can use db.select - which is also a function that can take arbitrary parameters:

amir = db.select("person", name="Amir Salihefendic", as_one=True)
print amir.name

Notice the way name is printed...

Selecting all persons and printing them out

The code speaks for itself:

all_persons = db.select("person")
for person in all_persons:
  print "%s is %i old" % (person.name, person.age)

Updating Amir's email

The code speaks for itself:

db.update("person", "name='Amir Salihefendic'", email="amix@amix.dk")
amir = db.select("person", id=amir_id, as_one=True)
print amir.email

Decorating a query

I really like this. One can enscauplate a query into arbitary objects...:

class Person:
  def getName(self):
    return "My name is %s" % self.name
amir = db.select("person", id=amir_id, obj_deco=Person, as_one=True)
print amir.getName()

This is a really nice feature!

Do a query

The code speaks for itself:

persons = db.query("SELECT * FROM person", True)
for p in persons:
  print "%s's email is '%s'" % (p.name, p.email)

Delete persons

The code speaks for itself:

db.delete("person", name="Amir Salihefendic")
db.delete("person", name="Alex J. Murphy")

Improvements or comments?

Always welcomed! I hope AmiDB gives you some new ideas...