Data Preprocessing using Python for SQL Server

“Data is the new science. Big Data holds the answers.” – By Pat Gelsinger, Chief Executive Officer at VMware

Table of contents:

1. Python to SQL Server connect
2. Data Pre-processing in python

First, we need to connect Python to SQL Server using pyodbc?

If so, I’ll show you the steps to establish this type of connection using a simple example.

The Example to be Used

To start, let’s review an example, where:

• The Server Name is: DESKTOP-CRBEE2U\MLMI
• The Database Name is: MLMI
• The Table Name (with a dbo schema) is: dbo. House_train
• The dbo.House_train table contains the following data:

Steps to Connect Python to SQL Server using pyodbc

Step 1: Install pyodbc

First, you’ll need to install the pyodbc package which will be used to connect Python to SQL Server.

Step 2: Retrieve the server name

Now retrieve your server name.

You can get your server name by opening SQL Server. You’ll then see the Connect to Server box, where the server name will be displayed.

In my case, the server name is: DESKTOP-CRBEE2U\MLMI

Step 3: Obtain the database name

Next, obtain the database name in which your desired table is stored.

You can find the database name under the Object Explorer menu (underneath the Databases section), which is located on the left side of your SQL Server.

In our example, the database name is: MLMI

Step 4: Connect Python to SQL Server

And for the final part, open your Python IDLE and fill the server name, database and table information.

Here is the structure of the code that you may use in Python:

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=db_name;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM db_name.Table')

for row in cursor:
    print(row)

And this is how the code would look like in Python using our example:

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DESKTOP-CRBEE2U\MLMI;'
                      'Database=MLMI;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

SQL_Query = pd.read_sql_query('''select * FROM MLMI.dbo.House_train''', conn)
data = pd.DataFrame(SQL_Query)

Click on below image, which will take you to coding page where you can see the steps involved starting from SQL connection to data prepossessing