Drop Shadow

SQLite

SQLite currently works from the command line (using the “sqlite3” command), Python, Perl, and PHP.

  1. SSH to your account.
  2. cd Documents
  3. mkdir databases
  4. cd databases
  5. sqlite3 DATABASENAME
  6. create table TABLENAME (table information);
  7. .quit

If you want to be able to change it from the web, add the following steps:

  1. chmod o+wr DATABASENAME
  2. chmod g-r DATABASENAME
  3. chmod o+wx .
  4. chmod g-rwx .

For example, to create an album database that stores title and artist, you might use:

sqlite3 albumdb create table albums (ID integer primary key, title text, artist text); insert into albums (title, artist) values ("From the Inside", "Alice Cooper"); insert into albums (title, artist) values ("The Wind", "Warren Zevon");

Then, “select * from albums;” will show:

sqlite> select * from albums; 1|From the Inside|Alice Cooper 2|The Wind|Warren Zevon sqlite>

Connecting with Perl

#!/usr/bin/perl $db = "/path/to/albumdb"; use DBI; $dbh = DBI->connect("dbi:SQLite:dbname=$db"); $query = "SELECT title, artist FROM albums"; $sth = $dbh->prepare($query); $sth->execute; while ($ref = $sth->fetchrow_hashref()) { $title = $$ref{'title'}; $artist = $$ref{'artist'}; print "$title ($artist)\n"; }

Connecting with PHP

<? $db = "/path/to/albumdb"; IF ($albums = new PDO("sqlite:$db")): $query = "SELECT title, artist FROM albums"; IF ($albumlist = $albums->query($query)): echo "<table>\n"; FOREACH ($albumlist as $album): $title = $album['title']; $artist = $album['artist']; echo "<tr>"; echo "<td>$title</td>"; echo "<td>$artist</td>"; echo "</tr>\n"; ENDFOREACH; echo "</table>\n"; ELSE: $error = $albums->errorInfo(); echo "<p>Error:", $error[2], "</p>\n"; ENDIF; ELSE: echo "<p>Unable to connect to $db.</p>\n"; ENDIF; ?>

Connecting with Python

#!/usr/bin/python #path to database db = "/path/to/albumdb" import sqlite3 as sqlite sql= sqlite.connect(db) query = "SELECT title, artist FROM albums" cursor = sql.cursor() cursor.execute(query) for row in cursor.fetchall(): title, artist = row print title + " (" + artist + ")"

Comments

If you have comments or tips for readers of this page, you may post them here. Questions are more appropriately directed to the webmaster. Comment on this page