In order to access and use a database in Python, you first need to decide which type of database to use and get the associated Python module. These modules might have different syntax for how to interact with them. Because of this Python offers a common specification for these systems to follow so that programmers can manage their own databases. This is known as the Python Database API. The Python Database API provides an abstraction layer that allows code to be more transferable across different types of databases. The great thing about this approach is that once you know how to interact with a database in Python using one type of database, the others will be almost identical to use.
Python Database API
There are some key points to consider regarding Python database modules and the Python Database API.
- A database module must implement a connect() function that returns a connection object.
- The connection object represents a connection to the given database.
connection = connect(params...)
The connection object offers several methods to tap into such as commit(), rollback(), and close().
connection = connect(params...) connection.commit() connection.rollback() connection.close()
Python Database Cursor
The connection object also provides access to what’s known as a cursor. The cursor provides the ability to actually work with the database and is how commands like insert and update are executed. Some of the common methods of the cursor object are as follows:
cursor.execute(params) – executes the given database operation (query or command)
cursor.executemany(params) – prepares a database operation (query or command) and executes it against all parameter sequences or mappings found in the sequence
cursor.fetchone(params) – returns a single record or None if no more rows are available.
cursor.fetchmany(size) – returns the number of rows specified by size argument.
cursor.fetchall() – fetches all the rows of a query result.
A great relational database management system to get started with when using Python is called SQLite. The sqlite3 module provides an interface that adheres to the Python database API specification. SQLite is super easy to set up and work with. SQLite does not need a server to run, it’s just a simple file. This makes accessing and manipulating data in the database very fast. Since SQLite is disk-based, there’s no need to install any SQLite drivers before using it. SQLite is also self-contained meaning it does not require external libraries or much support from the operating system to run. This is why you find SQLite on all kinds of different platforms including embedded devices like mobile phones and even gaming consoles.
Create an SQLite Database
Creating an SQLite Database using Python is very easy. The first thing to do is import the sqlite3 module so we can access the functionality needed to create the database. This module follows the Python database API specification we learned about just above.
The next thing to do is get a connection object with the connect() function. We’ll call our database “cars.db” and we’ll save the connection object in a variable called “connection”. What is really cool about this connect function is that it will create the database for you if there is not one existing already. The next time we run this function, connect will just connect us with the already-created cars database.
connection = sqlite3.connect('cars.db')
To create a table in this relational database, we can use the cursor object. This is done using “connection.cursor” and then saving it in a variable also called “cursor”. Once we have a cursor object, we can then execute commands and queries on the database. The first order of business is to create a table. We’ll write “cursor” and then “.execute” to execute the command. Inside of that command, we use a triple quoted string to provide the SQL commands to create a table named cars, with columns of make, model, and year. Each column has a data type of TEXT except for year which is an INT. After that, we can save our change of creating the database and the table and then close the connection. To save changes, we’ll write “connection.commit”, committing these changes to the database, and then close the connection with “connection.close”. Here is the full code so far and we have placed it in a file named cars.py.
import sqlite3 connection = sqlite3.connect('cars.db') cursor = connection.cursor() cursor.execute(''' CREATE TABLE IF NOT EXISTS cars ( make TEXT, model TEXT, year INT ) ''') connection.commit() connection.close()
Running The Code
When we run the code, a new file named cars.db shows up in our PyCharm project.
If we double click on the SQLite database file, a new Database tab opens in PyCharm and we can see the newly created table and associated columns. Cool!
Insert Into Database
With our first table created, we can start adding data to the SQLite database. For the first entry, we’ll add the new Ford Electric Mustang. To do this, we can use the execute() function on the cursor and use the INSERT SQL command to insert the data. We’ll insert this data into the cars table using the values we want to insert. These values are for the columns we already created of make, model, and year. Finally, we use .commit() and .close() to finish the insert operation.
import sqlite3 connection = sqlite3.connect('cars.db') cursor = connection.cursor() cursor.execute("INSERT INTO cars VALUES ('Ford', 'Mustang Mach E', 2022)") connection.commit() connection.close()
View Database Records In PyCharm
To see if the code above succeeded in inserting our new record into the database, we can use the console in PyCharm to enter SQL commands. At the console, we can run ‘select * from cars’ and notice that we see our new record in the database!
How To Use executemany()
Let’s try to add a bunch of cars to the cars table, not just one. To do this we’d first create the data we want to insert into the database. In this example, we create a tuple containing three values for each car we want to insert. Then we place several tuples together inside of a list and store that collection in a variable named cool_cars. We can actually insert all of these cars with one line of code. The executemany() function gives us this option. This statement will look at each tuple within cool_cars and fill in the three placeholder question marks with their values. Then it will run the insertion for each tuple.
import sqlite3 connection = sqlite3.connect('cars.db') cursor = connection.cursor() cool_cars = [('Tesla', 'Model 3', 2020), ('Kia', 'Niro EV', 2022), ('Porsche', 'Taycan 4S', 2021)] cursor.executemany('INSERT INTO cars VALUES (?,?,?)', cool_cars) connection.commit() connection.close()
We are able to check in the PyCharm console once again to see if the executemany() insert was successful, and it was. There are now 4 rows in the database.
Using SQL In execute() Function
Instead of viewing the records in the database using PyCharm, let’s see how to just run SQL commands to view the records in our database. In the code below, we use SQL to select all records from the database, but then we use the cursor.fetchone() function to retrieve the first result. The code correctly gets the first record in the database which is the Ford Mustang Mach E.
import sqlite3 connection = sqlite3.connect('cars.db') cursor = connection.cursor() cursor.execute("SELECT * FROM cars") record = cursor.fetchone() print(record) connection.commit() connection.close()
('Ford', 'Mustang Mach E', 2022)
To see all of the records that the SQL query returned, you can use fetchall().
import sqlite3 connection = sqlite3.connect('cars.db') cursor = connection.cursor() cursor.execute("SELECT * FROM cars") record = cursor.fetchall() print(record) connection.commit() connection.close()
[('Ford', 'Mustang Mach E', 2022), ('Tesla', 'Model 3', 2020), ('Kia', 'Niro EV', 2022), ('Porsche', 'Taycan 4S', 2021)]
You can also loop over the results if you like.
import sqlite3 connection = sqlite3.connect('cars.db') cursor = connection.cursor() records = cursor.execute("SELECT * FROM cars") for record in records: print(record) connection.commit() connection.close()
('Ford', 'Mustang Mach E', 2022) ('Tesla', 'Model 3', 2020) ('Kia', 'Niro EV', 2022) ('Porsche', 'Taycan 4S', 2021)
Filter Records Using WHERE
To filter the results of the database query we can use the WHERE clause. First, we’ll look at the code and then discuss why it is structured the way it is.
import sqlite3 connection = sqlite3.connect('cars.db') cursor = connection.cursor() year = (2020,) record = cursor.execute("SELECT * FROM cars WHERE year = ?", year) print(record.fetchone()) connection.commit() connection.close()
('Tesla', 'Model 3', 2020)
In the above code, we have a variable of year that we use with the SQL query and we store the value to filter by in a tuple. Now you might be thinking why use a tuple? Why not just use the value of 2020? We could just use string operations to assemble the SQL queries with appropriate values but we don’t because doing so is very insecure. It makes your program vulnerable to a SQL injection attack. If you need to use values from Python variables in your SQL statements, use the question mark placeholder, and provide a tuple of values as the second argument to the execute or executemany function, as we’ve done here.
How To Use fetchmany()
The fetchmany() function is really nice as it allows you to get a specific number of records from the database quite easily. In this example, we use fetchmany() to fetch exactly 2 rows from the database.
import sqlite3 connection = sqlite3.connect('cars.db') cursor = connection.cursor() records = cursor.execute("SELECT * FROM cars") print(records.fetchmany(2)) connection.commit() connection.close()
How To Use A Database In Python Summary
Various Database modules exist to store data when working with Python. Thanks to Pep 249 (the Python Database API Specification 2.0), there is a common standardized approach to working with any type of database. In this tutorial, we looked at some examples using SQLite. Regardless of the database used, the concept is the same. You need a database, a connection to that database, and a cursor object to interact with the database. Some of the commonly used curser object methods are listed here.