Create a function in Python that does SQLite queries and inserts

Question

This function I'm trying to write is not working, can someone help me figure out why?

I've looked at the example HERE, but it is a little too different from what I have.

Something is wrong with the strings, I'm not sure how to properly place the SQLite queries in the string, the database I'm creating is not being populated.

Here is the what the function looks like so far:

def checkInsert(db_c, column, table, item, db_db):
    strng1 = ("SELECT %s FROM %s WHERE %s=?", (item,)) % (column, table, column)
    strng2 = ("INSERT INTO %s (%s) VALUES(?);", (item,)) % (table, column)
    db_c.execute(strng1) 
    check = db_c.fetchone()
    if check is None:
        db_c.execute(strng2)
        db_db.commit()
    else:
        pass

Currently I have to write out all of the following script:

dog = "mastiff"
cat = "jaguar"
bird = "parrot"

mem_c.execute("SELECT dog FROM dogs WHERE dog=? ", (dog,))
dog_check = mem_c.fetchone()
if dog_check is None:
    mem_c.execute("INSERT INTO dogs (dog) VALUES(?);", (dog,))
    mem_db.commit()
else:
    pass
mem_c.execute("SELECT cat FROM cats WHERE cat=? ", (cat,))
cat_check = mem_c.fetchone()
if cat_check is None:
    mem_c.execute("INSERT INTO cats (cat) VALUES(?);", (cat,))
    mem_db.commit()
else:
    pass
mem_c.execute("SELECT bird FROM birds WHERE bird=? ", (bird,))
bird_check = mem_c.fetchone()
if bird_check is None:
    mem_c.execute("INSERT INTO birds (bird) VALUES(?);", (bird,))
    mem_db.commit()
else:
    pass

If I get this right, the function should allow me to take above script and instead write it out simply, like this:

dog = "mastiff"
cat = "jaguar"
bird = "parrot"

checkInsert(mem_c, 'dog', 'dogs', dog, mem_db)
checkInsert(mem_c, 'cat', 'cats', cat, mem_db)
checkInsert(mem_c, 'bird', 'birds', bird, mem_db)

Answer 1

I got it working! Originally I was not formatting the string correctly.

The problem was that I was trying to somehow place the item variable in the string, when it is supposed to be in the db_c.execute line so that the query can be properly executed.

Basically the query strings need to first be defined using the column and table strings, then once the query needs to be executed, the query string and item variable is placed in the db_c.execute() function.

Hopefully this helps if anyone else is having this problem.

def checkInsert(db_c, column, table, item, db_db):
    strng1 = "SELECT %s FROM %s WHERE %s=?" % (column, table, column)
    strng2 = "INSERT INTO %s (%s) VALUES(?);" % (table, column)
    db_c.execute(strng1, (item,))
    check = db_c.fetchone()
    if check is None:
        db_c.execute(strng2, (item,))
        db_db.commit()
    else:
        pass