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:
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.