Re: [pysqlite] [APSW] "SQLError: cannot rollback - no transaction is active"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> bfm_select = "select * from %s where %s %s;"

bfm_select="select * from %(table)s where %(column)s=? %(limit)s %(order)s"

>     def generate_select (self, state=None, key=None, tpblue_ID = None,
> order_field= None ):

   if state and key:
      raise ValueError("bad state and key argument")
   if state:
      column="state"
      bindings=[state]
   else:
      column="trap_id"
      bindings=[key]

   limit=""
   if tpblue_ID:
      limit=" and tpblue_id=?"
      bindings.append(tpblue_ID)

   sort_order=""
   if order_field:
      sort_order=" ORDER BY "+order_field

   sql=bfm_select % {"table": self.table_name,
                     "column": column,
                     "limit": limit,
                     "order": sort_order}

   log(sql, bindings)
   # or if you want strings
   log("SQL:%s  Bindings: %s" % (sql, bindings)

   cursor.execute(sql, bindings)

Some observations:

This code implies a rather convoluted schema for the database.  Lookup
"normalization" for some best practise on schemas.

>             raise "bad state and key argument"

String exceptions were deprecated a really long time ago.  They have
been completely removed from Python 3.

In the code above I ended up using a dictionary for the string
interpolation.  I could have used just plain %s but by using the
dictionary the code is easier to read and is more self documenting.
 I could also have made the bindings be a dictionary.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkycGsACgkQmOOfHg372QSPHgCdG+9KgAp+6Az9zLj57hvIi+qi
+eIAnAihWJCdqkCyd6vAjftUP42Z6+GB
=DdGc
-----END PGP SIGNATURE-----