In this article, we will see how to query using SQLAlchemy ORM in Python.
To follow along with this article, we need to have sqlalchemy and anyone database installed in our system. We have used the MySQL database for this article’s understanding.
Created a Profile table and a Students table:
Here we are going to cover the following methods:
- add_columns()
- add_entity()
- count()
- delete()
- distinct()
- filter()
- get()
- first()
- group_by()
- join()
- one()
add_columns()
The add_columns() method helps to add any additional columns or entities to the existing query. As it can be seen in the output the initial query only consisted of the first_name column but later when we used the add_columns() method, we were able to add last_name and course columns as well. Please note that add_column() is deprecated and should be avoided, instead, we can make use of the add_columns() (as shown above) or with_entities() method.
Syntax: sqlalchemy.orm.Query.add_columns(*column)
Where: Add one or more column expressions to the list of result columns to be returned.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Students(Base): __tablename__ = 'students' first_name = db.Column(db.String(50), primary_key=True) last_name = db.Column(db.String(50), primary_key=True) course = db.Column(db.String(50), primary_key=True) score = db.Column(db.Float)# CREATE A SESSION OBJECT TO INITIATE QUERY# IN DATABASESession = sessionmaker(bind=engine)session = Session()# SELECT first_name FROM studentsresult = session.query(Students.first_name)print("Query 1:", result)# SELECT first_name, last_name, course# FROM studentsresult = result.add_columns(Students.last_name, Students.course)print("Query 2:", result)# VIEW THE ENTRIES IN THE RESULTfor r in result: print(r.first_name, "|", r.last_name, "|", r.course) |
Output:
count()
The count() method is a synonym to the COUNT we use in the SQL queries. It returns the number of records present in the table. In our case, the students table consists of 12 records, the same can be verified from the students table screenshot shown at the beginning.
Syntax: sqlalchemy.orm.Query.count()
Return a count of rows this the SQL formed by this Query would return.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Students(Base): __tablename__ = 'students' first_name = db.Column(db.String(50), primary_key=True) last_name = db.Column(db.String(50), primary_key=True) course = db.Column(db.String(50), primary_key=True) score = db.Column(db.Float)# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASESession = sessionmaker(bind=engine)session = Session()# SELECT * FROM PROFILEresult = session.query(Students).count()# VIEW THE RESULTprint("Count:", result) |
Output:
Count: 12
distinct()
The distinct() method of sqlalchemy is a synonym to the DISTINCT used in SQL. It will return the distinct records based on the provided column names as a reference. In the above example, we have taken the distinct records present in the first_name field. Out of the 12 entries, we get 5 unique first name.
Syntax: sqlalchemy.orm.Query.distinct(*expr)
Apply a DISTINCT to the query and return the newly resulting Query.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Students(Base): __tablename__ = 'students' first_name = db.Column(db.String(50), primary_key=True) last_name = db.Column(db.String(50), primary_key=True) course = db.Column(db.String(50)) score = db.Column(db.Float)# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASESession = sessionmaker(bind=engine)session = Session()# SELECT DISTINCT(first_name) FROM students;result = session.query(Students) \ .with_entities(db.distinct(Students.first_name)).all()# VIEW THE ENTRIES IN THE RESULTfor r in result: print(r) |
Output:
delete()
The delete() method will delete the record present in the table based on some condition. In our case, we have provided the condition where email is ravipandey@zmail.com. The method returns the count of the number of records that got affected. After deleting, the same is verified by using the count() method. Before deleting any entry, the profile table consisted of 3 records (as shown at the start). Since one record got affected, now we have 2 records left in the table.
Syntax: sqlalchemy.orm.Query.delete(synchronize_session=’evaluate’)
Perform a DELETE with an arbitrary WHERE clause. Deletes rows matched by this query from the database and return the count of rows matched as returned by the database’s “row count” feature.
Python
import sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Profile(Base): __tablename__ = 'profile' email = db.Column(db.String(50), primary_key=True) name = db.Column(db.String(100)) contact = db.Column(db.Integer)# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASEfrom sqlalchemy.orm import sessionmakerSession = sessionmaker(bind = engine)session = Session()# DELETE FROM profile WHERE email = 'ravipandey@zmail.com'result = session.query(Profile) \ .filter(Profile.email == 'ravipandey@zmail.com') \ .delete(synchronize_session=False)print("Rows deleted:", result)result = session.query(Profile).count()print("Total records:", result) |
Output:
filter()
The filter() method works like the WHERE clause in SQL. It takes in an expression and returns only those records which satisfy the provided expression. There can be one or more expressions separated by ‘&‘. In the example, we have provided the LIKE condition for the name column in the profile table.
Syntax: sqlalchemy.orm.Query.filter(*criterion)
Apply the given filtering criterion to a copy of this Query, using SQL expressions.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Profile(Base): __tablename__ = 'profile' email = db.Column(db.String(50), primary_key=True) name = db.Column(db.String(100)) contact = db.Column(db.Integer)# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASESession = sessionmaker(bind=engine)session = Session()# SELECT email FROM student WHERE name LIKE 'Amit%';result = session.query(Profile) \ .with_entities(Profile.email) \ .filter(Profile.name.like('Amit%')).all()# VIEW THE ENTRIES IN THE RESULTfor r in result: print("\n", r.email) |
Output:
group_by()
GROUP BY clause in SQL can be written in SQLAlchemy using the group_by() method. It takes in the entity or column names as the parameter and does grouping based on these columns. The result that we see above is the group by operation done on first_name and last_name columns of students table where score is aggregated by SUM function.
Syntax: sqlalchemy.orm.Query.group_by(*clauses)
Apply one or more GROUP BY criterion to the query and return the newly resulting Query.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Students(Base): __tablename__ = 'students' first_name = db.Column(db.String(50), primary_key=True) last_name = db.Column(db.String(50), primary_key=True) course = db.Column(db.String(50)) score = db.Column(db.Float)# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASESession = sessionmaker(bind=engine)session = Session()# SELECT first_name, last_name, SUM(score)# AS total FROM students GROUP BY first_name, last_name;result = session.query(Students) \ .with_entities( Students.first_name, Students.last_name, db.func.sum(Students.score).label('total')).group_by( Students.first_name, Students.last_name).all()# VIEW THE ENTRIES IN THE RESULTfor r in result: print(r.first_name, r.last_name, "| Score =", r[2]) |
Output:
order_by()
In the above example, we have ordered the students table records based on the score column. The order_by() clause takes in the column names as the parameters. By default, it is assumed to be sorted in ascending order unless the column objects are passed through the desc() method.
Syntax: sqlalchemy.orm.Query.order_by(*clauses)
Apply one or more ORDER BY criteria to the query and return the newly resulting Query.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Students(Base): __tablename__ = 'students' first_name = db.Column(db.String(50), primary_key=True) last_name = db.Column(db.String(50), primary_key=True) course = db.Column(db.String(50), primary_key=True) score = db.Column(db.Float)# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASESession = sessionmaker(bind=engine)session = Session()# SELECT * FROM students ORDER BY score DESC, course;result = session.query(Students) \ .order_by( Students.score.desc(), Students.course).all()# VIEW THE ENTRIES IN THE RESULTfor r in result: print(r.first_name, r.last_name, r.course, r.score) |
Output:
first()
The first() method returns the first record from the query. It is similar to applying LIMIT 1 at the end of the SQL query. In the output, we can see that we were able to fetch a record from the table. However, if we try to iterate through the result we get an error that the object is not iterable, the reason being there is only one record present in the result.
Syntax: sqlalchemy.engine.Result.first()
Fetch the first row or None if no row is present. Closes the result set and discards remaining rows.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Profile(Base): __tablename__ = 'profile' email = db.Column(db.String(50), primary_key=True) name = db.Column(db.String(100)) contact = db.Column(db.Integer)class Students(Base): __tablename__ = 'students' first_name = db.Column(db.String(50), primary_key=True) last_name = db.Column(db.String(50), primary_key=True) course = db.Column(db.String(50), primary_key=True) score = db.Column(db.Float)# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASESession = sessionmaker(bind=engine)session = Session()# SELECT * FROM profile LIMIT 1result = session.query(Profile).first()print(result.email, "|", result.name, "|", result.contact)# VIEW THE ENTRIES IN THE RESULTfor r in result: print(r.email, r.name, r.contact) |
Output:
one()
Methods one() and first() might sound similar but they aren’t. In the above example, we see an error trace because the one() method tries to fetch records from a table have just a single entry. If the table has 0, 2, or more entries then it throws an exception. Its usage is pretty rare.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Profile(Base): __tablename__ = 'profile' email = db.Column(db.String(50), primary_key=True) name = db.Column(db.String(100)) contact = db.Column(db.Integer)# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASESession = sessionmaker(bind=engine)session = Session()# profile table should contain only one row, else errorresult = session.query(Profile).one() |
Output:
join()
In order to understand the join() method, we have created an additional table named location which has two columns, namely, email and location. We have inserted one entry in this table with email as ‘amitpathak@zmail.com’ and location as ‘Mumbai’. We already have one entry with this email ID in the profile table, so let us try to join these two tables in the email column. within the join() method, the first argument is the other table (profile) that we need to join to, and the second argument in the condition (ON in SQL).
Syntax: sqlalchemy.orm.Query.join(target, *props, **kwargs)
Create a SQL JOIN against this Query object’s criterion and apply generatively, returning the newly resulting Query.
Python
from sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass Profile(Base): __tablename__ = 'profile' email = db.Column(db.String(50), primary_key=True) name = db.Column(db.String(100)) contact = db.Column(db.Integer)class Location(Base): __tablename__ = 'location' email = db.Column(db.String(50), primary_key=True) location = db.Column(db.String(100))# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASESession = sessionmaker(bind=engine)session = Session()# SELECT * FROM PROFILEresult = session.query( Profile.email, Profile.name, Profile.contact, Location.location).join(Location, Profile.email == Location.email)print("Query:", result)print()# VIEW THE ENTRIES IN THE RESULTfor r in result: print(r.email, "|", r.name, "|", r.contact, "|", r.location) |
Output:
