0

I'm doing a Software Engineering Project for one of my final courses as a Comp Sci major and I'm getting hungup on this particular error while I'm trying to do my data/unit testing of the methods before merging my methods with our teammates GUI. Anyone who can help me solve this issue is my hero

class Student:
def __init__(self, StudentID, StudentName, conn: sql.Connection, curs: sql.Cursor):
    self.StudentID = StudentID
    self.StudentName = StudentName

def addStudent(self, curs: sql.Cursor):
    query = f"""INSERT INTO Student
            (StudentID, StudentName)
            VALUES ({self.StudentID},{self.StudentName})"""
    curs.execute(query)
Demigas
  • 3
  • 2
  • Please read on [parameterization](https://stackoverflow.com/search?tab=votes&q=%5bsql%5d%20parameterization). In Python and all general-purpose programming languages, developers should avoid interpolating or concatenating values to SQL statements, specifically here with f-strings. – Parfait Nov 25 '21 at 16:21

1 Answers1

1

As commented, consider parameterization. Right now your string formatting does not enclose potential string values in single quotes in VALUES clause. With parameterization which involves placeholders in a prepared SQL statement and binding values in the execution call, you do not need to worry about such quotes.

def addStudent(self, curs: sql.Cursor):
    # PREPARED STATEMENT (NO VARIABLES)
    query = """INSERT INTO Student (StudentID, StudentName)
               VALUES (?, ?)
            """

    # EXECUTE BY BINDING PARAMS
    curs.execute(query, [self.StudentID, self.StudentName])

Above assumes you are using the sqlite3 DB-API which uses qmark placeholder, ?. Most other Python DB-APIs use %s for placeholders (not to be confused with the outmoded string modulo format symbol).

Parfait
  • 104,375
  • 17
  • 94
  • 125
  • I concur. Just to add a bit: the `<` likely came from either `StudentID` or `StudentName`. @Demigas Try posting an example of the input. – carrvo Nov 25 '21 at 18:56
  • 1
    And @carrvo, with params, if that symbol is in `StudentName` it will render in binded string value as is without error. If `StudentID` is an integer, SQLite may throw a different error due to non-number characters. Without params or quotes, `<` becomes a structural part of SQL statement and not part of literal value. – Parfait Nov 25 '21 at 19:05
  • Hello! Thank you for the awesome feedback. I tried your methodology and now it's telling me "sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type." – Demigas Nov 26 '21 at 14:03
  • Update: Now its not adding to the database but I'm not getting any errors. – Demigas Nov 26 '21 at 14:24
  • Are you running [committing](https://stackoverflow.com/a/15271977/1422451) after execute: `connection.commit()`? – Parfait Nov 26 '21 at 17:47