我是Python新手,尝试连接到sqlserverdb并将查询的输出转换成一个flat.txt文件。在
一些代码正在工作,但是只写了将近1000条记录,然后就停止了。在
Python版本:2.7.13。在
下面的代码能够把100万条记录全部写入csv文件而不是.txt文件,这是问题所在。在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()