K2 Data Science and Engineering

Python and Databases…

As most software applications need to interact with data in some form, programming languages like Python provide tools for storing and accessing these data sources.

What is SQL?

SQL stands for Structured Query Language and is a widely used programming language for maintaining and managing relational databases. There are many popular SQL bases databases such as MySQL, PostgreSQL, SQLite, and SQL Server. All of these databases are compliant with the SQL standards but with varying degrees of compliance.

MySQL -

Being open source since its inception in 1995, MySQL quickly became a market leader among SQL solutions. MySQL is also a part of the Oracle ecosystem. While its core functionality is completely free, there are some paid add-ons as well. Currently, MySQL is used by all major tech firms, including Google, LinkedIn, Uber, Netflix, Twitter, and others.

MySQL and Python -

We can use MySQL as a database for our projects in python. MySQL Connector/Python enables Python programs to access MySQL databases, using an API that is compliant with the Python Database API Specification v2.0 (PEP 249). Connector/Python runs on any platform where Python is installed. Python comes preinstalled on most Unix and Unix-like systems, such as Linux, macOS, and FreeBSD. On Microsoft Windows, a Python installer is available at the Python Download website. If necessary, we need to download and install Python for Windows before attempting to install Connector/Python. Now while installing the connector we need to run the following command in the command prompt.

install mysql-connector-python.

Now we have downloaded and installed MySQL driver

Connecting to MySQL using Connector/Python -

The connect() constructor creates a connection to the MySQL server and returns a MySQLConnection object.

The following example shows how to connect to the MySQL server:

import mysql.connector

mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”
)

print(mydb)

Now we can start querying using SQL statements

For Creating, Deleting and Updating we use the CREATE, DELETE and UPDATE statements respectively. You can get more information and examples about these statements from the links referenced below.

Some Information about NOSQL and types of NOSQL -

NoSQL is an umbrella term to describe any alternative system to traditional SQL databases.

NoSQL databases are all quite different from SQL databases. They all use a data model that has a different structure than the traditional row-and-column table model used with relational database management systems (RDBMSs).

There are 4 major types of NOSQL databases

1. Document Databases
2. Key-Value Stores
3. Column-oriented Databases
4. Graph Databases

Here we will focus on Graph Databases -

A graph database focuses on the relationship between data elements. Each element is stored as a node (such as a person in a social media graph). The connections between elements are called links or relationships. In a graph database, connections are first-class elements of the database, stored directly. In relational databases, links are implied, using data to express the relationships.

A graph database is optimized to capture and search the connections between data elements, overcoming the overhead associated with JOINing multiple tables in SQL.

Very few real-world business systems can survive solely on graph queries. As a result graph databases are usually run alongside other more traditional databases.

Use cases include fraud detection, social networks, and knowledge graphs.

For further information on both MySQL and NOSQL please refer to the given links

MySQL :: MySQL Connector/Python Developer Guide :: 5.1 Connecting to MySQL Using Connector/Python

Python MySQL

Types of NoSQL Databases