can't insert data into sqlite3 using python

Question

I can successfully create table in sqlite3 database using Python but I can't insert data to it

# coding: utf-8
import sqlite3
text = "Welcome"
def cur_execute(data):
 con = sqlite3.connect('sqlite3.db')
 try:
 with con:
 cur = con.cursor()
 cur.execute(data)
 con.commit()
 except Exception as why: 
 print(why)
 finally:
 if con: con.close()
cur_execute("CREATE TABLE Hello(test TEXT)")
cur_execute("INSERT INTO Hello VALUES(?)", (text))

show to me this error : cur_execute() takes exactly 1 argument (2 given)

thx.

• Paul Rooney: if you use with for the connection. You do not need to call close.

Answer 1

@zoosuck answer works, but it also removed the sql sanitation built in for the library. You can can use the built in sql sanitization by implementing the following:

import sqlite3
text = "Welcome"
def cur_execute(data, args=()):
 con = sqlite3.connect('sqlite3.db')
 with con:
 cur = con.cursor()
 cur.execute(data, args)
 con.commit()
cur_execute("CREATE TABLE Hello(test TEXT)")
cur_execute("INSERT INTO Hello VALUES(?)", (text, ))

Please note that with the with statement, you do not need to close the connection. I removed the try catch around the connection to show the full error if errors do occur.

Additionally, there was a problem with your original code. The second argument in cur_execute("INSERT INTO Hello VALUES(?)", (text)) needs to be a tuple/list.

Answer 2

You could do it like this

# coding: utf-8
import sqlite3
text = "Welcome"
def cur_execute(data, *args):
 con = sqlite3.connect('sqlite3.db')
 with con:
 cur = con.cursor()
 cur.execute(data, args)
 con.commit()
cur_execute("CREATE TABLE Hello(test TEXT)")
cur_execute("INSERT INTO Hello VALUES(?)", text)

This way you keep the functionality of cur.execute to escape your input data. This can prevent SQL injection attacks.

If you want to add 2 things into your table you could call your function like this instead.

cur_execute("CREATE TABLE Hello2(test1 TEXT, test2 TEXT)")
cur_execute("INSERT INTO Hello2 VALUES(?, ?)", text, "stuff")

It will make another table with 2 text fields. Is that what you were asking to do?

• Aaron D: Upvote for not breaking SQL injection attack protection
• Paul Rooney: @Andrew Guy beat me to it :)

Answer 3

You have defined your cur_execute function as only taking one argument, and then trying to pass two arguments to it, as zoosuck has said.

However, his method is still vulnerable to sql injection - you should let the database API handle string formatting. You want to be able to pass two arguments to your function, not just the one. You could try:

import sqlite3
data = "Welcome"
def cur_execute(data,*args):
 con = sqlite3.connect('sqlite3.db')
 try:
 with con:
 cur = con.cursor()
 if args is not None:
 cur.execute(data,args)
 else:
 cur.execute(data)
 con.commit()
 except Exception as why: 
 print(why)
 finally:
 if con: con.close()
cur_execute("CREATE TABLE Hello(test TEXT)")
cur_execute("INSERT INTO Hello VALUES(?)", (data))

• Deounix: thx, can I add two thing to insert like data1 = "welcome1" data2 = "welcome2" how can I insert it to table thx

Answer 4

The function take one parameter: SQL(string type), your second call give it two parameters, just change your code to format the SQL will be ok:

import sqlite3
data = "Welcome"
def cur_execute(data):
 con = sqlite3.connect('sqlite3.db')
 try:
 with con:
 cur = con.cursor()
 cur.execute(data)
 con.commit()
 except Exception as why: 
 print(why)
 finally:
 if con: con.close()
cur_execute("CREATE TABLE Hello(test TEXT)")
cur_execute("INSERT INTO Hello VALUES('{0}')".format(data)) # format sql and call it

• Deounix: Hi; what if I wan't to insert two data data1 = "welcome" data2 = "welcome2" cur_execute("CREATE TABLE Hello(test TEXT, test2 TEXT)") how can I insert data1 & data2
• lqhcpsgbl: You need to call the function twice.