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() # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine( "mysql+pymysql://root:password@localhost/sakila" ) class Actor(Base): __table__ = db.Table( "actor" , Base.metadata, autoload_with = engine) __mapper_args__ = { 'column_prefix' : '_' } # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine( "mysql+pymysql://root:password@localhost/sakila" ) # CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() # SELECT COUNT(*) FROM Table LIMIT 1; result = session.query(Actor).first() # DISPLAY FIRST NAME OF FIRST RECORD IN ACTOR TABLE print ( "First Name (Record 1):" , result._first_name) |
Output:
First Name (Record 1): PENELOPE
SQLAlchemy – Mapping Table Columns
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
Contact Us