sql - python syntax error making view and selecting data-CodePudding

hi and hope you have a good time , i was making an app in python and SQL and i'm getting a syntax error in SQL , can you help?

self.mycursor.execute(f'''CREATE VIEW [usable products] AS
            SELECT * FROM products
            WHERE {str(self.counts.value())}> (SELECT count FROM products)
            ''')

and the error is :

ight syntax to use near '[usable products] AS
            SELECT * FROM products
            WHER' at line 1

version for the right syntax to use near '[usable products] AS
            SELECT * FROM products
            WHER' at line 1

thank you!

CodePudding user response：

Consider parameterizing your query and avoiding F-string interpolation. Also, the subquery is not necessary if count (not mistaken for COUNT() aggregate) is a column in products table.

Below assumes you are using pyodbc or sqlite3 which use qmarks, ?, for parameter placeholders. If using other DB-APIs (psycopg2, pymysql, etc.) use %s placeholder.

sql = (
    "CREATE VIEW [usable products] AS "
    "   SELECT * FROM products "
    "   WHERE count < ?"
)

self.mycursor.execute(sql, [self.counts.value()])