How to get started with SQLAlchemy
Tags: python, coding, sqlalchemy
Published: 2023-08-28 03:57:37

## 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:
```python
pipenv install alchemy
```
![Installing sqlalchemy](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qs5mp8a2xv0c2tplg072.png)

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:

```python
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: 

```python
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:

```python
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:

```python
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:

```python
# 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
python <filename.py>
```
If everything is done properly you can open up your `league.db` database and find that these tables are showing:
![League table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/srzs431jdux3rmhqqffi.png)
![Team table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sq81zwplj1kmwcm38q87.png)

![Player table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9p0h4mspur0ny9qo7j0j.png)

##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
{% embed https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_creating_session.htm#:~:text=A%20session%20object%20is%20the,%3D%20sessionmaker(bind%20%3D%20engine) %}
{% embed https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_building_relationship.htm#:~:text=The%20relationship%20function%20is%20a,child%20or%20associative%20table%20relationship. %}
{% embed https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/basic_use.html %}
{% embed https://docs.sqlalchemy.org/en/20/orm/quickstart.html#create-an-engine %}