python Extract sql data by combining below code.

[mg24]

Hi Team,

How to combine below two code both code works, I want sql data using SCP Method.
SCP method is faster to extract data. SCP method does not have extract header option , hence I am using PYODBC Module header.



---------Extract header of sql table -----------into csv

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)

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

with open(fullpath, "a", newline="") as outfile:
            writer = csv.writer(outfile, delimiter="|", quoting=csv.QUOTE_NONNUMERIC)
		writer.writerows([header, row])


-----------Append sql data to csv without header---------------
[Python]
from pathlib import Path
import subprocess
 
source_table = "xxx_xxxx_xxx_POC.dbo.Table1"
destination = str(Path("E:\test\table1.csv"))
subprocess.run(["bcp", source_table, "out", destination,  "-ABCD12345678\\ABCD5678,10010","-c", '-t"|"', "-T" ])

[mg24]

Hi Mob,

sorry , my mistake , it is not SCP , it should be bcp [Bulk Copy] from sql server.