Python - Tricks - Part-1

1. Printing Horizontally 

Default it will print one after another

list1 = [1, 3, 6, 7]

for number in list1:

print(number,end=" ")

2. Print using separator

print(1,2,3,4, sep="|") 

3. Merging Dictionaries using pipe |

agegen1 = { "prasad":38, "dilip" : 33}

agegen2 = { "karthik":12, "Maanas" : 6}

agegen = agegen1 | agegen2

print(agegen) 

4. Merging Dictionaries using pipe **

agegen1 = { "prasad":38, "dilip" : 33}

agegen2 = { "karthik":12, "Maanas" : 6}

agegen = {**agegen1 , **agegen2}

print(agegen) 

5. Calendar with Python

import calendar

year_2024_isleap_year = calendar.isleap(2024)

year_2023_isleap_year = calendar.isleap(2023)

if year_2024_isleap_year == True:

print("Year 2024 is leap Year")

else:

print("Year 2024 is not a leap Year")

if year_2023_isleap_year == True:

print("Year 2023 is leap Year")

else:

print("Year 2023 is not leap Year") 

 

6. Get current time and Date

from datetime import datetime

time_now = datetime.now()

print("Timestamp Now: ",time_now)

time_now = datetime.now().strftime('%H:%M:%S')

print("Time Now: ",time_now)

from datetime import date

date_now = date.today()

print("Today's Date: ",date_now) 

 

 7. Sort a List in descending order

list1 = [11,1,3,5,8,4,2]

list1.sort(reverse = True)

print(list1) 

8. Swapping Variables

x, y = 10, 40

print("x is ", x)

print("y is ", y)

x, y = y, x

print("x is ", x)

print("y is ", y) 

9. Counting Item Occurrences

from collections import Counter
list1 = ['Karthik','Maanas','Karthik','Prasad','Dilip']
count_karthik = Counter(list1).get('Karthik')

print(f'Karthik Appears {count_karthik} times') 

 

 10. Flatten the list

#method 1

list1 = [[1,2,3],[3,5,6]]

newlist = []

for list2 in list1:

for i in list2:

newlist.append(i)

print('method 1', newlist)

#method 2

import itertools

newlist1 = list(itertools.chain.from_iterable(list1))

print('method 2', newlist1)

#method 3

newlist2 = [i for j in list1 for i in j]

print('method 3', newlist2) 

 

Power Bi Report - Data Load from Multiple Files

 Requirement

Load data from multiple files into Power BI Report for the reporting.

Require Tools

• PowerBI desktop
• Data load files in CSV

Prepare Data Load files

Create one folder and save few csv files as shown below -

Files format or sample data is like below:-

Every day we will get a file with server log details.

For example, if today we have three files and based on that PowerBI report has been created then report will show loaded data, but let say tomorrow a new file is added then after just by refreshing the report it should show new file data as well.

Lets first create report based on existing files.

PowerBI report development

Launch the PowerBI desktop and select Get Data and select Folder

After selecting the folder, it will ask for the folder path, provide the path of your source files

It will identify the number of files currently in the folder.

Combine and Load the files as we are not doing any transformation on the files

Press Ok on above screen, data is loaded and can see the list of fields in report Fields Pane

Now use the fields to create the report.

In above report we can see data till 3-Jan as we have loaded 3 files starting from 1st Jan to 3rd Jan.

Now, we will add one more file with 4-Jan and will see the data refresh in the report.

After adding new file lets refresh report as shown below :-

By clicking refresh the report is refresh with the latest file data as we

We have seen that PowerBI report can load data from multiple files on daily.

SSIS Connecting Snowflake Database

 

Requirement

Snowflake as source or destination when using in SSIS as ETL tool.

Tools needed.

• Snowflake ODBC 32-bit driver - download link - ODBC - Snowflake Developers
• Snowflake account.
• SSDT (SQL Server Data Tools) with Visual Studio.

Install & Configure Snowflake ODBC Driver

Install only 32 bit ODBC driver if SSIS is 32 bit installed. In my case I have installed 64 bit ODBC driver first and find issues in SSIS as it was not showing in the ADO.NET task after selecting ODBC as connection.

After I installed 32-bit ODBC driver, I am able to see the DSN in my ODBC source.

Create DSN (data source name)

After installed Snowflake DSN, open the ODBC Data Sources (32-bit) by typing in the start in your PC.

Below screen will come, here we need to add new DSN using Snowflake driver.

In User DSN tab, click Add and look for Snowflake driver and select it and click finish.

Provide the connection details of your Snowflake account and click Test.

 

DSN is created successfully.

Configure ODBC Source In SSIS

Create new SSIS package and select ODBC Source as shown below:-

Create new ODBC connection and configure to select the DSN created before as shown below:-

After connection setup properly, when we use to look for the tables in the source it is throwing the error.

This is kind of known issue, and some databases ODBC connection does not work.

Let's switch to ADO NET source to connect the DSN.

Configure ADO NET Source In SSIS

Select the ADO NET source and configure as shown below: -

After selecting ODBC driver in above screen below screen will appear and where after selecting the DSN created by us in above step can be selected and tested the connection.

Now able to preview the list of tables available in the database.

After selecting one of the tables, we can preview data as well.

Pipeline/Package Validation

After enabling the data viewer in the SSIS, we can see the data when pipeline/package is executed.

Another way by checking the executed query on the Snowflake Activity under Query History

This is how we can connect Snowflake using SSIS.

Power BI Desktop Report Connecting to Snowflake Database

 

Requirement

    Develop the power bi report using Snowflake as source of data.

Tools needed

• Snowflake account.
• Power BI Desktop        

Explore Data in Snowflake

    Let's connect to Snowflake account in web, Below screenshot is the interface of the Snowflake

    

Go to the Data section highlighted above.

We can use the sample data provided by Snowflake.

You can navigate from Data tab to see the sample data as shown below-

Data -> Databases -> SNOWFLAKE_SAMPLE_DATA ->  TPCH_SF1 ->  Tables

We can use above tables to do reporting on PowerBI

Connect Snowflake In PowerBI

Open the PowerBI desktop and select Get Data, it will open below like screen. Select Snowflake from the list of different databases and click Connect.

After clicking on Connect, it will ask for the server details and warehouse.

Enter the details like 

Server -  <your_account_identifier>.snowflakecomputing.com

e.g. my server - fzjlcwk-ns72307.snowflakecomputing.com

Warehouse - You can create your own warehouse as shown below screenshot.

Basically, warehouse is the cluster which helps to run the code or in data load.

My details for connecting Snowflake database is shown below-

After connected to the source, all the databases having access I can see in the Navigator.

Select the required tables to add into the PowerBI model and click Load.

After Load, you need to select its Import or DirectQuery, we will select Import for now.

It will start loading the data.

All the tables were loaded and can see in the PowerBI Data pane.

Go to the Model and establish the relationship between the tables as if we don't do that then the data will not be related and show wrong (it apply cross join).

After establishing the correct relationship, we can see the model like below-

Go to Report view and use the fields to create the report

We successfully created the PowerBI report using Snowflake database,