Sources: https://www.sqlite.org/index.html, https://sqlite.org/cli.html
Installation steps:
1. download from here, for linux here #Sqlite bowser GUI is here http://sqlitebrowser.org/
#for browser in ubuntu 14 -
add-apt-repository -y ppa:linuxgndu/sqlitebrowser
apt-get update
apt-get install sqlitebrowser
2. unzip sqlite-tools-linux-x86-3190300.zip
3. cd sqlite-tools-linux-x86-3190300
4. ./sqlite3 <PATH TO DB FILE WHERE DATA IS OR WILL BE STORED>
sqlite> .help #Lists all available dot-commands
sqlite> create table users(user_id int, user_name varchar(10), password varchar(10));
sqlite> insert into users values(1, 'rohit verma', 'xxxxxx');
sqlite> insert into users values(2, 'rahul verma', 'zzzzzz');
sqlite> select * from users;
1|rohit verma|xxxxxx
2|rahul verma|zzzzzz
#default mode of output is list which can be changed as follows:
sqlite> .mode quote
sqlite> select * from users;
1,'rohit verma','xxxxxx'
2,'rahul verma','zzzzzz'
sqlite> .mode column
sqlite> select * from users;
1 rohit verma xxxxxx
2 rahul verma zzzzzz
sqlite> .mode line
sqlite> select * from users;
user_id = 1
user_name = rohit verma
password = xxxxxx
user_id = 2
user_name = rahul verma
password = zzzzzz
sqlite> .mode insert new_table
sqlite> select * from users;
INSERT INTO new_table VALUES(1,'rohit verma','xxxxxx');
INSERT INTO new_table VALUES(2,'rahul verma','zzzzzz');
#write output to file.
sqlite> .mode list
sqlite> .separator |
sqlite> .output /tmp/test.dump
sqlite> select * from users;
sqlite> .exit
#List available database and tables.
sqlite> .tables
student users
sqlite> .schema
CREATE TABLE student(id int, name varchar(10), phone bigint);
CREATE TABLE users(user_id int, user_name varchar(10), password varchar(10));
sqlite> .database
main: /home/likewise-open/UGAM/rohit.verma/projects/rohit/working/sqlite/test_20170607.db
temp:
#Run in shell mode:
rohit.verma@localhost:sqlite-tools-linux-x86-3190300]#./sqlite3 /home/rohit.verma/projects/rohit/working/sqlite/test_20170607.db 'select * from users;'
1|rohit verma|xxxxxx
2|rahul verma|zzzzzz
Usage in python:
(py_common_env) rohit.verma@localhost:~/py_common_env$ python
Python 2.7.6 (default, Jun 22 2015, 17:58:13)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> con=sqlite3.connect('/home/rohit.verma/projects/rohit/working/sqlite/test_20170607.db')
>>> cur=con.cursor()
>>> cur.execute('create table student(id int, name varchar(10), phone bigint)')
<sqlite3.Cursor object at 0x7ffba8ef6d50>
>>> cur.execute('insert into student values(1, "Rohit Verma")')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: table student has 3 columns but 2 values were supplied
>>> cur.execute('insert into student values(1, "Rohit Verma", 229988888)')
<sqlite3.Cursor object at 0x7ffba8ef6d50>
>>> firstParam=('Shukra',) #note necessary trailing coma
>>> cur.execute('insert into student(name) values(?)', firstParam)
>>> params=(1, "Rahul") #not trailing coma is required, coz
>>> cur.execute('insert into student(id, name) values(?, ?)', params)
>>> con.commit() #this is necessary after any update statement to preserve same.
>>> students=cur.execute('select * from student')
>>> for student in students :
... print student
...
(1, u'Rohit Verma', 229988888)
>>> con.close()
Usage in java:
Usage in android: