CODEWITHSUNDEEP
pythonsql
garoo
garoo

Reputation: 173

SQL Insert query from flask not working

db.execute('CREATE TABLE IF NOT EXISTS test (name TEXT)')
print("Table created successfully")
db.execute('INSERT INTO %s VALUES (%s)' % ('test', 'sample'))
db.close()

I am using Python. The table is created successfully with a column of "name", but I am unable to insert anything.

I get the error:

sqlite3.OperationalError: no such column: sample

Why?

Upvotes: 3

Views: 4061

Answers (5)

Laur Ivan
Laur Ivan

Reputation: 4177

I think you need something like:

INSERT INTO test (name)
VALUES
  ('sample');

Translated into python:

db.execute('INSERT INTO %s (name) VALUES (\'%s\')' % ('test', 'sample'))

Upvotes: 2

JL Peyret
JL Peyret

Reputation: 12214

You definitely don't want to use string substitutions. That opens you to https://www.owasp.org/index.php/SQL_Injection. Instead, use database binding which incidentally fixes your actual error (which is that you are treating sample as a column name, rather than data).

Ok, might as well write it up:

import sqlite3
db = sqlite3.connect(':memory')

#you control this stuff, as the db schema isn't typically coming from user data
#so less likely to be a mess...
#i.e. build your query templates with string substitutions, but exec with binds.
tablename = 'test'
db.execute('CREATE TABLE IF NOT EXISTS %s (name TEXT)' % (tablename))
print("Table created successfully")
qry = 'INSERT INTO %s VALUES (?)' % (tablename)

#the data is where you want to be careful
db.execute(qry, ('sample',))
print ("insert done")
db.close()

which gives:

Table created successfully
insert done

Check out docs @ https://docs.python.org/2/library/sqlite3.html Starting at # Never do this -- insecure!.

Upvotes: -1

cco
cco

Reputation: 6301

You need to commit your changes. If you don't call db.commit(), your changes (including creating the table) will be rolled back when you close the database.

Upvotes: 0

Fuzzy
Fuzzy

Reputation: 3810

This should work:

db.execute('INSERT INTO test VALUES (%s)' % ('sample'))

Upvotes: -1

dadde
dadde

Reputation: 659

You forgot the column name?

db.execute('CREATE TABLE IF NOT EXISTS test (name TEXT)')
print("Table created successfully")
db.execute('INSERT INTO %s (name) VALUES (%s)' % ('test', 'sample')) 
db.close()

Upvotes: 1

Related Questions