How to write the SQL Server query output into .txt file using Python?

Question

I am new to Python and trying to connect to the SQL Server db and get the output of query into a flat .txt file.

Some code was working but only close to 1000 records were written and then it stops.

Python version: 2.7.13.

The below code is able to write all 1 million records into csv file not .txt file which is the problem.

import sys
print sys.path
import pyodbc
import pandas as pd
connection = pyodbc.connect('DRIVER={SQL Server};SERVER=HCR046TW5SQL\HCRMIG50016;DATABASE=ENT;UID=pmatsa1;PWD=password@2015_1711;autocommit=True')
print 'Trying to assign cursor connection'
cursor = connection.cursor()
sql = """SELECT 
LEFT(ltrim(ISNULL(IN_OUT_BUILDING_NUM,' '))+REPLICATE(' ', 10) , 10)+
LEFT( ltrim(ISNULL(IN_OUT_ADR_ORIG_SHORT,' '))+REPLICATE(' ', 50) , 50)+
LEFT(ltrim(ISNULL(IN_OUT_ADR_ORIG_CITY,' '))+REPLICATE(' ', 28) , 28)+
LEFT(ltrim(ISNULL(IN_OUT_ADR_ORIG_STATE,' '))+REPLICATE(' ', 2) , 2)+
LEFT(ltrim(ISNULL(IN_OUT_ADR_ORIG_ZIP,' '))+REPLICATE(' ', 9) , 9)
 FROM ADDR_VAL_STAN_PB;"""
DataOut = open("Address_Validation_Input_File.txt", "a+")
cursor.execute(sql)

# Get data in batches
while True:
    # Read the data
    df = pd.DataFrame(cursor.fetchmany(1000))
    # We are done if there are no data
    if len(df) == 0:
        break
    # Let's write to the file
    else:
        df.to_csv(DataOut, header=False)

# Clean up
DataOut.close()
cursor.close()
connection.close()

Try using fetchall() instead of fetchmany(1000). It should work. — user15051990
– user15051990, Commented Jan 24, 2018 at 18:33
Hi Kaushik, I do not want csv file as output at all.I just need text file.The code which I have given was for csv but I need it in text file. — Praveen
– Praveen, Commented Jan 24, 2018 at 22:09
What have you done to convert this script from CSV output to text file output, Praveen? It seems to me that in regard to this question, (a) there is not a clear statement of your requirements, and (b) you have not tried anything. Note that Stack Overflow is not a clearing house for free labour - we need you to make a clear effort first. — halfer
– halfer, Commented Jan 24, 2018 at 23:18

user15051990 · Accepted Answer · 2018-01-25 17:04:15Z

1

Replace the below code with the code below while in your code:

df_csv=pd.DataFrame()
while True:
# Read the data
     df = pd.DataFrame(cursor.fetchall())
     # We are done if there are no data
     if len(df) == 0:
            break
     # Let's write to the file
     else:
          df_csv.append(df)
df_csv.to_csv('D:/path/test.txt', header=None, index=None, sep=' ', mode='a')

edited Jan 25, 2018 at 17:04

answered Jan 24, 2018 at 18:38

user15051990

1,9454 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Praveen Over a year ago

Hi Kaushik, I do not want csv file as output at all.I just need text file.The code which I have given was for csv but I need it in text file

user15051990 Over a year ago

I have edited code, have a look. Please select the answer if it works.

Collectives™ on Stack Overflow

How to write the SQL Server query output into .txt file using Python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related