How to INNER JOIN with SQLAlchemy

I was addicted to it because I didn't have much information, so I'll take a note.

This time, I will show an example of INNER JOIN using the sample table provided by MySQL.

MySQL :: MySQL Documentation: Other MySQL Documentation Get the sample data from the link to the right of the menagerie database on the page above and load it.

The loading method to MySQL is omitted. The contents of the sample data are two tables, each containing 10 and 9 records.

mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+
2 rows in set (0.01 sec)

mysql> select * FROM event;
+----------+------------+----------+-----------------------------+
| name     | date       | type     | remark                      |
+----------+------------+----------+-----------------------------+
| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
| Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
| Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
| Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
| Slim     | 1997-08-03 | vet      | broken rib                  |
| Bowser   | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1998-08-28 | birthday | Gave him a new chew toy     |
| Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
| Whistler | 1998-12-09 | birthday | First birthday              |
+----------+------------+----------+-----------------------------+
10 rows in set (0.00 sec)

mysql> select * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

The story of SQLAlchemy from here. I often wanted to make this kind of connection. (By the way,'litter' means giving birth to an animal)

mysql> SELECT event.name, event.date, event.type, event.remark, pet.species  
        FROM event INNER JOIN pet ON event.name = pet.name
        WHERE event.type ='litter';
+--------+------------+--------+-----------------------------+---------+
| name   | date       | type   | remark                      | species |
+--------+------------+--------+-----------------------------+---------+
| Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male | cat     |
| Buffy  | 1993-06-23 | litter | 5 puppies, 2 female, 3 male | dog     |
| Buffy  | 1994-06-19 | litter | 3 puppies, 3 female         | dog     |
+--------+------------+--------+-----------------------------+---------+
3 rows in set (0.00 sec)

How to express this in SQLAlchemy is as follows.

   #Create an instance of each table
   events = Table('event', metadata, autoload=True)
   pets = Table('pet', metadata, autoload=True)

   #:Create a list of columns you want to get
   columns = [events, pets.c.species]

    #: join()Specify the table and condition you want to join with select()Call
    q = join(events, pets, events.c.name == pets.c.name) \
        .select() \
        .where(events.c.type == 'litter')
    #:Finally with_only_columns()Pass the list of columns you want to get to
    q =  q.with_only_columns(columns)

with_only_columns () is used to narrow down the fields to get.

Next, create the following query as an example of a query that uses count () using GROUP BY.

mysql> SELECT event.name, count(event.name) AS litter_count 
       FROM event INNER JOIN pet ON event.name = pet.name 
       WHERE event.type = 'litter' GROUP BY event.name;

+--------+--------------+
| name   | litter_count |
+--------+--------------+
| Buffy  |            2 |
| Fluffy |            1 |
+--------+--------------+
2 rows in set (0.00 sec)
    #: func.count()Specify the columns to be aggregated with
    #: AS litter_label count('litter_count')Specified by
    columns = [events.c.name, func.count(events.c.name).label('litter_count')]

    q = join(events, pets, events.c.name == pets.c.name) \
        .select() \
        .where(events.c.type == 'litter') \
        .group_by(events.c.name)  # <=Add one line to the previous query
    q =  q.with_only_columns(columns)

In a shorter way, the code looks like this:

    q =  select(events.c.name == pets.c.name) \
        .where(events.c.type == 'litter') \
        .group_by(events.c.name)
    q =  q.with_only_columns(columns)

The join condition has moved into select () without join ().

With SQLAlchemy, you can write queries using INNER JOIN, GROUP BY and COUNT () with just this much code.

It's a difficult story, but by the time I got here, I read various documents and finally made this query. SQLAlchemy is so rich in features that it's a good idea to read the documentation before using it in earnest. Japanese information is not so much, so I read and studied Stack Overflow and English sites quite a bit. If you do Python, it's hard if you can't speak English. For Ruby, there is a lot of Japanese information thanks to Rails, but Python is not popular in Japan, so I feel that there is little know-how.

I think it will take a long time to understand once, but once I understand it, I realized that it is a fairly powerful library. I'm still studying, so I don't think the above writing style is always the best, and there should be a better writing style that is more readable.

