[PYTHON EXPORT SQL TO .txt file]

[PYTHONDUDE]

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()

[buran]

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)

[PYTHONDUDE]

can you please elaborate with pseudo code

[buran]

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

[PYTHONDUDE]

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

[nilamo]

How would you print every column out, if it wasn't a file?
What is a row?
What is a row[0]?

[buran]

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

[PYTHONDUDE]

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

[buran]

please, post your code. if the previous code worked row is tuple as expected. it should work

[nilamo]

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.