CODEWITHSUNDEEP
pythonsqlite
Nvasion
Nvasion

Reputation: 620

Sqlite3 with python inserting same row

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()

Upvotes: 1

Views: 496

Answers (1)

Cheruvian
Cheruvian

Reputation: 5867

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

Upvotes: 2

Related Questions