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 Objectmeta_data = db.MetaData(bind=engine)db.MetaData.reflect(meta_data)# Get the `employees` table from the Metadata objectEMPLOYEES = meta_data.tables['employees']# SQLAlchemy Query to extract DISTINCT recordsquery = db.select([db.distinct(EMPLOYEES.c.emp_address)])# Fetch all the recordsresult = engine.execute(query).fetchall()# View the recordsfor 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.
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.
Contact Us