How to Generate SQLAlchemy Model from an Existing Database

Hi, In this article we will see a complete guide to Generate SQLAlchemy Model from an Existing Database in Python with the the help of the examples.
As a developer, sometimes we need to generate the models of the tables available in a database for developing API or other operations in that scenario we can use a Python library to generate the SQLAlchemy model code.

Why do we need to generate the SQLAlchemy Model Code?

Suppose you want to perform some operations in a database table with the help of the SQLAlchemy ORM ( Object Relational Mapping ) and you don’t have a model of the table beforehand, In that case, you must have an SQLAlchemy model of the tables to operations.

In that case, you can use a Python library called sqlacodegen to generate model code from the existing table.

What is sqlacodegen?

sqlacodegen is a Python library that is responsible for reading the structure of an existing database and generating appropriate SQLAlchemy model code using the declarative style if possible.

Features of sqlacodegen:

• It Supports SQLAlchemy 0.8.x – 1.3.x
• It Produces declarative code that almost looks like it was handwritten
• Produces PEP 8-compliant code
• Accurately determines relationships, including many-to-many, one-to-one
• Automatically detects joined table inheritance
• Excellent test coverage

Getting Started – Generate SQLAlchemy Model from an Existing Database in Python

To work with sqlacodegen, you must have installed sqlacodegen in your Python environment.

Use the below command to install sqlacodegen.

pip install sqlacodegen

For demonstration of this article, I have already created some tables in MySQL Database as you can see below and throughout this article, we will try to generate a model of these tables.

Now, Let’s see the use case of the sqlacodegen.

To connect with the database, You need to create a connection string.

Here, I have listed the syntax of connection strings for popular databases. The only thing you need to replace in your connection string syntax:

• username:- Replace with your database username.
• password:- Replace with your database password.
• database_name:- Replace with your database name.
• localhost:- Replace with our database hostname.
• port:- Replace with your database port

PostgreSQL Database
postgresql://username:password@localhost/database_name
postgresql+psycopg2://username:password@localhost/database_name
postgresql+pg8000://username:password@localhost/database_name

Note:- Install psycopg2 Python library in case of Postgres Database

MySQL Database
mysql://username:password@localhost/database_name
mysql+mysqldb://username:password@localhost/database_name
mysql+pymysql://username:password@localhost/database_name

Note:- Install mysqlclient Python library in case of MySQL Database

Oracle Database
oracle://username:password@hostname:port/database_name
oracle+cx_oracle://username:password@tnsname

Microsoft SQL Server Database
mssql+pyodbc://username:password@mydsn
mssql+pymssql://username:password@hostname:port/database_name

Note:- Install pyodbc Python library in case of first connection string and install pymssql in case of second connection string.

Although I am using MySQL Database that’s why we need to create a connection string for MySQL.
Let’s make a connection string for MySQL Database so that you can easily generate sqlalchemy model existing database in Python.

I my case:

• The database name is testing
• The username name is the root
• The password name is root21
• The hostname name is localhost.

Let’s fill in these details in MySQL connection string syntax.

So My final MySQL connection string would be:

mysql://root:root21@localhost/testing

Let’s generate SQLAlchemy code for MySQL, Make sure you have installed sqlacodegen in your environment.

Generate SQLAlchemy Model Code for Single Table

To generate SQLalchemy model code for single tables you need to use --tables options along with the table name you want to generate code.

In My case, I want to generate a model for the employee table in the testing database, and then I will use the below command.

sqlacodegen mysql://root:root21@localhost/testing --tables employee

As you can see, How I have generated a model for the employee table.

Generate SQLAlchemy Model Code for Multiple Tables

To generate SQLalchemy model code for multiple tables you need to use --tables options along with table names separated by comma you want to generate code.

In My case, I want to generate a model for employee and department tables in the testing database.

The command would be:

sqlacodegen mysql://root:root21@localhost/testing --tables employee,department

As you can see, How I have generated SQLAlchemy Model code for two tables.

Generate SQLAlchemy Model code for All Tables

To generate sqlalchemy model code for all tables you don’t need to use --tables default it will generate a model for all the tables.

The command will be:

sqlacodegen mysql://root:root21@localhost/testing

Save Model into Output File

In all the above Examples, the model was displayed on the console but sometimes we want to save the model in a separate Python file. In that case, sqlacodegen provides an option called --outfile along with the location file where you want to save the model.

In my case I want to save all the models into models.py file then My final command would be.

sqlacodegen mysql://root:root21@localhost/testing --outfile models.py

This is how you can use sqlacodegen to generate the SQLAlchemy model from an existing database in Python.

Summary

To generate SQLAlchemy models from an existing database, This Python library is one of the best libraries and this supports almost all the popular Relation Databases like MySQL, PostgreSQL, Oracle, and SQL Server. The only thing you need to keep in mind is that your connection string should be correct.

If you found this article helpful, Please share and keep visiting for further interesting tutorials.

Thanks for your valuable time.