Take a deep breath! This blog is everything you need about SQLite; we will go through sqlite3 with Python and the procedures for sqlite3 installation. We will also go through the advanced steps with sqlite3 to make some tasks easier.
But, before starting this blog, I recommend having some knowledge of SQL. If not, then it will be easier to understand SQLite with Python.
What is SQLite?
SQLite is a database, but an embedded one that is written in C. If you are familiar with MySQL or PostgreSQL, then you will get it right. These technologies use a client-server approach, which is installed as a server, and the client connects with it.
But on the other hand, SQLite is different. It's an embedded database, and it's included in a program as a library. The data is stored in a file with the extension ".db."
The benefits are great; you will get concurrent access so that more processes can access the database simultaneously. What are the uses of SQLite? Is it suitable for any apps?
Where Can We Use SQLite and Beneficial?
- SQLite can be the best choice if you need a serverless data storage solution, as SQLite is used in mobile OSs like iOS and Android.
- You can drop all data into an SQLite database rather than using bulkier CSV files.
- SQLite is very useful for storing app configuration data and is 35% faster than a file-based system.
Where Can't We Use SQLite?
- SQLite doesn't have multiuser functionalities like MySQL and PostgreSQL.
- SQLite is a file-based storage solution, not a service, so you cannot manage it as a process task. Also, you can't stop or start or manage its resource usage.
The Interface of Python to SQLite
SQLite, as we know from the start of this blog, is written in C and uses a C library. There are many interfaces written in many languages, including Python. Here you should know that sqlite3 has a module that provides an SQL interface and needs a version of SQLite 3.7.15.
Another good thing to know is that sqlite3 comes with Python installed, so you don't need to install it, and it will save time.
SQLite3: Getting Started with Python
It's time to GO CODE! It's the first part where we create the basic database, like the code below. It's the first step to creating and connecting.
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()
In line 1, you can see that we are importing the sqlite3 library, and then we are calling sqlite3.connect() inside the code block try/except for starting a connection to the database.
If all goes perfectly, the connection's instance will be the conn. We will print an exception if the try fails and the database connection is terminated. A connection object signifies each open SQLite database if you have read the official documentation.
You can see that we must command a connection to execute an SQL command each time.
Now, if we run the code, we should get the following output:
Database created!
Now, if we look in the python script folder, we can see a file named database.db, and sqlite3 creates that file.
Edit, Create, and Modify Records
Now, let's create a new table, and we will see the first entries and will run commands like SELECT, DROP or UPDATE.
Now let's execute a simple SQL statement to make the table, and we are adding a students list table:
We will add the below code after the print Database created!.
# Create operation
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 and Read operation
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 are creating a table now and calling the cursor.execute()method to run a single SQL statement.
Later we will do INSERT for all rows we want to put in. Later we will call conn.commit() to obligate the remaining transaction to the database. And at last, we will close the connection to the database by using the method conn.close().
Now, let's start querying our database. To save the results of this, we will need a variable, so we will save the results by a variable called records.
records = cursor.execute("SELECT * FROM student")
for row in findrecords:
print(row)
We will see a record like the one below:
(1, 'John', 'Smith')
(2, 'Lucy', 'Jacobs')
(3, 'Stephan', 'Jacobs')
Now you should note that the cursor.execute() methods must be run.
Placeholders
You should note that the method curser.execute() will need a string as an argument.
In the above code, we insert data into our database, but what if the data is variable? For this reason, sqlite3 has some fancy placeholders that will help us use a parameter substitution. It will help make inserting variables into a query very easy.
See the below code example to learn more.
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()
The method insert_command() has four arguments:
- The first one is a connection instance
- The other three are in the SQL command
You can see that each "?" means a placeholder inside the command. It means if you call the insert_command function with student_id=1, the insert statement with name='John' and surname='Smith' will become INSERT INTO student VALUES(1, 'John', 'Green').
From now on, we must add insert_command() with the required parameters.
Transactions
They are a sequence of operations that are performed in a database. They are treated as a single unit logically. The benefit of transactions is data integrity. The transaction is helpful here if there is more data on the table.
The module sqlite3, before the execute() and executemany() run INSERT, starts the transaction for UPDATE, REPLACE, and DELETE.
The problem is that we now have to call the commit() method. If we call connection.close() without commit(), the changes we make in the transaction will be lost. Also, another problem is we need help to open transactions in the same process as BEGIN.
The Solution
We will use the function called sqlite3.connect(dbName, isolation_level=None), but not using sqlite3.connect(dbName). We will set isolation_level to none. Now, let's rewrite the code like the one below for the usage 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 operation
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 and Read operation
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()
The Verdict: SQLite3 with Python
Cool, right? We have gone through SQLite with Python. It's relatively easy to use Python with SQLite and connect to the database. But this scenario is easy, and sometimes you may face challenging scenarios and a crowded database. Regular practice will only perfect it.
You can contact us or get help from our trending blogs if you have more queries. Thanks for reading. Comment down what next topic you would love to read.
Top comments (0)