SQLAlchemy Core is an adaptable SQL toolkit and Object Relational Mapping (ORM) library, for Python. It is specifically designed to integrate with databases offering an extensive set of functionalities, for creating, querying, and manipulating database tables and records. When working with databases a frequent necessity is the ability to dynamically specify column names in your queries or operations. In this article, we are going to see how to pass the column name as a parameter in SQLAlchemy against the MySQL database in Python.
Prerequisites:
Creating Student Table
Import necessary functions and modules from the SQLAlchemy package. Establish a connection with the MySql database using the create_engine() function as shown below, and create a model class called Student with attributes student_id, student_name, gender,phone_number, and branch. Insert the record into the student table using add() and commit() the changes as shown.
Python3
from sqlalchemy import Column, create_engine, Integer, String, textfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_base# create Base classbase_class = declarative_base()# Establish connection# Model class# the model class should have one primary key attribute# other wise it will give an errorclass Student(base_class): __tablename__ = "student" student_id = Column(Integer, primary_key=True) student_name = Column(String(50)) gender = Column(String(10)) phone_number = Column(Integer) branch = Column(String(10))# create_all method will create a table in database named with studentbase_class.metadata.create_all(engine)# creating sessionSession = sessionmaker(bind=engine)session = Session()# creating Instance of Student class# No need to give student_id explicitly. it automatically assigned by Sqlalchemy# if you given not a problem it will worksudnt1 = Student(student_name="Alice", gender="Male", phone_number=123456789, branch="ECE")sudnt2 = Student(student_name="Kohli", gender="Male", phone_number=123456789, branch="CSE")sudnt3 = Student(student_name="Bob", gender="Male", phone_number=123456789, branch="CSE")sudnt4 = Student(student_name="Dhoni", gender="Male", phone_number=123456789, branch="ECE")# adding Instance to session(adding data to table)session.add(sudnt1)session.add(sudnt2)session.add(sudnt3)session.add(sudnt4)# committing changessession.commit()# closing connectionsession.close() |
Student Table
Pass the Name of a Column as a Parameter in SQLAlchemy Core
Below are the methods and steps by which we can pass the name of a column as a parameter in SQLAlchemy Core:
- By using bindparam()
- By using literal_column()
- By using getattr()
- Updating Data using bindparam() and literal_column()
By using bindparam()
bindparam() in SQLAlchemy core allows us to create named parameters and dynamically pass values to SQL statements or queries.
Syntax: bindparam(name, value=None,type=None)
Parameters:
name : name of the bind parameter
value : default value for the bind parameter (optional)
type : The SQLAlchemy type to be associated with the bind parameter (optional)
Returns: returns a BindParameter object, which represents the bind parameter and its associated properties.
Example: Here, we create a bind parameter “branch_param” with value=”CSE” using the bindparam. Then we create a query to select all the student details whose branch column matches the brach_param value.
Python3
from sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_base# create Base classbase_class = declarative_base()# Establish connection# model classclass Student(base_class): __tablename__ = "student" student_id = Column(Integer, primary_key=True) student_name = Column(String(50)) gender = Column(String(10)) phone_number = Column(Integer) branch = Column(String(10))# creating sessionSession = sessionmaker(bind=engine)session = Session()# creating branch_param with value equal to CSEbranch_param = bindparam("branch_param", value="CSE")# creating the query to select the student details# whose branch column matches with the brach_param valuequery = session.query(Student).filter(branch_param == Student.branch)# executing the queryresult = query.all()print("Bind Param Output"print("____________________")for row in result: # print(row) it print refference of row object print(row.student_id, row.student_name, row.phone_number, row.branch) print(row.student_id, row.student_name, row.phone_number, row.branch) # closing db connectionsession.close() |
Output
Bindparam Output
By using literal_column()
In SQLAlchemy Core literal_column() function allows you to include a literal SQL expression or column in your queries. It allows you to include raw SQL expressions or column values directly into your queries.
Syntax: sqlalchemy.sql.expression.literal_column(text)
Parameters:
text: it can be any SQL expression.
Returns: returns a ColumnClause object representing the literal column expression that you have specified.
Example: Here, we are creating a query to select all student details whose branch is ECE. We create a query using query(Student). This sets to select all the rows present in the Student table. Then we apply a filter condition to select all student whose branch is ECE using filter(literal_column(“branch”)==”ECE”). Here literal_column(“branch”) represents the branch column in student table. Then we execute the query and print the results.
Note: The argument of literal_column should be present in model class otherwise it givens an error.
Python3
from sqlalchemy import *from sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_base# create Base classbase_class = declarative_base()# Establish connection# model classclass Student(base_class): __tablename__ = "student" student_id = Column(Integer, primary_key=True) student_name = Column(String(50)) gender = Column(String(10)) phone_number = Column(Integer) branch = Column(String(10))# creating sessionSession = sessionmaker(bind=engine)session = Session()# creating the query to select the student details# whose branch is ECEquery = session.query(Student).filter(literal_column("branch") == "ECE")# executing the queryresult = query.all()print("literal_column output")print("____________________")for row in result: # print(row) it print refference of object print(row.student_id, row.student_name, row.phone_number, row.branch)# closing the connectionsession.close() |
Output
Literal Column output
By using getattr()
The getattr() is used to retrieve an attribute value from an object dynamically, based on its name. It is Python built-in method not specific to SQLAlchmey.
Python3
# creating the query to select the student details# whose branch is ECEquery = session.query(Student).filter(getattr(Student, "branch") == "ECE")# executing the queryresult = query.all()print("getattr output")print("____________________")for row in result: # print(row) it print refference of object print(row.student_id, row.student_name, row.phone_number, row.branch)session.close() |
Output
getattr output
Updating Data using bindparam() and literal_column()
In the following example, we update the student’s phone number to 987654321 where the student ID is even using bindparam() and literal_column(). First, we create phone_param with the value 987654321. Then, we create the update statement using update(), and in the where clause, we check if the student ID is even or not using literal_column() and the modulo operator. In the value(), we simply update the phone number of the student.
Python3
# bind paramphone_param = bindparam("phone_param", 987654321)# update queryupdateQuery = update(Student).\ where(literal_column("student_id") % 2 == 0).\ values(phone_number=phone_param)session.execute(updateQuery)# commit is mendatory without this method data wont be updatedsession.commit()print("student data")res = session.query(Student)for s in res: print(s.student_id, s.student_name, s.phone_number)# closing connectionsession.close() |
