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