OperationalError: near "?": syntax error python sqlite3

Question

OperationalError: near "?": syntax error python sqlite3

Navigation

#1 by (2 votes)

0

Good evening.

I'm trying to put a pivot with the function

class db():

    def __init__(self,coluna):
        self.coluna = coluna

    def inserir_coluna_tabela1(self):
        import sqlite3 as sql
        conn = sql.connect('db/dbase.db')

        cursor = conn.cursor()

        cursor.execute("""
        ALTER TABLE 'tabela01'
        ADD COLUMN ? 'TEXT NOT NULL'
        """, self.coluna)

        conn.commit()
        #
        print('Novo campo adicionado com sucesso.')
        #
        conn.close()

camponovo = db('2018')
camponovo.inserir_coluna_tabela1()

and is returning the error: sqlite3.OperationalError: near "?": syntax error

Thank you!

python sqlite3

asked by anonymous 16.07.2018 / 04:57

1 answer

Insert documents in MongoDB via pymongo Why is my connection to the database not working? PHP object-oriented

score 2 · Accepted Answer

Replacing "?" (or "%", "{}") in calls to SQL drivers in Python, including SQLlite does not function exactly like normal string substitution:

These calls only override values to be inserted, but they do not replace column names, tables, or SQL clauses - they are designed in a way that "understands" the SQL structure, and automatically inserts and escapes of single quotes - ' - when needed, in order to prevent SQL injection.

In this case what you should do is use the normal Python string substitution, with .format if it is a version below 3.6, or with f-strings - and leave the substitution that is provided by the call to exectute only for the same values:

 cursor.execute(f"""
        ALTER TABLE 'tabela01'
        ADD COLUMN '{self.coluna}' 'TEXT NOT NULL'
        """)

Or, if it's up to Python 3.5:

 cursor.execute("""
        ALTER TABLE 'tabela01'
        ADD COLUMN '{}' 'TEXT NOT NULL'
        """.format(self.coluna))