How to get database size in SQL Alchemy?

Question

I am looking for a way to get the size of a database with SQL Alchemy. Ideally, it will be agnostic to which underlying type of database is used. Is this possible?

Edit: By size, I mean total number of bytes that the database uses.

What do you suppose to be the "size"? The number of rows in all tables? Number of bytes of data in the rows? The physical size of files that contain tables? Anything else? — 9000, Jun 25 '19 at 20:20

score 1 · Accepted Answer · answered Jul 13 '19 at 12:40

1

The way I would do is to find out if you can run a SQL query to get the answer. Then, you can just run this query via SQLAlchemy and get the result.

answered Jul 13 '19 at 12:40

kotartemiy

94
1
3

can you provide example? – Averner Mar 13 '20 at 12:08

score 0 · Answer 2 · answered Apr 13 '20 at 20:10

Currently, SQLAlchemy does not provide any convenience function to determine the table size in bytes. But you can execute an SQL statement. Caveat is that you have to use a statement that is specific to your type of SQL (MySQL, Postgres, etc)

Checking this answer, for MySQL you can execute a statement manually like:

from sqlalchemy import create_engine

connection_string = 'mysql+pymysql://...'
engine = create_engine(connection_string)
statement = 'SELECT table_schema, table_name, data_length, index_length FROM information_schema.tables'
with engine.connect() as con:
     res = con.execute(statement)
     size_of_table = res.fetchall()

For SQLite you can just check the entire database size with the os module:

import os

os.path.getsize('sqlite.db')

score 0 · Answer 3 · answered Nov 26 '21 at 20:22

For PostgreSQL you can do it like this:

    from sqlalchemy.orm import Session

    dbsession: Session

    engine = dbsession.bind
    database_name = engine.url.database
    # https://www.a2hosting.com/kb/developer-corner/postgresql/determining-the-size-of-postgresql-databases-and-tables  
    # Return the database size in bytes
    database_size = dbsession.execute(f"""SELECT pg_database_size('{database_name}')""").scalar()

How to get database size in SQL Alchemy?

3 Answers3