Running Multiple SQL Server Statements in One Variable pyodbc

Viewed 955

I'm having trouble running some statements in pyodbc. I want to create a table variable, insert into the table variable, update the table variable, then select all from the table variable. The code below is what I have currently, although it only executes the first SQL statement and then errors because it treats each statement as its own, not as one.

Without splitting on the semi colons, pyodbc throws and error saying that the statement is not a valid SQL query. Any help is appreciated.

import pyodbc
conn = pyodbc.connect('Driver={Driver};'
                      'Server=servername;'
                      'Database=db;'
                      'UID=user;'
                      'PWD=pass;')
cursor = conn.cursor()

def main():
    statement = """DECLARE @table TABLE (Brand int not null);
    INSERT INTO @table (Brand) VALUES (1);
    UPDATE @table SET Brand = 2 WHERE Brand = 1;
    SELECT * FROM @table;"""

    for lines in statement.split(";"):
        with conn.cursor() as cur:
            cur.execute(lines)
        print(lines)

    conn.commit()

main()

Edit: Thanks @Gord-Thompson for the solution, I'm posting my new function so others can see.

def main():
    statement = """SET NOCOUNT ON;
    DECLARE @SQL varchar(1000)
    SET @SQL = 'DECLARE @table TABLE (ClientBrand int not null)
    INSERT INTO @table (Clientbrand) VALUES (1)
    UPDATE @table SET Clientbrand = 2 WHERE ClientBrand = 1
    SELECT * FROM @table'
    EXECUTE (@SQL)"""

    cursor.execute(statement)
    results = cursor.fetchall()
    
    for i in results:
        print(i)
    conn.commit()
    print('success')

main()
0 Answers
Related