boodebr: Sql2: Portability

These notes provide tips for writing portable SQL with boodebr.sql2.

To aid in portability, boodebr.sql2 limits the builtin datatypes to those in the following table:

SQL	To DB Passed to database from Python	From DB Passed from database to Python
INTEGER	int,long	int,long
REAL	float	float
TEXT	str,unicode	unicode
BLOB	buffer	str
NULL	None	None

Notes:

Some of the types change between passing to and from the database. This is required for portability between lowlevel APIs, beyond the control of boodebr.sql2
You can write a custom converter to support more types, but the converter must output one of the basic types listed above.

Portability tips:

Always use '?' as the placeholder for values in query strings. It will be converted as needed.
Avoid hardcoding typenames in CREATE TABLE. The below example shows how to create a table using, all available types:
from boodebr.sql2 import *

sql = opensql(..)
sql.create_table('foo',
         [('id', AUTO_PRIMARY_KEY),
          ('an_int', INTEGER), ('a_float', REAL),
          ('a_text', TEXT), ('a_blob', BLOB)])
Use INSERT like this:
INSERT into tablename (a,b,c) values (?,?,?)
Do NOT use the following statements inside a transaction (they cannot be portably rolled back):
- CREATE TABLE
- DROP TABLE
- ALTER TABLE