Joseph Wahba Joseph Wahba - 18 days ago 5
Python Question

Python sqlite3 no such column very weird error

I am trying to insert values to columns in a table of a sqlite3 DB
as follows:

c.execute("""CREATE TABLE IF NOT EXISTS keys(name TEXT, po TEXT, options TEXT, identifier TEXT, currtime TEXT)""")
c.execute('INSERT INTO keys VALUES ( ' + Customer_Name + ' , ' + Purchase_Order + ' , ' + options + ' , ' + UUID + ' , ' + currtime + ' )')


These arguments are actually passed from HTML front end. I convert them from unicode to string as follows:

options = str(request.form.get("Software_Options"))
UUID = str(request.form.get("UUID_Identifier"))
Customer_Name = str(request.form.get("Customer_Name"))
Purchase_Order = str(request.form.get("Purchase_Order"))
currtime= str(datetime.datetime.now())[:10]


If the are integers there is no error.
If the input is a string ( e.g. joseph) it gives the following error. Any idea why?

OperationalError: no such column: joseph

Answer

You seem to be obtaining the contents for one database record at a time from the web front-end. That being the case, you can build a record as a list and insert it in the database in the following way.

import sqlite3

conn = sqlite3 . connect ( 'db.sqlite' )
c = conn . cursor ( )

c.execute("""CREATE TABLE IF NOT EXISTS keys(name TEXT, po TEXT, options TEXT, identifier TEXT, currtime TEXT)""")

oneRecord = [ 'joe', 'DA123', 'nilbymouth', '0034', '1653', ]
c.execute('INSERT INTO keys VALUES (?,?,?,?,? )', oneRecord)

conn.close()

The use of this type of construction, involving the '?' character, is also considered safer that others against injection attacks.