Adding Variables to SQL queries

Question

I have the following code which returns a table from my ms sql Database

one_yrs_ago = datetime.now() - relativedelta(years=1)


 all =  'SELECT Master_Sub_Account , cAccountTypeDescription , Debit , Credit FROM [Kyle].[dbo].[PostGL] AS genLedger'\
                    ' Inner JOIN [Kyle].[dbo].[Accounts] '\
                    'on Accounts.AccountLink = genLedger.AccountLink '\
                    'Inner JOIN [Kyle].[dbo].[_etblGLAccountTypes] as AccountTypes '\
                    'on Accounts.iAccountType = AccountTypes.idGLAccountType'\
                    ' WHERE genLedger.AccountLink not in (161,162,163,164,165,166,167,168,122)'\
                    

How would I add the one_yrs_ago variable to the SQL query in this situation , like so :

one_yrs_ago = datetime.now() - relativedelta(years=1)

 all =  'SELECT Master_Sub_Account , cAccountTypeDescription , Debit , Credit FROM [Kyle].[dbo].[PostGL] AS genLedger'\
                    ' Inner JOIN [Kyle].[dbo].[Accounts] '\
                    'on Accounts.AccountLink = genLedger.AccountLink '\
                    'Inner JOIN [Kyle].[dbo].[_etblGLAccountTypes] as AccountTypes '\
                    'on Accounts.iAccountType = AccountTypes.idGLAccountType'\
                    ' WHERE genLedger.AccountLink not in (161,162,163,164,165,166,167,168,122)'\
                    ' AND WHERE genLedger.TxDate > ' one_yrs_ago''

Answer 1

Not seeing the full context but this is how I would do it in SQL Server

I would say parameterize what you pass in is important as otherwise you could get SQL injection!

Full example to play with, note that you declare the parameters and their types and then pass the actual values you want in all on that one line

EXEC sp_executesql @sqlToExec, N'@TheName NVARCHAR(100)', @TheName = 'Andrew'

Little example to run below:

BEGIN TRANSACTION
BEGIN TRY

    CREATE TABLE #Users (
        Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
        [Name] NVARCHAR(50) NOT NULL
    )

    INSERT INTO #Users ([Name]) 
    VALUES('Andrew'),
    ('Bob')

    DECLARE @sqlToExec NVARCHAR(4000) = N'SELECT * 
    FROM #Users AS U 
    WHERE U.[Name] = @TheName'
    
    EXEC sp_executesql @sqlToExec, N'@TheName NVARCHAR(100)', @TheName = 'Andrew'


    ROLLBACK TRANSACTION
END TRY
BEGIN CATCH
    PRINT 'Rolling back changes, there was an error!!' 
    ROLLBACK TRANSACTION
    DECLARE @Msg NVARCHAR(MAX)  
    SELECT @Msg=ERROR_MESSAGE() 
    RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG
END CATCH