Package ais :: Module sqlhelp

Module sqlhelp

source code

Helper functions to create SQL statements.

License: GPL

To Do:

How do I assemble queries like this:

   SELECT COUNT(samplename) AS count FROM \
       (SELECT DISTINCT(samplename) AS samplename FROM \
        ams WHERE corenum=1 AND coretype='p');

subqueries
make a super class so that inserts and selects can verify based on the create str
take the super class info from the database?

Bugs:

FIX: write some doc tests!
had no protection from SQL injection attacks or quoting mistakes

Notes:

This is not as snazzy as SQLAlchemy or SQLObject, but it works and is simple
postgres is sort of case sensitive, so go all lowercase for fields and tables

Author: Kurt Schwehr

Version: 6398

Copyright: 2006

Date: 2007-06-20

Classes

[hide private]

select
Construct an sql select query

create
Helper for building create SQL commands.

insert
Help create an SQL insert statement for injecting data into a database.

Functions

[hide private]

addVerbosityOptions(parser)
Added the verbosity options to a parser

source code

sec2timestamp(utcsec)
Convert a UTC sec time to a SQL timestamp

source code

str

sqlInsertStrFromList(table, aList, dbType='postgres')
Take a list and make an insert string. source code

Variables

[hide private]

BOMBASTIC = 4

VERBOSE = 3

TRACE = 2

TERSE = 1

ALWAYS = 0

NEVER = 0

Function Details

[hide private]

sec2timestamp(utcsec)

source code

Convert a UTC sec time to a SQL timestamp

>>> sec2timestamp(int(1169703371))
'2007-01-25 05:36:11'

sqlInsertStrFromList(table, aList, dbType=`'postgres'`)

source code

Take a list and make an insert string. This works with dictionaries too. Here is a quick example:

>>> aList = [('one',1),('2','two'),('threepoint',3.)]
>>> sqlInsertStrFromList('myTable',aList,dbType='sqlite')
"insert into myTable (one,2,threepoint) values (1,'two',3.0);"
>>> sqlInsertStrFromList('myTable',aList)
"insert into mytable (one,2,threepoint) values (1,'two',3.0);"

Parameters:

table (str) - Which table to insert into
aList - list of tubles pairs to insert - (name, value) @type aList(list)

Returns: str

complete SQL insert command

Module sqlhelp

sec2timestamp(utcsec)

sqlInsertStrFromList(table, aList, dbType='postgres')

sqlInsertStrFromList(table, aList, dbType=`'postgres'`)