Operationalerror: near "?": syntax error python sqlite3

Asked

Viewed 566 times

0

Good night.

I’m trying to put a dynamic column 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 error: sqlite3.Operationalerror: near "?": syntax error

Thank you!

python sqlite3
  • 1

    You cannot pass something structural as parameter of Prepared statement. In this case, you should interpolate the value of self.coluna in the string of ALTER TABLE. The marks of ? only for DML, not for DDL

  • Thanks! I made this change and it worked: cursor.execute("ALTER TABLE " + self.table +" ADD COLUMN '" + self.column +"' TEXT")

  • The jsbueno answer answers your question. I think it is appropriate to mark it as accepted

1 answer

2


The substitution of "?" (or "%", "{}") in calls to SQL drivers in Python, including Sqllite does not work exactly like normal string replacement:

These calls only replace values to be inserted, but do not replace column names, tables or SQL clauses - they are drawn in a way that "understands" the SQL structure, and automatically insert and escape simple quotes - ' - when necessary, in order to prevent SQL Injection.

In this case what you should do is use the normal Python string replacement, with the .format if it is a version below 3.6, or with f-strings - and leave the substitution 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 you go to Python 3.5:

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

Browser other questions tagged python sqlite3

You are not signed in. Login or sign up in order to post.