PySqlite is a Python binding for the ​SQLite light-weight database engine

Reference

Install

PySqlite 2 has been bundled as sqlite if you are using Python 2.5 and up. If you want to use the latest version of sqlite, installation from a source, which can be downloaded from here, is needed.

Install at local directory

  1. Download a source
    mkdir -p /home/username/make/pysqlite
    cd /home/username/make/pysqlite
    wget http://pysqlite.googlecode.com/files/pysqlite-2.6.3.tar.gz
    tar zxvf pysqlite-2.6.3.tar.gz
  2. Compile & Install
    cd pysqlite-2.6.3
    python setup.py -n build_static
    python setup.py -n install --prefix=/home/username/opt/pkgs/pysqlite-2.6.3
    python setup.py build_static
    python setup.py install --prefix=/home/username/opt/pkgs/pysqlite-2.6.3
    emacs -nw opt/pkgs/pysqlite-2.6.3/lib64/python2.6/site-packages/pysqlite2/__init__.py
    from dbapi2 import *
    You may need to copy "amalgamation" from a source of sqlite downloaded here. You need to put the directory as follows,
    [chinoney@pbfs pysqlite-2.6.3]$ ls
    amalgamation  build  cross_bdist_wininst.py  cross_bdist_wininst.pyc  doc  lib  LICENSE  MANIFEST.in  PKG-INFO  setup.cfg  setup.py  src
    [chinoney@pbfs pysqlite-2.6.3]$ ls amalgamation/
    sqlite3.c  sqlite3.h
  3. Set environment
    • global directory: Copy created directory:
      sudo cp -r /home/username/opt/pkgs/pysqlite-2.6.3/lib64/python2.6/site-packages/pysqlite2/* /usr/lib64/python2.6/pysqlite2/
    • local directory: Set PYTHONPATH
      cd
      cd opt
      ln -sf pkgs/pysqlite-2.6.3/lib64/python2.6/site-packages/pysqlite2
      cd
      emacs -nw .bashrc
      # PATH for pysqlite w/ sqlite3.7
      export PYTHONPATH=${OPT}:${PYTHONPATH}  
  4. Test
    cd
    python
    from pysqlite2 import test
    test.test()
    ......................................................................................................................................................
    ----------------------------------------------------------------------
    Ran 213 tests in 0.069s
    OK

Tip: Rename column

sqlite can't rename a column directly. Then you must make a new table and insert original entries into a new table with a correct column name.

cp ./pb1_net_psd.db.org ./pb1_net_psd.db

cat<<EOF | sqlite3 ./pb1_net_psd.db
ALTER TABLE pb1_net_psd RENAME TO tmp;

CREATE TABLE pb1_net_psd(
       run_id INTEGER NOT NULL,
       run_subid INTEGER NOT NULL,
       boloid INTEGER NOT NULL,
       NET_psd REAL NOT NULL,
       PRIMARY KEY(run_id, run_subid, boloid)
       );

INSERT INTO pb1_net_psd(run_id, run_subid, boloid, NET_psd)
SELECT run_id, run_subid, boloid, NET
FROM tmp;

DROP TABLE tmp;

VACUUM;

CREATE INDEX id_pb1_net_psd on pb1_net_psd(run_id, run_subid, boloid);
EOF

Error: Operational Error: too many SQL variables

See "Maximum Number Of Host Parameters In A Single SQL Statement" here.

A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces.
Many SQL programmers are familiar with using a question mark ("?") as a host parameter.
SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123".
Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter.
However, when the "?123" form is used, the host parameter number is the number that follows the question mark.
SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used.
Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage.
This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is 
SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.

Increase "SQLITE_MAX_VARIABLE_NUMBER" by editing "sqlite3.c",

/*
** The maximum value of a ?nnn wildcard that the parser will accept.
*/
#ifndef SQLITE_MAX_VARIABLE_NUMBER
# define SQLITE_MAX_VARIABLE_NUMBER 250000
#endif

or setting CXXFLAGS,

CPPFLAGS="-DSQLITE_MAX_VARIABLE_NUMBER=250000" python setup.py -n build_static

then compile and install again.