Beginners Guide to SQLite

Beginners Guide to SQLite

Learn the basics of SQLite databases from SQLite dot commands to an example of their practical applications using the command line interface.

If you are familiar with relational database systems, it is likely that you have heard of heavyweights such as MySQL, SQL Server or PostgreSQL. However, SQLite is another useful RDBMS that is very simple to set up and operate that has many distinct features over other relational databases. These features include:

  • No need for servers: That is, there are no server processes that need to start, stop or be configured. There is no need for database administrators to assign instances or manage user access permissions.
  • Simple Database Files: A SQLite database is a single ordinary disk file that can be stored in any directory. This also makes it simple to share since database files can easily be copied onto a memory stick or sent through email.
  • Manifest Typing: The majority of SQL database engines rely on static typing. That is, you can only store values of the same datatype that is associated with a particular column. However, SQLite uses manifest typing, which allows the storage of any amount of any data type into any column without no matter the column's declared datatype. Note that there are some exceptions to this rule. One such exception is integer primary key columns, which can only store integers.

As with most things though, there are also some limitations of SQLite with respect to other database engines. For instance, SQLite has no option to do Right or Full Outer Joins, and the serverless nature can also be a disadvantage for security and protection against data misuse. Nevertheless, it is a very powerful tool that comes very much in handy for certain situations. For instance, I have used a SQLite database in a recent social media analysis work project to store an $edge \space list^{1}$ of millions of Twitter users in a very simple and efficient manner.

Thus, for this tutorial, I will illustrate how to operate with a SQLite database. We will go over SQLite setup, table creation and operations, SQLite dot commands, and how to use simple SQL queries to manipulate the data inside a SQLite database.

$^{1}$ An edge list or adjacency list is a collection of unordered lists used to represent a graph in network analysis. Each list describes the set neighbors of a given node or vertex contained in the graph.

The first thing you need to do if you have a Windows device is to go to the following site and download sqlite-tools-win32-x86-3270200.zip. Once you unzip the folder, you should see the following three files:

Beginners Guide to SQLite

After that, initiating the SQLite command line is as simple as clicking on the sqlite3 application, which will make the command line pop up like this:

Beginners Guide to SQLite

Now you may notice that the default for SQLite is to use a transient in-memory database. The vast majority of the time you will not want to use that. Instead, we will create a new database using the dot command .open followed by the name you want to give to your new database file (See Below):

Beginners Guide to SQLite

Just like that, you have created a new database. Also, note that it is vital that you put .db after your desired database name to create a database file. We named the database Tweet_Data.db since I will be using tweets to populate the tables in the database.

With the database now created, we can proceed to create our table of tweets. We can do this in two ways. First, we can simply import a .csv file and make it a table. This is done by setting SQLite to csv mode using the .mode dot command followed by csv, and with the .import dot command that takes a file (in this case a csv located in the same folder, but it can be any csv in your computer if you type the full path) and a table name of your choice. You may also want to display the column headers using the .headers dot command followed by on. (See below)

Beginners Guide to SQLite

Once you have done this, you can use the .tables command to display all the available tables in a database file as well as the .schema command followed by a table name to display the table's structure. Furthermore, you can execute a simple query such as SELECT $ FROM Table_Name* to verify that the data is there.

Beginners Guide to SQLite

In addition to creating the tables by importing files such as csv's, you can also create tables using CREATE TABLE and then insert new rows using INSERT INTO. In the following example, you will see how you can create a table from scratch and insert two new rows.

Note: In the SQLite command line, if you want to keep writing the same query on the next line as I do in the example simply press Enter when you want a new line. The query will be evaluated after pressing Enter when there is a semicolon at the end.

Beginners Guide to SQLite

If at one point you decide that you want to export the results of a query to an external file such as a csv, you can also do this using the power of SQLite. For a clean export to a csv, you can use a process similar to that of importing. First, you use .header followed by on and .mode followed by csv. Then, you use the .output dot command followed by your desired csv file name and, finally, you execute the SQL query that you want to export and exit SQLite using the .exit command. Let's try to export to a csv the new table we created in the previous step.

Beginners Guide to SQLite

