Diff for "SQLite" - Python Wiki

Differences between revisions 1 and 9 (spanning 8 versions)

TableOfContents

URL: http://sqlite.org/
licence: Sources are uncopyrighted. Use for any purpose.
platforms: Built and tested under Linux and Win2K.

DB API 2.0 Drivers

pysqlite

URL: http://pysqlite.org/
SourceForge: http://sourceforge.net/projects/pysqlite
licence: zlib/libpng License
platforms: Windows 95/98/2000/XP, POSIX, MacOS X
Python versions: 2.1 or later (1.x branch)/2.3 or later (2.0 branch). Included in Python 2.5.

Extensions to DB API

Extensible type conversion
Factories for connection and cursor objects
row converter factory to easily and efficiently switch to a nonstandard type for rows (e. g. dicts)
User-defined functions and aggregates

Comments

Other Drivers

APSW

URL: http://www.rogerbinns.com/apsw.html
licence: zlib/libpng license
platforms: Windows, POSIX
Python versions: ?

Programming Model

APSW is a SQLite 3 wrapper that provides a thin layer over SQLite 3. Although APSW looks vaguely similar to the DBAPI, it is not compliant with that API and instead mirrors the way SQLite 3 works.

Comments

Supported Python Applications

Thuban (GIS application)
Roundup (issue tracker)
PyPI (Python Package Index)
Trac (issue tracker, wiki, Subversion web frontend)
Cloud Wiki (wiki)
Supybot (IRC bot framework)
PyAddbook (Address Book)

Pros

I think SQLite may be a good replacement for gadfly, because:

the main engine is written in C, so it should be faster than the gadfly implementation in Python
it's extensible in a very easy way via Python
it doesn't put all data in memory like gadfly does (yet you can do that if you want, just use ':memory:' as filename
It's very cool for small databased application, because you do not have to start an external DBMS
Implements almost all of SQL92

Cons

SQLite only supports the basic types NULL, INTEGER, FLOAT, TEXT and BLOB
If you want to use other types like DATE and TIME in pysqlite, you need to use its "pysqlite types mode", where things can get a little nastier.

Usage Notes

