An Introduction to SQLite with Python-Python Tutorial-php.cn

An Introduction to SQLite with Python

This article will explore in-depth SQLite database and its use with Python. We will learn how to manipulate SQLite databases through Python's sqlite3 library and finally explore some of the advanced features provided by sqlite3 to simplify our work.

Note: Before you start, it is best to be familiar with SQL. If you are not familiar with it, you can refer to Simply SQL learning.

Core Points

SQLite is a lightweight, file-based associated database management system that is commonly used in Python applications because of its simplicity and ease of configuration. It supports concurrent access, allowing multiple processes or threads to access the same database. However, it lacks multi-user capabilities and is not managed as a process like other database technologies such as MySQL or PostgreSQL.
The sqlite3 module in Python provides SQLite with SQLite and is pre-installed with Python. It allows users to create databases, connect to databases, create tables, insert data, and execute SQL commands. The module also supports placeholders, allowing parameter replacement in SQL commands, making it easier to insert variables into queries.
Transactions in SQLite are a series of database operations that are treated as a unit to ensure data integrity. Python's sqlite3 module starts a transaction before executing INSERT, UPDATE, DELETE, or REPLACE statements. The user must call the commit() method to save changes made during the transaction and can explicitly handle the transaction by setting isolation_level to None when connecting to the database.

What is SQLite?

SQLite's motto is: "Small, fast, and reliable. Choose two of them."

SQLite is an embedded database library written in C language. You may be familiar with other database technologies, such as MySQL or PostgreSQL. These techniques use the client-server method: the database is installed as a server and then connect to it using the client. SQLite is different: it is called an embedded database because it is included in the program as a library. All data is stored in a file—usually with .db extension—and you can use functions to run SQL statements or do any other action on the database.

File-based storage solutions also provide concurrent access, which means multiple processes or threads can access the same database. So, what is SQLite for? Is it suitable for any type of application?

SQLite performs well in the following situations:

Since it is included in most mobile operating systems such as Android and iOS, SQLite may be the perfect choice if you need a standalone, serverless data storage solution.
Compared to using large 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 applications. In fact, SQLite is 35% faster than file-based systems such as configuration files.

On the other hand, what are the reasons why they don’t choose SQLite?

Unlike MySQL or PostgreSQL, SQLite lacks multi-user capabilities.
SQLite is still a file-based database, not a service. You cannot manage it as a process, nor can you start, stop it, or manage resource usage.

Python's SQLite interface

As I mentioned in the introduction, SQLite is a C library. However, there are many languages that write interfaces, including Python. The sqlite3 module provides an SQL interface and requires at least SQLite 3.7.15.

Amazingly, sqlite3 is available with Python, so you don't need to install anything.

Get to use sqlite3

It's time to write code! In the first part, we will create a basic database. The first thing to do is create a database and connect to it:

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

Copy after login

In line 1, we import the sqlite3 library. Then, in a try/except code block, we call sqlite3.connect() to initialize the connection to the database. If everything goes well, conn will be an instance of the Connection object. If try fails, we will print the received exception and close the connection to the database. As stated in the official documentation, each open SQLite database is represented by a Connection object. Every time we have to execute SQL commands, the Connection object has a method called cursor(). In database technology, cursors are a control structure that allows traversing records in a database.

Now, if we execute this code, we should get the following output:

<code>> Database created!</code>

Copy after login

If we look at the folder where the Python script is located, we should see a new file named database.db. This file is automatically created by sqlite3.

Create, read and modify records

At this point, we are ready to create a new table, add the first entry and execute SQL commands such as SELECT, UPDATE, or DROP.

To create a table, we only need to execute a simple SQL statement. In this example, we will create a students table with the following data:

id	name	surname
1	John	Smith
2	Lucy	Jacobs
3	Stephan	Taylor

After the print("Database created!") line, add the following:

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

Copy after login

We create a table and call the cursor.execute() method, which is used when we want to execute a single SQL statement.

Then, we perform an INSERT operation for each row we want to add. After all changes are completed, we call conn.commit() to submit the pending transaction to the database. If the commit() method is not called, any pending changes to the database will be lost. Finally, we close the connection to the database by calling the conn.close() method.

Okay, now let's query our database! We need a variable to hold the result of the query, so let's save the result of cursor.execute() to a variable named records:

<code>> Database created!</code>

Copy after login

After doing this, we will see all records output to standard output:

# 创建操作
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()

Copy after login

At this point, you may have noticed that in the cursor.execute() method, we placed the SQL commands that must be executed. If we want to execute another SQL command (such as UPDATE or DROP), the Python syntax will not change anything.

Placeholder

The

cursor.execute() method requires a string as an argument. In the previous section, we saw how to insert data into our database, but everything is hardcoded. What if we need to store the contents in variables into the database? For this purpose, sqlite3 has some clever features called placeholders. Placeholders allow us to use parameter replacement, which will make it easier to insert variables into queries.

Let's look at this example:

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

Copy after login

We created a method called insert_command(). This method accepts four parameters: the first parameter is an Connection instance, and the other three will be used in our SQL commands.

Each

in the command? variable represents a placeholder. This means that if you call the student_id=1, name='Jason' and surname='Green', the INSERT statement will become insert_command. INSERT INTO student VALUES(1, 'Jason', 'Green')

When we call the

function, we pass our command and all variables that will be replaced with placeholders. From now on, every time we need to insert a row into the execute() table, we will call the student method with the required parameters. insert_command()

Transactions

I will quickly review the importance of it even if you are not new to transaction definition. A transaction is a series of operations performed on a database and is logically considered a unit.

The most important advantage of a transaction is to ensure data integrity. In the example we introduced above, it may not be useful, but the transaction does have an impact when we process more data stored in multiple tables.

Python's sqlite3 module starts a transaction before execute() and executemany() execute INSERT, UPDATE, DELETE, or REPLACE statements. This means two things:

We must pay attention to calling the commit() method. If we call commit() without executing Connection.close(), all changes we make during the transaction will be lost.
We cannot open transactions with BEGIN in the same process.

Solution? Process transactions explicitly.

How? By using function calls sqlite3.connect(dbName, isolation_level=None) instead of sqlite3.connect(dbName). By setting isolation_level to None, we force sqlite3 to never open the transaction implicitly.

The following code is a rewrite of the previous code, but explicitly uses the transaction:

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

Copy after login

Conclusion

I hope you have a good understanding of what SQLite is, how to use it for your Python project, and how some of its advanced features work. Managing transactions explicitly can be a bit tricky at first, but it can certainly help you make the most of it. sqlite3