sqlite3.OperationalError: near "<": syntax error: Issue with sql in python formatting?

Question

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)

Please read on parameterization. In Python and all general-purpose programming languages, developers should avoid interpolating or concatenating values to SQL statements, specifically here with f-strings. — Parfait
– Parfait, Commented Nov 25, 2021 at 16:21

Parfait · Accepted Answer · 2021-11-25 18:42:19Z

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).

answered Nov 25, 2021 at 18:42

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

carrvo Over a year ago

I concur. Just to add a bit: the < likely came from either StudentID or StudentName. @Demigas Try posting an example of the input.

Parfait Over a year ago

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.

Demigas Over a year ago

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 Over a year ago

Update: Now its not adding to the database but I'm not getting any errors.

Parfait Over a year ago

Are you running committing after execute: connection.commit()?

Collectives™ on Stack Overflow

sqlite3.OperationalError: near "<": syntax error: Issue with sql in python formatting?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related