Using an Object Relational Mapping Database

UNDER CONSTRUCTION

Being able to treat database tables like other Python objects is very valuable. Expecting each row to be represented by a particular Python object allows us to access the columns as properties of the class and handles the insert/update/delete functions for us without having to depend on fragile SQL strings. (Fragile SQL strings means that we are putting code logic into a string which isn’t checked by Python before being run, so if there is a change in the database, we have to search through strings in our code to update the logic rather than being able to depend on refactoring tools provided by our IDEs. Glad you asked what ‘fragile SQL strings’ means?)

The library most often used to do ORM (object relational mapping) is a library called SQLAlchemy. But SQLAlchemy provides more than just ORM utilities, we can use it to create database connections (called engines in SQLAlchemy) as well. The pandas library requires that we provide either a SQLAlchemy engine or the name of a SQLite3 database if we want to leverage the handy .to_sql() and .from_sql() methods. If you are going to manipulate the data in a dataframe anyway, creating a SQLAlchemy engine and using pandas saves a few steps.

The example below shows how to make a SQLAlchemy engine to connect to a SQL Server database. An example on the power of ORM is visited further down the notebook.

from dotenv import load_dotenv
from os import getenv
from sqlalchemy import create_engine

# This library allows the environment variables to be loaded from a file
load_dotenv()
# Set the values for server, user_id, password, and database from environment variables
svr = getenv("MSSQL_SERVER")
uid = getenv("HALLUX_USER")
pwd = getenv("HALLUX_PASSWORD")
db = getenv("HALLUX_DB")

# To get this working quickly, you can just replace these variables with your own values
# svr = 'my-server-name'
# uid = 'my-user-id'
# pwd = 'my-password'
# db = 'my-database-name'

engine = create_engine(f"mssql+pyodbc://{uid}:{pwd}@{svr}/{db}?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes", fast_executemany=True)

cnn = engine.connect()
for row in cnn.execute('select top 10 * from Band'):
    print(row)

(1000, 'A', 'The A-Bomb Project                                                                                  ', '90001', 110000, datetime.datetime(1999, 1, 4, 0, 0))
(1001, 'A', 'Festive Deepak                                                                                      ', '39577', 110010, datetime.datetime(2011, 4, 24, 0, 0))
(1002, 'A', 'Crash The Drug                                                                                      ', '00924', 110020, datetime.datetime(1999, 8, 19, 0, 0))
(1003, 'A', 'Fight The Shame                                                                                     ', '11694', 200037, datetime.datetime(2013, 10, 6, 0, 0))
(1004, 'A', 'Capricious Revelation                                                                               ', '75681', 200002, datetime.datetime(2011, 8, 17, 0, 0))
(1005, 'A', 'Offbeat Sofia                                                                                       ', '17046', 200040, datetime.datetime(2012, 7, 11, 0, 0))
(1006, 'A', 'Ramakrishna Jain                                                                                    ', '37211', 110060, datetime.datetime(2007, 9, 15, 0, 0))
(1007, 'A', 'The Fernandez Disaster                                                                              ', '92243', 110070, datetime.datetime(2007, 11, 27, 0, 0))
(1008, 'A', 'Gabby Dorothy                                                                                       ', '11375', 110080, datetime.datetime(2009, 8, 13, 0, 0))
(1009, 'A', 'The Rivera Gamble                                                                                   ', '21532', 200016, datetime.datetime(1998, 9, 15, 0, 0))