How to get database size in SQL Alchemy?

user_8106112 0
Viewed 1862

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.

sqlalchemy python
3 Answers

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.

user_66131180

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')
user_39261720

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()
user_3151680
Related