How to loop through multiple queries seperated by ; in the python-CodePudding

I am working on a python code that will read multiple files in a directory and check all the queries in those files. The code will perform two checks:

it will check if the table name in a CREATE TABLE query is enclosed in double quotes and give a warning if it is, and
it will check if any of the column names in the CREATE statement are predefined names such as CURRENT_DATE, CURRENT_ROLE, CURRENT_TIME, CURRENT_TIMESTAMP, and CURRENT_USER and give an error if it is.

However, the current code only seems to work for one query even though multiple queries are provided in the input document. Can anyone help me modify the code so that it can handle multiple queries in the input document that are separated by ';'

import os
import xlsxwriter
import sqlparse

def get_value():
    file_directory = input("Enter file directory: ")
    output_directory = file_directory
    wb = xlsxwriter.Workbook(output_directory   '\Summary of code.xlsx')
    summary_sheet = wb.add_worksheet('Summary')
    row = 1
    column = 0
    index = 0
    predefined_column_names = ["CURRENT_DATE", "CURRENT_ROLE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER"]
    for filename in os.listdir(file_directory):
        filepath = os.path.join(file_directory, filename)
        with open(filepath, 'r') as f:
            sf_file = f.read()
        parsed_sql = sqlparse.split(sf_file)
        for i, statement in enumerate(parsed_sql):
            if statement.strip().upper().startswith("CREATE TABLE"):
                if "\"" in statement:
                    table_name = statement.split()[2]
                    if table_name.startswith("\"") and table_name.endswith("\""):
                        print(f"{filename} sqlcode{i 1} Warning: Table Name should not be enclosed in double quotes")
                        summary_sheet.write(row, column, filename)
                        summary_sheet.write(row, column 1, f'sqlcode{i 1}')
                        summary_sheet.write(row, column 2, statement)
                        summary_sheet.write(row, column 3, "Warning: Table Name should not be enclosed in double quotes")
                        row  = 1
                for col_name in predefined_column_names:
                    if col_name in statement.upper():
                        print(f"{filename} sqlcode{i 1} Error: Column name '{col_name}' cannot be a predefined name")
                        summary_sheet.write(row, column, filename)
                        summary_sheet.write(row, column 1, f'sqlcode{i 1}')
                        summary_sheet.write(row, column 2, statement)
                        summary_sheet.write(row, column 3, f"Error: Column name '{col_name}' cannot be a predefined name")
                        row  = 1
    wb.close()
    print("Check Completed")

get_value()

I am working on a python code that will read multiple files in a directory and check all the queries in those files. The code will perform two checks:

it will check if the table name in a CREATE TABLE query is enclosed in double quotes and give a warning if it is, and
it will check if any of the column names in the CREATE statement are predefined names such as CURRENT_DATE, CURRENT_ROLE, CURRENT_TIME, CURRENT_TIMESTAMP, and CURRENT_USER and give an error if it is.

CodePudding user response：

In Python, you can use a for loop to iterate through a list of queries separated by semicolons (;). Here's an example of how you can do this:

queries = "SELECT * FROM table1; SELECT * FROM table2; SELECT * FROM table3" query_list = queries.split(";")

for query in query_list: query = query.strip() # Execute the query here (e.g. using a database library) print(query)

In this example, the string queries contains multiple queries separated by semicolons. The split() method is used to split the string into a list of individual queries, which are stored in the query_list variable. The for loop then iterates through each query in the list, and the strip() method is used to remove any leading or trailing whitespace from the query. Finally, the query can be executed (e.g. using a database library).

You can also use the re library of python to split the queries using regular expressions ;

import re queries = "SELECT * FROM table1; SELECT * FROM table2; SELECT * FROM table3" query_list = re.split(r";\s*", queries)

for query in query_list: # Execute the query here (e.g. using a database library) print(query)

In this way, you can loop through multiple queries separated by semicolons (;) in python