Returning distinct rows in SQLAlchemy with SQLite

In this article, we are going to see how to return distinct rows in SQLAlchemy with SQLite in Python.

Installation

SQLAlchemy is available via pip install package.

pip install sqlalchemy

However, if you are using flask you can make use of its own implementation of SQLAlchemy. It can be installed using –

pip install flask-sqlalchemy

Creating Database and Table using SQLite

We are going to make use of the sqlite3 database. You can download the database from this link. Unzip the downloaded file to a directory. After that, create a database to work on. Follow the below process to create a database named users:

• Open the command prompt and point to the directory to which the sqlite.exe file is present.
• Create a database named users using the command sqlite3 users.db
• Check the created database using the command .databases

Creating database using sqlite3

Before we can run the SQLAlchemy query, we will require a database table and a few records to work with. Let us create a table named employees and insert some values in it. The raw SQL query is given by:

CREATE TABLE employees (
  emp_name VARCHAR(50),
  emp_email VARCHAR(50),
  emp_address VARCHAR(50)
);
INSERT INTO employees VALUES
  ('John', 'john.doe@email.com', 'Washington'),
  ('Sundar', 'spichai@email.com', 'California'),
  ('Rahul', 'rahul@email.com', 'Mumbai'),
  ('Sonia', 'sonia@email.com', 'Mumbai'),
  ('Aisha', 'aisha@email.com', 'California');

The above two queries will create the employees table and insert 5 records in it. The query can be run in a sqlite3 shell as shown below –

Running Query in SQLite shell

Fetch Distinct Records using SQLAlchemy

Now, we have the table ready with us so we can write the SQLAlchemy code to extract the distinct records from the table. We will fetch the distinct (i.e. unique records) from the employees table for the emp_address field.

Python

import sqlalchemy as db

# Define the Engine (Connection Object)
engine = db.create_engine("sqlite:///users.db")

# Create the Metadata Object
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)

# Get the `employees` table from the Metadata object
EMPLOYEES = meta_data.tables['employees']

# SQLAlchemy Query to extract DISTINCT records
query = db.select([db.distinct(EMPLOYEES.c.emp_address)])

# Fetch all the records
result = engine.execute(query).fetchall()

# View the records
for record in result:
    print("\n", record)

Output:

Code Output

Explanation:

• First, we import the sqlalchemy library as db for simplicity. All the sqlalchemy objects, methods, etc will be imported using this db prefix for better clarity.
• We then create the engine which will serve as a connection to the database to perform all the database operations.
• Create the metadata object. The metadata object ‘metadata’ contains all the information about our database.
• Use the metadata information to fetch the ’employees’ table from the database.
• We can now write an SQLAlchemy query to fetch the unique records. We perform the DISTINCT operation on the emp_address field to retrieve the unique set of values in the respective field using the SQLalchemy’s ‘distinct()’ function.
• Print all the fetched records. In the output, we can view that we have only 3 distinct employee address values.