Dynamic Column Parameterization in SQLAlchemy Core

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:

• Table Creation
• Query and Filter in SQLAlchemy

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, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# create Base class
base_class = declarative_base()

# Establish connection
engine = create_engine("mysql+pymysql://user_name:password@host:port/db_name")

# Model class
# the model class should have one primary key attribute
# other wise it will give an error
class 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 student
base_class.metadata.create_all(engine)

# creating session
Session = 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 work

sudnt1 = 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 changes

session.commit()

# closing connection
session.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 sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# create Base class
base_class = declarative_base()

# Establish connection
engine = create_engine("mysql+pymysql://userName:password@host:port/dbName")

# model class
class 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 session
Session = sessionmaker(bind=engine)
session = Session()

# creating branch_param with value equal to CSE
branch_param = bindparam("branch_param", value="CSE")

# creating the query to select the student details
# whose branch column matches with the brach_param value
query = session.query(Student).filter(branch_param == Student.branch)

# executing the query
result = 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 connection
session.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 sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# create Base class
base_class = declarative_base()

# Establish connection
engine = create_engine("mysql+pymysql://root:Ravi123@localhost/employee_db")

# model class


class 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 session
Session = sessionmaker(bind=engine)
session = Session()

# creating the query to select the student details
# whose branch is ECE
query = session.query(Student).filter(literal_column("branch") == "ECE")

# executing the query
result = 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 connection
session.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 ECE
query = session.query(Student).filter(getattr(Student, "branch") == "ECE")

# executing the query
result = 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 param
phone_param = bindparam("phone_param", 987654321)

# update query
updateQuery = 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 updated
session.commit()


print("student data")
res = session.query(Student)
for s in res:
    print(s.student_id, s.student_name, s.phone_number)


# closing connection
session.close()