How to Get Value from SQLAlchemy Instance by Column Name?

SQLAlchemy is a SQL toolkit and Object-Relational Mapping (ORM) system for the Python programming language. It offers a full suite of well-known enterprise-level persistence patterns, intended for data manipulation in SQL. One feature developers often seek to utilize is accessing the value from a SQLAlchemy instance using a column name. This powerful technique can streamline database operations and improve code readability. This article guides you through the process of using this feature in your Python program, showcasing two practical approaches.

Syntax

Before we delve into the procedures, let's establish the syntax of the method we will use in our following codes −

# Syntax for accessing a column value by name:
value = SQLAlchemy_instance.column_name

Algorithm

The step-by-step process of obtaining value from SQLAlchemy instance by column name is as follows −

• Import SQLAlchemy module in Python

• Define your database and tables using SQLAlchemy's declarative base

• Create an instance of your defined table

• Use the syntax provided above to access the value from a column of this instance

Approach 1: Accessing Directly

This approach involves accessing the value of a column directly using the instance. This is a simple and straightforward method, where the column value can be fetched just by calling the column name through the instance.

Example

from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# Define a User model
class User(Base):
   __tablename__ = 'users'

   id = Column(Integer, primary_key=True)
   name = Column(String)

# Create a SQLite database and a "users" table
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

# Add an instance of User
Session = sessionmaker(bind=engine)
session = Session()
new_user = User(name='John Doe')
session.add(new_user)
session.commit()

# Access the name directly
print(new_user.name)

Output

John Doe

Explanation

This method is straightforward - you simply access the column value directly using the instance.

Import necessary modules and classes: The first step is to import the necessary components from SQLAlchemy. create_engine is used to set up the database, Column, String, and Integer are used to define the columns of the table, sessionmaker is used to create a session for adding and committing data, and declarative_base is the base class for all models.

Define the table structure: We create a new class called User, which inherits from Base (an instance of declarative_base). Inside the class, we define two columns, id and name.

Create the engine and table: We create an SQLite database stored in memory (':memory:') and create all tables defined by Base or its subclasses. Then we create a session using the engine.

Insert data: Here, we create a new User instance and add it to the session. Finally, we commit the session to execute the transaction.

Access the column value: We access the name column value of the new_user instance directly.

Approach 2: Using getattr() function

The second approach methodology utilizes Python's underlying getattr() function. This strategy can be helpful when the column name is put away in a variable, and we need to powerfully bring the value.

Example

from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# Define a User model
class User(Base):
   __tablename__ = 'users'

   id = Column(Integer, primary_key=True)
   name = Column(String)

# Create a SQLite database and a "users" table
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

# Add an instance of User
Session = sessionmaker(bind=engine)
session = Session()
new_user = User(name='Alice Smith')
session.add(new_user)
session.commit()

# Access the name using getattr
column_name = 'name'
print(getattr(new_user, column_name))

Output

Alice Smith

Explanation

In the second approach, we use Python's built-in getattr() function to access the column value. This method is handy when you don't know the column name beforehand and get it dynamically at runtime.

Import necessary modules and classes: The first step is to import the necessary components from SQLAlchemy. create_engine is used to set up the database, Column, String, and Integer are used to define the columns of the table, sessionmaker is used to create a session for adding and committing data, and declarative_base is the base class for all models.

Define the table structure: We create a new class called User, which inherits from Base (an instance of declarative_base). Inside the class, we define two columns, id and name.

Create the engine and table: We create an SQLite database stored in memory (':memory:') and create all tables defined by Base or its subclasses. Then we create a session using the engine.

Insert data: Here, we create a new User instance and add it to the session. Finally, we commit the session to execute the transaction.

Access the segment esteem: Rather than getting to the section straightforwardly, we use getattr(). We indicate the article and the name of the quality we need to get the worth from. For this situation, the trait name is put away in the column_name variable.

Conclusion

SQLAlchemy is a flexible and strong ORM instrument that, when combined with Python, offers broad elements to work with consistent cooperation with your data set.The ability to fetch values from a SQLAlchemy instance by column name significantly enhances readability and eases data manipulation. With two different yet equally proficient ways to achieve this - directly accessing the column value or leveraging the getattr() function - you can choose the one that best aligns with your requirements, ensuring you extract maximum value from your SQLAlchemy instances. As we continue to unravel the many layers of SQLAlchemy's functionality, it's evident how integral it has become in managing and navigating our database-related operations in Python.