Trying to drop table using cursor in django

Question

I am trying to drop a table using the cursor but, it always says syntax error although I used it before and worked but not to drop table

def check_TempTableNames(tableName):
    print(tableName)
    with connections['DataAdmin'].cursor() as cursor:
        print("Check Done")
        cursor.execute("DROP TABLE %s",[tableName]) 

def preStepBtn2(request):
    #empty=Rfde2003Syd0827.objects.using('DataAdmin').all()
    sourcePE = request.GET.get('sourcePE')
    targetPE =request.GET.get('targetPE')
    dropdownConnType =request.GET.get('dropdownConnType')
    sourceInterFace =request.GET.get('sourceInterFace')
    targetInterFace =request.GET.get('targetInterFace') 
    temp_looback = "sop_ce_loopback_interface_" + sourcePE + "_" + targetPE 
    TEMP_ROUTER_STATUS = "sop_ce_router_status_" + sourcePE + "_" + targetPE
    Temp_virtual_int = "sop_ce_virtual_interface_" + sourcePE + "_" + targetPE
    print(temp_looback)
    check_TempTableNames(temp_looback)
    check_TempTableNames(TEMP_ROUTER_STATUS)
    check_TempTableNames(Temp_virtual_int)

My databases:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'Fileade',
        'HOST': '10.238.76.53',
        'USER': 'SDS_dataflow',
        'PASSWORD': 'SDS_dataflow',

        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
        }

    } ,

    'DataAdmin': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'Data_Admin',
        'HOST': '10.238.76.53',
        'USER': 'SDS_dataflow',
        'PASSWORD': 'SDS_dataflow',

        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
        }
}  

}

Traceback (most recent call last): File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\core\handlers\exception.py", line 34, in inner response = get_response(request) File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\core\handlers\base.py", line 126, in _get_response response = self.process_exception_by_middleware(e, request) File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\core\handlers\base.py", line 124, in _get_response response = wrapped_callback(request, *callback_args, **callback_kwargs) File "D:\College\Orange Training\MassiveMigrationPortalTool\posts\views.py", line 170, in preStepBtn2 check_TempTableNames(temp_looback) File "D:\College\Orange Training\MassiveMigrationPortalTool\posts\views.py", line 147, in check_TempTableNames cursor.execute("DROP TABLE %s;",[tableName]) File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\db\backends\utils.py", line 100, in execute return super().execute(sql, params) File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\db\backends\utils.py", line 68, in execute return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\db\backends\utils.py", line 77, in _execute_with_wrappers return executor(sql, params, many, context) File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\db\backends\utils.py", line 85, in _execute return self.cursor.execute(sql, params) File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\db\utils.py", line 89, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\django\db\backends\utils.py", line 85, in _execute return self.cursor.execute(sql, params) File "C:\Users\Mohammed\Envs\TestEnv\lib\site-packages\sql_server\pyodbc\base.py", line 546, in execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)") [02/Sep/2019 15:43:57] "GET /posts/preStepBtn2?targetInterFace=GE2%2F3%2F4&targetPE=byyc916&sourcePE=byyc815&sourceInterFace=GE4%2F0%2F1&dropdownConnType=Ethernet HTTP/1.1" 500 20592

bruno desthuilliers bruno desthuilliers · Accepted Answer · 2019-09-02T14:23:57

The placeholders are only for fields values, you cannot use them in DDL statements. Here you have to use plain string formatting...

Also:

1/ you definitely don't want to do this on a GET request. According to the HTTP spec, a GET request MUST be idempotent, and there are reasons for this (there's a famous DailyWTF story about a website's database being erased each time the googlebot kicked in...).

2/ you definitly don't want to trust user data, specially when it comes to database operations, and even more when those data cannot be sanitized by the db-api. Use a django form for validation and sanitization, and make sure the user doing the action is allowed to drop those exact specific tables.

ow and yes: "check_xxx" is a very bad name for something that actually deletes 'xxx'. "clean_temp_tables" or something similar might make the intent clearer.

Trying to drop table using cursor in django

1 Answers