Starting from the basic connection, this lecture will tell you how to get the total number of the grouped subquery records.
Basic connection
# Access the app container
docker exec -it container_id sh
# Run python
python
# Connect to the database
import sqlalchemy as db
engine = db.create_engine('mysql+pymysql://root:root@container_name/db_name')
connection = engine.connect()
# Connection Test
metadata = db.MetaData()
table_name = db.Table('table_name', metadata, autoload=True, autoload_with=engine)
# Print all columns in the table
table_columns = table_name.columns.keys()Query
query = db.select([table_name]).limit(1)
result_proxy = connection.execute(query)
result_set = result_proxy.fetchall()Session
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()Model
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class TableName(Base):
__tablename__ = 'table_name'
id = db.Column('key', primary_key=True)
name = db.Column('title')
class AnotherTableName(Base):
__tablename__ = 'another_table_name'
id = db.Column('id', primary_key=True)
tablename_id = db.Column('tablename_id')
title = db.Column('title')
result = session.query(db.func.count(TableName.id),TableName.id).group_by(TableName.id).all()Join
query = session.query(TableName)
query = query.outerjoin(AnotherTableName, TableName.id == AnotherTableName.tablename_id)
query = query.filter(AnotherTableName.title == "apple")
result = query.all()Declare the Query
mysql> select count(*) from table_name group by id
+----------+
| count(*) |
+----------+
| 1 |
...
| 1 |
| 1 |
| 1 |
+----------+
1483 rows in set (0.25 sec)
mysql> select count(*) from (select count(*) from table_name group by id) a;
+----------+
| count(*) |
+----------+
| 1483 |
+----------+
1 row in set (0.01 sec)Group by
query = session.query(TableName.id, func.count('*').label("count"))
query = query.outerjoin(AnotherTableName, TableName.id == TableName.tablename_id)
query = query.filter(AnotherTableName.title == 'apple'))
query = query.group_by(TableName.id)
result = query.all()Subquery
query = session.query(TableName.id, func.count('*').label("count"))
query = query.outerjoin(AnotherTableName, TableName.id == AnotherTableName.tablename_id)
query = query.filter(AnotherTableName.title == 'apple'))
query = query.group_by(TableName.id)
t = query.subquery()
total = session.query(func.count(t.c.count)).scalar()