Building custom queries for your database can be time consuming and complex. Object-Relational Mapping is a concept designed to ease this burden and make code easier to read. The job of an ORM is to take our data objects and take care of the heavy lifting for us. Today we will utilize the built in ORM features of the SqlAlchemy library for python.
pip install sqlalchemy
From within sqlalchemy we will utilize something called declarative_base. This function will construct a base class that we will associate with our data models.
from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker
Building Our Data Models
Next we will create a base object from our declarative base class and build a few data model classes.
Base = declarative_base() class PetOwners(Base): __tablename__ = "pet_owners" owner_id = Column('owner_id', Integer, primary_key=True) owner_name = Column('owner_name', String) owner_age = Column('owner_age', Integer) class Pets(Base): __tablename__ = "pets" pet_id = Column('pet_id', Integer, primary_key=True) pet_name = Column('pet_name', String) pet_species = Column('pet_species', String) pet_age = Column('pet_age', Integer)
Start your engines
Now we have a two classes, 1 for owners and another for pets. Each class will translate into a table and each attribute to a field. Now lets build a database session and sqlalchemy will auto build our schema for us. For this example we will be utilizing a sqllite database with a file name ‘testdb.db’
#Create our connection to our sqllite Database engine = create_engine('sqlite:///testdb.db', echo=True) #Create a database schema based on our pet owner and pet classes above. Base.metadata.create_all(bind=engine) #Create a session object we can use to communicate with our database Session = sessionmaker(bind=engine) session=Session()
Add some data
Now that we have our data models built and schema deployed we can start adding data. All we have to do is create a object from one of our classes, pass data to it and add it to our session. SqlAlchemy will automatically take care of our unique IDs and auto increment them as necessary.
#Create our owner and pet objects and pass some data to them newOwner = PetOwners(owner_name="Mary", owner_age="34") newPet = Pets(pet_name='Olly', pet_species='Golden Retriever', pet_age='3') #Add our data objects to our session session.add(newOwner) session.add(newPet) #Commit our data to the database and close our session session.commit() session.close()
Sqlalchemy will take our data objects and generate the insert statements on the backend. It’s just that easy! You can also check that the data is there using a tool like sqlite browser (Found here: https://sqlitebrowser.org/). SqlAlchemy ORM is a powerful tool that can speed up development time and make your code much easier to read. This concept can easily be paired with flask to make a restful api or web form. Check back soon and we will go over how to model relationships with SqlAlchemy ORM.
Full Code can be found here: https://github.com/BuckarewBanzai/SqlAlchemy-ORM-Example/blob/master/main.pycomments powered by Disqus