Here are some quick & dirty notes on getting SQLITE3 up-and-running on a Windows machine in less than an hour.
#1 Decide where to store SQLITE3 and where you will work
- I decided to keep a copy of SQLITE3 in a sub-directory of my c:tools directory where I have stored other tools
- I also decided to put a copy in the directory where I will keep the database. Being able to access SQLITE3 from the data directory saves me some typing later.
- My data directory is what is sometimes called my desktop. It is the Windows default setting. To see the full “path” in Windows, click at the top of the screen where your browser bar usualy is. C>USER> ….etc turns into the path looking like this C:USERUSERNAMEDocuments… We’ll cut and paste this path later.
#2 Download SQLITE3
- The Window binaries for SQLITE3 are here.
- Download the command shell only.
- Copy the zip file into both directories: c:toolssqlite and the data directory c:userusernamedocuments… [wherever you will be keeping your database]
#3 Prepare a little file to structure your database
- As an example copy and paste this code into Wordpad
- Save the file as “creategobble.sql”
- Make sure it goes into the directory where you will be working (see above)
- This file is going to make four tables in the new database.
#4 Go to your command prompt
- Go to Start and type in “command prompt”. You should see it come up top right
- Select Command Prompt
- Go back briefly to the directory holding your data and copy the path at the top (click on the >Libraries etc and the path will come up C:Users…etc
- Go back to the Command Prompt and type in cd (for change directory) followed by the path (right click and paste). Return
- You will see that you are now working in your data directory
#5 Set up your database
- Type in “sqlite3 gobble” (gobble is the name of our database)
- You will see the sqlite come up
- Type in this line: .read creategobble.sql <return>
- Hey presto you have a made the little database
#6 Now get some data
- Open Excel and label four sheets, one for each table : Person, Frequents, Eats, Serves
- Add data to each table. Either cut and past the information below, or open this teaching file from Stanford and cut and paste their data.
1 Amy 16 female
2 Bert 20 male
3 Charles 22 male
1 Amy PizzaHut
2 Bert Dominos
3 Charles PizzaHut
1 Amy mushroom
2 Amy cheese
3 Bert cheese
4 Bert seafood
5 Charles cheese
1 PizzaHut cheese 7.75
2 Dominos cheese 9.75
3 PizzaHut mushroom 8
4 Dominos seafood 11
- Save each sheet as separate .csv file called persontable.csv, eatstable.csv, etc.
#7 Load your data into your table
- Go back to your command prompt
- If you have closed it, then check you are in the right directory. If not look at #4.
- Re-open your database “sqlite3 gobble”
- Tell Sqlite3 that you are uploading .csv files. Type in “.mode csv” <return>
- Upload all four files one-by-one: .import persontable.csv person <enter>
#8 Prove to yourself that your data is in the database
- Type “select * from person;” <return>
- You should see your data.
There you are. You have made your first database in SQLITE3 and you are ready to play around with it and learn more commands.