#!/usr/bin/env python

import sys; sys.path.append('../../../') # show python where the modules are

import web.database
connection = web.database.connect(
    adapter="snakesql", 
    database="database",
    autoCreate = 1,
)
cursor = connection.cursor()

import datetime

# Crete a table using the DB-API 2.0 interface and inset some information
cursor.execute('CREATE TABLE test( columnDate Date,  columnString String)')
cursor.execute(
    "INSERT INTO test ( columnDate, columnString) VALUES (?, 'This i\\s a str''ing with some awkward quoting')",
    datetime.date(2005,01,27)
)
# Retrieve the information
cursor.execute("SELECT * from test WHERE columnDate = '2005-01-27'")
print cursor.fetchall(format='dict')

# Update the row using the abstraction interface and retrieve the information
cursor.update(
    table = 'test',
    columns = ['columnString'],
    values = ["James's New String conta\\\\ining an apostrophe and awkward quoting"],
)
print cursor.select(columns='*', tables=['test'])

connection.close() # Close the connection without saving changes