Forum Discussion

LeboeufHarry's avatar
LeboeufHarry
Helper I
4 years ago

Troubles Runnen Python sqlparser

Hello,

I'm trying to 'extract' the sql tables/views used in some sql statements.

In Python this is quite easy with sqlparser. So I thought using this in PowerBI/Query.

This is my code (Temporary only 1 row)

let
    Source = Sql.Database("syn-eu2-prd-edw-001.database.windows.net", "syndpeu2prdedw1"),
    SynapsMonitor_SqlStmt = Source{[Schema="rdv_60_169",Item="SynapsMonitor_SqlStmt_V_Just_Stmt"]}[Data],
    #"Replaced Value" = Table.ReplaceValue(SynapsMonitor_SqlStmt,"#(cr)#(lf)"," ",Replacer.ReplaceText,{"statement"}),
    #"Kept First Rows" = Table.FirstN(#"Replaced Value",1),
    #"Run Python script" = Python.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)import sql_metadata as sm#(lf)dataset[""Test""] = '/'.join(sm.Parser('""' + str(dataset[""statement""]) + '""').tables)",[dataset=#"Kept First Rows"]),
    #"Expanded Value" = Table.ExpandTableColumn(#"Run Python script", "Value", {"statement", "SqlIdx", "Test"}, {"Value.statement", "Value.SqlIdx", "Value.Test"})
in
    #"Expanded Value"

I've tried al posible combinations, this one is ending into a error

DataSource.Error: ADO.NET: Python script error.
<pi>Not supported query type: "0    select r.[ROUTINE_SCHEMA], r.[ROUTINE_NAME], r...
Name: statement, dtype: object"
ValueError: Not supported query type!
</pi>
Details:
    DataSourceKind=Python
    DataSourcePath=Python
    Message=Python script error.
<pi>Not supported query type: "0    select r.[ROUTINE_SCHEMA], r.[ROUTINE_NAME], r...

Might be caused due to the extra "

But when I just enter field fieldname I'm getting the error

DataSource.Error: ADO.NET: Python script error.
<pi>ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
</pi>
Details:
    DataSourceKind=Python
    DataSourcePath=Python
    Message=Python script error.
<pi>ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
</pi>
    ErrorCode=-2147467259

This (I think) comes from the pandas problem with AND / OR that in some aricles is changed into & |

 

But if I change that the SQL statement goes wrong.

 

Funny thing is that when I use this code all works fine....

let
    Source = Sql.Database("syn-eu2-prd-edw-001.database.windows.net", "syndpeu2prdedw1"),
    SynapsMonitor_SqlStmt = Source{[Schema="rdv_60_169",Item="SynapsMonitor_SqlStmt_V_Just_Stmt"]}[Data],
    #"Replaced Value" = Table.ReplaceValue(SynapsMonitor_SqlStmt,"#(cr)#(lf)"," ",Replacer.ReplaceText,{"statement"}),
    #"Kept First Rows" = Table.FirstN(#"Replaced Value",1),
    #"Run Python script" = Python.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)import sql_metadata as sm#(lf)dataset[""Test""] = '/'.join(sm.Parser(""select r.[ROUTINE_SCHEMA], r.[ROUTINE_NAME], r.[ROUTINE_TYPE], p.create_date [CREATED_DATE], p.modify_date [MODIFIED_DATE], cast(e.value as nvarchar(max)) [DESCRIPTION] from [INFORMATION_SCHEMA].[ROUTINES] r join sys.schemas s on s.name = r.[ROUTINE_SCHEMA] join sys.objects p on p.name = r.[ROUTINE_NAME] and p.schema_id = s.schema_id and p.parent_object_id = 0 left outer join sys.extended_properties e on p.object_id = e.major_id and e.minor_id = 0 and e.class = 1 and e.name = 'MS_Description'"").tables)",[dataset=#"Kept First Rows"]),
    #"Expanded Value" = Table.ExpandTableColumn(#"Run Python script", "Value", {"statement", "SqlIdx", "Test"}, {"Value.statement", "Value.SqlIdx", "Value.Test"})
in
    #"Expanded Value"

But off course this is not the final wanted result...

 

Any suggestions ?

Need Help

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi LeboeufHarry ,

     

    Did you try to use SQL Server connector's advanced options to execute SQL statements, like this:

     

     

    Best Regards,
    Winniz

    • LeboeufHarry's avatar
      LeboeufHarry
      Helper I
      4 years ago

      This has nothing to do with executing SQL command, its about parsing the commands found in the SQL Auditing files.

      This is Python, but the saving with Pandas to csv files is internal PowerBI Engine stuff, we have no control on this. 

  • romankris's avatar
    romankris
    New Member
    3 years ago

    Pandas follows the numpy convention of raising an error when you try to convert something to a bool. This happens in a if or when using the boolean operations, and, or, or not. It is not clear what the result of.

    example

     

    5 == pd.Series([12,2,5,10])

     

    The result you get is a Series of booleans, equal in size to the pd.Series in the right hand side of the expression. So, you get an error. The problem here is that you are comparing a pandas pd.Series with a value, so you'll have multiple True and multiple False values, as in the case above. This of course is ambiguous, since the condition is neither True or False. You need to further aggregate the result so that a single boolean value results from the operation. For that you'll have to use either any or all depending on whether you want at least one (any) or all values to satisfy the condition.

     

    (5 == pd.Series([12,2,5,10])).all()
    # False

     

    or

     

    (5 == pd.Series([12,2,5,10])).any()
    # True