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 thecreate_engine()
function we have'sqlite:///league.db'
that tells the database that an SQLite database should be created in the fileleague.db
. -
Base.metadata.create_all(engine)
: This line tells the engine that the classes that hadBase
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!
Top comments (0)