Untitled

# imports general
import pandas as pd
import json
import requests
import sqlalchemy as sa
import pyodbc

# import 'own' packages
# import pedb

# # Define the connection string
conn_str_sqlprod02 = (
        'mssql+pyodbc:///?odbc_connect=' +
        'DRIVER={ODBC Driver 17 for SQL Server};'
        'SERVER=rh-sqlprod02.raedthuys.local;'
        'DATABASE=Energy;'
        'Trusted_Connection=yes;'
        'Encrypt=yes;'
        'TrustServerCertificate=yes;'
)

conn_str_petradringai = (
    'mssql+pyodbc://petradingai.database.windows.net:1433/TradingBRP?'
    'driver=ODBC+Driver+17+for+SQL+Server&'
    'authentication=ActiveDirectoryIntegrated&'
    'Encrypt=yes&'
    'TrustServerCertificate=no&'
    'Connection Timeout=30'
)

# Create the SQLAlchemy engine
conn = sa.create_engine(conn_str_sqlprod02)
conn2 = sa.create_engine(conn_str_petradringai)

# Get property's to process
query_ean = """
SELECT 
    [LDN]
    ,[Startdate]
    ,[EndDate]
    ,[Type]
    ,[ReferencedTable]
    ,[StringValue]
    ,[EnumValue]
    ,[IntValue]
    ,[BoolValue]
    ,[FloatValue]
    ,[GuidValue]
    ,[sysModified]
    ,[sysCreated]
    ,[Description]
    ,[ConnectionPropertyId]
    ,[SysUser]
FROM [Energy].[dbo].[ConnectionProperty]
-- where type = 'ExternalAvailability'
where type not in (
    'Meetbedrijf',
    'HeeftBrutoProductieMeter',
    'Metercode',
    'RtuActive',
    'ExternalAvailability'
)
"""
df = pd.read_sql(query_ean, conn)


# Get property's to process
query_ean2 = """
SELECT TOP (1000) [WTGid]
      ,[WTGnr]
      ,[WTGname]
      ,[WTGpower]
      ,[WTGManufacturer]
      ,[WtgType]
      ,cast([GoO] as nvarchar(18)) as GoO
      ,cast([LDN] as nvarchar(18)) as LDN
      ,[Projectname]
      ,[StartDate]
      ,[Enddate]
      ,[WTGLatitude]
      ,[WTGLongitude]
      ,[Hubheight]
      ,[Syv]
      ,[FullLoadHours]
      ,[SubsidiaryPrice]
      ,[SubsidiaryTotalkWh]
      ,[SubsidiaryEnddate]
      ,[SubsidiaryType]
      ,[Address]
      ,[Zipcode]
      ,[Location]
      ,[TurbineID]
      ,[SysCreated]
      ,[SysModified]
      ,[WTG_Cut_out_ms]
      ,[IsDemolished]
      ,[SysUser]
  FROM [assets].[WTG_Masterdata]
"""
df2 = pd.read_sql(query_ean2, conn2)

print(df2)