I've seen a lot of documentation that SQLAlchemy uses as an ORM, but I like to use SQLAlchemy as a query builder for SELECT statements.

Rather than making a library of your own query builder in a project and making it impossible or not I found it much easier for everyone to study and use SQLAlchemy.

sample.py


#!/usr/bin/env python2.7
# -*- coding:utf-8 -*-

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.sql import select, join, func

from pprint import pprint

config = {
    'user': 'user',
    'passwd':'password',
    'host': 'localhost',
    'port': 3306,
    'database': 'menagerie',
}
dsn_fmt = 'mysql+pymysql://%(user)s:%(passwd)s@%(host)s:%(port)d/%(database)s'
dsn = dsn_fmt % config

engine = create_engine(dsn, echo=True)

metadata = MetaData(bind=engine)

events = Table('event', metadata, autoload=True)
pets = Table('pet', metadata, autoload=True)

if __name__ == '__main__':
    #: part1
    columns = [events, pets.c.species]
    q = join(events, pets, events.c.name == pets.c.name) \
        .select().where(events.c.type == 'litter')
    q =  q.with_only_columns(columns)
    print q
    for row in q.execute():
        pprint(dict(row))

    #: part2
    columns = [events.c.name, func.count(events.c.name).label('litter_count')]
    q = join(events, pets, events.c.name == pets.c.name) \
        .select() \
        .where(events.c.type == 'litter') \
        .group_by(events.c.name)
    q =  q.with_only_columns(columns)
    print q
    for row in q.execute():
        pprint(dict(row))

    #: part2 another version
    q =  select(events.c.name == pets.c.name) \
        .where(events.c.type == 'litter') \
        .group_by(events.c.name)
    q =  q.with_only_columns(columns)
    print q
    for row in q.execute():
        pprint(dict(row))

Recommended Posts

How to INNER JOIN with SQLAlchemy
How to update with SQLAlchemy?
How to Alter with SQLAlchemy?
How to Delete with SQLAlchemy?
How to get parent id with sqlalchemy
How to use SQLAlchemy / Connect with aiomysql
Introduction to RDB with sqlalchemy Ⅰ
How to cast with Theano
How to separate strings with','
How to RDP with Fedora31
How to convert a class object to a dictionary with SQLAlchemy
How to get more than 1000 data with SQLAlchemy + MySQLdb
How to handle session in SQLAlchemy
Python: How to use async with
Connect to multiple databases with SQLAlchemy
How to use virtualenv with PowerShell
How to deal with imbalanced data
How to install python-pip with ubuntu20.04LTS
How to deal with imbalanced data
Introduction to RDB with sqlalchemy II
How to get started with Scrapy
How to get started with Python
How to deal with DistributionNotFound errors
How to get started with Django
How to use FTP with Python
How to calculate date with python
How to install mysql-connector with pip3
How to install Anaconda with pyenv
How to authenticate with Django Part 2
How to authenticate with Django Part 3
How to do arithmetic with Django template
How to title multiple figures with matplotlib
Join CentOS 8.3 with SSSD to Active Directory
How to add a package with PyCharm
How to use OpenVPN with Ubuntu 18.04.3 LTS
How to use Cmder with PyCharm (Windows)
How to prevent package updates with apt
How to work with BigQuery in Python
How to use Ass / Alembic with HtoA
How to deal with enum compatibility errors
How to use Japanese with NLTK plot
How to do portmanteau test with python
How to search Google Drive with Google Colaboratory
How to display python Japanese with lolipop
How to download youtube videos with youtube-dl
How to use jupyter notebook with ABCI
How to power off Linux with Ultra96-V2
"How to pass PATH" to learn with homebrew
How to use CUT command (with sample)
How to enter Japanese with Python curses
[Python] How to deal with module errors
How to install zsh (with .zshrc customization)
How to read problem data with paiza
How to get started with laravel (Linux)
How to group volumes together with LVM
How to install python3 with docker centos
How to use JDBC driver with Redash
How to selectively delete past tweets with Tweepy
How to upload with Heroku, Flask, Python, Git (4)
How to deal with memory leaks in matplotlib.pyplot
How to create sample CSV data with hypothesis