append data to sqlite3 table

[wolf29]

I have a csv that needs to be put into a database format so I can sort and count the data.
My approach has been to use python csv and sqlite modules.
I am communicating with the database and creating one of the 2 tables.
Second table has many more rows and I am wanting to use an append to db model which isn't working.

The code is

def load_titles(f):
    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)

#           print titles

            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]),

#           print(tests)
    try:
        con = lite.connect('test.db')
        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):
    filename = f
    with open(filename, 'rb') as mycsv:
        print(filename)
        id = 1
        reader = csv.reader(mycsv)
        counter = 0
        for counter,row in enumerate(reader):
            if counter > 8: 
                continue
            print(row)
#           chine = (test.tests.id, row[0],row[2], row[4])
            vuln = (id, str(row[0]),str(row[1]), str(row[2]), str(row[3]), str(row[4]), str(row[5]), str(row[6]), str(row[7]), str(row[8]), str(row[9]), str(row[10]), str(row[11]), str(row[12]), str(row[13]), str(row[14]), str(row[15]), str(row[16]), str(row[17]))
            print "Look, this is vuln =>  ", vuln
            try:
                con = lite.connect('test.db')
                with con:
                    cur = con.cursor()    
#                   cur.execute("DROP TABLE IF EXISTS machines")
#                   cur.execute("CREATE TABLE machines(Id INTEGER PRIMARY KEY, Test_ID INT, IP TEXT, NetBIOS TEXT, OS TEXT)")
#                   cur.executemany("INSERT INTO machines VALUES(?, ?, ?, ?, ?)", chines)

#                   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.executemany("INSERT INTO vulnerabilities VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", vuln)

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

            finally:
                if con:
                    con.close() 

and the error during the first run through the "for loop" is:

lin_content_test31.csv   #print scaffolding tells me it is pulling the right file
['IP', 'CVSS Base', 'NetBIOS', 'OS', 'QID', 'First Detected', 'Last Detected', 'Port', 'CVE ID', 'Vendor Reference', 'Bugtraq ID', 'Threat', 'Impact', 'Solution', 'Exploitability', 'Associated Malware', 'Results', 'PCI Vuln']
Look, this is vuln =>   (1, 'IP', 'CVSS Base', 'NetBIOS', 'OS', 'QID', 'First Detected', 'Last Detected', 'Port', 'CVE ID', 'Vendor Reference', 'Bugtraq ID', 'Threat', 'Impact', 'Solution', 'Exploitability', 'Associated Malware', 'Results', 'PCI Vuln')
# Above is the scaffolding of the row and the captured values to be pushed into the db.

Traceback (most recent call last):
  File "test0811.py", line 255, in <module>
    main()
  File "test0811.py", line 65, in main
    lite_push_2 = load_content(outFileC)
  File "test0811.py", line 242, in load_content
    cur.executemany("INSERT INTO vulnerabilities VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", vuln)
ValueError: parameters are of unsupported type

https://github.com/wolf29/csv-edit

[woooee]

You call executemany but only supply one value. What is the point of creating the tuple "tests" or "vuln" instead of supplying the fields directly to the insert statement. You should also open or create the SQLite db once at the top of the function before the for loop. As it is, you open it on every pass through the for loop which will lead to unknown results. Take a look at this tutorial Click Here especially the "INSERT INTO Cars" examples. You have too much code here that is not tested/does not work. Start by testing each piece individually and then go on to the next piece. Post back with the snippet that does not work. Also include some test data and the version of Python, 2 or 3.

[wolf29]

I found that tutorial helpful.
I moved the open the db command to the top of the function.
I will see about test data - I am actually working with old versions of the real data, which needs sanitizing before I could publish it to the web.
Got rid of the executemany when I really needed just execute

def load_content(f, d):
    filename = f
    con = lite.connect(d)
    with open(filename, 'rb') as mycsv:
        print(filename)
        id = 1
        reader = csv.reader(mycsv)
        counter = 0
        for counter,row in enumerate(reader):
            if counter > 8: 
                continue
            print(row)
#           chine = (test.tests.id, row[0],row[2], row[4])
            vuln = (str(row[0]),str(row[1]), str(row[2]), str(row[3]), str(row[4]), str(row[5]), str(row[6]), str(row[7]), str(row[8]), str(row[9]), str(row[10]), str(row[11]), str(row[12]), str(row[13]), str(row[14]), str(row[15]), str(row[16]), str(row[17]))
#           print "Look, this is vuln =>  ", vuln
            try:
#               con = lite.connect('test.db')
                with con:
                    cur = con.cursor()    
#                   cur.execute("DROP TABLE IF EXISTS machines")
#                   cur.execute("CREATE TABLE machines(Id INTEGER PRIMARY KEY, Test_ID INT, IP TEXT, NetBIOS TEXT, OS TEXT)")
#                   cur.executemany("INSERT INTO machines VALUES(?, ?, ?, ?, ?)", chines)

                    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 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() 
# result    Traceback
lin_content_test31.csv
['IP', 'CVSS Base', 'NetBIOS', 'OS', 'QID', 'First Detected', 'Last Detected', 'Port', 'CVE ID', 'Vendor Reference', 'Bugtraq ID', 'Threat', 'Impact', 'Solution', 'Exploitability', 'Associated Malware', 'Results', 'PCI Vuln']
Error Incorrect number of bindings supplied. The current statement uses 19, and there are 18 supplied.:

The issue is now apparently that the content does not include the 1st field (the auto-increment ID). It seems like the easiest solution is to specify which content goes in each field

like:
cur.execute("INSERT INTO vulnerabilities(IP) VALUES (str(row[0]);")
etc.

[wolf29]

cur.execute("INSERT INTO vulnerabilities(IP) VALUES (str(row[0]));")
# fails  The sqlite comand seems to be choking on the variable

# Whatis should be getting out of this is the sting 'IP'

lin_content_test31.csv
['IP', 'CVSS Base', 'NetBIOS', 'OS', 'QID', 'First Detected', 'Last Detected', 'Port', 'CVE ID', 'Vendor Reference', 'Bugtraq ID', 'Threat', 'Impact', 'Solution', 'Exploitability', 'Associated Malware', 'Results', 'PCI Vuln']
Error near "[0]": syntax error:

Getting closer.