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 *
  3. Set environment
    • global directory: Copy created directory:
      sudo cp -r /home/username/opt/pkgs/pysqlite-2.6.3/lib64/python2.6/site-packages/pysqlite* /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

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