SQLAlchemy is a Python library that provides a high-level interface for communicating with relational databases. It supports many databases, including SQLite, MySQL, and PostgreSQL, and allows you to perform common database operations using Python code instead of writing raw SQL.

To query data using SQLAlchemy, you start by defining a Python class that maps to a database table, called a “model”. You then create an instance of this class, called a “session”, and use its methods to interact with the database. The following is an example of how you can query data using SQLAlchemy:

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

result = session.query(User).all()
for user in result:
    print(user.name, user.age)

In this example, we create a User class that maps to the “users” table in the database and define columns for id, name, and age. We then create an engine that connects to an SQLite database, and a session to interact with the database. Finally, we use the query method of the session to fetch all rows from the “users” table and print the name and age of each user.

Database Used

We picked the dataset from Kaggle. We picked the ‘European Football Database’, the link given as: https://www.kaggle.com/datasets/groleo/european-football-database?select=european_database.sqlite. 

The database has 2 tables ‘matchs’ and ‘divisions’. Today we will be performing querying operations on this dataset.

The outline of the database is provided below:

Querying Data From a Dataset

As it is told earlier, our dataset has 2 tables, divisions, and matchs. We will be using SQLAlchemy’s query API to perform several tasks. We will also provide you with an equivalent SQL statement for better understanding.

SQLAlchemy provides a powerful query API that allows you to build complex queries by chaining together methods that filter and modify the data. For example, you can use the filter method to limit the result set to rows that match certain conditions, and the order_by method to sort the result set:

result = session.query(User).filter(User.age > 18).order_by(User.name).all()
for user in result:
    print(user.name, user.age)

In this example, we use the filter method to only include users whose age is greater than 18, and the order_by method to sort the result set by the name column.

There are several other query features that we will see in a while.

1. LOAD DATASET IN SQLALCHEMY

Loading data is an easy task. First, download the dataset from the link provided above. You just need to write these lines of code:

import sqlalchemy as db
engine = db.create_engine("sqlite:///european_database.sqlite")

connection = engine.connect()
metadata = db.MetaData()
divisions = db.Table('divisions', metadata, autoload=True, autoload_with=engine)


We created an engine, then opened the connection, defined the divisions’ table with metadata, and autoload is set to True which is obvious. 

To check tables’ columns:

print(divisions.columns.keys())

['division', 'name', 'country']

2. SELECT STATEMENT (UNCONDITIONAL)  QUERY:

A SELECT statement is a type of SQL (Structured Query Language) command used to retrieve data from one or more tables in a database. It is used to query the database and return a result set based on the criteria specified in the statement. It can be based on a condition or can be unconditional.

To perform Select Statement in SQLAlchemy for the divisions table, you should write:

# SELECT * FROM divisions                 equivalent sql statement
# Sqlaclchemy: 
query = db.select([divisions]) Res = connection.execute(query)Result = Res.fetchall()Result[:3]

The output will be as follows:

[('B1', 'Division 1A', 'Belgium'),
('D1', 'Bundesliga', 'Deutschland'),
('D2', '2. Bundesliga', 'Deutschland')]

3. SELECT STATEMENT (CONDITIONAL)  QUERY:

A condition in SQL Select Statement is given by the ‘WHERE’ clause. To perform the SQL Select operation in SQLAlchemy, just write these lines of code:

# SQL :
# SELECT * FROM divisions  WHERE country ='England'

# SQLAlchemy :
query=db.select([divisions]).where(divisions.columns.country == 'England')
Res = connection.execute(query)
Result = Res.fetchall()

Result

The output will be as follows:

[('E0', 'Premier League', 'England'),
('E1', 'EFL Championship', 'England'),
('E2', 'EFL League One', 'England'),
('E3', 'EFL League Two', 'England'),
('EC', 'National League', 'England')]

To select a particular column using Select, write these lines of code:

# SQL :
# SELECT name FROM divisions  WHERE country ='England'
# SQLAlchemy :

