Introducing LitePieSQL: A basic Python wrapper for SQLite

I never liked writing SQL INSERT and UPDATE statements. Not that it's hard, but it's time consuming and I don't like how it clutters my code.
In PHP I had a nice wrapper for that made by ricocheting.

So I decided to create one just like it for SQLite in Python. I haven't been using Python for long, and I wrote this in a few hours, so it's possible a bit too rough for your use, but still...

How can you use it?

# Import and create an object like this
import litepiesql
db = litepiesql.Database('filename.db')

# This will produce a list with a dictionary for every row, containing the column names
result = db.query('SELECT * FROM stocks')
print result

"""
Will result in something like this:
[{'date': u'2006-01-05', 'symbol': u'RHAT', 'trans': u'BUY', 'price': 35.140000000000001, 'qty': 103.0},
{'date': u'2011-04-18', 'symbol': None, 'trans': u'SELL', 'price': None, 'qty': None}]
"""

How can you insert data in a table?

# You can use the insert function.
# Its parameters are the tablename and the dictionary with unescaped data
# It will return the ID of the inserted row.

# For example: Create a new dictionary
data = {}
data['date'] = '2011-04-19'
data['symbol'] = 'TEST'
rowid = db.insert('tablename', data)

# You can also use special values, like:
new = {}
new['date'] = 'now()'
new['symbol'] = 'null'
newrowid = db.insert('tablename', new)

#Do note, now() will insert the UNIX timestamp, not a date.

How can you update something?

# You can use the update function.
# Its parameters are the tablename, the dictionary with unescaped data and the ESCAPED WHERE parameter (without the actual WHERE)

# For example: Create a new dictionary
change = {}
change['date'] = '2011-04-19'
change['symbol'] = 'UPDATED'
db.update('tablename', change, 'symbol = "TEST"')

# You can use the same special values here, but there's an extra one:
newchange = {}
newchange['qty'] = 'increment(1)'
db.update('tablename', newchange, 'qty > 0')

# This will increment the qty field with 1. You can also use negative numbers, of course.

How can you empty a table?

# You can use the truncate() function to empty a table and reset the autoincrement counter.
db.truncate('tablename')

Why LitePieSQL?

The code:

#!/usr/bin/env python
"""
Name: Database.singleton.php
File Description: SQLite Wrapper for Python providing easy access to basic functions.
Author: Jelle De Loecker (skerit)
Inspired by: ricocheting's MySQL Wrapper for PHP
Web: http://www.kipdola.com/
Update: 2011-04-19
Version: 0.1
Copyright 2011 kipdola.com

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see .
"""

import sqlite3
import itertools
import re
import time

class Database:

    def __init__(self, dbfile):
        self.conn = sqlite3.connect(dbfile)
        self.conn.row_factory = sqlite3.Row
        
    def insert(self, tablename, data):
        """
        Insert data into a table.
        The data does not have to be escaped.
        """
        
        # Create a new cursor
        tc = self.conn.cursor()
        
        tablelist = ""
        valueholder = ""
        valuelist = []
        
        for key, value in data.items():
            if len(tablelist) > 0:
                tablelist += ', '
                valueholder += ', '
                
            # Add to table list
            tablelist += key
            
            # Add a holder
            valueholder += '?'
            
            # Look for the increment() function
            increment = re.match("^increment\((\-?\d+)\)$",str(value))

            if(str(value).lower() == 'null'):
                valuelist.append(None)
            elif(str(value).lower() == 'now()'):
                valuelist.append(str(int(time.time())))
            else:
                valuelist.append(value)
                
        
        # Perform and commit the insert
        self.query("INSERT INTO " + tablename + " (" + tablelist + ") VALUES (" + valueholder + ");", valuelist)
        
        # Get the last inserted id
        id = self.query('SELECT last_insert_rowid();')[0]['last_insert_rowid()']
        
        # Close this connection
        tc.close()
        
        # Return the id
        return id
    
    def query(self, query, escapeList=None):
        """
        Perform a query. When an escapeList is provided it'll be used for
        variable substitution.
        
        Returns a list with dictionaries containing the result of your SELECT,
        or an empty list after an INSERT or UPDATE.
        """
        
        # Create a new cursor
        tc = self.conn.cursor()
        
        # Execute our query with or without values to escape
        if(escapeList):
            tc.execute(query, tuple(escapeList))
        else:
            tc.execute(query)
        
        # Make an empty result list
        result = []
    
        # A description is only set after a SELECT statement
        # Even when there are no results.
        if(tc.description):
            # Fetch the field names out of our cursor
            field_names = [d[0].lower() for d in tc.description]
            
            # Generate a dictionary
            while True:
                rows = tc.fetchmany()
                if not rows: break
                for row in rows:
                    result.append(dict(itertools.izip(field_names, row)))
        else:
            # If there is no description this must mean we're doing an insert
            # or update. Anything that needs a commit.
            self.conn.commit()
        
        # Close the cursor        
        tc.close()

        # Return the list with the dictionaries
        return result
    
    def update(self, tablename, data, where):
        """
        Update a table.
        The WHERE variable you give has to be escaped
        """
        
        # Create a new cursor
        tc = self.conn.cursor()
        
        # Store all the field names we'll be updating in order
        updatelist = ""
        
        # Add all the updates values to a list in order,
        # which we'll convert to a tuple when needed.
        valuelist = []
        
        for key, value in data.items():
            if len(updatelist) > 0:
                updatelist += ', '
            
            # Look for the increment() function
            increment = re.match("^increment\((\-?\d+)\)$",str(value))
            
            if(str(value).lower() == 'null'):
                updatelist += key + "=?"
                valuelist.append(None)
            elif(increment):
                updatelist += key + "=" + key + '+' + list(increment.groups(0))[0]
            elif(str(value).lower() == 'now()'):
                updatelist += key + "=?"
                valuelist.append(str(int(time.time())))
            else:
                updatelist += key + "=?"
                valuelist.append(value)
        
        self.query("UPDATE " + tablename + " SET " + updatelist + " WHERE " + where + ";", valuelist)
        
        tc.close()
    
    def truncate(self, tablename):
        """
        Delete all rows from a table and reset the autoincrement
        """
        
        # Create a new cursor
        tc = self.conn.cursor()
        
        # Clear the table
        tc.execute("delete from "+ tablename + ";")
        
        # Reset the autoincrement
        tc.execute("delete from sqlite_sequence where name='"+ tablename + "';")
        
        tc.close()