I want to add an option but where do I have a mistake?
conn=sqlite3.connect('tutorial.db')
c=conn.cursor()
def create_table():
c.execute('CREATE TABLE IF NOT EXISTS test (UNIX REAL, datestamp TEXT, keyword TEXT, value REAL)')
def data_entry(a,b,c,d):
c.execute("INSERT INTO test VALUES ({}, '{}', '{}', {})".format(a,b,c,d))
conn.commit()
c.close()
conn.close()
#create_table()
data_entry(1,"test","test2",3)
There is a local variable c in def data_entry(a,b,c,d): which is being passed as a string, and is used as an execute function, which ends up doing "test2".execute().
That issue needs to be fixed. And one fix is if I explicitly use curr as a variable to represent the cursor.
conn=sqlite3.connect('tutorial.db')
curr=conn.cursor()
def create_table(curr):
curr.execute('CREATE TABLE IF NOT EXISTS test (UNIX REAL, datestamp TEXT, keyword TEXT, value REAL)')
#curr is the last argument
def data_entry(a,b,c,d, curr):
curr.execute("INSERT INTO test VALUES ({}, '{}', '{}', {})".format(a,b,c,d))
conn.commit()
curr.close()
conn.close()
#Explicitly passing curr to functions
create_table(curr)
data_entry(1,"test","test2",3, curr)
In future ensure you name your variable short and descriptive, like curr, and perhaps the strings as val_1, val_2 etc. So that you know from the name what the variables are used for.
This is a good resource for variable naming: PEP-8
'{}'.format(s) IS PRONE TO SQL INJECTION ATTACK. It may have been safe for the OP's use case but future readers must be aware that this is generally a bad practice. To know more and learn an easy, safe alternative, see realpython.com/prevent-python-sql-injection.In your original code your local parameter c shadows the global cursor variable c. You should pass the cursor and connection as arguments:
def create_table(cursor):
cursor.execute('CREATE TABLE IF NOT EXISTS test (UNIX REAL, datestamp TEXT, keyword TEXT, value REAL)')
def data_entry(cursor, conn, a, b, c, d):
cursor.execute("INSERT INTO test VALUES ({}, '{}', '{}', {})".format(a, b, c, d))
conn.commit()
cursor.close()
conn.close()
conn = sqlite3.connect('tutorial.db')
cursor = conn.cursor()
create_table(cursor)
data_entry(cursor, conn, 1,"test","test2",3)
cto keepc=conn.cursor()and to assign"text2"indef data_entry(a,b,c,d):so finally you have"test2".execute(..)curr = conn.cursor()as in @DeveshKumarSingh answer. This name better describe its value.