#!/usr/bin/env python
     # encoding: utf-8
     #
     # Copyright (c) 2010 Doug Hellmann.  All rights reserved.
     #
     """Working with an in-memory database
     """
     #end_pymotw_header
     
     import sqlite3
     
     schema_filename = 'todo_schema.sql'
     
     with sqlite3.connect(':memory:') as conn:
         conn.row_factory = sqlite3.Row
         
         print 'Creating schema'
         with open(schema_filename, 'rt') as f:
             schema = f.read()
         conn.executescript(schema)
     
         print 'Inserting initial data'
         conn.execute("""
             insert into project (name, description, deadline)
             values ('pymotw', 'Python Module of the Week', '2010-11-01')
             """)
         data = [
             ('write about select', 'done', '2010-10-03', 'pymotw'),
             ('write about random', 'waiting', '2010-10-10', 'pymotw'),
             ('write about sqlite3', 'active', '2010-10-17', 'pymotw'),
             ]
         conn.executemany("""
             insert into task (details, status, deadline, project)
             values (?, ?, ?, ?)
             """, data)
     
         print 'Looking for tasks...'
         cursor = conn.cursor()
         cursor.execute("""
         select id, priority, status, deadline, details from task
         where project = 'pymotw' order by deadline
         """)
         for row in cursor.fetchall():
             print '%2d {%d} %-25s [%-8s] (%s)' % (
                 row['id'], row['priority'], row['details'], row['status'], row['deadline'],
                 )
     
     with sqlite3.connect(':memory:') as conn2:
         print '\nLooking for tasks in second connection...'
         cursor = conn2.cursor()
         cursor.execute("""
         select id, priority, status, deadline, details from task
         where project = 'pymotw' order by deadline
         """)
         for row in cursor.fetchall():
             print '%2d {%d} %-25s [%-8s] (%s)' % (
                 row['id'], row['priority'], row['details'], row['status'], row['deadline'],
                 )