0

I don't know why it's unable to add this value, even though i've defined it as a string: Here's my code:

import gspread
import sqlite3
import oauth2client
from oauth2client.service_account import ServiceAccountCredentials 
import time

scope = ['https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name("secret key.json",scope)
gc = gspread.authorize(credentials)

wks = gc.open('File').sheet1
arr = wks.col_values(8)

class Tables():
  def __init__(self,id_number=0,NationalGridReference = ""):
    self.id_number = id_number
    self.NationalGridReference = NationalGridReference
    self.connection = sqlite3.connect("Conversion")
    self.cursor = self.connection.cursor()
    self.cursor.execute('''CREATE TABLE IF NOT EXISTS {} (
  id INTEGER PRIMARY KEY,
  {} TEXT
  );'''.format(name,arr[0]))
  
    
  def load_info(self,id_number):
    self.cursor.execute('''
    SELECT * FROM {}
    WHERE id = {}
    '''.format(name,id_number)) 
    results = self.cursor.fetchall()
    self.id_number = id_number
    self.NationalGridReference = results

    print("From ",name,"value",self.id_number,"is",self.NationalGridReference)
  def add_info(self,id_number,value):
    self.cursor.execute('''
    INSERT INTO {} VALUES(
    {},{})'''.format(name,id_number,value))
    self.connection.commit()
    self.cursor.execute('''
    SELECT * FROM {}
    WHERE id = {}
    '''.format(name,id_number)) 



global name
name  = "Converting"
tbl1 = Tables()
tbl2 = Tables()
#tbl1.add_info(1,'Armaan','Khaitan',17)
#tbl1.add_info(2,'Vikas','Khaitan',47)
#tbl1.add_info(3,'Nandini','Chatterjee',47)
#tbl1.connection.commit()
#for i in range(1,4):
#  tbl1.load_info(i)



print(arr[0])
print(arr[2])

tbl1.add_info(2,arr[2])
#url = f'https://uk.images.search.yahoo.com/search/images;_ylt=AwrIfln4XytkPt4DqAAM34lQ;_ylu=Y29sbwNpcjIEcG9zAzEEdnRpZAMEc2VjA3BpdnM-?p=aesthetic+#{insert}+gym+diagram+instructions&fr2=piv-web&type=E210GB91082G0&fr=mcafee'#
#page = requests.get(url)
#soup = BeautifulSoup(page.content,'html.parser')
#holder = soup.find('img')
#if holder['alt'] == "":
#  holder['alt'] = "Something"


#for i in range(1,865):
#  tbl1.add_info(i,arr[i])


tbl1.load_info(1)
tbl1.connection.close()

returns:

sqlite3.OperationalError: near 10074: syntax error

using a table of values that have values like this:

SU 10020 31197
SU 10074 31063
SU 16110 27173
SU 17136 26567
JIST
  • 1,139
  • 2
  • 8
  • 30
Armaan Khaitan
  • 9
  • 3
  • 1
    Please show the full traceback so we can see which query is getting the error. – Barmar Apr 28 '23 at 16:56
  • 1
    Don't use string formatting to substitute values into SQL, use a prepared statement with parameters. – Barmar Apr 28 '23 at 16:57
  • The spreadsheet only has 3 columns. What is `col_values(8)` supposed to be? – Barmar Apr 28 '23 at 16:58
  • And if you do use string formatting, just print the string that you're going to execute. You should be able to see the problem. – Barmar Apr 28 '23 at 17:03
  • The `global` keyword in Python doesn't work the way you are you using it. See: [https://stackoverflow.com/questions/13881395/in-python-what-is-a-global-statement]. Also, Barmar wasn't explicit, the reason you shouldn't use string formatting to make a SQL statement is that it leaves you wide open to a SQL injection attack if someone else is providing or can modify the source of the data. – nigh_anxiety Apr 28 '23 at 17:14
  • Is `SU 10020 31197` the value of column 8 in the table (I earliler assumed it was 3 different columns)? Since it's a string you need quotes around it in the SQL. But if you use a prepared statement it will take care of this automatically. – Barmar Apr 28 '23 at 17:17
  • See https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python – Barmar Apr 28 '23 at 17:18
  • `name` is really an instance variable and should be passed into `Tables("Converting")` – JonSG Apr 28 '23 at 17:31
  • When i tried to pass name into the class, it didn't work, I read somewhere that it can't be parametrised – Armaan Khaitan Apr 28 '23 at 22:28

0 Answers0