Sqlite3 with python inserting same row
Question: I am having a problem with a python script inserting on different rows in
sqlite3. But I want it to insert on the same row. I am fairly new to using
scripts to insert information into a db so my knowledge is limited in this.

My Id in my database autoincrement. Is there a way to update the previous row
inserted with information? I want everything to be on a single line in the
database. Any help appreciated, Thanks.

Sqlite table:

    
    
    create table ansible_packagelist (id integer PRIMARY KEY, date datetime default current_timestamp, host text, package_name text, installed_version text NULL, upgradeable_version text NULL, required_version text NULL);
    

Below is my python code for this.

    
    
    import apt
    import sys, getopt
    import subprocess
    import sqlite3
    
    con = sqlite3.connect("ansible.db")
    
    def aptpkg(package_name):
        cache = apt.Cache()
        pkg = cache[package_name]
        host = subprocess.Popen('hostname', stdout=subprocess.PIPE, universal_newlines=True).stdout.read().strip()
        if pkg.is_installed:
            print host
            print 'Current ' + package_name + ' installed:', pkg.installed.version
            con.execute("insert into ansible_packagelist (date, host, package_name, installed_version) values (current_timestamp,?,?,?)", (host, package_name, pkg.installed.version,))
        else:
            print host, package_name + ' is not installed on this system.\n'
        if pkg.is_upgradable:
            print 'Upgradeable version of ' + package_name + ' :', pkg.candidate.version
            con.execute("insert into ansible_packagelist (upgradeable_version) values (?)", (pkg.candidate.version,))
        con.commit()
    
    def aptver(package_version):
        cache1 = apt.Cache()
        pkg1 = cache1[packname]
        con.execute("insert into ansible_packagelist (required_package) values (?)", (package_version,))
        if package_version >= pkg1.installed.version:
            print 'The Package needs to be upgraded, ' + package_version + ' is the version needed'
        elif package_version == pkg1.installed.version:
            print 'The package is at the correct version'
        elif package_version <= pkg1.installed.version:
            print 'The package is at a greater version than requested'
        else:
            print 'The package is at the correct version'
        con.commit()
    


Answer: In SQL Insert will always create a new row.

To modify an existing row, you need to use an Update statement. To do this
though you need a unique key to match on. In this case you can probably get
away with using the package name. But typically you want to fetch the ID (that
you set to auto increment) and reference that in your code.

Your upgradeable update statement should look like

    
    
     con.execute("Update ansible_packagelist Set upgradeable_version = ? Where package_name = ?", (pkg.candidate.version, package_name))
    

