PythonOnWheels and MSSQL with docker and auto-reflection for Database Models

The usecase

You have an existing MSSQL database and need to expose an API for it, make a Web or APP UI for it or just want to make it easily acessible for python.

I work with Linux (Ubuntu desktop), so this seem a little more complex then on windows at first glance. But with technologies like docker and python which make your life really platform independent this is not as hard as it seems.

The scenario

To make it really easy for you to follow this hands-on tutorial we will use a docker based MSSQL instance, a prepared DB schema, prepared sample data and of course PythonOnWheels to get easy access to our database.

We will go through this step by step so you can execute anything on your system in parallel. Since we use docker nothing will be permanently installed or remain on your system.

I use MSSQL here as a popular example. But anything will work on SQLite, Postgres, MySQL, MariaDB, Oracle and many others as well, since PythonOnWheels is based on SQLAlchemy.

Let's start

The Model

# 
# Model Uidtest 
# 
from sqlalchemy import Column, Integer, String, Boolean, Sequence 
from sqlalchemy import BigInteger, Date, DateTime, Float, Numeric, Unicode, Text 
from sqlalchemy import PrimaryKeyConstraint, Table 
from testtest.lib.powlib import relation 
from testtest.database.sqldblib import Base, engine 
from testtest.lib.powlib import PowBaseMeta 
#@relation.has_many("<plural_other_models>") 
@relation.setup_sql_schema() 
class Uidtest(Base, metaclass=PowBaseMeta): 
     
    schema = {} 
     
    # if you dont want to use the pow schema extension 
    _use_pow_schema_attrs= False 
    # define class attributes/variables here that should be included in to_dict()  
    # conversion and also handed to the encoders but that are NOT part of the schema. 
    include_attributes=[] 
     
    __tablename__ = "uidtest" 
     
    __table__ = Table( 
        'uidtest', 
        Base.metadata, 
        PrimaryKeyConstraint("id", name="pk_id"), 
        autoload_with=engine 
    ) 
     
    # 
    # init 
    # 
    def __init__(self, **kwargs): 
        self.setup_instance_values() 
        self.init_on_load(**kwargs) 
         
    # 
    # your model's methods down here 
    #

MSSQL docker

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<your-password>' --name mssql -p 1433:1433 
-v <your-local-data-dir>:/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2017-latest

This is it.

Now MSSQL should run on your system.

tags: