put python variable into sqlite3

[wolf29]

I have 2 functions, one of which works and one of which doesn't. I do not understand this, because the only difference is that the second function puts in just the name of the variable, where the 1st function puts in the value.

There is only one line to build for the test from a given dataset, but the content function has several lines. I am using a for loop to handle the iteration through the csv to get content, but it fails to get anything but the first line. There is no error traceback from this code, unless I take the quotes from around the vuln_0 variable name cur.execute("INSERT INTO vulnerabilities(IP) VALUES ('vuln_0');")
With no quotes, it errors out in sqlite3, saying "there is no column vuln_0."

def load_titles(f, d):
    print("This is da 'd,' y\'all! ", d )
    filename = f
    titles=[]
    with open(filename, 'rb') as mycsv:
            reader = csv.reader(mycsv)
            counter = 0
            for counter,row in enumerate(reader):
                if counter < 1: continue
                if counter > 6: break
                titles.append(row)

            tests = (1, titles[0][0], titles[0][1], titles[0][2], titles[0][3], titles[0][4], titles[0][5], titles[0][6], titles[1][0], titles[1][1], titles[1][2], titles[4][0], titles[4][1], titles[4][2], titles[4][3], titles[4][4], titles[4][5], titles[4][6]),

    try:
        con = lite.connect(d)
        with con:
            cur = con.cursor()    
            cur.execute("DROP TABLE IF EXISTS tests")
            cur.execute("CREATE TABLE tests(Id INTEGER PRIMARY KEY, Corp TEXT, Address_1 TEXT, Address_2 TEXT, City TEXT, State TEXT, Country TEXT, Postal_Code TEXT, Requester TEXT, Code_1 TEXT, Role TEXT, Asset_Groups TEXT, IPs TEXT, Active_Hosts INT, Hosts_Matching_Filters INT, Trend_Analysis TEXT, Date_Range TEXT, Asset_Tags TEXT)")
            cur.executemany("INSERT INTO tests VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", tests)

    except lite.Error, e:
        if con:
            con.rollback()
        print "Error %s:" % e.args[0]
        sys.exit(1)

    finally:
        if con:
            con.close() 

def load_content(f, d):
    filename = f

    with open(filename, 'rb') as mycsv:
        print(filename)
        id = 1
        reader = csv.reader(mycsv)
        counter = 0
        for counter,row in enumerate(reader):
#           con = lite.connect(d)
            if counter > 8: 
                continue
            print(row)
#           chine = (test.tests.id, row[0],row[2], row[4])
#           vuln = (id, row[0],row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17])

            vuln_0 = str(row[0])
            vuln_1 = str(row[1])
            vuln_2 = str(row[2])
            vuln_3 = str(row[3])
            vuln_4 = str(row[4])
            vuln_5 = str(row[5])
            vuln_6 = str(row[6])
            vuln_7 = str(row[7])
            vuln_8 = str(row[8])
            vuln_9 = str(row[9])
            vuln_A = str(row[10])
            vuln_B = str(row[11])
            vuln_C = str(row[12])
            vuln_D = str(row[13])
            vuln_E = str(row[14])
            vuln_F = str(row[15])
            vuln10 = str(row[16])
            vuln11 = str(row[17])

            try:
                con = lite.connect(d)

                with con:
                    cur = con.cursor()    

                    cur.execute("DROP TABLE IF EXISTS vulnerabilities")
                    cur.execute("CREATE TABLE vulnerabilities(Id INTEGER PRIMARY KEY, IP TEXT, CVSS_Base TEXT, NetBIOS TEXT, OS TEXT,  QID TEXT, First_Detected TEXT, Last_Detected TEXT, Port TEXT, CVE_ID TEXT, Vendor_Reference TEXT, Bug_traq_ID TEXT, Threat TEXT, Impacts TEXT, Solution TEXT, Exploitability TEXT, Associated_Malware TEXT, Results TEXT, PCI_Vuln TEXT)")

                    cur.execute("INSERT INTO vulnerabilities(IP) VALUES ('vuln_0');")
                    con.commit()

            except lite.Error, e:
                if con:
                    con.rollback()
                    print "Error %s:" % e.args[0]
                    sys.exit(1)

            finally:
                if con:
                    con.close() 

#

There are 19 values I want to enter, but for now, only 0th value is being entered into the db.
The first iteration of the for loop runs, but nothing else.

A data file you could use: https://github.com/wolf29/csv-edit/blob/master/mouse.csv
The source: https://github.com/wolf29/csv-edit/blob/master/test0813.py

This is python 2.7.3. It has occurred to me that I might be mixing ver 2.x and 3.x in my code.

[woooee]

The first iteration of the for loop runs, but nothing else.

That is because of the "if counter > 0"

    for counter,row in enumerate(reader):
        if counter > 8: 
            continue

You also have an extra semicolon at the end of this statement

   Cur.execute("INSERT INTO vulnerabilities(IP) VALUES ('vuln_0');")

I will try to look at this again tonight and provide a working example, so post back if you get it working before that.

[wolf29]

I think I found most of the issues. I decided to really look at the first function and figure out what was really happening. THe thing was working because I was properly doing the VALUES (?, ?, ?)

cur.executemany("INSERT INTO tests VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", tests)

This was taking each entry in the tests tuple and properly putting them in the table from the first column and so on.
I wanted to have the option to use the same table space to do more than one test at some time in the future, so I replaced that line with:

            cur.executemany("INSERT INTO test_detail(Corp, Address_1, Address_2, City, State, Country, Postal_Code, Requester, Code_1, Role, Asset_Groups, IPs, Active_Hosts, Hosts_Matching_Filters, Trend_Analysis, Date_Range, Asset_Tags) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", tests)

This let me have a properly incrementing ID number for the test_detail table. This worked so well that I did the same thing for the content. I had to remove the DROP TABLE command in the second function. It would make a new table for each row in the csv.

Now I need to figure out how to make sure that the vulns continue to be connected to the test_detail table. Probably need to combine the 2 functions, somehow.

def load_titles(f, d):
    print("This is da 'd,' y\'all! ", d )
    filename = f
    titles=[]
    with open(filename, 'rb') as mycsv:
            reader = csv.reader(mycsv)
            counter = 0
            for counter,row in enumerate(reader):
                if counter < 1: continue
                if counter > 6: break
                titles.append(row)

            tests = (titles[0][0], titles[0][1], titles[0][2], titles[0][3], titles[0][4], titles[0][5], titles[0][6], titles[1][0], titles[1][1], titles[1][2], titles[4][0], titles[4][1], titles[4][2], titles[4][3], titles[4][4], titles[4][5], titles[4][6]),

    try:
        con = lite.connect(d)
        with con:
            cur = con.cursor()    
            cur.execute("DROP TABLE IF EXISTS test_detail")
            cur.execute("CREATE TABLE tests(Id INTEGER PRIMARY KEY, Corp TEXT, Address_1 TEXT, Address_2 TEXT, City TEXT, State TEXT, Country TEXT, Postal_Code TEXT, Requester TEXT, Code_1 TEXT, Role TEXT, Asset_Groups TEXT, IPs TEXT, Active_Hosts INT, Hosts_Matching_Filters INT, Trend_Analysis TEXT, Date_Range TEXT, Asset_Tags TEXT)")
            cur.executemany("INSERT INTO test_detail(Corp, Address_1, Address_2, City, State, Country, Postal_Code, Requester, Code_1, Role, Asset_Groups, IPs, Active_Hosts, Hosts_Matching_Filters, Trend_Analysis, Date_Range, Asset_Tags) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", tests)

    except lite.Error, e:
        if con:
            con.rollback()
        print "Error %s:" % e.args[0]
        sys.exit(1)

    finally:
        if con:
            con.close() 

def load_content(f, d):
    filename = f

    with open(filename, 'rb') as mycsv:
        print(filename)
        id = 1
        reader = csv.reader(mycsv)
        counter = 0
        for counter,row in enumerate(reader):
#           con = lite.connect(d)
            if counter > 8: 
                continue
            print(row)
            vuln = (row[0],row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17])

            try:
                con = lite.connect(d)

                with con:
                    cur = con.cursor()    

#                   cur.execute("DROP TABLE IF EXISTS vulnerabilities")
                    cur.execute("CREATE TABLE IF NOT EXISTS vulnerabilities(Id INTEGER PRIMARY KEY, TablesIP TEXT, CVSS_Base TEXT, NetBIOS TEXT, OS TEXT,  QID TEXT, First_Detected TEXT, Last_Detected TEXT, Port TEXT, CVE_ID TEXT, Vendor_Reference TEXT, Bug_traq_ID TEXT, Threat TEXT, Impacts TEXT, Solution TEXT, Exploitability TEXT, Associated_Malware TEXT, Results TEXT, PCI_Vuln TEXT)")
                    cur.execute("INSERT INTO vulnerabilities(IP, CVSS_Base, NetBIOS, OS, QID, First_Detected, Last_Detected, Port, CVE_ID, Vendor_Reference, Bug_traq_ID, Threat, Impacts, Solution, Exploitability, Associated_Malware, Results, PCI_Vuln) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", vuln)
                    con.commit()

            except lite.Error, e:
                if con:
                    con.rollback()
                    print "Error %s:" % e.args[0]
                    sys.exit(1)

            finally:
                if con:
                    con.close()