in Ram database?

[Gerhard Häring]

William Trenker wrote:
> Here is a simple [PySQLite] example:
> 
> import sqlite   #module name exposed by the PySQLite wrapper
> conn = sqlite.connect(db=':memory:')   #special connect string to open an in-memory db

Please use 'database' or just use an unnamed parameter instead of a 
named one. 'db' is deprecated.

> cursor = conn.cursor()  #standard DB-API-2 from this point
> cursor.execute('create table test (a varchar, b int);')

No need for using semicolons in SQLite statements with the Python 
DB-API. Don't be surprised if you create problems this way.

> cursor.execute('commit;') #end PySQLite implicit transaction

If you use the Python DB-API, use the .commit() and .rollback() methods 
on the connection object. If you invoke COMMIT/ROLLBACK using 
.execute(), except the unexpected.

> cursor.execute('begin transaction;')  #wrap the inserts in an explicit transaction for speed
> for i in range(0,10000):
>     cursor.execute('insert into test (a,b) values (%s,%i);'%(str(i),i))
> cursor.execute('commit;')
> cursor.execute('select sum(b) from test;')
> print cursor.fetchall()[0][0]

Yuck! What's the point of all this building connections manually crap?

Also, what's the point of working around the DB-API way of quoting 
parameters with .execute()? The DB-API modules will quote parameters 
correctly for you, be it string, int, float or whatever else the backend 
supports. This works by *not* using the Python % quote operator, but by 
using a second parameter to .execute() which will hold a tuple with the 
parameters to be inserted. So the correct example would look something like:

#v+
import sqlite
cx = sqlite.connect(":memory:")
cu = cx.cursor()
cu.execute("create table test(a varchar, b int)")
for i in xrange(10000):
     cu.execute("insert into test(a, b) values (%s, %s)", (str(i), i))
     # Please use %s for all parameter types in PySQLite
cx.commit()
cu.execute("select sum(b) as RESULTCOLUMN from test")
print cu.fetchone()[0]
# PySQLite extension: print cu.fetchone().RESULTCOLUMN
cx.close()
#v-

Btw. for mass inserts you can use .executemany(), which with some DB-API 
modules is more efficient than calling .execute() in a loop.

Like all Python DB-API modules whose backends support transactions, 
PySQLite uses transactions transparently. All you have to do is 
.commit() or .rollback() on the connection object.

PySQLite does have an option to disable transparent transaction 
management for those who think they must shoot themselves in the foot. 
Those should use the parameter autocommit=0 in the sqlite.connect() call.

-- Gerhard