Python3 and Sqlite3 can't Insert

Question

I am trying to write a function to do a simple insert. Here is what I have tried so far

#! /usr/bin/env python3
#import
import sqlite3 as lite

#trying an insert version 1 (does nothing)

def createTableTask():
"""
Create a new table with the name Task
"""
#Connnection to the database and cursor creation
con = lite.connect('./exemple.sqlite')
con.row_factory = lite.Row
cur = con.cursor()
#that does nothing
try:
    cur.execute('''CREATE TABLE Tasks (\
    Name TEXT PRIMARY KEY, \
    Description TEXT, \
    Priority TEXT);''')
except lite.IntegrityError as error_SQLite:
    print("error: "+ str(error_SQLite))
else:
    print("No error has occured.")
con.close();


def insert1():
    """
    insert a new task
    """
    #Allocating variables data
    taskName = 'finish code'
    taskDescription = 'debug'
    taskPriority = 'normal'
    #Connnection to the database and cursor creation
    con = lite.connect('./exemple.sqlite')
    con.row_factory = lite.Row
    cur = con.cursor()
    #that does nothing
    try:
        with con:
            cur.execute('''INSERT INTO Tasks (Name, Description, Priority) \
            VALUES (?, ?, ?)''', (taskName, taskDescription, taskPriority))
    except lite.IntegrityError as error_SQLite:
        print("error: "+ str(error_SQLite))
    else:
        print("No error has occured. but no insert happend ?")
    con.close();

def showResult():
    """
    Show the result of the insert
    """
    con = lite.connect('./exemple.sqlite')
    con.row_factory = lite.Row
    cur = con.cursor()
    cur.execute\
    ('''SELECT * FROM Tasks ;''')
    row = cur.fetchone()
    while row:
        print(row["Name"], '  |  ', row["Description"], '  |  ', \
        row["Priority"])
        row = cur.fetchone()
    con.close();


#trying an insert version 2 (this one crash giving :Value error)
def insert2():
    """
    insert a new task
    """
    #Allocating variables data
    taskName = 'finish code'
    taskDescription = 'debug'
    taskPriority = 'normal'
    #Connnection to the database and cursor creation
    con = lite.connect('./exemple.sqlite')
    con.row_factory = lite.Row
    cur = con.cursor()
    queryInsert = ('''INSERT INTO Tasks (Name, Description, Priority) \
    VALUES (?, ?, ?)''', (taskName, taskDescription, taskPriority))
try:
    with con:
        cur.execute(queryInsert)
except lite.IntegrityError as error_SQLite:
    print("error: "+ str(error_SQLite))
else:
    print("No error has occured.")
con.close();

def run():
    createTableTask()
    insert1()
    showResult()
    insert2()
    showResult()

#calling section
run()

The problem is that none of the insert that I have made so far worked. The first one does actualy nothing but has a correct syntax
The second one, well it crash.

Here is the output:

spark@spark-Razer-Blade-Pro:~/Documents/testing$ ./exemp.py
No error has occured.
No error has occured. but no insert happend ?
Traceback (most recent call last):
File "./exemp.py", line 98, in
run()
File "./exemp.py", line 94, in run
insert2()
File "./exemp.py", line 83, in insert2
cur.execute(queryInsert)
ValueError: operation parameter must be str
spark@spark-Razer-Blade-Pro:~/Documents/testing$ sqlite3 exemple.sqlite
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM Tasks;
sqlite>

I am looking for the most simple fix and maybe know what is wrong with my code. Because Right now I do not know what is going on with the no insert one. Normally it should, or am I missing something ?

Answer 1

ok I got around my error. Just posting it because it might help others. cur.execute() is a fonction that seek a query as it's first argument, than the other argument are the variables needed for the query.

step one: make the query into a variable without it's parameters

queryString = ''' INSERT INTO someTables rowName, rowName2 ... VALUES (?, ?);''' 

there should be as much as ? as there are variables needed. in this exemple I need 2

queryValue1 = 'something'
queryValue2 = '123'

Step 2 to call and execute the query :

cur.execute(queryString, queryValue1, queryValue2)

this should be working without problem

Answer 2

queryInsert = ('''INSERT ...''', (taskName, taskDescription, taskPriority))

This makes queryInsert a tuple with two elements.
But to call the execute method, you need two separate parameters.

You could just unpack the tuple:

cur.execute(*queryInsert)

but it might be clearer to use two separate variables:

queryString = '''INSERT ...'''
queryParams = (taskName, taskDescription, taskPriority)

cur.execute(queryString, queryParams)