Inserting new row into SQLite

[KevinBrown]

While trying to INSERT into SQLite, can anyone advise on the reason for the error syntax error given in the example CODE 1 while CODE 2 and CODE 2 function as intended.

def AddToClpBrd():
    print (' AddToClpBrd')
    sqlite_file = 'mydatabase.sqlite'     # name of the sqlite database file
    table_name1 = 'VendClpBrd'             # name of the table to be creat
    
    id_col   = 'rowid'
    col_01   = 'Vend_01'

    ClpIns = 10       # Hard code for test
    CX = StringVar()
    #CX = Entbuy1.get() # For Code 1
    CX= '1234567890'   # For Code 2
    print(CX)

    
      # Connecting to the database file
            
    #  +++++++ CODE 1 This code below Produces the error
    ###  File "C:\MyScripts\Vendors.py", line 522, in AddToClpBrd
    ###  format(tn=table_name1, idf=id_col, cn=col_01))
    ###  sqlite3.OperationalError: near "BASCO": syntax error
    ###  'Where BASCO is the leading part of text from the Entry widget Entbuy1.get() 
      
    #conn = sqlite3.connect('/Users/Kevin/Documents/mydatabase.sqlite')
    #c = conn.cursor()
    #STRR = "INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES ( "+ str(ClpIns) +"," + CX + ")"
    #c.execute(STRR.\
    #          format(tn=table_name1, idf=id_col, cn=col_01))


     #  +++++++ CODE2 This code functions without error
    #conn = sqlite3.connect('/Users/Kevin/Documents/mydatabase.sqlite')
    #c = conn.cursor()
    #STRR = "INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES ( "+ str(ClpIns) +",' Test data ')"
    #c.execute(STRR.\
    #          format(tn=table_name1, idf=id_col, cn=col_01))
   


     #  +++++++ CODE3 This code functions without error
    conn = sqlite3.connect('/Users/Kevin/Documents/mydatabase.sqlite')
    c = conn.cursor()
    STRR = "INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES ( "+ str(ClpIns) +"," + CX +")"
    c.execute(STRR.\
              format(tn=table_name1, idf=id_col, cn=col_01))  

   #  From  Sebastian Raschka
   # c.execute("INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES (123456, 'test')".\
   #     format(tn=table_name, idf=id_column, cn=column_name))
  
    conn.commit()
    conn.close()

[nilamo]

You're not sanitizing the values, so if what you're trying to insert has a quote character in it, the sql wouldn't be valid. Could you share the contents of the variables? CX, for example, would also throw an error if it isn't a number.

[KevinBrown]

Nilamo

CD take its data from an entry widget Entbuy1 in this case it’s a company name API Basco INC
There are no reserved characters in the string

[KevinBrown]

Correction in the previous reply CD should in fact have been CX, apologies.

[KevinBrown]

By trial and error this approach works.

Taking this string from Entbuy1.get() and adding ' as the leading and trailing characters.
If this is clumsy or coarse solution then please advise or show me a more elegant way to perform the task.

My real objective with the VendClpBrd ( Vendor Clipboard) is to do the following:

• Have a fixed data base of say 100 rows for vendors.
  
• Have row one as a blank record i.e no vendor.
  
• INSERT any new vendors into row two.
  
• Drop off (DELETE) the last vendor (row 100).
  
  

I then want to be able to load this data into various comboboxes for users to select vendors.

If anyone had done something similar i'd appreciate any help or guidance.

def AddToClpBrd():
    print (' AddToClpBrd')
    sqlite_file = 'mydatabase.sqlite'     # name of the sqlite database file
    table_name1 = 'VendClpBrd'            # name of the table 
    
    id_col   = 'rowid'
    col_01   = 'Vend_01'

    ClpIns = '17'                   # Hard code for test
    CX = StringVar()
    CX = "'" + Entbuy1.get() + "'"  # For Code 4 this works when used with script below
    #CX = Entbuy1.get()             # For Code 1
    #CX= '1234567890'               # For Code 2
    print(CX)
      
      # Connecting to the database file

  
     #  +++++++ CODE4 This code functions without error
    conn = sqlite3.connect('/Users/Kevin/Documents/mydatabase.sqlite')
    c = conn.cursor()
    STRR = "INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES ( "+ str(ClpIns) +"," + CX + " )"
    c.execute(STRR.\
              format(tn=table_name1, idf=id_col, cn=col_01))
   
 

   #  From  Sebastian Raschka
   # c.execute("INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES (123456, 'test')".\
   #     format(tn=table_name, idf=id_column, cn=column_name))
  
    conn.commit()
    conn.close()

    #OpenRecord (Rcrd)

[nilamo]

Taking this string from Entbuy1.get() and adding ' as the leading and trailing characters.
If this is clumsy or coarse solution then please advise or show me a more elegant way to perform the task.

It's a string, and needs to be quoted. But you shouldn't be doing that manually, you should be sanitizing the input and letting the db driver handle it. So roughly like this:

STRR = "INSERT OR IGNORE INTO {tn} ({idf}, {cn}) values (?, ?)".format(tn=table_name1, idf=id_col, cn=col_01)
values = [str(ClpIns), CX]
c.execute(STRR, values)