import pyodbc
import csv

def fetch_and_export_data():
    try:
        # Static connection details
        server = "Blacky"  # Replace with your server name or IP
        port = "1433"         # Replace with your port number if different
        database = "test"     # Replace with your database name
        username = "sa"       # Replace with your username
        password = "xxxxx"  # Replace with your password

        # Define the connection string
        connection_string = (
            f"DRIVER={{ODBC Driver 17 for SQL Server}};"
            f"SERVER={server},{port};"
            f"DATABASE={database};"
            f"UID={username};"
            f"PWD={password};"
        )
        # Connect to the database
        connection = pyodbc.connect(connection_string)
        print("Connected to MS SQL Server successfully.")

        # Create a cursor and execute the query
        cursor = connection.cursor()
        query = "SELECT name, address, phone FROM Customer_Info"
        cursor.execute(query)

        # Fetch all results
        rows = cursor.fetchall()
        #print("\nQuery Results:")
        #print(f"{'Name':<20} {'Address':<30} {'Phone':<15}")
        #print("-" * 65)

        # Print rows to the screen
        #for row in rows:
        #    print(f"{row.name:<20} {row.address:<30} {row.phone:<15}")

        # Export results to CSV
        output_file = "Customer_Info_Export2.csv"
        with open(output_file, "w", newline="", encoding="utf-8") as csv_file:
            writer = csv.writer(csv_file)
            # Write header
            writer.writerow(["Name", "Address", "Phone"])
            # Write data rows
            writer.writerows(rows)

        #print(f"\nData exported to {output_file}")
    except pyodbc.Error as e:
        print(f"Error connecting to SQL Server: {e}")
    finally:
        # Close the connection if it was successfully created
        if 'connection' in locals() and connection:
            connection.close()
            #print("Connection closed.")

if __name__ == "__main__":
    # Call the function
    fetch_and_export_data()