An Introduction to SQLite with Python

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 sqlite3,

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 SELECT, UPDATE either DROP,

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:

identity Name surname
1 John Blacksmith
2 Lucy Jacob
3 Stephen Tailor

After 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 conn.close() way.

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,

records = cursor.execute("SELECT * FROM student")
for row in findrecords:
  print(row)

After executing this, we will see all the records stdout,

(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 UPDATE either DROP,

placeholders

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.

each ? inside command The variable represents a placeholder. This means that, if you call insert_command work with student_id=1, name="Jason" And surname="Green"The 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.

transactions

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.

Python’s sqlite3 module first starts a transaction Execution() And execute multiple() implemented INSERT, UPDATE, DELETEeither REPLACE Statement. This means two things:

  • 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 BEGIN,

Solution? handle transactions clearly,

How? using function call sqlite3.connect(dbName, isolation_level=None) Instead sqlite3.connect(dbName), by setting isolation_level To Nonewe emphasize 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()

conclusion

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 sqlite3,

Related Reading:

Leave a Reply