Categories
Software

SQLite crash-course

Backstory for this thread is I have a project where I want to review SQLite data.  SQLite is more a less, a compressed database in a flat-file.   Usage tends to be for storing application data, especially in the case of mobile apps.  In my case I wish to query quite a bit and to do so across multiple databases.  As I have the most database experience in MsSQL, I am exporting data from SQLite so I can place it into a MsSQL Database for better querying and results.

There are a few GUI tools for reviewing SQLite databases but if you want to collect data from them outside of their native application, this is where and why I am exporting and importing the data into Microsoft SQL Server.   You could do the same with MySQL and your usage would be slightly different (in the case of using ` instead of ‘ [single quote]).  So pick the database platform you are the most comfortable with or like more.

Task 01:
Reading the SQLite Database.
You can open up the .sqlite in a text editor but as I noted it being compressed, your results will essentially be gibberish characters.  While there are some plaintext values, we want the actual raw data set.  This will look like your standard database dump / csv / tables view.

Task 02:
Running SQLite.
Let’s grab a download of the SQLite binary.  Pick your OS of choice.  In my case I am a Windows main user so I grabbed the sqlite-tools-win32-x86-3270100 windows binary and extracted it to a target folder.   Once extracted we will see sqlite3.exe.  Get used to running this, as this will get us into the SQLite console.

Task 03:
Reading the SQLite database(s).
Starting off, let’s grab a copy of the .sqlite file you want to read and paste a copy into your extracted SQLite tools folder.  I tried full path loading to my sqlite data file but it was giving me issues.  Instead of fighting with that, I just pasted a copy into the same folder as sqlite3.exe we will be running. This is a helpful document on the SQLite website for querying as well.

Once your .sqlite file is in the same folder, bring up a command prompt (cmd.exe) into that folder.  I recently learned a nice trick about getting a cmd prompt into a current folder in explorer.  Browse to said folder and in the address bar, replace the filepath with ‘cmd.exe’ (without quotes) and you will get a command prompt into that folder.  Saving you from changing your drive letter and folder path in the command prompt.

In this cmd window, start by running sqlite3.exe.  By doing so your console will change to sqlite> as you are now running sqlite.  .help will give you all the available options.  Below I will give you a cheat guide in the case of how to: Load a database, select a table, set your export mode and to export the table contents to a flat file! Yeet

.open 'SQLite_DB_in_folder.sqlite' 
.tables
.mode csv
.header on
.output filename.csv
select * from table;
.quit

– For the above console / code example, we start by opening the .sqlite database file.
– List the tables in said database.
– Set our export mode to CSV.
– Export with header / column names as first row.
– Output results of next line query to target flat-file.
– Enter the query with desired table from listed .tables results (You can review these in console by just typing select statement in console, before you enter the .output line).
– .quit exits sqlite3.exe console.  I suggest exiting after an export or your output file will remain in use by the sqlite3.exe console connection.

Step 04:
Review your output then import to MsSQL, etc.
Open up your output .csv files and they should look like plaintext output.  With that being the case you should be able to import them into the relational database system of your choice and go wild querying away!
I should end noting you can also query from the SQLite console too, but since I am looking to compare a large amount of data from various databases, I will import these exported tables into one database on MsSQL with different tables for each.

Note: Your exported .csv will NOT have column labels (unless you follow revised guide).  I edited this post and added ‘.header on’ into the commands so our output would have the column names as first row of the .csv export.

Thanks for reading and have fun heccing all the things!