1

I was trying to create a query loop, which does interactive steps from one instance to the next. After fetching the right data.

Connected python to SQL server and returned table schema column name and type in a variable.

Here I am trying to create hashbyte for every record in the database save a column name as hk

I am getting error:

ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ''COL_NAMES''. (207) (SQLExecDirectW)")

My desired output will be column name as hk should be saved in the database and has a unique code for all the row as

1 Answers1

0

So you already have your list of column names ...

>>> COL_NAMES = ['ID', 'NAME', 'Date']  # sample data

... and you can insert them into the lower(isnull(... fragment with a list comprehension and then join them together with plus signs ...

>>> s = '+'.join([f"lower(isnull(convert(nvarchar(255),[{x}]), ''))" for x in COL_NAMES])
>>> s
"lower(isnull(convert(nvarchar(255),[ID]), ''))+lower(isnull(convert(nvarchar(255),[NAME]), ''))+lower(isnull(convert(nvarchar(255),[Date]), ''))"

... and then insert that into the rest of the SQL statement

>>> sql = f"SELECT HASHBYTES('MD5', {s}) as hk, * INTO [dbo].[ABCD_A] FROM [dbo].[ABC_A]"
>>> sql
"SELECT HASHBYTES('MD5', lower(isnull(convert(nvarchar(255),[ID]), ''))+lower(isnull(convert(nvarchar(255),[NAME]), ''))+lower(isnull(convert(nvarchar(255),[Date]), ''))) as hk, * INTO [dbo].[ABCD_A] FROM [dbo].[ABC_A]"
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • @PURU - Look [here](https://docs.python.org/3/whatsnew/3.6.html#whatsnew36-pep498). – Gord Thompson Aug 03 '19 at 14:48
  • @PURU - That's a different problem so you should [ask a new question](https://stackoverflow.com/questions/ask). – Gord Thompson Aug 03 '19 at 15:35
  • hey @Gord Thompson - look into it https://stackoverflow.com/questions/57340041/removing-on-the-basis-of-condition help me out thanks –  Aug 03 '19 at 16:12