chat_gtp_test_sql

chat_gtp_test_sql.md

To modify the program to write the results to a markdown file with a table, we'll adjust the script to collect the results in a list and then write this list to a markdown file at the end. Each row in the markdown table will contain the SQL file name and the output status.

Here's the updated script:

import os
import psycopg2

# Database configuration
db_config = {
    'dbname': 'your_database_name',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host'
}

# Connect to your postgres DB
conn = psycopg2.connect(**db_config)

# Function to add LIMIT 1000 to SQL query
def add_limit_to_query(sql):
    # Basic implementation: append LIMIT 1000 if not already in query
    if 'LIMIT' not in sql.upper():
        return sql.strip() + ' LIMIT 1000'
    return sql

# Prepare a list to hold the results
results = []

# Loop through each .sql file in the folder
for file in os.listdir('path_to_your_sql_files'):
    if file.endswith('.sql'):
        with open(os.path.join('path_to_your_sql_files', file), 'r') as f:
            sql = f.read()
            modified_sql = add_limit_to_query(sql)

            try:
                # Execute the SQL command
                with conn.cursor() as cur:
                    cur.execute(modified_sql)
                    query_results = cur.fetchall()

                    # Determine the status
                    status = 'success' if query_results else 'empty'
                
                results.append((file, status))

            except Exception as e:
                # Record error status
                results.append((file, 'error'))

# Close the connection
conn.close()

# Write results to a markdown file
with open('sql_execution_results.md', 'w') as md_file:
    md_file.write('| SQL File | Status |\n')
    md_file.write('|----------|--------|\n')
    for file, status in results:
        md_file.write(f'| {file} | {status} |\n')

This script will create a markdown file named sql_execution_results.md. The file will contain a table with two columns: "SQL File" and "Status". Each row in the table corresponds to one of the SQL files processed, listing the file name and its execution status.

As before, replace 'your_database_name', 'your_username', 'your_password', 'your_host', and 'path_to_your_sql_files' with your actual database details and the path to your SQL files. Remember that the function add_limit_to_query is basic and may need to be adjusted for more complex SQL queries.