I am trying to read a file which contains a list of table_names and I want to execute a simple query:
SELECT *
FROM $TABLE_NAME
from each SQL Server database.
The results of which I need to store in a separate .csv file.
Can you please help how to achieve this?
You have to read data from server and write into csv:
get data from sql:
import pyodbc
import csv
mydb = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=Server;"
"Database=Database;"
"uid=username;pwd=password")
cursor = mydb.cursor()
sql = """SELECT * FROM $TABLE_NAME"""
cursor.execute(sql)
row = cursor.fetchall()
write data into csv:
with open('test.csv', 'w', newline= '') as f:
a = csv.writer(f, delimiter=',')
a.writerow(["Header 1", "Header 2"]) ## etc
a.writerows(row)
Give this code a try.
import pyodbc
import csv
# SQL Server Connection settings
conn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=server;"
"Database=dbName;"
"uid=User;pwd=password"
"Trusted_Connection=yes;")
cursor = conn.cursor()
inputFile= open("absolute_inputfile_path","w+")
outputDataLocation="absolute_outputfile_path"
# Reading inout file line by line, assuming each line is a table name
line = inputFile.readline()
while line:
tableName = line
line = f.readline()
query = "SELECT * FROM " + str(tableName)
# Read query data
cursor.execute(query)
rows = cursor.fetchall()
# Write to File as CSV
fileWriter = open(outputDataLocation + "/" + str(tableName), 'w')
myFile = csv.writer(fileWriter)
myFile.writerows(rows)
fileWriter.close()
inputFile.close()
