CODEWITHSUNDEEP
pythonpython-3.xazure-sql-databasepyodbc
sumit salunke
sumit salunke

Reputation: 125

How to drop and create database using sql sanitizing

cursor.execute("DROP DATABASE ?", (databasename,))

I am using python3 with pyodbc driver. Only facing issue while create and delete database. other operations like select are working fine.

Getting below error: pyodbc.ProgrammingError: ('42000', u"[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)")

Upvotes: 0

Views: 1006

Answers (1)

tezzo
tezzo

Reputation: 11105

In order to sanitize your data you can use SQL Server QUOTENAME to returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

You also need to set autocommit=True in your pyodbc connection to allow dropping of databases.

conn = pyodbc.connect("DRIVER={SQL Server};"
    "SERVER="+server+";"
    "UID="+username+";"
    "PWD="+password,
    autocommit=True)

cursor = conn.cursor()

your_database_name = "YOUR_DB_NAME"
sql_drop = (
    "DECLARE @sql AS NVARCHAR(MAX);"
    "SET @sql = 'DROP DATABASE ' + QUOTENAME(?);"
    "EXEC sp_executesql @sql"
)

cursor.execute(sql_drop, your_database_name)

Upvotes: 2

Related Questions