query=db.select([divisions.columns.name]).where(divisions.columns.country == 'England')
ResultProxy = connection.execute(query)
ResultSet = ResultProxy.fetchall()

ResultSet

The output will be as follows:

[('Premier League',),
('EFL Championship',),
('EFL League One',),
('EFL League Two',),
('National League',)]

4. AND, and OR operations.

The AND and OR operations are used in SQL to combine multiple conditions in a query. They allow you to specify multiple conditions that must be met (or not met) for a row to be included in the result set.

The AND operation is used to specify that multiple conditions must be true for a row to be included in the result set. For example:

# SQL :
# SELECT * FROM census WHERE name = 'Scottish Premiership' AND country = 'Scotland'

# SQLAlchemy :

query=db.select([divisions]).where(db.and_(divisions.columns.name == 'Scottish Premiership', divisions.columns.country == 'Scotland'))

Res = connection.execute(query)
Result = Res.fetchall()

Result
[('SC0', 'Scottish Premiership', 'Scotland')]

The OR operation is used to specify that at least one of multiple conditions must be true for a row to be included in the result set. For example:

# SQL :
# SELECT * FROM census WHERE name = 'Scottish Premiership' OR country = 'Scotland'

# SQLAlchemy :

query=db.select([divisions]).where(db.or_(divisions.columns.name == 'Scottish Premiership', divisions.columns.country == 'Scotland'))

Res = connection.execute(query)
Result = Res.fetchall()

Result

The output will be as follows:

[('SC0', 'Scottish Premiership', 'Scotland'),
('SC1', 'Scottish Championship', 'Scotland'),
('SC2', 'Scottish League One', 'Scotland')]

5. ORDER BY Operation

The ORDER BY operation in SQL is used to sort the result set of a query in ascending or descending order based on one or more columns.

The ORDER BY in SQLAlchemy is done like this:

# SQL :
# SELECT * FROM divisions  ORDER BY division DESC

# SQLAlchemy :

query=db.select([divisions]).order_by(db.desc(divisions.columns.division))
Res = connection.execute(query)
Result = Res.fetchall()

Result

The output will be as follows:

[('T1', 'Süper Lig', 'Turkey'),
('SP2', 'LaLiga 2', 'Spain'),
('SP1', 'LaLiga', 'Spain'),
('SC2', 'Scottish League One', 'Scotland'),
('SC1', 'Scottish Championship', 'Scotland'),
('SC0', 'Scottish Premiership', 'Scotland'),
('P1', 'Liga NOS', 'Portugal'),
('N1', 'Eredivisie', 'Netherlands'),
('I2', 'Seria B', 'Italy'),
('I1', 'Seria A', 'Italy'),
('G1', 'Superleague', 'Greece'),
('F2', 'Ligue 2', 'France'),
('F1', 'Ligue 1', 'France'),
('EC', 'National League', 'England'),
('E3', 'EFL League Two', 'England'),
('E2', 'EFL League One', 'England'),
('E1', 'EFL Championship', 'England'),
('E0', 'Premier League', 'England'),
('D2', '2. Bundesliga', 'Deutschland'),
('D1', 'Bundesliga', 'Deutschland'),
('B1', 'Division 1A', 'Belgium')]

The division column in the divisions table is now arranged in descending order.

6. DISTINCT COMMAND:

The DISTINCT keyword in SQL is used to return only unique values in the result set of a query. It removes duplicate rows from the result set, leaving only one row for each unique combination of values in the selected columns.

We will try to find the unique names of countries in the divisions table:

# SQL :
# SELECT DISTINCT country FROM divisions

# SQLAlchemy :
query=db.select([divisions.columns.country.distinct()])
Res = connection.execute(query)
Result = Res.fetchall()

Result

This will be the output:

[('Belgium',),
('Deutschland',),
('England',),
('France',),
('Greece',),
('Italy',),
('Netherlands',),
('Portugal',),
('Scotland',),
('Spain',),
('Turkey',)]

These are the unique country names in the divisions table.