How Can I Autogenerate Orm Code For Sqlalchemy When Consuming Tsql (mssql On Sql Server) From Python?

SQLAlchemy relies on me building ORM classes like this: from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func from sqlalchemy.orm import relationship, backref

Solution 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.

Solution 2:

Do you need to have the classes explicitly defined, or would having them defined without writing the code work okay? If the latter is okay, then SQLAlchemy's own automap might be enough.

Otherwise, the sqlacodegen tool looks like it should do code generation for you.