In this tutorial you will learn how to use the SQLite database management system with Python. You will learn how to use SQLite, SQL queries, RDBMS and more of this cool stuff!
Related course:Master SQL Databases with PythonPython sqlite3 package SQLite is a disk based lightweight database. It does not require a separate server unlike MySQL. The data stored is persistent and therefore can be accessed during subsequent application runs in a timeline.
To create an SQLite Database in Python, use the sqlite3 inbuilt module. The sqlite3 module provides an API through which you can create the database. It is compliant with Python Database API. It does not require any external libraries. SQLite3 (what we'll just call SQLite) is part of the standard Python 3 package, so you won't need to install anything. If you're not running Python 3, check out this link to get started. If you're new to SQL or want a refresher, check out our complete Beginner's Guide to SQL here and download a ton of free content!
Pyton Database
Python Database.Data is retrieved from a database system using the SQL language. Data is everywhere and software applications use that. Data is either in memory, files or databases.
Python has bindings for many database systems including MySQL, Postregsql, Oracle, Microsoft SQL Server and Maria DB.
One of these database management systems (DBMS) is called SQLite. SQLite was created in the year 2000 and is one of the many management systems in the database zoo.
SQL is a special-purpose programming language designed for managing data held in a databases. The language has been around since 1986 and is worth learning. The is an old funny video about SQL
SQLite
SQLite, a relational database management system. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.
It is a self-contained, serverless, zero-configuration, transactional SQL database engine. The SQLite project is sponsored by Bloomberg and Mozilla.
Install SQLite:
Use this command to install SQLite:Verify if it is correctly installed. Copy this program and save it as test1.py
Execute with:
It should output:
What did the script above do?
The script connected to a new database called test.db with this line:
It then queries the database management system with the command
Sqlite3 Python Pip
which in turn returned its version number. That line is known as an SQL query.
Related course:
The script connected to a new database called test.db with this line:
It then queries the database management system with the command
Sqlite3 Python Pip
which in turn returned its version number. That line is known as an SQL query.
Related course:Master SQL Databases with Python
SQL Create and Insert
The script below will store data into a new database called user.db
SQLite is a database management system that uses tables. These tables can have relations with other tables: it's called relational database management system or RDBMS. The table defines the structure of the data and can hold the data. A database can hold many different tables. The table gets created using the command:
We add records into the table with these commands:
The first value is the ID. The second value is the name. Once we run the script the data gets inserted into the database table Users:
SQL Table
SQLite query data
We can explore the database using two methods: the command line and a graphical interface.
From console: To explore using the command line type these commands:
This will output the data in the table Users.
From GUI: If you want to use a GUI instead, there is a lot of choice. Personally I picked sqllite-man but there are many others. We install using:
We start the application sqliteman. A gui pops up.
sqlitemanPress File > Open > user.db. It appears like not much has changed, do not worry, this is just the user interface. On the left is a small tree view, press Tables > users. The full table including all records will be showing now.
sqlitemanThis GUI can be used to modify the records (data) in the table and to add new tables.
Related course:Master SQL Databases with PythonThe SQL database query language
SQL has many commands to interact with the database. You can try the commands below from the command line or from the GUI:
We can use those queries in a Python program:
This will output all data in the Users table from the database:
Creating a user information database
We can structure our data across multiple tables. This keeps our data structured, fast and organized. If we would have a single table to store everything, we would quickly have a big chaotic mess. What we will do is create multiple tables and use them in a combination. We create two tables:
Users:
SQL TableJobs:
SQL TableTo create these tables, you can do that by hand in the GUI or use the script below:
The jobs table has an extra parameter, Uid. We use that to connect the two tables in an SQL query:
You can incorporate that SQL query in a Python script:
It should output:
You may like: Databases and data analysis
To explore SQLite along with Python, which is a user-friendly and no-nonsense language, we are going to build a simple tic-tac-toe game. So buckle up and get your machines ready!
Introduction to SQLite and Embedded Databases
SQLite is a small, self-contained, client/embedded database written in the C Programming language and can be summarized using the following points:
- Application library.
- Directly writes to disk.
- Cross-platform, stable, and the most widely deployed and used database engine.
- Zero configuration.
- Automatic recovery.
- Public domain license.
In this article, we are going to see many of these features in action. Features such as data replication, network access, etc., are intentionally absent from SQLite since it was built for a much more lightweight use case. SQLite databases are very useful in constrained environments such as mobile devices, lightweight desktop apps, IoT devices, and games.
Setting Up SQLite with Python
Let's get our learning environment up and running by installing:
Sqlite3 Python
- Python 3;
- any IDE for Python coding—I recommend PyCharm;
- Arctype SQL Client;
- and SQLite tools installed on your OS for CLI (Mac OS comes with it by default).
Library Setup and Database Creation
Sqlite3 comes packaged with Python by default and there is no need to install any additional libraries. Let's go ahead and import Sqlite3 and then create our database:
That's it. The database is now created and we can create tables and insert data now. Isn't this a breeze when compared to setting up a full-blown RDBMS?
Where Are SQLite Databases Stored?
Once you run the above application, a sqlite.db file is automatically created at the project root level. As we will see later, this file will be re-used whenever the application is started. We can customize the location of this file by just connecting to the database with its path.
This will create the sqlite.db file inside the data folder. Keep in mind that the directory data needs to be created beforehand. The file extension .db is also optional—SQLite will just create a binary file with the name we provide it with.
Configuring and Managing an SQLite Database
As we just saw, there is no configuration required to get our database set up. Since it is an application library, there is no server and the database comes up and goes down with the connection initiated by the application running behind it.
Connecting to an SQLite Database from the Command Line
SQLite (version 3) comes pre-installed on Mac OS operating systems. For other platforms, we can download the CLI and other tools from the SQLite website itself. The command line can be invoked by typing the command sqlite3.
Notice that I have connected directly to the database that I used from the Python script. We can do so by using sqlite3 /path/to/file.
Creating Sample Tables and Running Basic Queries
Let's create a table for testing purposes.
And then insert some rows into the table.
We can see the records by doing a simple select * statement.
There are a ton of helper functions that SQLite provides to learn more about tables and the schema. Below are some of the functions.
.tables gives me information about all of the tables. .schema gives the information about a specific table. pragma table_info is a function that gives more information about the table (i.e. the data type and column name).
Now, let's connect to this database using our Python script and try to read these tables.
Above code will print.
An Example Tic-Tac-Toe Game Using Python and SQLite
Let's consider a real-world use case—games. Games in mobile applications make heavy use of SQLite for storing their game status and then sync to a backend server periodically/based on a user trigger. In this example, we will see a Tic-Tac-Toe game developed in Python that uses SQLite to store game data and player information. We can then query this database offline to retrieve game-related information.
Database Table Design for Tic-Tac-Toe
Before we attempt to write the game, let's create our database ER diagram that is going to represent our tables.
First, we are going to create our Player table which has the id, name, and created_date which is going to be used to store the player data. Next, we are going to create our Game table which will have the player details, game status, and winner details. The Entity-Relationship diagram for these tables is given below.
We could further normalize this by introducing a game status entity, but we are looking for a simple model to understand SQLite rather than a perfect design. Based on this spec, tables can be created accordingly:
The database is now set up, so let's code our game!
Coding the Tic-Tac-Toe Game in Python
Before starting to write everything out in Python, let's first determine our game's logic. For this simple example, I would recommend something like this:
We are going to a simple two-dimensional array to hold the game board. Instead of walking through every element of the code, I am going to paste the whole code below and give a high-level walkthrough since the code is pretty self-explanatory.
The game is nothing fancy. As you can see, the game Opera turbo. method is where the core logic begins.
Go ahead and try it out. Below is a sample game output on the console.
You can put the above code in a separate game.py file for clarity. Now to the next part, integrating this game with our database.
SQLite Integration
Sqlite3 Python Tutorial
Now, we have to make the user entries whenever the users have been created and make game entries when the game is won or a draw. Before we create our queries, let's make the actual Data Access layer methods (i.e. creating a player entry and a game entry).
Now, we can use these simple methods from our game:
We can do a similar code for storing game status during draw as well. Now we can play the game and the data should be recorded in our tables.
Importing and Exporting Data from SQLite
Having now played a few test games, it's now time to examine the data. As everyone knows, Arctype makes querying, analyzing, and visualizing data better than ever, so before we continue, we need to learn about importing and exporting data from SQLite. Data can be exported/imported either at the table or database level. The table level is typically used to export to other databases/applications and the database level is typically used for backup.
Importing and Exporting SQLite Tables
Apart from programmatic integration, once can easily export data from an SQLite database using data export facility. The most commonly used db agnostic standard is a CSV format. Let's export our game table.
game.csv would contain the below content:
This is very similar to other database systems.
Import is quite easy, first we need to create a .sql file with the below content.
And then from the command line,
Importing and Exporting Databases
Database backup is actually pretty easy, just copy the SQLite file to back up system. But this method is usually difficult since users/application might be running simultaneously and the data is stored in a binary format. To do a point-in-time snapshot/human readable snapshot when the database is running/being used then we can dump the database content to a file format of our choice.
The dump.sql content will look like below.
Depending on what data is there, the above SQL file content might change but the backup/format is just plain SQL.
If we are not bothered about human readability then we can just dump it in a native format like below.
Import for binary files are quite similar -
Import for SQL file -
Querying Our SQLite Game Data in Arctype
Our SQLite data is now ready to be loaded into Arctype. This can be achieved in one of two ways—by creating and populating new tables using the SQLite-generated database file or by importing CSV data into existing tables. First, let's create a new SQLite database by adding a new connection:
Next, you can choose a name for your new database. For this example, tictactoe seems fitting enough:
Now, it's time to import our data.
Importing SQLite Tables into Arctype
We can create and populate tables in our new SQLite database using the SQL table dump from above. Simply copy and paste the contents of the dump.sql file into a query, remove the manual BEGIN TRANSACTION; and COMMIT; lines (Arctype automatically wraps all of your statements in transactions) and click 'Run':
And that's it! Your tables should be successfully created and filled with data. This method is fairly simple, but is only recommended for your initial data import—deleting and re-creating tables with increasingly large datasets is unsustainable, so ultimately, you'll want to simply import new data into your existing tables.
Importing .CSV Data into Existing Arctype SQLite Tables
Let's say we have already created our game and player tables, rather than dropping them and creating new tables using the SQL from our dump.sql file, we can instead simply import the table data from our CSV files. As you remember from above, we exported our tables into game.csv and player.csv respectively. First, select the table into which you want to import CSV data, and then click the 'Import CSV' button:
Then, simply review the data preview and click 'Accept' if everything looks correct:
Viewing and Querying Game Data in Arctype
Now that all of our data has been imported into Arctype, you can view it by simply selecting the table of your choosing. Here is our game table:
Quite similarly, this is what our player table looks like:
Now for the fun part, let's select players who have won the most games. Simply open a new tab and select 'query':
Now, let's run this query:
Your results should look something like this:
As you can see, in terms of its querying capabilities, SQLite is as powerful as any SQL database. We can run more queries like:
- Which players were involved in most games that ended up in a draw?
- Which players lost the most?
- Which player got scores more than the average wins?
What Are The Trade-Offs of Using SQLite?
SQLite is often misunderstood and not properly utilized. Yes, it comes under the category of client/embedded database but is essentially trying to solve a very different problem. So, comparing SQLite with MySQL/PostgreSQL/Other RDBMS is definitely not the right way. In most environments, SQLite actually works in tandem with such client-server databases as we just saw in the examples above.
In some situations, SQLite can be used without any second thoughts, while in others, a more careful analysis of the project requirements may be necessary. The SQLite webpage on when to use SQLite is pretty comprehensive. Below are some of the key highlights from that page:
- SQLite only supports one writer at a time per database file. So it is not suited for heavy concurrent writes. Readers can be N where N is decided by various factors such as OS, hardware, and other apps running on the system.
- You will first run into hardware issues before running into database size issues with SQLite as it supports 281TB of data storage. Good luck hitting that limit!
- SQLite can handle traffic very well—in fact, the website https://www.sqlite.org/ itself is hosted on SQLite and it handles close to 400K to 500K hits per day. So unless you are on a fairly high traffic website (queries per second/qps measure), then SQLite should serve you very well.
The key thing with any software is to use the right tool for the right use case. At the end of the day, that's what Software craftsmanship/Software Engineering is all about.
Closing Thoughts
Let's pause and observe how widely SQLite is used.
- Mac OSX comes with SQLite by default.
- Python 3 comes with SQLite by default.
- Windows 10 uses SQLite internally—it cannot operate without it.
- Built into PHP and Ruby as well.
- There are tons of others places where it is used. The well-known users page gives a good list.
SQLite is literally everywhere. It is very widely used and extremely well tested and almost a drop-in replacement for fopen in Linux. Learning it and befriending SQLite has a lot of advantages and it is a must-have tool for every programmer. Hopefully, this article along with its examples gave a good introduction to SQLite. So go ahead and play with it and before jumping to the conclusion of using a client-server RDBMS, give SQLite a try and you will be surprised at how it simplifies the tech stack and operations around it.
