Reflecting an existing SQL Database schema with python in minutes with PythonOnWheels.

4 min readMay 19, 2019

PythonOnWheels is a layer of glue around some great existing libraries/modules and tools to make your python life easier for the boring tasks.

This is just one example but might be pretty common if you have SQL DB already and just want to add an API or create a web GUI very quickly.

We focus on the access layer to the DB in this article. But adding a REST API is just another couple of minutes away.

The typical development workflow is pretty simple and straight forward. You create Models, define schemas, apply them to a Database and everything is fine.

But sometimes you do not start from zero. Sometimes there already is a Database and you just want to create a model, an API or a whole Application around it. This is also perfectly possible with PythonOnWheels and database reflection. We will cover how this works in the following short hands-on tutorial.

Working with an existing SQL Database.

Prerequisites:

Install PythonOnWheels

pip install -U pythononwheels

I will use MariaDB for this example.
I will use an open source sample DB “employee.sql” for MySQL/MariaDB. You can download it here on github.
I use pymysql for this so you might want to: (It’s included in PoW from 0.903)

pip install PyMySQL

Create the DB using the mysql cli tool:

mysql -u root -p < employees.sql

This is how the DB and table structure looks like

Let’s configure the connection

I added a user named employee_db and granted the according rights. But for tests you can also go with root. Just as usual configure your sql DB connection in conf.config.py:

"type"      :   "mysql+pymysql", 
        "dbname"    :   "employees",           
        "host"      :   "127.0.0.1",      
        "port"      :   3306,      
        "user"      :   "employee_db", 
        "passwd"    :   "<your_pwd_here>",

We use reflection to automatically create a model schema

We will go through the workflow once for the employees table

Just generate a model as usual:

python generate_model.py -n employee -t sql

Now we need to change four things:

set a custom table name: employees unsing the __tablename__ attribute. (actually PythonOnWheels automatically chooses the right tablename plural for us but this is to show you that it can manually be changed)
disable using the PoW schema enhancements (id, created_at, last_updated) using: _use_pow_schema_attrs = False
give an empty schema. We will use reflection from the DB to create the schema automatically.
set the autoload parameter to enable reflection in __table_args__ : add “autoload” : True

This is how the final model should look like:

@relation.setup_sql_schema() 
class Employee(Base, metaclass=PowBaseMeta): 
     
    # 
    # cerberus style schema 
    # 
    schema = {} 
    # define a custom tablename to link for this model: 
    __tablename__ = "employees" 
     
    # Toggle using the pow schema extensions (id, created_at, last_updated) 
    _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=[] 
    # Add sqlalchemy table_args here. Add "autoload" : True for reflection 
    __table_args__ = { "extend_existing": True, "autoload" : True  } 
     
    # 
    # init 
    # 
    def __init__(self, **kwargs): 
        self.setup_instance_values() 
        self.init_on_load(**kwargs)

Let’s see if we can connect and work with Employees

Lets go with the python interpreter

Init the sql environment

python init_sqldb_environment.py 
---------------------------------------------------------------------- 
updated migration environment: mysql+pymysql://employee_db:employee@127.0.0.1:3306/employee 
----------------------------------------------------------------------

Import the model and create an Employee instance.

>>> from testapp.models.sql.employee import Employee 
setup_schema:employee 
>>> e=Employee() 
trying to find possible observer in testapp.models.sql.employee_observer.EmployeeObserver 
>>>

If we look at the structure of an employee you can see that

the entire model schema is automatically reflected from the DB.
there are no (id, created_at and last_updated) attributes.

emp_no              : None (primary key) 
birth_date          : None 
first_name          : None 
last_name           : None 
gender              : None 
hire_date           : None

Let’s see if we can make queries:

>>> e.find_first() 
emp_no              : 10001 (primary key) 
birth_date          : 1953-09-02 
first_name          : Georgi 
last_name           : Facello 
gender              : M 
hire_date           : 1986-06-26 
>>>

Looks good. Let’s get a little more complex

And find and count all employees who’s firstname starts with Ge%

>>> e.find(Employee.first_name.like("Ge%")).count()3740

Last thing we do is to add and update an employee

Let’s first count. This is what Heidi SQL says:

This is what PoW counts:

>>> e.find_all().count() 
300024

So this seems to fit. Now let’s add an employee.

>>> e=Employee()>>> e.first_name="python" 
>>> e.last_name="on wheels" 
>>> import datetime 
>>> e.birth_date=datetime.date.today() 
>>> e.hire_date=datetime.date.today() 
>>> e.gender="F" 
>>> e.emp_no="999997" 
>>> e.upsert()

Lets query the new employee:

>>> e.find_first(Employee.emp_no=="999997") 
emp_no              : 999997 (primary key) 
birth_date          : 2019-04-08 
first_name          : python 
last_name           : on wheels 
gender              : F 
hire_date           : 2019-04-08

Sum it up:

We connected to an existing Database and reflected an existing schema. All we needed to do was:

configure the Database connection parameters in conf.config.py
generate a model and set the attributes __tablename__ = “employees”, _use_pow_schema_attrs=False and autoload = True
init the SQL environment.
Done.

You can add an API and a GUI in some minutes as the next step. See here:

Or check the PythonOnWheels homepage