8 min read Author: klaas

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

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