Create multiple/single csv file for each sql records

[buran]

This

        for idx, row in enumerate(crsr, start=1):
            writer.writerows(crsr)

should be just

writer.writerows(crsr)

With your current code it will write n*n number of rows, where the n is number of rows returned. Actually it will write it only once (i.e. n rows), because crsr will be exhausted after first run. Still better fix it.

If there are no rows returned it will write empty file, with just header.
There is option to use crsr.rowcount, BUT note there are limitations:

rowcount

The number of rows modified by the last SQL statement.

This is -1 if no SQL has been executed or if the number of rows is unknown. Note that it is not uncommon for databases to report -1 immediately after a SQL select statement for performance reasons. (The exact number may not be known before the first records are returned to the application.)

You can try fetchone record, then check if anything is returned, write to the file and then write all the remaining records in one go (i.e. the crsr not yet consumed).

---

not tested:

import pyodbc
import csv
import os
 
connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-GQK64O6;DATABASE=Customer;Trusted_Connection=yes;')
with connection.cursor() as crsr:
    qry = "Select * from employee"
    crsr.execute(qry)
    first_record = crsr.fetchone()

    if first_record:
        folderPath = "C:\\Users\\malle\\OneDrive\\Desktop\\C\\test_data\\output"
        header = next(zip(*crsr.description))

        fname = "main.csv"
        fullpath = os.path.join(folderPath, fname)
        with open(fullpath, "w", newline="") as outfile:
            writer = csv.writer(outfile, delimiter="|", quoting=csv.QUOTE_NONNUMERIC)
            writer.writerows([header, first_record])
            writer.writerows(crsr)
    else:
        print('No records')