sqlite3

sqlite3.py

def main():
    import sqlite3
    from sqlite3 import Error
 
 
    def create_connection(db_file):
        """ create a database connection to a SQLite database """
        try:
            conn = sqlite3.connect(db_file, isolation_level=None)
            #print(sqlite3.version)
            return conn
        except Error as e:
            print(e)
        return None

    def create_table(conn, create_table_sql):
        """ create a table from the create_table_sql statement
        :param conn: Connection object
        :param create_table_sql: a CREATE TABLE statement
        :return:
        """
        try:
            c = conn.cursor()
            c.execute(create_table_sql)
        except Error as e:
            print(e)

    def create_project(conn, project):
        """
        Create a new project into the projects table
        :param conn:
        :param project:
        :return: project id
        """
        
        sql = ''' INSERT INTO projects(name,begin_date,end_date)
                  VALUES(?,?,?) '''
        try:
            cur = conn.cursor()
            cur.execute(sql, project)
            return cur.lastrowid
        except Error as e:
            print(e)

    def create_task(conn, task):
        """
        Create a new task
        :param conn:
        :param task:
        :return:
        """
 
        sql = ''' INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)
                  VALUES(?,?,?,?,?,?) '''
        try:
            cur = conn.cursor()
            cur.execute(sql, task)
            return cur.lastrowid
        except Error as e:
            print(e)

    def update_task(conn, task):
        """
        update priority, begin_date, and end date of a task
        :param conn:
        :param task:
        :return: project id
        """
        sql = ''' UPDATE tasks
                  SET priority = ? ,
                      begin_date = ? ,
                      end_date = ?
                  WHERE id = ?'''
        try:
            cur = conn.cursor()
            cur.execute(sql, task)
            return cur.lastrowid
        except Error as e:
            print(e)

    def delete_task(conn, id):
        """
        Delete a task by task id
        :param conn:  Connection to the SQLite database
        :param id: id of the task
        :return:
        """
        sql = 'DELETE FROM tasks WHERE id=?'
        try:
            cur = conn.cursor()
            cur.execute(sql, (id,))
        except Error as e:
            print(e)

    def delete_all_tasks(conn):
        """
        Delete all rows in the tasks table
        :param conn: Connection to the SQLite database
        :return:
        """
        sql = 'DELETE FROM tasks'
        try:
            cur = conn.cursor()
            cur.execute(sql)
        except Error as e:
            print(e)

    def select_all_tasks(conn):
        """
        Query all rows in the tasks table
        :param conn: the Connection object
        :return:
        """
        try:
            cur = conn.cursor()
            cur.execute("SELECT * FROM tasks")
 
            rows = cur.fetchall()
 
            for row in rows:
                print(row)
        except Error as e:
            print(e)

    def select_task_by_priority(conn, priority):
        """
        Query tasks by priority
        :param conn: the Connection object
        :param priority:
        :return:
        """
        try:
            cur = conn.cursor()
            cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))
 
            rows = cur.fetchall()
 
            for row in rows:
                print(row)
        except Error as e:
            print(e)

    database="F:\\DBs\\pythonsqlite.db"
    sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
                                        id integer PRIMARY KEY,
                                        name text NOT NULL,
                                        begin_date text,
                                        end_date text
                                    ); """

    sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
                                    id integer PRIMARY KEY,
                                    name text NOT NULL,
                                    priority integer,
                                    status_id integer NOT NULL,
                                    project_id integer NOT NULL,
                                    begin_date text NOT NULL,
                                    end_date text NOT NULL,
                                    FOREIGN KEY (project_id) REFERENCES projects (id)
                                );"""
    
    conn = create_connection(database)
    if conn is not None:
        # create projects table
        create_table(conn, sql_create_projects_table)
        # create tasks table
        create_table(conn, sql_create_tasks_table)

        # create a new project
        project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30')
        project_id = create_project(conn, project)
        print("project_id",project_id )
        # tasks
        task_1 = ('Analyze the requirements of the app', 1, 1, project_id, '2015-01-01', '2015-01-02')
        task_2 = ('Confirm with user about the top requirements', 1, 1, project_id, '2015-01-03', '2015-01-05')
        # create tasks
        task_1_id =create_task(conn, task_1)
        task_2_id =create_task(conn, task_2)
        print("task_1",task_1_id )
        print("task_2",task_2_id )
        
        task_2_id = update_task(conn, (2, '2015-01-04', '2015-01-06',task_2_id))
        print("task_2",task_2_id )
        

        #delete_task(conn, task_1_id);
        ##delete_all_tasks(conn);

        print("1. Query task by priority:")
        select_task_by_priority(conn,1)
 
        print("2. Query all tasks")
        select_all_tasks(conn)

    else:
        print("Error! cannot create the database connection.")
    conn.close()

if __name__ == '__main__':
    main()