Here is the newly made csv with the data of Sample_Tweets_2 as well.

Beginners Guide to SQLite

Apart from creating and exporting tables, you can also use the UPDATE/SET and DROP TABLE SQL statements to update rows in a table or to drop a table all together. In the following, we example we will update the second tweet in the toy Sample_Tweets_2 table.

Beginners Guide to SQLite

Now let's say goodbye to the toy Sample_Tweets_2 table and delete it from the database file using DROP TABLE:

Beginners Guide to SQLite

Tiny Twitter Case Study

Now, you pretty much know the essentials of how to operate with a SQLite database and manipulate tables, all that is left for you to do is to derive useful information using SQL queries. For now, I'll show a couple of relatively simple queries that I performed during my Twitter social media analysis project. You can think of it as a tiny case study to illustrate a real-world application of a SQLite database.

Let's start with something simple; finding all the unique Twitter users' screen names in the Sample_Tweets table. In the project for which I used these tweets, it was essential for me to find all the unique users from whom I collected tweets since I needed to retrieve their followers in order to create the edge list that I mentioned at the start of this tutorial.

Beginners Guide to SQLite

26,860 unique users in the database. That is an excellent sample given that the topics that I was listening to were website localization and translation. Let's see how the top 20 users that had the most tweets in the sample using a slightly more complex SQL query (retweets are excluded):

Beginners Guide to SQLite

Would you look at that, the largest number of tweets belongs to a bot! As it turned out though, the last member from that list, UweMuegge, is quite the influencer according to the various network statistics that I calculated for the project. However, he was definitely not the most important.

Lastly, let's take a look at some of the tweets that UweMuegge wrote using another SQL Query:

Beginners Guide to SQLite

Two main topics it seems, translation job ads and translation conferences. It kind of makes sense why people in the field would follow him on Twitter.

Another thing that stands out from the above screenshot is that you run into one of the limitations of the SQLite command line interface. If you have large text fields, they won't display nicely, and they will be hard to read. That is why it is a good idea to use the DB Browser for SQLite in this instance.

Conclusions

In this tutorial, you have learned how to create SQLite databases and to manipulate tables using dot commands and SQL statements. In addition, you have seen a small real-world case study where I used a SQLite database to handle data from Twitter. That is a lot, so congrats!

It is worth noting that SQLite databases are most useful when combined with R and Python. You can manipulate SQLite databases through Python using the sqlite3 module or with R using RSQLite. In fact, the edge list of the Twitter users' followers that I have mentioned before, is stored in a SQLite database that I created using RSQLIte by appending each users' follower network to a table. In this step of the project, the use of SQLite was vital since things like power outages or Windows automatic updates can forcefully shut down your computer and make you start collecting follower networks from scratch. This can take around 4 weeks due to the Twitter's API rate limits, so starting from 0 if this happens is very undesirable. However, if you save the follower networks in a SQLite database as you go, even if your computer forcefully shuts down, you can start gathering again from the user after the last one you have in the database, which is a great time saver.

That is just one example of how SQLite databases can be a fantastic tool in the arsenal of a data scientist. I encourage you to further research SQLite databases and SQL beyond what is covered in this tutorial. Keep learning; the Sky is the limit!

sql database

What's new in Bootstrap 5 and when Bootstrap 5 release date?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Random Password Generator Online

HTML Color Picker online | HEX Color Picker | RGB Color Picker

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Writing a SQL Database from scratch in Go: A Database/SQL Driver

Writing a SQL Database from scratch in Go, we'll extend gosql to implement the database/sql driver interface. This will allow us to interact with gosql the same way we would interact with any other database.

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts|Simpliv

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts

How to become a SQL DBA: Database Administrator

Database Administration consists of everything essential for managing a database. This makes it accessible as per your need. The DBA is the person who maintains, backs up and ensures the data generation and consumption. You can access them by...

Migrate Entity Framework Core to SQL Database on Startup

This ASP.NET Core tutorial explains how to migrate Entity Framework Core to SQL Database on Startup. How to automatically migrate database changes from code in ASP.NET Core using Entity Framework Core from the Startup.cs file. Use the EF Core DB Context Service to automatically migrate database changes.