git.maemo.org Git - meabook/blob - database/SQLite.py

   1 import os
   2 import sqlite3
   3
   4 DATABASE_NAME = 'contacts.db'
   5
   6
   7 class SQLite:
   8     def __init__(self, basedir):
   9         self._path = os.path.join(basedir, DATABASE_NAME)
  10         self.conn = None
  11         if not os.path.exists(self._path):
  12             self.new()
  13         else:
  14             self.conn = sqlite3.connect(self._path)
  15
  16     def new(self):
  17         """Creates new databse."""
  18
  19         self.conn = sqlite3.connect(self._path)
  20         self.conn.execute("""CREATE TABLE data (user_id int, field_id int, \
  21             value str)""")
  22         self.conn.execute("""CREATE TABLE field (id int primary key, name str)""")
  23         self.conn.execute("""CREATE TABLE relation (data_id int, \
  24             struct_id int)""")
  25         self.conn.execute("""CREATE TABLE struct (id int, name str, \
  26             parent_id int)""")
  27         self.conn.commit()
  28
  29     def close(self):
  30         """Closes connection with database."""
  31
  32         self.conn.commit()
  33         self.conn.close()
  34
  35     def update_field(self, fname):
  36         """Adds new field to database."""
  37
  38         fields = self.conn.execute("""SELECT name from field""").fetchall()
  39         if not fname in fields:
  40             self.conn.execute("""INSERT INTO field values((SELECT max(id)
  41             FROM field)+1, ?)""", (fname,))
  42
  43     def get_field_id_by_name(self, fname):
  44         """Returns field id by it name."""
  45
  46         return self.conn.execute("""SELECT id FROM field WHERE name=?""", \
  47             (fname,)).fetchone()[0]
  48
  49     def get_field_name_by_id(self, _id):
  50         """Returns field id by it name."""
  51
  52         return self.conn.execute("""SELECT name FROM field WHERE id=?""", \
  53             (_id,)).fetchone()[0]
  54
  55     def get_entry_by_id(self, _id):
  56         """Returns full entry by it id."""
  57
  58         sql_res = self.conn.execute("""SELECT id_field, value FROM data
  59             WHERE id=?""", (_id,)).fetchall()
  60         return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
  61             value in sql_res])
  62
  63     def get_all_entries(self):
  64         """Returns list of all entries."""
  65
  66         ids = set([item[0] for item in self.conn.execute( \
  67             """SELECT id FROM data""").fetchall()])
  68         return [self.get_entry_by_id(_id) for _id in ids]
  69
  70
  71
  72
  73 if __name__ == "__main__":
  74     db = SQLite('/home/plastun/meabook')
  75     #db.update_field('test')
  76     #print db.get_field_id_by_name('fax')
  77     #print db.get_entry_by_id(1)
  78     print db.get_all_entries()
  79     db.close()
  80
  81