Python3 convert result of DB query to individual strings

Question

I have 2 functions in a python script.

The first one gets the data from a database with a WHERE clause but the second function uses this data and iterates through the results to download a file.

I can get to print the results as a tuple?

[('mmpc',), ('vmware',), ('centos',), ('redhat',), ('postgresql',), ('drupal',)]

But I need to to iterate through each element as a string so the download function can append it onto the url for the response variable

Here is the code for the download script which contains the functions:-

import requests
import eventlet
import os
import sqlite3


# declare the global variable
active_vuln_type = None
# Get the active vulnerability sets


def GetActiveVulnSets() :
    # make the variable global
    global active_vuln_type
    active_vuln_type = con = sqlite3.connect('data/vuln_sets.db')
    cur = con.cursor()
    cur.execute('''SELECT vulntype FROM vuln_sets WHERE active=1''')
    active_vuln_type = cur.fetchall()
    print(active_vuln_type)
    return(active_vuln_type)
    # return str(active_vuln_type)

def ExportList():
    vulnlist = list(active_vuln_type)
    activevulnlist = ""
    for i in vulnlist:
        activevulnlist = str(i)
        basepath = os.path.dirname(__file__)
        filepath = os.path.abspath(os.path.join(basepath, ".."))
        response = requests.get('https://vulners.com/api/v3/archive/collection/?type=' + activevulnlist)
        with open(filepath + '/vuln_files/' + activevulnlist + '.zip', 'wb') as f:
            f.write(response.content)
            f.close()
        return activevulnlist + " - " + str(os.path.getsize(filepath + '/vuln_files/' + activevulnlist + '.zip'))

Currently it creates a corrupt .zip as ('mmpc',).zip so it is not the actual file which would be mmpc.zip for the first one but it does not seem to be iterating through the list either as it only creates the zip file for the first result from the DB, not any of the others but a print(i) returns [('mmpc',), ('vmware',), ('centos',), ('redhat',), ('postgresql',), ('drupal',)]

There is no traceback as the script thinks it is working.

replace active_vuln_type = cur.fetchall() with something like active_vuln_type = [x[0] for x in cur]? — Abdou
– Abdou, Commented Sep 25, 2017 at 12:25
I hadn't thought to loop it at that level - brain fart moment! I'll give that a whirl! — Luke
– Luke, Commented Sep 25, 2017 at 12:26
That gets me the first result correctly but it's not iterating and getting the rest of the results — Luke
– Luke, Commented Sep 25, 2017 at 12:28
That is because you have a return statement inside your for-loop. That breaks the loop and nothing else but the first one gets processed. Also, when using the with statement, you do not need to close the file. It will be closed for you after processing. Switch the return statement with a print function. — Abdou
– Abdou, Commented Sep 25, 2017 at 12:31
That fixed it, If you can put it as an answer I can mark it as the answer and give rep for it :) — Luke
– Luke, Commented Sep 25, 2017 at 12:38

Abdou · Accepted Answer · 2017-09-25 12:53:31Z

The following fixes two issues: 1. converting the query output to an iterable of strings and 2. replacing the return statement with a print function so that the for-loop does not end prematurely.

I have also taken the liberty of removing some redundancies such as closing a file inside a with statement and pointlessly converting a list into a list. I am also calling the GetActiveVulnSets inside the ExportList function. This should eliminate the need to call GetActiveVulnSets outside of function definitions.

import requests
import eventlet
import os
import sqlite3


# declare the global variable
active_vuln_type = None
# Get the active vulnerability sets


def GetActiveVulnSets() :
    # make the variable global
    global active_vuln_type
    active_vuln_type = con = sqlite3.connect('data/vuln_sets.db')
    cur = con.cursor()
    cur.execute('''SELECT vulntype FROM vuln_sets WHERE active=1''')
    active_vuln_type = [x[0] for x in cur]
    print(active_vuln_type)
    return(active_vuln_type)
    # return str(active_vuln_type)

def ExportList():
    GetActiveVulnSets()
    activevulnlist = ""
    for i in active_vuln_type:
        activevulnlist = str(i)
        basepath = os.path.dirname(__file__)
        filepath = os.path.abspath(os.path.join(basepath, ".."))
        response = requests.get('https://vulners.com/api/v3/archive/collection/?type=' + activevulnlist)
        with open(filepath + '/vuln_files/' + activevulnlist + '.zip', 'wb') as f:
            f.write(response.content)
        print(activevulnlist + " - " + str(os.path.getsize(filepath + '/vuln_files/' + activevulnlist + '.zip')))

While this may solve the problem you are encountering, I would recommend that you write functions with parameters. This way, you know what each function is supposed to take in as an argument and what it spits out as output. In essence, avoid the usage of global variables if you can. They are hard to debug and quite frankly unnecessary in many use cases.

I hope this helps.

Collectives™ on Stack Overflow

Python3 convert result of DB query to individual strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related