How can redirect output of SQL query to a text file using Python

Question

Redirect SQL query output to a text file using Python and pyodbc module.

import pyodbc
import os
import sys
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=win-intrst-srv;'
                      'Database=Interests_db;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
count1 = cursor.execute("select count(*) from MissedEvents  where  TenantId > 10000 and remarks like 'Mandatory%' AND RowCreatedDate >= dateadd(hh, -2, getdate())")
print(count1.fetchone()[0]) # This prints out no of rows updated in last 1 hour.

f = open('c:\MonitoringStats\staticentry.txt','a')
f.write('\n' + 'Mandatory field missing count:'+ count1.fetchone()[0])
file.close()

But it's failing with the error:

Type error: Nonetype object is not subscriptable

Can someone help me in redirecting the SQL query output to a file?

It seems likely that your query only returns one row. Therefore, calling fetchone() twice could be the problem. — FiddleStix
– FiddleStix, Commented Feb 27, 2020 at 17:28
Since you are only retrieving a single (scalar) value, just use row_count = count1.fetchval() and you'll have an int variable named row_count that you can print. — Gord Thompson
– Gord Thompson, Commented Feb 27, 2020 at 20:41

rolf82 · Accepted Answer · 2020-02-27 19:55:08Z

1

As you already fetch your result when you print it, you cannot fetch that same result again. So you should assign it to a variable :

count = count1.fetchone()[0]

Then use it as you want:

print(count)
...
f.write('\n' + 'Mandatory field missing count:'+ str(count))

answered Feb 27, 2020 at 19:55

rolf82

1,5811 gold badge7 silver badges16 bronze badges

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

1 Comment

Gord Thompson Over a year ago

pyodbc's .fetchval() is a handy alternative to .fetchone()[0].

Collectives™ on Stack Overflow

How can redirect output of SQL query to a text file using Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related