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

possible duplicate of Python - update database

Bryan
– Bryan

2014-07-07 17:24:36 +00:00
Commented Jul 7, 2014 at 17:24 — Bryan
– Bryan, Commented Jul 7, 2014 at 17:24

Cheruvian · Accepted Answer · 2014-07-07 17:27:44Z

2

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

answered Jul 7, 2014 at 17:27

Cheruvian

5,9071 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nvasion Over a year ago

Yup this worked. Edit: actually no, it works but it does every row.

Cheruvian Over a year ago

It should update it anywhere package_name is = to whatever the package name was. I'm not sure what your DB structure is at this point.

Nvasion Over a year ago

Everything is fine, I over thought it. I was just thinking that something was wrong but I am only going to be running this script like once a month anyways. Thank you very much.

Collectives™ on Stack Overflow

Sqlite3 with python inserting same row

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related