How to handle multi-Line SQL Statement in Python

Question

I'm using pypyodbc to connect to my SQL2014 server and I have below query to be executed. However, it gives error message saying:

pypyodbc.ProgrammingError: (u'42S22', u"[42S22] [Microsoft][SQL Server Native Cl ient 11.0][SQL Server]Invalid column name '2017-02-07'.")

I can run sql query on the server without any problems. I'm wondering what is the correct way to include long sql statement and get it executed?

Python Code:

result = cursor.execute('''
    declare @date date ="2017-02-07",
        @env varchar (10)= "TEST",
        @runid int,
        @val smallint,
        @timelen int,
        @interface varchar(30),
        @table_t varchar(30),
        @del_t int,
        @upd_t int,
        @ins_t int

    declare @import_test TABLE
    (RUN_DATE date,
     ENV varchar(10),
     ID int,
     [CHECK] varchar (100),
     CODE varchar(30),
     TABLE_NAME varchar(30),
     VAL_1 int,
     VAL_2 int,
     VAL_3 int )

    set @val=(select count(*) from MY_TABLE where convert(date,[timestamp])=@date and msg_cd="OK")

    insert into @import_test values (@date,@env,null,"Check 1 (count of OK codes)",null,null,@val,null,null)
 
    select * from @import_test

    ''')
for row in result:
    print(row)

nicogen · Accepted Answer · 2017-05-26 12:38:51Z

3

In MSSQL, strings surrounded by double quotes are interpreted as column names. You need to use double quotes for the Python multi-line string and surround strings within the SQL query with single quotes.

Try:

result = cursor.execute("""
declare @date date ='2017-02-07',
    @env varchar (10)= 'TEST',
    @runid int,
    @val smallint,
    @timelen int,
    @interface varchar(30),
    @table_t varchar(30),
    @del_t int,
    @upd_t int,
    @ins_t int
...
""")

answered May 26, 2017 at 12:38

nicogen

2041 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

weijie lin Over a year ago

I changed the singe quotes to double quotes...but it throws me another error saying pypyodbc.ProgrammingError: (u'24000', u'[24000] [Microsoft][SQL Server Native Cl ient 11.0]Invalid cursor state')

Collectives™ on Stack Overflow

How to handle multi-Line SQL Statement in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related