SQLAlchemy – Mapping Table Columns
Last Updated :
18 Mar, 2022
In this article, we will see how to map table columns using SQLAlchemy in Python.
You will need a database (MySQL, PostgreSQL, SQLite, etc) to work with. Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations changes with change in the database except for the SQL connectors.
pip install pymysql
We will use the sample sakila database from MySQL. All the examples covered in this article will make use of the actor table within the sakila database. If you do not have the sakila database and want to follow along with this article without installing it then use the SQL script present in the link mentioned below to create the required schema and actor table along with the records.
Databased Used: Sakila Actor Table Script
We will be referring to the same SQL query in each of the examples mentioned below –
SELECT first_name FROM sakila.actor LIMIT 1;
The different ways in which we can map the columns in SQLAlchemy are –
- Mapping columns directly to the attribute names
- Mapping columns distinctly from attribute names
- Mapping columns using reflection
- Mapping columns using a prefix
Mapping columns directly to the attribute names
In the below example, the column mapping is done by mapping each of the table columns as class attributes. Each of the attributes is provided with the same name as the corresponding table columns that it represents. We then establish the SQLAlchemy engine connected to the sakila database in MySQL. Then a session object is created to query the database. Using this session object, we will query the first record in the `actor` table. We get the value of the column `first_name` for the first record by accessing the `first_name` attribute of the `result` object. This shows that the column of the actor table is mapped against the attributes of the Actor class.
Python3
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Actor(Base):
__tablename__ = 'actor'
actor_id = db.Column(db.SmallInteger, autoincrement=True, primary_key=True)
first_name = db.Column(db.String(45), nullable=False)
last_name = db.Column(db.String(45), nullable=False)
last_update = db.Column(db.TIMESTAMP, nullable=False)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Actor).first()
print("First Name (Record 1):", result.first_name)
|
Output:
First Name (Record 1): PENELOPE
Mapping columns distinctly from attribute names
This is similar to the first example with a small change. The attribute names mentioned in this example are different than the column names. This is possible by providing an additional parameter inside the `Column()` method. The method’s first argument takes in the actual column name which allows using different attribute names for referring to these columns. If we look at the final `print()` method, the first name of the first record in the actor table is referenced using the `fname` attribute as opposed to the `first_name` or actual column name seen in the first example.
Python3
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Actor(Base):
__tablename__ = 'actor'
id = db.Column('actor_id', db.SmallInteger, autoincrement=True,
primary_key=True)
fname = db.Column('first_name', db.String(45), nullable=False)
lname = db.Column('last_name', db.String(45), nullable=False)
update_on = db.Column('last_update', db.TIMESTAMP, nullable=False)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Actor).first()
print("First Name (Record 1):", result.fname)
|
Output:
First Name (Record 1): PENELOPE
Mapping columns using reflection
In the previous two examples, we needed to explicitly map each column with the table using class and its attributes. In this method, we do not need to provide this explicit mapping of each table column separately. Using reflection, this task is automatically done by providing the metadata object and the SQLAlchemy engine connection. In the `__table__` attribute. We can then use the engine and session objects to query the actor table to fetch the first name as done in earlier examples using the column name `first_name` as the attribute name itself.
Python3
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Actor(Base):
__table__ = db.Table("actor", Base.metadata, autoload_with=engine)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Actor).first()
print("First Name (Record 1):", result.first_name)
|
Output:
First Name (Record 1): PENELOPE
Mapping columns using a prefix
The usage of prefixes is rare but still, it can be found in some use cases. In this example, it can be seen that we have used an additional attribute `__mapper_args__` which is a python dictionary. It is provided with a key as `column_prefix` and a value of `_`. This means that we want to prefix all the column names or attribute names with an underscore. For this reason, we used `_first_name` instead of `first_name` as the attribute for the respective column.
Python3
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Actor(Base):
__table__ = db.Table("actor", Base.metadata, autoload_with=engine)
__mapper_args__ = {'column_prefix': '_'}
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Actor).first()
print("First Name (Record 1):", result._first_name)
|
Output:
First Name (Record 1): PENELOPE
Similar Reads
SQLAlchemy Core - Creating Table
In this article, we are going to see how to create table in SQLAlchemy using Python. SQLAlchemy is a large SQL toolkit with lots of different components. The two largest components are SQLAlchemy Core and SQLAlchemy ORM. The major difference between them is SQLAlchemy Core is a schema-centric model
3 min read
Column and Data Types in SQLAlchemy
SQLAlchemy is an open-source library for the Python programming language that provides a set of tools for working with databases. It allows developers to interact with databases in a more Pythonic way, making it easier to write code that is both efficient and readable. Column TypesA column type in S
4 min read
SQLAlchemy Core - Multiple Tables
SQLAlchemy is an open-source library that provides a set of tools for working with relational databases. It offers a high-level Object-Relational Mapping (ORM) interface as well as a lower-level SQL Expression Language (Core) interface. SQLAlchemy Core is a lightweight and flexible SQL toolkit that
4 min read
SQLAlchemy ORM - Declaring Mapping
In this article, we will see how to declare mapping using SQLAlchemy in Python. You will need a database (MySQL, PostgreSQL, SQLite, etc) to work with. Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations ch
4 min read
SQLAlchemy - Mapping Python Classes
SQLAlchemy is a popular Python library that provides a nice API for interacting with databases. One of its key features is the ability to map Python classes to database tables, allowing you to use Python objects to represent rows in a database table. This is known as an "object-relational mapper" (O
7 min read
Sum multiple columns in SQLAlchemy
In this article, we are going to sum multiple columns and get results using the SQLAlchemy module of python. Installation To install the SQLAlchemy module, run the following command in your terminal: pip install sqlalchemy pymysql Note: pymysql is a dependency of SQLAlchemy which we need to install
3 min read
SQLAlchemy Core - Multiple Table Delete
In this article, we are going to see how to perform multiple-table DELETE in SQLAlchemy against a PostgreSQL database in Python. Creating table for demonstration - BOOKS Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() fu
3 min read
How to divide two columns in SQLAlchemy?
In this article, we are going to divide two columns using the SQLAlchemy module of python. Installing SQLAlchemy To install SQLAlchemy, run the following command in the terminal. pip install sqlalchemy pymysql So, what we have to do in this post is to divide two columns and get output using SQLAlche
2 min read
SQLAlchemy Core - Multiple Table Updates
SQLAlchemy Core provides a powerful feature for performing updates on multiple tables in a database. This allows us to update related data in multiple tables in a single transaction, ensuring data consistency and integrity. In this approach, we define the tables using SQLAlchemy's Table object and c
6 min read
How to get column names from SQLAlchemy?
In this article, we will discuss how to get column names using SQLAlchemy in Python. SQLAlchemy is an open-source SQL toolkit and object-relational mapper for the Python programming language released under the MIT License. It gives full power and flexibility of SQL to an application. To follow along
3 min read