vcs.maemo.org Git - meabook/blob - database/db.py

   1 #!/usr/bin/python -tt
   2 # -*- coding: utf-8 -*-
   3 # vim: sw=4 ts=4 expandtab ai
   4
   5 """Module for work with database"""
   6
   7 import os
   8 import sqlite3
   9
  10 class DbSqlite:
  11
  12     """class for work with database"""
  13
  14     def __init__(self):
  15         self.conn = self.connect()
  16
  17     def connect(self):
  18
  19         """connect to database"""
  20         dbname = "meabookdb"
  21         if os.access(dbname, os.F_OK|os.R_OK|os.W_OK):
  22             conn = sqlite3.connect(dbname)
  23         else:
  24             print "access to database file error"
  25             conn = None
  26         return conn
  27
  28     def get_record(self,  id):
  29
  30         """return record field"""
  31         cur = self.conn.cursor()
  32         cur.execute("select field.name, data.value "
  33                     "from data left join field "
  34                     "on data.id_field=field.id "
  35                     "where data.id=?", (id,))
  36         self.data = {}
  37         for row in cur:
  38             print "key = %s value = %s" % (row[0], row[1])
  39             self.data[row[0]] = row[1]
  40         return self.data
  41
  42     def get_allrecords(self, field):
  43
  44         """return records"""
  45         cur = self.conn.cursor()
  46         cur.execute("select data.id, data.value "
  47                     "from data left join field "
  48                     "on data.id_field=field.id "
  49                     "where field.name=?", (field,))
  50         self.data = {}
  51         for row in cur:
  52             #print row
  53             self.data[row[0]] = row[1]
  54         return self.data
  55
  56     def ins_record(self, rec):
  57
  58         """insert record into database"""
  59         #get id new record
  60         #conn = self.connect()
  61         if self.conn is None:
  62             print "database connect error"
  63             return None
  64         cur = self.conn.cursor()
  65         cur.execute("select max(id) from data")
  66         id_record = cur.fetchone()[0]
  67         if id_record is None:
  68             id_record = 1
  69         else:
  70             id_record = id_record + 1
  71         print "id_record=%s" % (id_record,)
  72
  73         for key, val in rec.items():
  74             print "key=%s value=%s" % (key, val)
  75             cur.execute("select id from field where name=?", (key,))
  76
  77             id_field = cur.fetchone()
  78             if id_field is None:
  79                 #write new field ваы
  80                 cur.execute("insert into field (name) values (?)", (key,))
  81                 id_field = cur.lastrowid
  82             else:
  83                 id_field = id_field[0]
  84             # insert row into data
  85             #print "id=%s id_field=%s value=%s" % (id_record, id_field, val)
  86             for i in val:
  87                 cur.execute("insert into data (id, id_field, value) values (?, ?, ?)",
  88                 (id_record, id_field, i))
  89         self.conn.commit()
  90         #conn.close()
  91
  92     def clear_data(self):
  93
  94         """clear all record from table data"""
  95         conn = self.connect()
  96         if conn is None:
  97             print "database connect error"
  98             return None
  99         cur = conn.cursor()
 100         cur.execute("delete from data")
 101         cur.execute("delete from field")
 102         conn.commit()
 103         conn.close()
 104
 105