How can I autogenerate ORM code for SQLAlchemy when consuming TSQL (mssql on SQL Server) from python?

Question

SQLAlchemy relies on me building ORM classes like this:

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Is there a tool/script/program than can do this for me?

For instance, in C# I can just drag and drop data items from the Database explorer into VisualStudio and have Entity Classes autogenerated for me (SQL to LINQ). I'm looking for something similar for python. I'm working in VisualStudio and/or Spyder.

Answer 1

I just successfully used sqlacodegen to generate the classes for my MS SQL Server 2014 database. It was super easy; I instantly fell in love with it!

I'm using Python 3.7 (if it matters). Here are the commands I used in (an administrator?) PowerShell:

1. pip install sqlacodegen
2. pip install pymssql
3. sqlacodegen mssql+pymssql://sql_username:sql_password@server/database > db_name.py
4. It will create db_name.py in your current directory.
5. You can then use those classes by moving db_name.py to the same folder as your main script and adding import db_name.

I did not specify a port and my server is setup to simply be the computer's name without specifying the installation, i.e. server\installation. I used the instructions for sqlacodegen here and the database URL from here.