I love the idea of ORMs/Object Relational Mappers, as they reduce the developer's repetitive workload. After spending enough time with ORMs, I found redeclaring models in Python is a boring chore, however. I spent my time wondering what can I do and then I fell in love with automap of SQLAlchemy as I discovered it.

The "normal" way

I had a customer and he asked me to have a system where he and his friends can login with his username and his password. We had a few meetings and he was sure that it was all he want. Felt confident, I start with this really simple table in PostgreSQL.

create table users
(
	username text,
	password text
);

It seemed like declaring the table in Python is not a big problem, it is?

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String

Base = declarative_base()

class User(Base):
	username = Column(String)
	password = Column(String)

But then I realized I need to add an id for my users table. Even though people could login to my system, it still "felt" wrong without an id.

I needed to alter my table, or drop and recreate it. Let us go with the second choice, since PostgreSQL does not let me rearrange the columns' positions.

create table users
(
	id serial primary key,
	username text,
	password text
);

Now I needed to change my model as well.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String

Base = declarative_base()

class User(Base):
	id = Column(Integer, primary_key=True)
	username = Column(String)
	password = Column(String)

My user called, and asked me if my system could store his full name as well. He would pay more. I sighed and said yes. I "only" needed to fire up my editor, and... Ugh... Maybe it was my fault that I could not fully understand my requirements and then design a perfect system. I am a horrible developer since I could not calculate them all, nor could I predict the future. The software industry will be doomed if they cannot do a flawless job at the start.

That sounded right, but quite... unfair. With human kinds, and animals, and living beings, in most cases, we all survive because we are fast to response to changes, and adaptable. Let us try making our response to changes less awkward and fumbling.

The "auto" way

We were back to square one with this SQL script:

create table users
(
	username text,
	password text
);

But in Python, we had an other way:

from sqlalchemy.ext.automap import automap_base

Base = automap_base()
Base.prepare(engine, reflect=True)

User = Base.classes.users

The story went the same: I add id to table users. I also add full_name to it.

create table users
(
	id serial primary key,
	username text,
	password text,
	full_name text,
);

So far so good. My customer was happy with my adaptation. He demanded me to also let the system store the books that he and his friends were having. Weird request but okay. I fired up my editor in enthusiasm and type:

create table books
(
	id serial primary key,
	book_name text
);

The only new line of code that I needed in Python was:

Book = Base.classes.books

Downsides

A few downsides of automap that I found is:

  • There would be no code suggestion for the automapped model in your text editor or IDE.
  • Table inheritance.

I personally was okay without code suggestion for a small part of my project, but the problem with table inheritance took me quite the time to fix. It came from the setup that my child table inherits primary key of parent table, and SQLAlchemy could not map that. The author of SQLAlchemy suggested me to do something with SQLAlchemy's event, but that did not work, either. Finally, I got it works by inheriting anything but the primary key. PostgreSQL was used that time, but I cannot be sure how would the problem be with other RDBMS.

Conclusion

At first, I thought of taking things a little bit further, but I got distracted and watched random clips on YouTube, and rushed to this ending. I hope that you got the hang of using automap in SQLAlchemy and found out how wonderful this little piece of magic is in your projects.

Thanks for reading and have fun coding!