If I try the python script below (sql that aggregates the data of one table in a postgresql database) I get errors.
If I try the same script but with Juypter it runs fine. I think there is an issue with the string literal: postgresql wants double quotes and powerbi wants it converted to single quotes.
let
pythonScript = "
import pandas as pd
import psycopg2
# Define the PostgreSQL connection parameters
conn_params = {
'host': '127.0.0.1',
'database': 'test',
'user': 'postgres',
'password': 'freya',
'port': '5432'
}
# Define the SQL query
query = '''
SELECT *
FROM public.address
WHERE ("Enterprise_nbr", "ID") IN (
SELECT "Enterprise_nbr", MAX("ID")
FROM public.address
GROUP BY "Enterprise_nbr")
'''
# Connect to the database and retrieve the data
conn = psycopg2.connect(**conn_params)
data = pd.read_sql(query, conn)
# Close the database connection
conn.close()
# Output the data to Power BI
print(data)
",
source = Python.Execute(pythonScript),
data = source{[Name="data"]}[Value],
#"Changed Type" = Table.TransformColumnTypes(data,{{"Enterprise_nbr", Int64.Type}, {"ID", Int64.Type}, {"street", type text}, {"nr", Int64.Type}, {"postal", Int64.Type}, {"city", type text}, {"latitude", Int64.Type}, {"longitude", Int64.Type}})
in
#"Changed Type"
This is the error I get when I convert the double quotes in the sql to single quotes. I don't know how to resolve this issue.
DataSource.Error: ADO.NET: Python script error.
<pi>C:\Users\freya\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\io\sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
warnings.warn(
psycopg2.errors.SyntaxError: non-integer constant in GROUP BY
LINE 7: GROUP BY 'Enterprise_nbr')
^
The above exception was the direct cause of the following exception:
pandas.io.sql.DatabaseError: Execution failed on sql '
SELECT *
FROM public.address
WHERE ('Enterprise_nbr', 'ID') IN (
SELECT 'Enterprise_nbr', MAX('ID')
FROM public.address
GROUP BY 'Enterprise_nbr')
': non-integer constant in GROUP BY
LINE 7: GROUP BY 'Enterprise_nbr')
^
</pi>
Details:
DataSourceKind=Python
DataSourcePath=Python
Message=Python script error.
<pi>C:\Users\freya\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\io\sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
warnings.warn(
psycopg2.errors.SyntaxError: non-integer constant in GROUP BY
LINE 7: GROUP BY 'Enterprise_nbr')
^
The above exception was the direct cause of the following exception:
pandas.io.sql.DatabaseError: Execution failed on sql '
SELECT *
FROM public.address
WHERE ('Enterprise_nbr', 'ID') IN (
SELECT 'Enterprise_nbr', MAX('ID')
FROM public.address
GROUP BY 'Enterprise_nbr')
': non-integer constant in GROUP BY
LINE 7: GROUP BY 'Enterprise_nbr')
^
</pi>
ErrorCode=-2147467259
ExceptionType=Microsoft.PowerBI.Scripting.Python.Exceptions.PythonScriptRuntimeException
