T-SQL vs Pandas

The goal of this exercise is to learn Pandas equivalents of T-SQL statements. I have a T-SQL background with some basics of C/Java programming somewhere in my head and now learning Python whenever I can.

This is a work in progress as I will document as and when I learn.

For my SQL queries, I have used an MS SQL Server 2016 engine. For Python, I have used a Python 3 Kernel. For running the queries and commands, I have used Azure Data Studio

Loading data from a file

Lets say I have a Persons.csv containing the following rows. The location is C:\Temp\Persons.csv

• T-SQL:
• Create a blank table and then use BULK INSERT to insert data into the table. You can also use SSMS to load the data after creating the table using the Import-Export Wizard

CREATE TABLE Persons( ID INT, Name VARCHAR(100));
BULK INSERT dbo.Persons
FROM 'C:\Temp\Persons.csv'
WITH
(
    FIELDTERMINATOR = ',',
    FIRSTROW = 2

)  

• Python:
• Can create a data frame directly using the Pandas read_csv function:
• import pandas as pd;

  df_persons = pd.read_csv("C:\Temp\Persons.csv")

Selecting all records:

• T-SQL: 
• SELECT * FROM Persons
• Python:
• You can use one of the following:
• print(df_persons)
• display(df_persons)
• df_persons

Return number of rows:

• T-SQL:
• I know there are better methods than COUNT(*)
• SELECT COUNT(*) FROM Persons
• Python:
• the "count" method returns the number of non-null records for each column
• df_persons.count()
• the "len" method returns the