Learn everything about Analytics

Home » SQL For Data Science: A Beginner’s Guide!

SQL For Data Science: A Beginner’s Guide!

This article was published as a part of the Data Science Blogathon

Introduction

Data Science is a most emerging field with numerous job opportunities. We all must have been heard about the topmost Data Science skills. To start with, the easiest, as well as an essential skill that every data science aspirant should acquire, is SQL.

Nowadays, most companies are going towards being data-driven. These data are stored in a database and are managed and processed through a Database Management system. DBMS makes our work so easy and organized. Hence, it is essential to integrate the most popular programming language with the incredible DBMS tool.

SQL is the most widely used programming language while working with databases and supported by various relational database systems, like MySQL, SQL Server, and Oracle. However, the SQL standard has some features that are implemented differently in different database systems. Thus, SQL becomes one of the most important concepts to be learned in this field of Data Science.

data science skills

Image source: KDnuggets

Need of SQL in Data Science

SQL (Structured Query Language) is used for performing various operations on the data stored in the databases like updating records, deleting records, creating and modifying tables, views, etc. SQL is also the standard for the current big data platforms that use SQL as their key API for their relational databases.

Data Science is the all-around study of data. To work with data, we need to extract it from the database. This is where SQL comes into the picture. Relational Database Management is a crucial part of Data Science. A Data Scientist can control, define, manipulate, create, and query the database using SQL commands.

Many modern industries have equipped their products data management with NoSQL technology but, SQL remains the ideal choice for many business intelligence tools and in-office operations.

Many of the Database platforms are modeled after SQL. This is why it has become a standard for many database systems. Modern big data systems like Hadoop, Spark also make use of SQL only for maintaining the relational database systems and processing structured data.

We can say that:

1. A Data Scientist needs SQL to handle structured data. As the structured data is stored in relational databases. Therefore, to query these databases, a data scientist must have a good knowledge of SQL commands.

2.Big Data Platforms like Hadoop and Spark provide an extension for querying using SQL commands for manipulating.

3.SQL is the standard tool to experiment with data through the creation of test environments.

4. To perform analytics operations with the data that is stored in relational databases like Oracle, Microsoft SQL, MySQL, we need SQL.

5. SQL is also an essential tool for data wrangling and preparation. Therefore, while dealing with various Big Data tools, we make use of SQL.

Key elements of SQL for Data Science

Following are the key aspects of SQL which are most useful for Data Science. Every aspiring Data Scientists must know these necessary SQL skills and features.

sql for data science

Image source: By me

Introduction to SQL with Python

As we all know that SQL is the most used Database Management Tool and Python is the most popular Data Science Language for its flexibility and wide range of libraries. There are various ways to use SQL with Python. Python provides multiple libraries that are developed and can utilize for this purpose. SQLite, PostgreSQL, and MySQL are examples of these libraries.

Why use SQL with Python

There are many use cases for when Data Scientists want to connect Python to SQL. Data Scientists need to connect a SQL database so that data coming from the web application can be stored. It also helps to communicate between different data sources.

There is no need to switch between different programming languages for data management. It makes Data scientists’ work more convenient. They will be able to use your Python skills to manipulate data stored in a SQL database. They don’t need a CSV file.

MySQL with Python

MySQL is a server-based database management system. One MySQL server can have multiple databases. A MySQL database consist two-step process for creating a database:

1. Make a connection to a MySQL server.

2. Execute separate queries to create the database and process data.

Let’s get started with MySQL with python

First, we will create a connection between the MySQL server and MySQL DB. For this, we will define a function that will establish a connection to the MySQL database server and will return the connection object:

!pip install mysql-connector-python
import mysql.connector
from mysql.connector import Error
def create_connection(host_name, user_name, user_password):
     connection = None
      try:
          connection = mysql.connector.connect(
                 host=host_name,
                 user=user_name,
                 passwd=user_password
            )
            print("Connection to MySQL DB successful")
      except Error as e:
            print(f"The error '{e}' occurred")
       return connection
connection = create_connection("localhost", "root", "")

In the above code, we have defined a function create_connection() that accepts following three parameters:

1. host_name

2. user_name

3. user_password

The mysql.connector is a Python SQL module that contains a method .connect() that is used to connect to a MySQL database server. When the connection is established, the connection object created will be returned to the calling function.

So far the connection is established successfully, now let’s create a database.

#we have created a function to create database that contions two parameters
#connection and query
def create_database(connection, query): #now we are creating an object cursor to execute SQL queries cursor = connection.cursor() try: #query to be executed will be passed in cursor.execute() in string form cursor.execute(query) print("Database created successfully") except Error as e: print(f"The error '{e}' occurred")
#now we are creating a database named example_app
create_database_query = "CREATE DATABASE example_app" create_database(connection, create_database_query)
#now will create database example_app on database server
#and also cretae connection between database and server
def create_connection(host_name, user_name, user_password, db_name): connection = None try: connection = mysql.connector.connect( host=host_name, user=user_name, passwd=user_password, database=db_name ) print("Connection to MySQL DB successful") except Error as e: print(f"The error '{e}' occurred") return connection
#calling the create_connection() and connects to the example_app database. connection = create_connection("localhost", "root", "", "example_app")

SQLite

SQLite is probably the most uncomplicated database we can connect to a Python application since it’s a built-in module we don’t need to install any external Python SQL modules. By default, Python installation contains a Python SQL library named sqlite3 that can be used to interact with an SQLite database.

SQLite is a serverless database. It reads and writes data to a file. That means we don’t even need to install and run an SQLite server to perform database operations like MySQL and PostgreSQL!

Let’s use sqlite3 to connect to an SQLite database in Python:

import sqlite3 from sqlite3 import Error
def create_connection(path): connection = None try: connection = sqlite3.connect(path) print("Connection to SQLite DB successful")
except Error as e: print(f"The error '{e}' occurred") return connection

In the above code, we have imported sqlite3 and the module’s Error class. Then define a function called .create_connection() that will accept the path to the SQLite database. Then .connect() from the sqlite3 module will take the SQLite database path as a parameter. If the database exists at the path specified in .connect, then a connection to the database will be established. Otherwise, a new database is created at the specified path, and then a connection is established.

sqlite3.connect(path) will return a connection object, which was also returned by create_connection(). This connection object will be used to execute SQL queries on an SQLite database. The following line of code will create a connection to the SQLite database:

connection = create_connection("E:\example_app.sqlite")

Once the connection is established we can see the database file is created in the root directory and if we want, we can also change the location of the file.

In this article, we discuss how SQL is essential for Data Science and also how we can work with SQL using python. Thanks for reading. Do let me know your comments and feedback in the comment section.

The media shown in this article are not owned by Analytics Vidhya and are used at the Author’s discretion.

 

You can also read this article on our Mobile APP Get it on Google Play