Reflecting an existing SQL Database schema

The normal PythonOnWheels 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. We will cover how this works in the following short hands-on tutorial. 

Working with an existing SQL Database.

Prerequisites:

• I will use MariaDB for this example. 
• I will use an open source sample DB "employee" for MySQL/MariaDB. You can find 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 config.py:

        "type"      :   "mysql+pymysql", 
        "dbname"    :   "employees",           # just a name for non file based DBs 
        "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 using the __tablename__ attribute.
• disable using the PoW schema enhancements (id, created_at, last_updated) using:  _use_pow_schema_attrs = False
• give an empty schema. 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

>>> 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 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:

• Create an API using REST handlers
• Scaffold a web GUI