Joseph Wahba Joseph Wahba - 1 year ago 75
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download