CODEWITHSUNDEEP
python
Deounix
Deounix

Reputation: 433

can't insert data into sqlite3 using python

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.

Upvotes: 1

Views: 886

Answers (4)

Paul Rooney
Paul Rooney

Reputation: 21609

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?

Upvotes: 2

jakebird451
jakebird451

Reputation: 2348

@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.

Upvotes: 2

Andrew Guy
Andrew Guy

Reputation: 9968

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))

Upvotes: 1

lqhcpsgbl
lqhcpsgbl

Reputation: 3782

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

Upvotes: 0

Related Questions