The following solution was difficult to discover with the available documentation (http://pysqlite.org/ was unavailable). If this page can be found by others searching for answers, it may save many hours of frustration.

Id of Most Recent Row

After creating a new row in a table that uses AUTOINCREMENT to create the PRIMARY KEY, one may wish to determine the value of the new row-id, for example if the value is need for a new row in a related table that will be inserted next. The answer is to use the lastrowid property of the cursor class as in newId=c.lastrowid shown below in a demo context. Tested in Python2.5.1 with the sqlite3 module:

           import sqlite3
           con = sqlite3.connect('demo.db')
           con.execute("""CREATE TABLE tbl (
               id INTEGER PRIMARY KEY AUTOINCREMENT,
               grp INTEGER)""")
           c = con.cursor()
           c.execute("""INSERT INTO tbl (grp) VALUES (0);""")

           newId = c.lastrowid

           print "New rowid =", newId
           c.close()
           con.close()

The result is printed: New rowid = 1

-  ⇤ ← Revision 1 as of 2002-08-30 15:19:41 → 
  Size: 1383
  Editor: c-44b871d5
  Comment:
+   ← Revision 9 as of 2008-01-02 08:09:26 → ⇥
  Size: 3364
  Editor: c-24-22-188-25
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 5:
-  URL:: http://www.hwaci.com/sw/sqlite/
  licence:: Sources are uncopyrighted. Use for any purpose.
  platforms:: Built and tested under Linux and Win2K.
+ URL:: http://sqlite.org/
 licence:: Sources are uncopyrighted. Use for any purpose.
 platforms:: Built and tested under Linux and Win2K.
-Line 11:
+Line 9:
-=== PySQLite ===

  URL:: http://pysqlite.sourceforge.net/
  SourceForge:: http://sourceforge.net/projects/pysqlite
  licence:: Python Licence
  platforms:: Windows 95/98/2000, POSIX
  Python versions:: 2.1 - 2.2
+=== pysqlite ===
 URL:: http://pysqlite.org/
 SourceForge:: http://sourceforge.net/projects/pysqlite
 licence:: zlib/libpng License
 platforms:: Windows 95/98/2000/XP, POSIX, MacOS X
 Python versions:: 2.1 or later (1.x branch)/2.3 or later (2.0 branch). Included in Python 2.5.
-Line 21:
+Line 16:
-  * Support for Unicode conversion
  * Due to SQLite being an embedded database, it's possible to write SQL functions and aggregates in Python
+ * Extensible type conversion
 * Factories for connection and cursor objects
 * row converter factory to easily and efficiently switch to a nonstandard type for rows (e. g. dicts)
 * User-defined functions and aggregates
==== Comments ====
== Other Drivers ==
=== APSW ===
 URL:: http://www.rogerbinns.com/apsw.html
 licence:: zlib/libpng license
 platforms:: Windows, POSIX
 Python versions:: ?
==== Programming Model ====
APSW is a SQLite 3 wrapper that provides a thin layer over SQLite 3. Although APSW looks vaguely similar to the DBAPI, it is not compliant with that API and instead mirrors the way SQLite 3 works.
-Line 26:
+Line 31:
-== Other Drivers ==

=== driver name ===

  URL::
  licence::
  platforms::
  Python versions::

==== Programming Model ====

==== Comments ====
-Line 41:
+Line 32:
+ * Thuban (GIS application)
 * Roundup (issue tracker)
 * PyPI (Python Package Index)
 * Trac (issue tracker, wiki, Subversion web frontend)
 * Cloud Wiki (wiki)
 * Supybot (IRC bot framework)
 * PyAddbook (Address Book)
-Line 43:
+Line 40:
-Line 47:
+Line 43:
- * it's extensible in a very easy way via Python 
 * it dosn't put all data in memory like gadflay does
+ * it's extensible in a very easy way via Python
 * it doesn't put all data in memory like gadfly does (yet you can do that if you want, just use ':memory:' as filename
-Line 50:
+Line 46:
+ * Implements almost all of SQL92
== Cons ==
 * SQLite only supports the basic types NULL, INTEGER, FLOAT, TEXT and BLOB
 * If you want to use other types like DATE and TIME in pysqlite, you need to use its "pysqlite types mode", where things can get a little nastier.
== Usage Notes ==
The following solution was difficult to discover with the available documentation (http://pysqlite.org/ was unavailable). If this page can be found by others searching for answers, it may save many hours of frustration.
-Line 51:
+Line 53:
-(Comment by Dirk Holtwick, holtwick at spirito.de)
+=== Id of Most Recent Row ===
After creating a new row in a table that uses AUTOINCREMENT to create the PRIMARY KEY, one may wish to determine the value of the new row-id, for example if the value is need for a new row in a related table that will be inserted next. The answer is to use the ''lastrowid'' property of the ''cursor'' class as in   {{{newId=c.lastrowid }}} shown below in a demo context. Tested in Python2.5.1 with the sqlite3 module:
-Line 53:
+Line 56:
-== Cons ==
+{{{
           import sqlite3
           con = sqlite3.connect('demo.db')
           con.execute("""CREATE TABLE tbl (
               id INTEGER PRIMARY KEY AUTOINCREMENT,
               grp INTEGER)""")
           c = con.cursor()
           c.execute("""INSERT INTO tbl (grp) VALUES (0);""")
-Line 55:
+Line 65:
- * Only a small subset of SQL.
 * Untyped!
+           newId = c.lastrowid

           print "New rowid =", newId
           c.close()
           con.close()
}}}
The result is printed:{{{ New rowid = 1}}}

Page

User

Masthead

DB API 2.0 Drivers

pysqlite

Extensions to DB API

Comments

Other Drivers

APSW

Programming Model

Comments

Supported Python Applications

Pros

Cons

Usage Notes

Id of Most Recent Row