Reputation: 173
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
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
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
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
Reputation: 3810
This should work:
db.execute('INSERT INTO test VALUES (%s)' % ('sample'))
Upvotes: -1
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