Author: tom

Below, a programme is provided that shows the point cloud with a line that is the best fit to it. import pandas as pd import sqlalchemy as sa import matplotlib.pyplot as plt import numpy as np table_name = ‘MIGRATED_DIVORCE_SETTLEMENT__C’ connection_string = “DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-8J58OIP\MSSQLSERVER_19;DATABASE=Speel;UID=sa;PWD=**” connection_url = sa.engine.URL.create(“mssql+pyodbc”, query={“odbc_connect”: connection_string}) engine = sa.engine.create_engine(connection_url)…

Below, I read a table and I apply the ordinary least squares methode to it: import pandas as pd from sqlalchemy.engine import create_engine from sqlalchemy.engine import URL import sqlalchemy as sa import statsmodels.api as sm table_name = ‘MIGRATED_DIVORCE_SETTLEMENT__C’ connection_string = “DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-8J58OIP\MSSQLSERVER_19;DATABASE=Speel;UID=sa;PWD=**” connection_url = URL.create(“mssql+pyodbc”, query={“odbc_connect”: connection_string}) engine = create_engine(connection_url) with…

import pandas as pd from sqlalchemy.engine import create_engine from sqlalchemy.engine import URL from sqlalchemy.types import String, Date, DateTime from sqlalchemy import text import sqlalchemy as sa import pyodbc import oracledb import math table_name = ‘payment__c’ conntarget = oracledb.connect( user=”HR”, password=”**”, dsn=”192.168.178.6/orcl”) cursortarget = conntarget.cursor() zoek_query = “select count(table_name) from user_tables where lower(table_name)=lower(‘” + table_name +…

An alternative to a previous post is the programme below. This uses Pandas dataframe to transport data. import pandas as pd from sqlalchemy.engine import create_engine from sqlalchemy.engine import URL from sqlalchemy.types import String, Date, DateTime from sqlalchemy import text import sqlalchemy as sa import pyodbc import oracledb table_name = ‘migrated_pension_accrual__c’ conntarget = oracledb.connect( user=”HR”, password=”AAaa11!!”,…

Plse find enclosed a script to write records from SQL Server to Oracle: import pyodbc import datetime conn = pyodbc.connect(‘DSN=SQLServer;UID=sa;PWD=**’) conntarget = pyodbc.connect(‘DSN=Rekendoos;UID=hr;PWD=**’) table_name = ‘MIGRATED_DIVORCE_SETTLEMENT__C’ cursor = conn.cursor() cursortarget = conntarget.cursor() cursor.execute(‘select * from Rapportage.’ + tabel) columns = [column[0] for column in cursor.description] kolommen = ” for x in range(len(columns) – 1): kolommen…

It might be that you see one record in a view that generates an error somewhere but you have not clue which record might generate that error. It could then be that the next logic helps. Create this procedure: create or replace package mypkg_tom as cursor c is select * from tpl_v_calc_dossier; type cur_tab is…

In a data warehouse environment, we may have to close records when we have new records that are added to the system. Let me first provide the sql. CASE mut.etl_valid_fromWHEN MAX(mut.etl_valid_from) OVER(PARTITION BY TO_NUMBER(mut.company_code||mut.gl_account_nr))THEN mut.etl_valid_toELSE LEAD(mut.etl_valid_from) OVER(PARTITION BY TO_NUMBER(mut.company_code||mut.gl_account_nr)ORDER BY mut.etl_valid_from)END This is a really nice concise text. Let me show a few items. With…

Oracle has the concept of partitions: the idea that a set of records is stored in one partition, that is directly accessible. This avoids the situation that large segments of data must be investigated before a record is found. The code to generate such table is: CREATE TABLE “HR”.”FCT_BLN_ALLOC_USAGE” ( “PROCESSING_DATE_SK” NUMBER, “INCREMENT_OFFSET_SEC” NUMBER, “ETL_REFRESH_DATE”…