Displaying SQL Database Usage Information retrieved through Python Script from SQL Server

In this YouTube video, we will learn how to create a dashboard that displays the space usage of a SQL Server database using Python. This exercise was done based on a request by one of our users in the comment section. We hope we were able to give you some good ideas!

To retrieve the database information from SQL Server, we will import the pyodbc library, while the itertools library will be used to convert the SQL query result into a list. To use data frames in our project, we will also need to import the pandas library.

We will create an empty data frame with predefined columns to store the database name, database size, and unallocated space. We can always use the print command to verify our code and ensure that the empty data frame has been created with our given column headings.

Similarly, we will create another data frame to store the usage statistics of tables within the database. We will define the table headings and print the data frame to ensure that it is properly created.

Using the PyODBC Library, we will connect to the SQL Server database. We need to select the appropriate driver, which is SQL Server, and provide the name of the SQL Server, which we can get from SQL Server Management Studio. To connect to the SQL Server database, we need to provide the name of the database in the connection string.

We will create a cursor to execute SQL statements and retrieve data from the SQL database. Using the “space used” SQL command, we will get the details of space used by the database. We will use a variable to store the result of the cursor in a list format and use the itertools library to convert the results from the cursor to a list.

To remove non-numeric characters and spaces from our list, we will use a for-loop. Finally, we will print the results to confirm that all the non-numeric characters and spaces have been removed.

Python Code:
import pyodbc
import itertools
import pandas
db_usage = pandas.DataFrame(columns=['database_name','database_size','unallocated_space'])
tb_usage = pandas.DataFrame(columns=['name','rows','reserves','data','index_size','unused'])
con = pyodbc.connect('Driver={SQL Server};'
                     'Server=DESKTOP-7V7PKKP\SQLEXPRESS;'
                     'Database=Call_Center;')
cursor = con.cursor()

cursor.execute('sp_spaceused')
dbs = list(itertools.chain.from_iterable(cursor))
dbs = [s.strip('MB') for s in dbs]
dbs = [s.strip(' ') for s in dbs]
db_usage = db_usage.append(pandas.DataFrame([dbs],columns=['database_name','database_size','unallocated_space']))

cursor.execute('select name from sys.tables')
tables = list(itertools.chain.from_iterable(cursor))

for t in tables:
    cursor.execute('sp_spaceused ' + t)
    tbs = list(itertools.chain.from_iterable(cursor))
    tbs = [s.strip('KB') for s in tbs]
    tbs = [s.strip(' ') for s in tbs]
    tb_usage = tb_usage.append(pandas.DataFrame([tbs],columns=['name','rows','reserves','data','index_size','unused']))

#SQLServer #Python #Dashboard #DataFrames #Pandas #ODBC #SpaceUsage #ITutorials