How to Extract Tables from SQL Queries using Python

Introduction

Extracting table names from SQL scripts is a valuable task for various data management activities. Python libraries can simplify this process, making it beneficial for tasks like data catalog creation, query optimization, and security auditing.

In this article, you will explore extracting tables from sql script using pandas library, and parser method.

Importing the dataset

Begin by importing the file containing the SQL scripts you want to extract tables from.

import pandas as pd

--Load the Excel file
excel_file_path = r'C:\\Users\\afoyetunji\\Documents\QueryScripts.xlsx'
df = pd.read_excel(excel_file_path)

Assign SQL Scripts in the Defined Column to a Variable

--Assume the SQL script is loaded into a column named 'sql_scripts'
sql_scripts = df['sql_scripts']

Create Function to extract table names from SQL scripts

This code defines a function named extract_table_names that takes a list of SQL scripts as input. The function aims to extract the table names present in those scripts.

It employs a try-except block to handle potential errors during the extraction process. Inside the try block

def extract_table_names(sql_scripts):
    try:
        parser = Parser(sql_script)
        table_names = parser.tables
        return ', '.join(table_names)
    except Exception as e:
        print(f"Error processing SQL script: {e}")
        return ''

The code above defines a function names extract_table_names which takes in 'sql_scripts ' as a parameter. The try- except blocks handles error during the extraction process.