SQLAlchemy – Label
Last Updated :
05 Jun, 2023
SQLAlchemy is a Python library used for working with relational databases. It provides an intuitive way to interact with databases and allows us to write database-independent code. In this article, we’ll explore one of the powerful features of SQLAlchemy called label(), which is used to add labels to queries and retrieve data in a more convenient way.
What is a Label in SQLAlchemy?
A label in SQLAlchemy is a means to give a result column in a query a name. When you run a query with labels, the result set will have the specified names for the columns rather than the standard column names. Any expression in a SELECT statement, such as columns, functions, and subqueries, can be given a label. In SQLAlchemy, labels are a potent tool for improving the readability of our queries and the practicality of data retrieval. Labels let us give columns in our query names, apply labels to functions, and aggregate our results in more useful ways. Labels help us write more understandable, maintainable code and facilitate database operationsWe will see how we can use label() feature of SQLAlchemy with the help of Python.
Database Table Used
For the purpose of this article, we’ll be using a simple database called library.db. It has a table called students containing columns id, name, age, and department.
DB Table
Displaying Records with Labels
This is an example query using SQLAlchemy to select the “name” column in uppercase and the “age” column for rows where the age is greater than 18, using the select function and the label and func methods from SQLAlchemy.
Python3
from sqlalchemy import *
connection = engine.connect()
metadata = MetaData()
table = Table( 'student' , metadata,
Column( 'id' , Integer, primary_key = True ),
Column( 'name' , String),
Column( 'age' , Integer),
Column( 'dept' , String),
)
metadata.create_all(engine)
q = text(
)
connection.execute(q)
print ( '\nDisplaying the table: \n' )
q = text( "SELECT * FROM student" )
result = connection.execute(q)
for row in result.fetchall():
print (row)
query = select(
label( 'name_uppercase' , func.upper(table.c.name)),
table.c.age
).where(table.c.age > 18 )
print ('\nDisplaying records labels with age" +
"greater than 18 age: \n')
with engine.connect() as conn:
result = connection.execute(query)
for row in result:
print (row)
connection.close()
|
Output:
In this example, the records with an age greater than 18 are converted to uppercase under the label “name_uppercase“.
Label Output
Using Functions with Labels
Labels can also be used with functions to make the result set more readable. For example, suppose we want to retrieve the number of students according to their department. We can use the label() function and the func.count() function to create a more informative result set.
Python3
query = select(
label( 'name_uppercase' , func.count(table.c.dept)),
table.c.dept
).group_by(table.c.dept)
print ( '\nDisplaying records labels count department wise: \n' )
with engine.connect() as conn:
result = connection.execute(query)
for row in result:
print (row)
|
Output:
Group By example
Share your thoughts in the comments
Please Login to comment...