CODEWITHSUNDEEP
pythonsql-serverpython-3.xpyodbc
Karan M
Karan M

Reputation: 309

PYODBC: Can't iterate through SQL Query

I am trying to extract devices from SQL server and do the ping test of those devices.

This is the code I have written so far:

import subprocess
import pyodbc

data = {}


def list():
    conn = pyodbc.connect("Driver={SQL Server};"
                          "Server=sqlserver;"
                          "Database=database;"
                          "Trusted_Connection=yes;")
    cursor = conn.cursor()
    cursor.execute('''
       SELECT TOP 10 Names FROM sqldatabasetable
       ''')
    results = cursor.fetchall()
    for result in results:
        return result.Names
        #print(result.Names)
    conn.close()


def pingtest():
    response = subprocess.Popen(['ping.exe', list()], stdout=subprocess.PIPE).communicate()[0]
    response = response.decode()
    if 'bytes=32' in response:
        status = 'Up'
        return status
    else:
        status = 'Down'
        return status

print(list())
print(pingtest())
#print(pingtest(), list())

When I print only print(result.Names) under the list() function, I get 10 devices, but when I return them to the list() and iterate those in pingtest() function, I get only the first device. I know I am missing something very obvious. And this should be a straight answer, but I'm not able to figure out exactly where should I loop through if I have to.

This prints the device names and ping status as:

['devicename1' , 'devicename2', 'devicename3', 'devicename4', 'devicename5']
Down

And I am expecting like this, going by how I have written the print commands:

devicename1
Ping status of devicename1
devicename2
Ping status of devicename2
and so on...

Upvotes: 2

Views: 1817

Answers (2)

shad0w_wa1k3r
shad0w_wa1k3r

Reputation: 13373

You could make pingtest accept a device_name parameter and pass to ping.exe. So, you could fetch your list() first and then iterate over that and pass each of the device to pingtest in that loop.

import subprocess
import pyodbc


data = {}


def mylist():
    conn = pyodbc.connect("Driver={SQL Server};"
                          "Server=sqlserver;"
                          "Database=database;"
                          "Trusted_Connection=yes;")
    cursor = conn.cursor()
    cursor.execute('''
       SELECT TOP 10 Names FROM sqldatabasetable
       ''')
    results = cursor.fetchall()
    conn.close()
    return [result.Names for result in results]


def pingtest(device):
    response = subprocess.Popen(['ping.exe', device], stdout=subprocess.PIPE).communicate()[0]
    response = response.decode()
    if 'bytes=32' in response:
        status = 'Up'
        return status
    else:
        status = 'Down'
        return status


devices = mylist()
for device in devices:
    print(device)
    print(pingtest(device))

Upvotes: 2

Gord Thompson
Gord Thompson

Reputation: 123849

When you print in your for result in results: loop your code continues to iterate through each row retrieved. However, when you replace print with return then the first time through the loop the return will immediately exit the list() function and therefore you will only get the value from the first row.

If you want list() to return the values from all of the rows then you can replace your loop with a list comprehension like

return [result.Names for result in results]

Upvotes: 3

Related Questions