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.
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?
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
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.
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.
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
Base.metadata.create_all(engine): This line tells the engine that the classes that had
Baseas 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
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:
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!