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:

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:

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):

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)

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.

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.

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.

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

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.

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

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.

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):

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:

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!

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

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

What’s new in HTML6

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

What is new features in Javascript ES2020 ECMAScript 2020

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

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

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts

Description
SQL developers are earning higher salary in IT industry, but, its not about writing queries its about understanding and applying the right query at right time and this course will let you understand complex SQL Statements in an easy way .

Moreover, This Course will teach you how to extract Data from Database and write complex queries to a database This course will focus on a wider scale by Covering Structure Query Language SQL concepts as a whole, whether Students work with MySQL, Microsoft SQL Server, Oracle Server, etc.

This course have 5 Chapters in which you will learn

Chapter 1 Fundamentals

Fundamentals
Building Blocks
Selecting Records from DB
Working with Arithmetic Expressions
Chapter 2 Conditioning Sorting and Operators

Logical Operators
Comparison Operators
Operator Precedence
Sorting Results
Chapter 3 Functions

Character Functions
Number Functions
Date Functions
Conversions
General Purpose Functions
Nesting Functions
Chapter 4 Grouping

Multiple Row Functions on a single Table
Multiple Row Functions on Many Table
Chapter 5 Joins

Understanding Primary Key
Understanding Foreign Key
Understanding Need of Joins
Cartesian Product
Equie Join Simple Join Self Join
Non Equie Join
Outer Join
Self Join
Course is Designed for College and University Students who want Solid SQL and Data Base Concepts in a short period of time.

Who this course is for:

Beginners
University or College students
Anyone who wants Solid SQL Concepts
Basic knowledge
No prior knowledge is required
PC or MAC
What will you learn
SQL Fundamentals
Understand Complex SQL Concepts in Easy way using daily life examples
Construct SQL Statements
Use SQL to retrieve data from database
Selecting Data From Database
Restricting and Sorting Data from DB
Grouping Data From DB
Construct SQL statements that will let them work with more than two tables
Use SQL Functions
Work with SQL Operators and find out precedence
Nesting in SQL
Joins
To continue:

Migrate Entity Framework Core to SQL Database on Startup

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.

Example code tested with ASP.NET Core 3.1

This is a super quick example of how to automatically migrate database changes from code in ASP.NET Core using Entity Framework Core from the Startup.cs file.

Solution

Register the EF Core DB Context as an ASP.NET Core Service

The Entity Framework Core DB Context is registered as a service with the ASP.NET Core Dependency Injection (DI) system from the ConfigureServices() method of the Startup.cs file.

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<DataContext>(x => x.UseSqlite("Data Source=LocalDatabase.db"));

    ...
}

Use the EF Core DB Context Service to automatically migrate database changes

An instance of the EF Core DB Context service is injected as a parameter into the Configure() method of the Startup.cs file, the DB Context instance is then used to apply any pending migrations to the database by calling the Database.Migrate() method.

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DataContext dataContext)
{
    // migrate any database changes on startup (includes initial db creation)
    dataContext.Database.Migrate();

    ...
}
Extra Info

While updating the tutorial from an EF Core InMemory database to SQLite I ran into some difficulties trying to automatically run database migrations from the Startup.cs. At first I was following a tutorial on the MS Docs website that called services.BuildServiceProvider().GetService<MyDatabaseContext>().Database.Migrate(); from within the ConfigureServices() method, but this resulted in the following warning in the console when I ran the application:

Startup.cs(39,13): warning ASP0000: Calling 'BuildServiceProvider' from application code results in an additional copy of singleton services being created.
Consider alternatives such as dependency injecting services as parameters to 'Configure'.
[/Users/jwatmore/Projects/aspnet-core-3-registration-login-api/WebApi.csproj]

A Practical Guide to Database Design and Access SQL|Simpliv

A Practical Guide to Database Design and Access SQL|Simpliv

Understanding the Concepts of Database design, develop normalized tables, analyse table designs to avoid redundancy and anomalies, write SQL statements to extract and manipulate requests and resolve many inherent problems associated with poor database design.

Description
In this course you will design and develop normalized tables, analyse table designs to avoid redundancy and anomalies, write SQL statements to extract and manipulate requests and resolve many inherent problems associated with poor database design. As you develop these skills, you will be exposed to LucidArt for diagramming ERD's; querySQL and QBE programming, to execute query statements; MS Access Workbench, for implementing business solutions.

Who this course is for:

IT staff and business employees who would like an understanding of how to organize, access and report data effectively and efficiently
Basic knowledge
An interest in computer information systems. Copy of MS Access 2016 software is optional

What will you learn
Build relational database solutions for home and business, including lots of practical examples, "best practice" guidelines, Access SQL design and implementation, templates that work
To continue: