How To Use A Database In Python

How To Use A Database In Python

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.


SQLite RDBMS

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.

import sqlite3

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

Adding Tables

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.
python sqlite database

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!
pycharm database tab

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!
View Database Records In PyCharm
pycharm database query results

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.
View Database Records In PyCharm
python executemany results

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)

Using fetchall()

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.

  • cursor.execute – Executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). Placeholders can be question marks (qmark style) and named placeholders (named style).
  • cursor.fetchall – Fetches all remaining rows of a query result, returning a list. An empty list is returned when no rows are available.
  • cursor.fetchone – Fetches the next row of a query result set, returning a single sequence, or None when no more data is available.
  • cursor.close – Closes the cursor.
  • cursor.executemany – Executes an SQL command against all parameter sequences or mappings found in the sequence
  • cursor.fetchmany – Fetches the next set of rows of a query result, returning a list. The number of rows to fetch per call is specified by the size parameter.