How to use variable in SQL Alchemy-CodePudding

I have successfully connected with SQL server using Alchemy and pyobdc, do update database, delete record also work as fine.

Now I want to use the variable to assign the statement in the SQL command

#import library
import pandas as pd
import os
from sqlalchemy import create_engine
from sqlalchemy.engine import URL
import pyodbc

#prepare for the connection

SERVER = 'IT\SQLEXPRESS'
DATABASE = 'lab'
DRIVER = 'SQL Server Native Client 11.0'
USERNAME = 'sa'
PASSWORD = 'Welcome1'
DATABASE_CONNECTION = f'mssql://{USERNAME}:{PASSWORD}@{SERVER}/{DATABASE}?driver={DRIVER}'

#prepare SQL query

year_delete = 2019 
sql_delete = ("DELETE FROM [dbo].table1 where dbo.table1.[Year Policy] = 2019")
result=connection.execute(sql_delete)

How I could use year_delete instead of manually input 2019 in the code?

CodePudding user response：

As Larnu points out in their comment, using f-strings or other string formatting techniques exposes an application to SQL injection attacks, and in any case can be error-prone.

SQLAlchemy supports parameter substitution, allowing values to be safely inserted into SQL statements.

from sqlalchemy import text

# Make a dictionary of values to be inserted into the statement.
values = {'year': 2019}
# Make the statement text into a text instance, with a placeholder for the value.
stmt = text('DELETE FROM [dbo].table1 where dbo.table1.[Year Policy] = :year')
# Execute the query.
result = connection.execute(stmt, values)

CodePudding user response：

You can use an f-string (standard python-technique to insert Python-Expressions/Variables):

sql_delete=(f"delete .... where dbo.table1[Year Policy] ={year_delete}")