0

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?

Larnu
  • 76,706
  • 10
  • 34
  • 63
van thang
  • 91
  • 1
  • 7
  • Does this answer your question? [Python, SQLAlchemy pass parameters in connection.execute](https://stackoverflow.com/questions/19314342/python-sqlalchemy-pass-parameters-in-connection-execute) – Larnu Apr 21 '22 at 07:42

2 Answers2

3

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)
snakecharmerb
  • 36,887
  • 10
  • 71
  • 115
-3

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}")
Rriskit
  • 394
  • 4
  • 10
  • This is injection, @vanthang , that is *not* the correct way, it is dangerous. Do **not** use this answer. – Larnu Apr 21 '22 at 07:41
  • Hi @Larnu , do you have an alternative ?? Could you please assist ? – van thang Apr 21 '22 at 11:47
  • You already have another [answer](https://stackoverflow.com/a/71953583/2029983) from [snakecharmerb](https://stackoverflow.com/users/5320906/snakecharmerb), @vanthang . – Larnu Apr 21 '22 at 11:48