Developer.com content and product recommendations are editorially independent. We may earn money when you click on links to our partners. Find out more.
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, as a versatile programming language, offers several modules and libraries for working with databases. We’ll explore the basics 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 structured collections of data that are organized in such a way that applications can quickly select and retrieve specific pieces of information that are often (but not always) related to each other. Databases are needed to store and manage data in applications, including small scripts and even large data-driven web applications.
SQLite is a C library that functions as a disk database. Unlike most other database management systems (DBMS), SQLite does not require a separate server process. In addition, SQLite allows database access using a non-standard variant of Structured Query Language (SQL). It’s a great option for embedded systems, testing, and small to medium applications.
SQLite is the perfect starting database for beginners because of its simplicity, easy configuration, and minimal setup requirements. It’s a serverless database, meaning developers don’t need to set up a separate server to use it. Additionally, 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 the door to more advanced database concepts.
Read: Top 10 Python Certifications
How to set up a development environment
Before we begin, we first need to make sure that Python is installed on your computer. To do this, 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 guide: How to install Python.
Installing SQLite
Python comes with sqlite3 module, which provides an interface to the SQLite database. Developers do not need to install anything extra to work with SQLite in Python.
Connecting to a database
As stated, 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 need to import the module into our Python scripts. We can do this in the following way:
import sqlite3
Connecting to a database in Python
In order to communicate with a SQLite database, developers must first establish a connection to the database. This can be achieved using Connect function contained in sqlite3 module. Note that if the specified 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 cursors in SQLite
In order to perform database queries and retrieve the results in an SQLite database, you must first create a cursor object. This process is happening after 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 consists of rows (horizontally) and columns (vertically). The table represents a certain concept, and the columns define the attributes of that concept. For example, the database may contain information about vehicles. Columns within that table can be marked to make, type, yearand model. Meanwhile, the rows would contain the data points that are aligned with each of those columns. For example, Lincoln, car, in 2023, Nautilus.
Read: PyCharm IDE Review
How to structure data using SQL
SQL is the standard language for working within relational databases. SQL provides data and database manipulation commands that include creating, retrieving, updating, and deleting data. To create a table, database developers use CREATING A TABLE statement.
Below we create a simple table to store student data, including their own student card, full nameand 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, CREATING A TABLE defines the table name, column names and their corresponding data types. The MAIN KEY from student card column is used to ensure that each ID value is unique, as primary values must always be unique.
If we want to add data to the table, we can use INSERT IN statement. This statement allows developers to specify which table and columns to insert data into.
Inserting data into a table
Below is an example of how to insert data into a SQLite database using a SQL statement INSERT IN:
# 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
SQL CHOOSE command is used when we want to query data from a given table. It allows developers to specify which columns they want to retrieve, filter rows (based on criteria) and sort all results.
How to query a database in Python
To perform a query in Python, you can use execute method on the 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 each row from the last query that was executed. Once they’re fetched — or fetched — we can 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 student table. We can customize CHOOSE command to retrieve specific columns if desired or filter results based on conditions and criteria.
Updating and deleting data in SQLite
There are times when we will want to update existing records. We will use it on those occasions UPDATE statement. If we want to delete records, we will use DELETE FROM statement instead. To start, we’ll update the age of our student named ‘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 have updated Ron Doe age from 49 to 50 years.
But what if we want to delete the record? In the example below, we will delete the record for the specified student 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 the database connections
Use parameterized queries
Developers and database administrators should always use parameterized queries to prevent SQL injection attacks. Parameterized queries are more secure because they separate the SQL code from the data, reducing the risk of malicious actors. Here’s 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
Developers should always close database operations try-except blocks for graceful handling of possible errors. 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
Database best practices require developers to always close database connections and pointers when finished working with databases. This ensures that resources are freed 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 guide to database and Python programming, we’ve covered the basics of working with databases in Python using SQLite. We learned how to connect to the database, create tables and insert, query, update and delete data. We also discussed best practices for working with databases, including using parameterized queries, handling exceptions, and closing database connections.
Want to learn how to work with Python and other database systems? See our tutorial on Python database programming with MongoDB.