+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: python Extract sql data by combining below code. (/thread-38361.html)
python Extract sql data by combining below code. - mg24 - Oct-03-2022
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" ])
RE: python Extract sql data by combining below code. - mg24 - Oct-03-2022
Hi Mob,
sorry , my mistake , it is not SCP , it should be bcp [Bulk Copy] from sql server.