Databases are an important part of most modern software development. They serve as a repository for storing, organizing, manipulating, and retrieving data and information. Python, being a versatile programming language, offers several modules and libraries for working with databases. We will explore the fundamentals of database programming in Python, with a focus on using the SQLite database system, which is lightweight, easy to use, and part of the Python standard library.
Jump to:
Introduction to SQLite
Databases can be thought of as a structured collection of data that is organized in such a manner that applications can quickly select and retrieve specific pieces of information that are often related to one another (but not always). Databases are necessary for storing and managing data in applications, including small scripts and even large-scale, data-driven web applications.
SQLite is a C library that functions as a disk-based database. Unlike most other database management systems (DBMS), SQLite does not require a separate server process. In addition, SQLite provides access to the database using a nonstandard variant of the structured query language (SQL). It is a great option for embedded systems, testing, and small to medium-sized applications.
SQLite is a perfect database to start with for beginners due to its simplicity, easy configuration, and minimal setup requirements. It is a Serverless database, which means developers do not need to set up a separate server to use it. In addition, SQLite databases are stored in a single file; this makes them easy to share and move between different systems. Below, we walk through the basics of working with SQLite using Python, opening doors for more advanced database concepts down the line.
Read: 10 Best Python Certifications
How to Set Up the Dev Environment
Before we begin, we have to first make certain that Python is installed on your computer. To do so, open a terminal or command prompt and type:
python --version
If Python is not installed, you will need to download and install it from the official Python website. You can also learn how to install Python in our tutorial: How to Install Python.
Installing SQLite
Python comes with the sqlite3 module, which provides an interface to the SQLite database. Programmers do not need to install anything extra to work with SQLite in Python.
Connecting to a Database
As stated, the sqlite3 module is part of the Python standard library and provides a powerful set of tools for working with SQLite databases. Before we can use it, we must import the module into our Python scripts. We can do so in the following manner:
import sqlite3
Establishing a Database Connection in Python
In order to interact with an SQLite database, programmers need to first establish a database connection. This can be achieved using the connect function contained in the sqlite3 module. Note that if the noted database file does not exist, SQLite will create it.
# Connect to the named database (or, if it does not exist, create one)
conn = sqlite3.connect('sample.db')
Creating a Cursor in SQLite
In order to execute database queries and retrieve results in an SQLite database, you must first create a cursor object. This process occurs after you create your connection object.
# How to create a cursor object in order to execute SQL queries cursor = conn.cursor()
Creating a Table
In relational database management systems (RDBMS), data is organized into tables, each of which is made up of rows (horizontal) and columns (vertical). A table represents a specific concept, and columns define the attributes of that concept. For instance, a database might hold information about vehicles. The columns within that table might be labeled make, type, year, and model. The rows, meanwhile, would hold data points that aligned with each of those columns. For instance, Lincoln, car, 2023, Nautilus.
Read: PyCharm IDE Review
How to Structure Data with SQL
SQL is the standard language for operating within relational databases. SQL provides commands for data and database manipulation that include creating, retrieving, updating, and deleting data. To create a table, database developers use the CREATE TABLE statement.
Below, we create a simple table to store information about students, including their student_id, full_name, and age:
# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        student_id INTEGER PRIMARY KEY,
        full_name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
''')
# Commit our changes
conn.commit()
In the above code snippet, CREATE TABLE defines the table name, column names, and their respective data types. The PRIMARY KEY of the student_id column is used to ensure that each id value is unique, as primary values must always be unique.
If we wish to add data to a table, we can use the INSERT INTO statement. This statement lets developers specify which table and column(s) to insert data into.
Inserting Data into a Table
Below is an example of how to insert data into an SQLite database with the SQL command INSERT INTO:
# Insert data into our table
cursor.execute("INSERT INTO students (full_name, age) VALUES (?, ?)", ('Ron Doe', 49))
cursor.execute("INSERT INTO students (full_name, age) VALUES (?, ?)", ('Dana Doe', 49))
# Commit changes
conn.commit()
In this code example, we used parameterized queries to insert data into our students table. The values are tuples, which helps prevent SQL injection attacks, improves code readability, and is considered a best practice.
How to Query Data in SQLite
The SQL SELECT statement is used when we want to query data from a given table. It allows programmers to specify which columns they want to retrieve, filter rows (based on criteria), and sort any results.
How to Execute Database Queries in Python
To execute a query in Python, you can use the execute method on a cursor object, as shown in the example SQL statement:
# How to query data
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
The fetchall method in the code above retrieves every row from the last query that was executed. Once retrieved — or fetched — we can then iterate over our query results and display the data:
# Display the results of our query for row in rows: Â Â Â Â print(row)
Here, we print the data stored in the students table. We can customize the SELECT statement to retrieve specific columns if we want, or filter results based on conditions and criteria as well.
Updating and Deleting Data in SQLite
There are times when we will want to update existing records. On those occasions, we will use the UPDATE statement. If we want to delete records, we would use the DELETE FROM statement instead. To begin, we will update the age of our student with the name ‘Ron Doe’:
# Updating our data
cursor.execute("UPDATE students SET age=? WHERE name=?", (50, 'Ron Doe'))
# Commit our changes
conn.commit()
In this code, we updated Ron Doe’s age from 49 to 50.
But what if we wanted to delete a record? In the below example, we will delete the record for the student named Dana Doe:
# Deleting a record
cursor.execute("DELETE FROM students WHERE name=?", ('Dana Doe',))
# Commit our changes
conn.commit()
Best Practices for Working With Databases in Python
Below we highlight some best practices and tips for working with databases in Python, including:
- Use parameterized queries
- Use exception handling
- Close database connections
Use Parameterized Queries
Developers and database administrators should always use parameterized queries in order to prevent SQL injection attacks. Parameterized queries are more secure because they separate SQL code from data, reducing the risk of malicious actors. Here is an example of how to use parameterized queries:
# How to use parameterized queries
cursor.execute("INSERT INTO students (full_name, age) VALUES (?, ?)", ('Ron Die', 49))
Use Exception Handling
Programmers should always encase database operations in try-except blocks to handle possible errors gracefully. Some common exceptions include sqlite3.OperationalError and sqlite3.IntegrityError.
try:
    # Database operation example
except sqlite3.Error as e:
    print(f" The SQLite error reads: {e}")
Close Database Connections
Best database practices call for developers to always close database connections and cursors when you are finished working with databases. This makes sure that resources are released and pending changes are committed.
# How to close the cursor and database connection cursor.close() conn.close()
Final Thoughts on Python Database Basics
In this database programming and Python tutorial, we covered the basics of working with databases in Python using SQLite. We learned how to connect to a database, create tables, and insert, query, update, and delete data. We also discussed best practices for working with databases, which included using parameterized queries, handling exceptions, and closing database connections.
Want to learn how to work with Python and other database systems? Check out our tutorial on Python Database Programming with MongoDB.