Failing to insert a record in sqlite using python

Question

Am getting a error when i attempt to insert a record in sqlite using python.

This is my code:

import sqlite3

db = sqlite3.connect('mydb')

ins_str = 'insert into filer_filer(number, ms_date, ms_time, mp_code, Amount, recipient_name,recipient_number, Tran_date, Tran_time, balance, userid_id ) values ('752098','09/09/16','17:54:19','K79NN251','5,000',' GWENDA WULFRIC','416','11/9/09','4:23 PM','396', -1)'

try:
    db.execute(ins_str)
except:
    db.close()

...

I get the following error

str: Traceback (most recent call last): File "C:\eclipse\plugins\org.python.pydev.debug_1.4.8.2881\pysrc\pydevd_vars.py", line 340, in evaluateExpression

result = eval(expression, updated_globals, frame.f_locals)

File "", line 1

 except
      ^

SyntaxError: unexpected EOF while parsing

Gath

Edit

My new insert statement looks like this

"insert into filer_filer(number, sms_date, sms_time, mp_code, Amount, recipient_name,recipient_number, Tran_date, Tran_time, balance, userid_id ) values ('+254722752098','09/09/16','17:54:19','K79NN251','5,000',' GEOFFREY NZIOKA','254720425416','11/9/09','4:23 PM','396', -1)"

Answer 1

It may be an artifact of SO's code block display but you seem to be missing quotes around the SQL string values.

If that is the case, you may resolve the issue by simply using double quotes for the ins_str variable.

Edit: My explanation was confusing. I apologize if I misled you. Now in more detail:

Python string literals can either use double quotes of single quotes; the following assignments are equivalent.

  myString = 'Hello World'
  myString = "Hello World"

The SQL syntax (unrelated to Python) requires single quotes (no choice) for its string variables. It is therefore a good idea to use double quotes for the Python string itself, because the single quotes for the SQL stuff won't interfere.

You can therefore use this (as suggested by Alex; I second this choice too)

ins_str = "insert into filer_filer(number, ms_date, ms_time, mp_code, Amount, recipient_name,recipient_number, Tran_date, Tran_time, balance, userid_id ) values ('752098','09/09/16','17:54:19','K79NN251','5,000',' GWENDA WULFRIC','416','11/9/09','4:23 PM','396', -1)"

or you should otherwise use two single quotes for each quote before and after the SQL variables. Having two single quotes is an escape sequence, interpreted as one single quote within the string by Python.

  ins_str = 'insert into blah, blah....    values(''752098'', ''09/09/16'',  etc... '

Answer 2

In the assignment to ins_str, you appear to be using single-quotes incorrectly: one at the start, another after values ( and right before 752098, and so on, and so forth. You seem to be using Eclipse (even though you don't mention that, the error messages suggests it), so the error you're getting as the Eclipse plug-in tries to make sense of that syntax is peculiar, but Python proper would give you a good plain old SyntaxError -- whatever you think you're doing with that use of single quotes, it's not going to work. What about using double quotes instead of single ones, " instead of ', at the start and end of string, so that the single quotes inside them get preserved and passed on to sqlite...?

Answer 3

Are you sure that you are getting the same error? Unless you have also changed your schema, your new insert statement will fail simply because the fields "sms_date" and "sms_time" should be "ms_date" and "ms_time". Might help if you show us yor schema, which you can do from the command line by:

sqlite3 mydb '.schema filer_filer'

Answer 4

Sorry guys, i fixed it!, there was a typo error on my code- excute instead of execute;

My original code looked like this

try:
   db.excute(ins_str) # Notice the typo on the method execute. written as excute instead of execute
except:
    db.close()

thanks.