I would like to export my sql server query into a txt file.
import PYODBC
db = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=server_name;"
"Database=table_name;"
"Trusted_connection=yes;")
cur= db.cursor()
sql = """SELECT TOP 5 * FROM Table"""
file= open("C:\\Users\\PYTHONDUDE\\Documents\\PYTHON_SCRIPTS\.test.txt", "w")
cur.execute(sql)
for row in cursor:
print>>file, row[0] ----- My error happens here using python 3.6
file.close()
in python3 print is function. to redirect the the output to something else, instead to stdout use
file argument
also it's good idea not to use builtin functions as variable names (file is builtin function in python2)
can you please elaborate with pseudo code
with pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=server_name;"
"Database=table_name;"
"Trusted_connection=yes;") as db:
cur = db.cursor()
sql = """SELECT TOP 5 * FROM Table"""
cur.execute(sql)
with open("C:/Users/PYTHONDUDE/Documents/PYTHON_SCRIPTS/.test.txt", "w", newline='') as f:
for row in cur:
print(row[0], file=f)NB - not tested, but should work
THANK YOU SO MUCH
final question
the print(row[0], file=f) only prints out 1 column (ID) , however my sql statement has over 10 cloumns, (ID, NAME, DATE , COUNTRY, STATE, .....) how can i modify to capture all data inmy sql.
AGAIN THANKS IN ADVANCE
How would you print every column out, if it wasn't a file?
What is a row?
What is a row[0]?
it prints just ID because of row[0], i.e. that is the first element in row
import csv
with pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=server_name;"
"Database=table_name;"
"Trusted_connection=yes;") as db:
cur = db.cursor()
sql = """SELECT TOP 5 * FROM Table"""
cur.execute(sql)
with open("C:/Users/PYTHONDUDE/Documents/PYTHON_SCRIPTS/.test.txt", "w", newline='') as f:
wrtr = csv.writer(f)
for row in cur:
wrtr.writerow(row)
#wrtr.writerows(cur.fetchall())better to use csv module
you can replace the for row block with the last line
for my knowledge why is it that i cant write all rows to my .txt file
the csv code returns an error wrtr.write(row) Attributerror: '_csv.writer' object has no attribute to write.
I updated the code to wrtr.writerow() the csv file was created but no data added to file
i also tried wrtr.writerow([ID, NAME, ADDRESS, DATE]) nothing written to file
please, post your code. if the previous code worked row is tuple as expected. it should work
(Feb-21-2018, 04:56 PM)nilamo Wrote: [ -> ]How would you print every column out, if it wasn't a file?
What is a row?
What is a row[0]?
You've gotten this far, I really do think you can solve this yourself if you take a step back and think about what you're doing.