Posted by: Radek Wojcik 9 years, 3 months ago

(Comments)

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

This is a handy tool for small django websites. Some handy commands :

Load up sqlite3 with a database

sqlite3 db.sqlite3.file

Check schema of a table

.schema table_name

Other commands:

#show tables
.tables
#help
.help

Create a database

mkdir /home/sqlite
chmod daemon /home/sqlite
touch sample.db
chmod daemon /home/sqlite/sample.db

Creating sample data

Use your favorite text editor to create the following file and save it as music.sql:

create table music(artist varchar(40), title varchar(60));
insert into music values('Beastie Boys','Paul''s Boutique');
insert into music values('John, Elton','Goodbye Yellow Brick Road');
insert into music values('Parliment','Mothership Connection');

Load and verify

sqlite3 sample.db < music.sql
sqlite3 sample.db 'select * from music;'

Now use the file you just created to populate your sample database.

Note: Pay particular attention to the repeated single quote in Paul''s Boutique. This is not a typo, it is how SQL escapes literal single quotes. If you forget to do this when entering data, you'll see an error about an incomplete SQL statement.

Backing/Restoring up the database

To make a backup copy of the database, simply do a "dump" and redirect the results to a file.

cd /home/sqlite
sqlite3 sample.db .dump > sample.bak
# Can also dump by name
sqlite3 sample.db ".dump 'table1' 'table2'"
#Restoring the database
cd /home/sqlite
mv sample.db sample.db.old
sqlite3 sample.db < sample.bak

References: http://www.sqlite.org/

Current rating: 1

Comments