Posts Tagged ‘ SQLite ’

Running an .sql script to generate a database

I’ve been messing around with SQLITE3 databases again, and just created my very first one. It’s called db01.db3 – how original! First I created a little .sql script where I could type in my commands all at once. It’s called michtest.sql. It looks like this:

-- This is my very first SQL database script
-- created:  2010/02/10
-- author:  Michelle W

CREATE TABLE survey_date (
       a_key INTEGER PRIMARY KEY,
       Year  INTEGER,
       Month INTEGER,
       Day   INTEGER,
       Hour  INTEGER,
       Minute	INTEGER,
       Second	DECIMAL(4,1)
       );

Yup, nothing but an empty, meaningless table.

To get it to run and populate an actual database file, I just had to run the following command:

$ sqlite3 db01.db3 < michtest.sql

The code runs, the database file is either created or updated. To see what's in it, I can just run:

$ sqlite3 db01.db3
sqlite> .tables
survey_date

2010
02/10
CATEGORY
Coding
software
TAGS

Write comment

SQLite configuration file

I just created a simple configuration file for SQLite. It’s in my home directory, and is called .sqliterc. It contains the following lines:

-- SQLite configuration file
.echo ON  # repeat every command
.header ON  # print column names
.separator  "\t" # change default separator to tab
.nullvalue "Null" # print the word rather than having empty fields

Now, every time I start SQLite, these commands will be run.

* Thanks to Kurt, who showed me how to do this!

2010-02-10
** NOTE: Correction has been made to .sqliterc –> # (pound signs) are NOT comments in this file. I need to use — (two dashes) to signify a comment. I was getting an error if I didn’t do this. Not sure why I didn’t catch this way back when I wrote this post.

2010
01/20
CATEGORY
Coding
TAGS


Write comment

Getting started with SQLite

I’m just running through some basic commands from Kurt’s SQLite cheatsheet. I guess I already had some version of SQLite installed that came with the Karmic distribution, but it turns out that SQLite3 wasn’t installed. It was easy, I just did a sudo apt-get install sqlite3.

Kurt made a couple of website/tutorial suggestions in his notes:
A database overview and tutorial
Data Types
Speed and optimization

2010
01/20
CATEGORY
Coding
TAGS

Write comment

Next on my list: Databases

Wow, talk about getting sidetracked… I decided today that I want to learn about databases. I want to create a GUI (see previous two posts) and use it to write to and read from a database. I want to be able to search for keywords and numbers from various fields, etc. Somehow, it didn’t seem like it should be that hard. It’s sort of like a spreadsheet, right? I wanted to be able to make a work and survey log, with specific fields that I could fill in each day. Right now, I have something that sort of works, but it’s just an excel spreadsheet where I can filter the contents of the columns. But it’s getting really big and awkward, and not that pretty. I thought I could do better if I could just make a GUI specifically for that task. I did some Google searching, and, quite frankly, it scared me. Everything I found seemed to be aimed at people who already have a bit of a clue (um, definitely not me). So I contacted Kurt, who does all kinds of amazing stuff with databases. He suggested that I start with SQLite, and the Python SQLite module. These two links are various degrees of overwhelming, so I’m looking forward to some more guidance from Kurt! :-)

2010
01/13
CATEGORY
Coding
software
TAGS



Write comment