01
Mar
07

SQLite examples with Bash, Perl and Python

If you are looking for a lightweight database solution take a look on SQLite. I made some very simple examples with Perl, Python and Bash interfacing with sqlite. Just when I have finished I find out this article bellow that have also some examples in Perl and Python to start playing. Take a look!

http://www.linuxjournal.com/article/6650/

Well, all those samples do the same thing. First it creates a table “n” with columns “f” and “l”. Then it insert (“john”, “smith”) as an entry. And finally dumps everything it finds in table “n”.

The perl sample.

#!/usr/bin/perl -w
use DBI;
use strict;
my $db = DBI->connect("dbi:SQLite:test.db", "", "",
{RaiseError => 1, AutoCommit => 1});

$db->do("CREATE TABLE n (id INTEGER PRIMARY KEY, f TEXT, l TEXT)");
$db->do("INSERT INTO n VALUES (NULL, 'john', 'smith')");
my $all = $db->selectall_arrayref("SELECT * FROM n");

foreach my $row (@$all) {
my ($id, $first, $last) = @$row;
print "$id|$first|$lastn";
}

The python sample.

#!/usr/bin/python
from pysqlite2 import dbapi2 as sqlite

db = sqlite.connect('test.db')
cur = db.cursor()
cur.execute('CREATE TABLE n (id INTEGER PRIMARY KEY, f TEXT, l TEXT)')
db.commit()
cur.execute('INSERT INTO n (id, f, l) VALUES(NULL, "john", "smith")')
db.commit()
cur.execute('SELECT * FROM n')
print cur.fetchall()

The bash sample.

#!/bin/bash
sqlite3 test.db  "create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);"
sqlite3 test.db  "insert into n (f,l) values ('john','smith');"
sqlite3 test.db  "select * from n";

Got free time? Spend 5 min and write the same for ruby, tcl, php…
and post as a comment here! Thank you very much!

About these ads

5 Responses to “SQLite examples with Bash, Perl and Python”


  1. 1 random vistor
    March 30, 2007 at 13:54

    query(‘CREATE TABLE n (id INTEGER PRIMARY KEY, f TEXT, l TEXT);’);
    $DB->query(“INSERT INTO n (f, l) VALUES (‘john’, ‘smith’);”);
    // Prepared query
    $q=$DB->query(‘SELECT * FROM n;’);
    print_r($q->fetch(PDO::FETCH_ASSOC));
    ?>

    # Bash again – check if a table exists
    DB=”test.db”

    function table_exists(){
    Q=”SELECT name FROM sqlite_master WHERE type=’table’ AND name=’$1′;”
    R=`sqlite3 $DB “$Q”`
    if [ "$R" = "$1" ]; then
    echo 1
    else
    echo 0
    fi
    }

    STATE=$( table_exists “n” )
    if [ "$STATE" -gt 0 ]; then
    sqlite3 $DB “CREATE TABLE n (id INTEGER PRIMARY KEY, f TEXT, l TEXT);”
    else
    echo “TABLE ALREADY EXISTS”
    fi

  2. 2 random vistor
    March 30, 2007 at 13:58

    Because WordPress destroyed the PHP example: http://pastebin.ca/416797

  3. 3 Fred
    July 31, 2009 at 12:24

    Hi,

    No example to add, sorry about that. However, I think I’ve found a very minor typo in your bash example.

    sqlite3 test.db “select * from n”;

    …should probably be…

    sqlite3 test.db “select * from n;”

  4. 4 darkwurm
    January 9, 2011 at 15:50

    You’ll note that the bash example is the least complex.

  5. 5 MagikK
    April 11, 2012 at 13:36

    print “$id|$first|$lastn”; <— little mistake with $lastn should be $last\n ;)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Quotes

"Don't worry about what anybody else is going to do. The best way to predict the future is to invent it. Really smart people with reasonable funding can do just about anything that doesn't violate too many of Newton's Laws!" -- Alan Kay in 1971

My Twitter

My Delicious

RSS My Shared RSS

  • An error has occurred; the feed is probably down. Try again later.

Blog Stats

  • 54,049 hits

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: