In this article, we’ll kick the tires of SQLite. We will learn how to use SQLite through a library called Python sqlite3, Finally, we’ll explore some of the more advanced features provided by
sqlite3 To make our work easy.
Note: Before we begin, it is good to be familiar with SQL. If you’re not, you might want to check just sql.
What is SQLite?
motto of sqlite its small. Fast. reliable. Choose any three.
SQLite is an embedded database library written in C. You may be familiar with other database technologies such as MySQL or PostgreSQL. These use a client-server approach: the database is set up as a server, and then clients are used to connect to it. SQLite is different: it is known as a built-in Database, because it is included in a program as a library. All data is stored in a single file – usually with a
.db Extensions – and you have functions that allow you to run SQL statements or perform any other operation on the database.
Also offers file-based storage solutions concurrent access, which means that multiple processes or threads can access the same database. Ok, so what are the uses of SQLite? Is it suitable for any type of application?
Well, there are a few cases where SQLite excels:
Due to its inclusion on most mobile operating systems such as Android and iOS, SQLite may be an ideal choice if you want to Spartan And serverless data storage solution.
Instead of using huge csv files, you can take advantage of the power of SQL And put all your data into a single SQLite database.
SQLite can be used to store configuration data for your application. In fact, SQLite is 35% faster More like a configuration file than a file-based system.
On the other hand, what are some reasons not to choose SQLite?
Unlike MySQL or PostgreSQL, SQLite lacks multi-user functionalities.
SQLite is still a file-based data storage solution, not a service. You can’t manage it as a process, you can’t start or stop it, or manage resource usage.
Python interface for SQLite
As I said in the introduction, SQLite is a C library. However, interfaces have been written in many languages, including Python.
sqlite3 The module provides a SQL interface and requires at least SQLite 3.7.15.
the amazing thing is
sqlite3 Comes with Python, so you don’t need to install anything.
getting started with sqlite3
It’s time to code! In this first part, we will create a basic database. The first thing to do is create and connect to a database:
import sqlite3 dbName = 'database.db' try: conn = sqlite3.connect(dbName) cursor = conn.cursor() print("Database created!") except Exception as e: print("Something bad happened: ", e) if conn: conn.close()
On line 1, we import
sqlite3 Library. then, in a
try/except code block, we call
sqlite3.connect() To initiate a connection to the database. if all goes well,
conn would be an example of
Connection Thing. If
try fails, we print the exception received and the connection to the database is closed. As stated in the official documentation, each open SQLite database is represented by a
Connection Thing. Every time we have to execute a SQL command, the
Connection object has a method called
cursor(), In database technologies, a cursor is a control structure that enables traversal over records in a database.
Now, if we execute this code, we should get the following output:
> Database created!
If we look at the folder where our python script is, we should see a new file
database.db, This file was automatically created by
Create, read and modify records
At this point, we are ready to create a new table, add the first entries and execute SQL commands like
To create a table, we just need to execute a simple SQL statement. In this example, we will create a Student table that will contain the following data:
print("Database created!") line, add this:
create_query = '''CREATE TABLE IF NOT EXISTS student( id INTEGER PRIMARY KEY, name TEXT NOT NULL, surname TEXT NOT NULL); ''' cursor.execute(create_query) print("Table created!") cursor.execute("INSERT INTO student VALUES (1, 'John', 'Smith')") print("Insert #1 done!") cursor.execute("INSERT INTO student VALUES (2, 'Lucy', 'Jacobs')") print("Insert #2 done!") cursor.execute("INSERT INTO student VALUES (3, 'Stephan', 'Taylor')") print("Insert #3 done!") conn.commit() conn.close()
We create a table and call
cursor.execute() method, which is used when we want to execute a single SQL statement.
then, we do a
INSERT For each row we want to add. After all our changes are made, we call
conn.commit() To commit pending transactions to the database. without calling
commit() method, any pending changes to the database will be lost. Finally, we close the connection to the database by calling
Ok, now let’s query our database! We will need a variable to save the results of our query, so let’s save the result of
cursor.execute() for a variable named
records = cursor.execute("SELECT * FROM student") for row in findrecords: print(row)
After executing this, we will see all the records
(1, 'John', 'Smith') (2, 'Lucy', 'Jacobs') (3, 'Stephan', 'Taylor')
At this point, you may have noticed that, inside
cursor.execute() method, we put the SQL command that should be executed. Nothing changes in Python syntax if we want to execute any other SQL command
cursor.execute() The method requires a string as an argument. In the previous section, we looked at how to insert data into our database, but everything was hard-coded. what if we need to store something in a variable in the database? because of this reason,
sqlite3 There is some fancy thing called placeholder. placeholder Allow us to use parameter substitution, which will make converting a variable to a query much easier.
Let’s look at this example:
def insert_command(conn, student_id, name, surname): command = 'INSERT INTO student VALUES (?, ?, ?)' cur = conn.cursor() cur.execute(command, (student_id, name, surname, )) conn.commit()
we create a method called
insert_command(), This method takes four arguments: the first one is
Connection example, and the other three will be used in our SQL command.
command The variable represents a placeholder. This means that, if you call
insert_command work with
INSERT the statement will become
INSERT INTO student VALUES(1, 'Jason', 'Green'),
when we call
execute() function, we pass our command and all the variables that will be substituted as placeholders. From now on, every time we need to insert a row into the Student table, we call
insert_command() method with the required parameters.
Even if you are not new to the definition of transaction, let me give a quick recap of its importance. a transactions A sequence is a sequence of operations performed on a database that are logically treated as a single unit.
The most important advantage of transactions is to ensure data integrity. It may be useless in the example above, but when we deal with more data stored in multiple tables, transactions make a difference.
- we should care to call
commit()way. if we call
Connection.close()without doing a
commit()All the changes made by us during the transaction will be lost.
- we can’t open transaction using same process
Solution? handle transactions clearly,
How? using function call
sqlite3.connect(dbName, isolation_level=None) Instead
sqlite3.connect(dbName), by setting
sqlite3 Never open a transaction implicitly.
The following code is a rewrite of the previous code, but with explicit use of transactions:
import sqlite3 dbName = 'database.db' def insert_command(conn, student_id, name, surname): command = 'INSERT INTO student VALUES (?, ?, ?)' cur = conn.cursor() cur.execute("BEGIN") try: cur.execute(command, (student_id, name, surname, )) cur.execute("COMMIT") except conn.Error as e: print("Got an error: ", e) print("Aborting...") cur.execute("ROLLBACK") conn = sqlite3.connect(dbName, isolation_level=None) cursor = conn.cursor() print("Database created!") create_query = '''CREATE TABLE IF NOT EXISTS student( id INTEGER PRIMARY KEY, name TEXT NOT NULL, surname TEXT NOT NULL); ''' cursor.execute(create_query) print("Table created!") insert_command(conn , 1, 'John', 'Smith') insert_command(conn , 2, 'Lucy', 'Jacobs') insert_command(conn , 3, 'Stephan', 'Taylor') insert_command(conn , 4, 'Joseph', 'Random') findRecords = cursor.execute("SELECT * FROM student") for row in findRecords: print(row) conn.close()
I hope you now have a better understanding of what SQLite is, how you can use it for your Python projects, and how some of its advanced features work. Clear management of transactions can be a bit tricky at first, but it can definitely help you make the most