Commits

Panagiotis Mavrogiorgos committed c60551f

Initial commit.

  • Participants

Comments (0)

Files changed (2)

sqlite_wrapper.py

+#! /usr/bin/env python
+# -*- coding: utf-8 -*-
+# module : sqlite_wrapper.py
+##------------------------------------------------------------------------------
+
+##------------------------------------------------------------------------------
+# Author : P. Mavrogiorgos (pmav99@gmail.com)
+# Licence :BSD
+##------------------------------------------------------------------------------
+
+"""
+sqlite_wrapper.py Module
+
+This class is a wrapper around the pySQLite library. It makes interaction with
+SQLite more pythonic.
+"""
+
+##------------------------------------------------------------------------------
+# Importing necessary libraries
+##------------------------------------------------------------------------------
+
+class SQLite3_DataBase(object):
+    def __init__(self, dbname):
+        """
+        It is a (py)SQLite wrapper. It simplifies the process of creating an
+        SQLite DataBase.
+        
+        Works with python 2 only. In order to work with python 3 the unicode
+        prefixes must be removed.
+        """
+        self.dbname = dbname
+        self.connect()
+        self.toggle_foreign_keys_on()
+        self.get_table_names()
+        
+    def table_was_found(self, t_name):
+        """
+        Checks if a table named t_name, has been created in the DataBase.
+        
+        If it does it returns True.
+        If it doesn't then it returns False and prints a warning message.
+        """
+        if t_name in self.table_names:
+            return True
+        else:
+            print("""No table named "{0}" was found""".format(t_name))
+            return False
+
+    def toggle_foreign_keys_on(self):
+        """
+        Sets the FOREIGN_KEY variable equal to 1.
+        """
+        self.execute("pragma foreign_keys = 1")
+
+    def toggle_foreign_keys_off(self):
+        """
+        Sets the FOREIGN_KEY variable equal to 0.
+        """
+        self.execute("pragma foreign_keys = 0")
+
+    def connect(self):
+        """
+        Tries to import sqlite3. If it isn't found a warning message is printed.
+
+        If it is found then it connects to a DataBase named dbname. If the 
+        DataBase doesn't exist then it is created. 
+        
+        Afterwards a cursor object is created.
+        """
+        try:
+            import sqlite3
+        except ImportError:
+            print("sqlite3 library not found.")
+        else:
+            self.conn = sqlite3.connect(self.dbname)
+            self.curs = self.conn.cursor()
+
+    def get_table_names(self):
+        """
+        Reads the database's tables and stores their names at table_names list.
+
+        The code is taken from sqlite source.
+        """
+        self.table_names = []
+        self.execute("""
+            SELECT name FROM sqlite_master
+            WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
+            UNION ALL
+            SELECT name FROM sqlite_temp_master
+            WHERE type IN ('table','view')
+            ORDER BY 1
+            """)
+        temp = self.fetchall()
+        for item in temp:
+            self.table_names.append(str(item[0]))
+
+    def get_column_names(self, t_name):
+        """
+        Returns a list containing the names of the columns of table t_name.
+        """
+        if self.table_was_found(t_name):
+            # Fetch the column info of the table
+            self.execute("pragma table_info({0})".format(t_name))
+            temp = self.fetchall()
+            
+            # Gets the columns names.
+            column_names = []
+            for item in temp:
+                column_names.append(str(item[1]))
+                
+            return column_names
+
+    def create_table(self, t_name, columns):
+        """
+        Creates a table named "t_name" with columns specified at "columns".
+        
+        If the table already exists, then a warning message is printed.
+        """
+        # Check if the table already exists.
+        if t_name in self.table_names:
+            print("A table with the name '{0}' already exists.".format(t_name))
+        else:
+            # A table cannot be created if no columns are defined.
+            # So we check if the columns dictionary has a length equal to 0.
+            # If it does then a warning message is printed.
+            n_args = len(columns)
+
+            if n_args == 0:
+                # Print warning message.
+                msg = "A table must contain some columns."
+                print(msg)
+            else:
+                # Construct the argument for the cursor execute method
+                s = ""
+                for item in columns:
+                    s += item
+
+                    # Adding a "," after each column definition.
+                    # If it is the last definition then nothing is added.
+                    if item != columns[-1]:
+                        s += ", "
+
+                s = """create table {0}({1});""".format(t_name, s)
+
+                # Actually create the table
+                self.execute(s)
+
+                # add t_name to t
+                self.table_names.append(t_name)
+
+    def add_column(self, t_name, n_column, t_column):
+        """
+        Add a column to table t_name.
+        
+        Parameters
+        ----------
+        
+        t_name : str
+            The name of the table.
+            
+        args : str
+            The SQL statement. The first word must be the name of the column.
+
+        """
+        # Check if the table exists.
+        if self.table_was_found(t_name):            
+            column_names = self.get_column_names(t_name)
+
+            # If the column doesn't exist, add the column at the table.
+            if n_column in column_names:
+                msg = "Table '{0}' already has a column named '{1}'.".format(t_name, n_column)
+                print(msg)
+            else:
+                self.execute("ALTER TABLE {0} ADD {1} {2}".format(t_name, n_column, t_column))
+
+    def insert_data(self, t_name, data):
+        """
+        Inserts data at table t_name.
+        
+        Doesn't check for unique values etc.
+        """
+        # Check if the table exists.        
+        if self.table_was_found(t_name):
+            length = len(data)
+            column_names = self.get_column_names(t_name)
+            
+            if length != len(column_names):
+                print("Can't insert data, wrong number of arguments.")
+            else:
+                data = tuple(data)
+    
+                s = ""
+                for i in range(length - 1):
+                    s += "?,"
+                    if i == length - 2:
+                        s += "?"
+                msg = "insert into {0} values ({1})".format(t_name, s)
+                self.curs.execute(msg, data)
+
+    def fetchall(self):
+        """Wrapper of the cursor's fetchall method."""
+        return self.curs.fetchall()
+
+    def fetchone(self):
+        """Wrapper of the cursor's fetchone method."""
+        return self.curs.fetchone()
+
+    def execute(self, argument):
+        """Wrapper of the cursor's execute method."""
+        self.curs.execute(argument)
+
+    def close(self):
+        """Wrapper of the connection's close method."""
+        self.conn.close()
+
+    def commit(self):
+        """Wrapper of the connection's commit method."""
+        self.conn.commit()
+
+    def rollback(self):
+        """Wrapper of the connection's rollback method."""
+        self.conn.rollback()
+
+    def select_all(self, t_name):
+        """Selects everything from a table named t_name."""
+        if t_name in self.table_names:
+            self.execute("select * from {0}".format(t_name))
+        
+    def select_some(self, t_name, *args):
+        if t_name in self.table_names:
+
+            column_names = self.get_column_names(t_name)
+
+            if args in column_names:
+                s = ""
+                for arg in args:
+                    s += arg
+                self.execute("select {1} from {0}".format(t_name))
+#! /usr/bin/env python
+# -*- coding: utf-8 -*-
+# module : test.py
+##------------------------------------------------------------------------------
+
+##------------------------------------------------------------------------------
+# Author : P. Mavrogiorgos (pmav99@gmail.com)
+# Licence :BSD
+##------------------------------------------------------------------------------
+
+"""
+test.py Module
+
+Just some code to test the SQLite3_DataBAse class
+"""
+
+##------------------------------------------------------------------------------
+# Importing necessary libraries
+##------------------------------------------------------------------------------
+from sqlite_wrapper import SQLite3_DataBAse
+
+def main():
+    
+    # Create DataBase
+    db = SQLite3_DataBAse("apodeixeis.db")
+    
+    # Columns of the first table
+    columns_1 = [u"Id integer primary key autoincrement",
+                 u"Επωνυμία varchar",
+                 u"ΑΦΜ varchar unique",
+                 u"ΔΟΥ varchar",
+                 u"Διεύθυνση varchar"]
+    
+    # Columns of the second table
+    columns_2 = [u"Id integer primary key autoincrement",
+                 u"Επιχείρηση_id integer",
+                 u"Ποσό real",
+                 u"Ημερομηνία varchar",
+                 u"FOREIGN KEY(Επιχείρηση_id) References Επιχειρήσεις(id)"]
+    
+    # Create tables                
+    db.create_table(u"Επιχειρήσεις", columns_1)
+    db.create_table(u"Αποδείξεις", columns_2)
+
+    # Add a column at table1
+    db.add_column(u"Επιχειρήσεις", u"Τηλέφωνο", u"integer")
+
+    # insert data at table1.
+    db.insert_data(u"Επιχειρήσεις", (None, u"Ο Κώστας",  u"000000000", u"Α ΔΟΥ Αθηνών", u"Οδός 123",       2100000000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Ο Μήτσος",  u"100000000", u"Γ ΔΟΥ Αθηνών", u"Πάροδος 456",   2110000000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Ο Μανώλης", u"200000000", u"Α ΔΟΥ Αθηνών", u"Λεωφόρος 789",  2120000000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Η Μαρία",   u"300300000", u"Δ ΔΟΥ Αθηνών", u"Οδός 234",       2130000000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Η Μαριώ",   u"000400000", u"Α ΔΟΥ Αθηνών", u"Πάροδος 345",   2100300000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Η Λενιώ",   u"100060000", u"Γ ΔΟΥ Αθηνών", u"Λεωφόρος 567",  2110400000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Η Άρτα",    u"200000700", u"Α ΔΟΥ Αθηνών", u"Οδός 678",       2120000005))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Τρικ",      u"300000070", u"Β ΔΟΥ Αθηνών", u"Πάροδος 890",    2130000500))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Άνω Κάτω", u"005000000", u"Α ΔΟΥ Αθηνών", u"Οδός 123",        2100006000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Μολύβι",   u"100030000", u"Β ΔΟΥ Αθηνών", u"Λεωφόρος 456",    2117000000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Στυλό",    u"200006000", u"Α ΔΟΥ Αθηνών", u"Πάροδος 789",     2120080000))
+    db.insert_data(u"Επιχειρήσεις", (None, u"Μπουκάλι", u"307000000", u"Β ΔΟΥ Αθηνών", u"Λεωφόρος 234",   2130200000))
+
+    #db.select_all("Companies")
+    #db.fetchall()
+    db.commit()
+    #db.close()
+    return db
+
+if __name__ == "__main__":
+    db = main()