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?

Thom A
  • 88,727
  • 11
  • 45
  • 75
van thang
  • 99
  • 2
  • 9
  • Does this answer your question? [Python, SQLAlchemy pass parameters in connection.execute](https://stackoverflow.com/questions/19314342/python-sqlalchemy-pass-parameters-in-connection-execute) – Thom A Apr 21 '22 at 07:42

1 Answers1

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
  • 47,570
  • 11
  • 100
  • 153
  • How do we use parameter substition when a table value is passed in as a variable? @snakecharmerb – Nithin Mar 02 '23 at 16:41
  • 1
    @Nithin you don't, because parameter substitution only works for values, not for identifiers like table or column names. If you need dynamic table names I'd suggest getting a `Table` object through reflection like in [this answer](https://stackoverflow.com/a/63832013/5320906). – snakecharmerb Mar 02 '23 at 17:03