What is SQLAlchemy

SQLAlchemy is an ORM (object-relational-mapper). It's an open-source Python library that gives us the tools to transfer data from an SQL database table into Python objects and classes. This gives developers the power to manipulate and manage databases while having the versatility of writing Python Code.

Task

Let's say there's an upcoming basketball league and the commissioner for the league has tasked you to organize the data for the league. The commissioner wants you to keep track of the wins and losses for each team as well as the stats of the players in the team. How can we do this?

Models

Assuming you already have Python and a virtual environment setup-
First we need to install SQLAlchemy. In your terminal write:

pipenv install alchemy

Next, we need to start importing from the library. These imports are essential to work with the databases you will be making. In a new file, we need to write:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = delcarative_base() #This will help us avoid from rewriting code

Now, let's make our classes. These classes are the tables in our database. They can also be referred to as data models:

class League(Base):
    __tablename__ = 'leagues'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    teams = relationship('Team', backref='league') #one-to-many relationship with Team class

class Team(Base):
    __tablename__ = 'teams'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    league_id = Column(Integer, ForeignKey('leagues.id'))
    league = relationship('League', backref='teams') #many-to-one relationship with League class
    players = relationship('Player', backref='team') #one-to-many relationship with Player class

class Player(Base):
    __tablename__ = 'players'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    team_id = Column(Integer, ForeignKey('teams.id'))
    team = relationship('Team', backref='players') #many-to-one relationship with Team class

Okay, let's dissect these lines of code real quick. In each class, we have __tablename__ attribute. This is what the database table will use for its name. There are also Column() objects with arguments such as String() and Integer() in them. This is what the database table will use for its columns and the datatype in that column. The Column() objects have an optional argument where you can enter a primary_key and a ForeignKey to establish relationships between the tables.

Relationships

Through the use of ForeignKey the classes will establish relationships with one another. The League class and the Team class have a one-to-many relationship meaning that there's only one league but many teams. The Team class has a one-to-many relationship as well but with the player class. There is one team with many players.

Configuring the engine

Now that we've made our classes, we can get started with creating the database. To do that, we need a few lines of code:

engine = create_engine('sqlite:///league.db')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

• engine = create_engine(): This line is what creates the connection with the database for us. Inside the create_engine() function we have 'sqlite:///league.db' that tells the database that an SQLite database should be created in the file league.db.
• Base.metadata.create_all(engine): This line tells the engine that the classes that had Base as an argument will be used to create tables.
• Session = sessionmaker(bind=engine): The handle to that let's us interact with the database
• session = Session(): default constructor for the session object

Insert Data

Now with all of this in place, we can start inserting in data:

league = League(name='MDCrabs')
teams_data = [
    ('Ravens', league),
    ('Orioles', league),
    ('Retrievers', league)
]

players_data = [
    # Team "Ravens"
    ("Dwayne Brown", 22, 'Ravens'),
    ("Kevin Shields", 26, 'Ravens'),
    ("Mack Lee", 23, 'Ravens'),
    ("Jasper Nguyen", 24, 'Ravens'),
    ("Andrew King", 29, 'Ravens'),

    # Team "Orioles"
    ("Joseph Wilson", 30, 'Orioles'),
    ("Keith Martinez", 22, 'Orioles'),
    ("Richard White", 31, 'Orioles'),
    ("Ryan Bolt", 23, 'Orioles'),
    ("Michael Ans", 21, 'Orioles'),

    # Team "Retrievers"
    ("Ben Reef", 27, 'Retrievers'),
    ("Joseph James", 26, 'Retrievers'),
    ("Lee Daniels", 28, 'Retrievers'),
    ("Charlie Nguyen", 24, 'Retrievers'),
    ("Neil Harris", 24, 'Retrievers')
]

Do note that all the data that we are inserting matches the attributes in the models that we created previously. If we created a team, it should have the name of the team or if we created a player it should have the name and age of the player.

Finally, we can add our teams and players into the database with the following code:

# Adds teams
teams = []
for team_name, league_instance in teams_data:
    team = Team(name=team_name, league=league_instance) #Creates team instance
    teams.append(team) #Adds team instance to the teams list
    session.add(team) #Adds the team instances into the session

# Adds players
for name, age, team_name in players_data:
    team = next(team for team in teams if team.name == team_name)
    player = Player(name=name, age=age, team=team) #Creates player instance
    session.add(player) #Adds the player instance into the session

session.commit() #Updates the database to match the session

Once we've written all the code all that we need to do is run the file. We can do this by writing:

python <filename.py>

If everything is done properly you can open up your league.db database and find that these tables are showing:

Conclusion

Congratulations! You now know how to use SQLAlchemy. You've completed the task and the commissioner of the league is very happy with your work. You've been introduced to a problem, worked through a solution and now you've solved it. There will be times like these when you as a software developer will be tasked to come up with a solution to a data problem. With the use of SQLAlchemy, you have the power and versatility to write and develop scripts that will efficiently manage your data. Thank you for reading!

Sources