.. _pysqlitediffs:

pysqlite differences
********************

.. currentmodule:: apsw

pysqlite and APSW approached the problem of providing access to SQLite
from Python from fundamentally different directions.  

APSW only wraps version 3 of SQLite and provides access in whatever
way is normal for SQLite.  It makes no effort to hide how SQLite is
different from other databases.

pysqlite tries to provide a DBAPI compliant wrapper for SQLite and in
doing so needs to make it have the same behaviour as other databases.
Consequently it does hide some of SQLite's nuances.

.. note:: 

   I suggest using APSW when you want to directly use SQLite and its
   functionality or are using your own code to deal with database
   independence rather than DBAPI.  Use pysqlite and DBAPI if your
   needs are simple, and you don't want to use SQLite features.


What APSW does better
=====================

APSW has the following enhancements/differences over pysqlite 2 (wrapping SQLite 3):

* APSW stays up to date with SQLite.  As features are added and
  functionality changed in SQLite, APSW tracks them.

* APSW gives all functionality of SQLite including :ref:`virtual
  tables`, :ref:`VFS`, :ref:`BLOB I/O <blobio>` and :ref:`backups
  <backup>`.

* You can use the same :class:`Connection` across threads with APSW
  without needing any additional level of locking.  pysqlite requires
  that the :class:`Connection` and any :class:`cursors <Cursor>` are
  used in the same thread.  You can disable its checking, but unless you
  are very careful with your own mutexes you will have a crash or a
  deadlock.
 
* APSW is a single file for the extension, :file:`apsw.pyd` on Windows and
  :file:`apsw.so` on Unix/Mac. There are no other files needed and the :ref:`build
  instructions <building>` show you how to include SQLite statically
  in this file. You can put this file anywhere your Python session can
  reach. pysqlite is one binary file and several .py files, all of
  which need to be available.

* **Nothing** happens behind your back. By default pysqlite tries to
  manage transactions by parsing your SQL for you, but you can turn it
  off. This can result in very unexpected behaviour with pysqlite.

* When using a :class:`Connection` as a :meth:`context manager
  <Connection.__enter__>` APSW uses SQLite's ability to have `nested
  transactions <http://www.sqlite.org/lang_savepoint.html>`__.
  pysqlite only deals with one transaction at a time and cannot nest
  them.  (Savepoints were introduced in SQLite 3.6.8 - another
  illustration of the benefits of keeping up to date with SQLite.)

* APSW **always** handles Unicode correctly (this was one of the major
  reasons for writing it in the first place). pysqlite has since fixed
  many of its issues but you are still stuck with some.

* You can use semi-colons at the end of commands and you can have
  multiple commands in the execute string in APSW. There are no
  restrictions on the type of commands used. For example this will
  work fine in APSW but is not allowed in pysqlite::

    import apsw 
    con=apsw.Connection(":memory:") 
    cur=con.cursor() 
    for row in cur.execute("create table foo(x,y,z);insert into foo values (?,?,?);" 
                           "insert into foo values(?,?,?);select * from foo;drop table foo;" 
                           "create table bar(x,y);insert into bar values(?,?);" 
                           "insert into bar values(?,?);select * from bar;", 
                           (1,2,3,4,5,6,7,8,9,10)): 
                               print row
        
  And the output as you would expect::

    (1, 2, 3) 
    (4, 5, 6) 
    (7, 8) 
    (9, 10)

* :meth:`Cursor.executemany` also works with statements that return
  data such as selects, and you can have multiple statements.
  pysqlite's :meth:`executescript` method doesn't allow any form of
  data being returned (it silently ignores any returned data).

* pysqlite swallows exceptions in your callbacks making it far harder
  to debug problems. That also prevents you from raising exceptions in
  your callbacks to be handled in your code that called
  SQLite. pysqlite does let you turn on `printing of tracebacks
  <http://pysqlite.googlecode.com/svn/doc/sqlite3.html#sqlite3.en    ursors                       3082
    Number of threads used for queries  21
    Total queries                       127973
    Number of distinct queries          578
    Number of rows returned             2369
    Time spent processing queries       120.530 seconds

This shows how many times each query was run.

  .. code-block:: text
  
    MOST POPULAR QUERIES

     121451 insert into foo values(?)
       1220 insert into abc values(1,2,?)
       1118 select x from foo
        909 select timesten(x) from foo where x=? order by x
        654 select * from foo
        426 update t1 set b=b||a||b
        146 begin
         88 create table foo(x,y)
         79 insert into foo values(1,2)
         76 rollback
         71 pragma locking_mode=exclusive
         71 insert into t1 values(2, 'abcdefghijklmnopqrstuvwxyz')
         71 insert into t1 values(1, 'abcdefghijklmnopqrstuvwxyz')
         71 insert into t1 select 4-a, b from t2
         71 insert into foo values(date('now'), date('now'))

This shows how many times a query was run and the sum of the
processing times in seconds.  The ``begin immediate`` query
illustrates how time spent busy waiting is included.

  .. code-block:: text

    LONGEST RUNNING - AGGREGATE
   
        413   94.305 select timesten(x) from foo where x=? order by x
     120637   12.941 select * from foo
         12    4.115 begin immediate
     121449    2.179 insert into foo values(?)
       1220    1.509 insert into abc values(1,2,?)
          3    1.380 create index foo_x on foo(x)
        426    0.715 update t1 set b=b||a||b
         38    0.420 insert into foo values(?,?)
         71    0.241 create table t1(a unique, b)
         88    0.206 create table foo(x,y)
         61    0.170 create table abc(a,b,c)
         27    0.165 insert into foo values(?,?,?)
          1    0.158 select row,x,snap(x) from foo
         80    0.150 insert into foo values(1,2)
         71    0.127 insert into foo values(date('now'), date('now'))

This shows the longest running queries with time in seconds. 

  .. code-block:: text

    LONGEST RUNNING - INDIVIDUAL

      3.001 begin immediate
      1.377 create index foo_x on foo(x)
      1.102 begin immediate
      0.944 select timesten(x) from foo where x=? order by x
      0.893 select timesten(x) from foo where x=? order by x
      0.817 select timesten(x) from foo where x=? order by x
      0.816 select timesten(x) from foo where x=? order by x
      0.786 select timesten(x) from foo where x=? order by x
      0.783 select timesten(x) from foo where x=? order by x
      0.713 select timesten(x) from foo where x=? order by x
      0.701 select timesten(x) from foo where x=? order by x
      0.651 select timesten(x) from foo where x=? order by x
      0.646 select timesten(x) from foo where x=? order by x
      0.631 select timesten(x) from foo where x=? order by x
      0.620 select timesten(x) from foo where x=? order by x


                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         ./usr/share/doc/python-apsw/html/_sources/pysqlite.txt                                              0000644 0000000 0000000 00000020463 11373763437 022106  0                                                                                                    ustar   root                            root                                                                                                                                                                                                                   .. _pysqlitediffs:

pysqlite differences
********************

.. currentmodule:: apsw

pysqlite and APSW approached the problem of providing access to SQLite
from Python from fundamentally different directions.  

APSW only wraps version 3 of SQLite and provides access in whatever
way is normal for SQLit