Analytics Vidhya
Published in

Analytics Vidhya

Database, SQL and NoSQL

Database is a collection of information that is organized so that it can be easily accessed, managed and updated. Databases typically contain aggregations of data records or files, containing information. A database is an abstraction over an operating system’s file system that makes it easier for developers to build applications that create, read, update and delete persistent data.

Databases make structured storage reliable and fast. They also give you a framework for how the data should be saved and retrieved instead of having to figure out what to do with the data every time you build a new application.

Relational databases

It store data in a series of tables. Interconnections between the tables are known as foreign key. Each table has at least one data category in a column, and each row has a certain data instance for the categories which are defined in the columns.

Relational databases

The Structured Query Language(SQL) is the standard user and application program interface for a relational database. SQLite, a database included with Python, creates a single file for all data per database. Other databases such as PostgreSQL, MySQL, Oracle and Microsoft SQL Server have more advanced feature that are useful for web application data storage.

The Python standard for database interfaces is the Python DB-API. The DB-API provides a minimal standard for working with databases using Python structures and syntax wherever possible. This API includes the following −

  • Importing the API module.
  • Acquiring a connection with the database.
  • Issuing SQL statements and stored procedures.
  • Closing the connection

SQL databases

SQL stands for Structured Query language. In SQL we can generate queries to retrieve or update the databases. SQL is the standard language for Relational Database System. It allows you to make relationship between tables, can be one to one, one to many, many to one, many to many. It is an example of relational database management system.

All the Relational Database Management Systems(RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

Using Python SQL Libraries to connect to a Database

To interact with any database through a Python SQL Library, we have to connect to the database. We’ll need to install a Python SQL driver for MySQL in order to interact with a MySQL database from within a Python application.

MySQL database has a two-step process for database creation:

  1. Make a connection to a MySQL server.
  2. Execute a separate query to create the database.
pip install mysql-connector-pythonimport mysql.connectormydb = mysql.connector.connect(host='localhost',
user='root',
user_password = '1234')
print(mydb.connection_id) #it'll give connection_id,if got connected

The database is not yet created. To do this, We’ll define another function create_database() that accepts two parameters :

  1. connection is the connection object to the database server that you want to interact with.
  2. query is the query that creates the database.

NoSQL Databases

NoSQL expands to “Not Only SQL”. It lends us a way to store and retrieve data that we can model in forms other than relational (tables). NoSQL databases largely find use in applications involving large data and real-time uses. The reason we call them “Not Only SQL” is because they may support query languages that are SQL-like. We can use NoSQL to store data in forms like key-value, document, columnar, and graph. When working with large sets of distributed data, we use NoSQL.

Examples- MongoDB, redis, Apache HBase , cassandra, etc.

We have four different types of data we can model with NoSQL Database in Python-

a. Document Databases

In a document database, each key pairs with a document. Documents are the main concept in document databases. The database stores and retrieves documents, which can be XML, JSON, BSON, and so on. These documents are self-describing, hierarchical tree data structures which can consist of maps, collections, and scalar values. A document is a complex data structure and can hold any of the following- key-value pairs, key-array pairs, nested documents.

b. Graph Stores

A graph store holds knowledge about data networks. One such network is social connections. In a graph store, a node is like a record in a relational database, and an edge is like a connection between nodes. Graph databases are very well suited to problem spaces where we have connected data, such as social networks, spatial data, routing information for goods and money, recommendation engines. We use these for applications like CRM and reservation systems. Some examples are Neo4J and Giraph.

c. Key-Value Stores

A key-value store holds key-value pairs in its structure. Key-value pair data stores are based on hash map data structures. Key-value stores are the simplest NoSQL data stores to use from an API perspective. We use these in applications like session management and caching in web applications. Some examples include Riak, Redis, and Berkeley DB.

d. Wide-Column Stores

When we want to store columns together working with queries over large datasets, we can use wide-column stores. Column-family databases store data in column families as rows that have many columns associated with a row key. We also find these in SQL databases and they query large volumes faster. Some examples are Cassandra, HBase and Google BigTable.

--

--

--

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Recommended from Medium

Radix Sort in Java

Some Computing Experiences Over Many Years

Let’s enjoy Plasm Testnet V3 ① Operator Trading

The Data Analytics Dilemma: Specialist or End-to-End?

How Useful are Technical Interviews?

EmiSwap’s Second Airdrop: Swap or Provide Liquidity to Win!

Welcome to Sitespeed.io

Learn how to dockerize a simple application with this quick guide

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Abu Qais

Abu Qais

The price of “anything” is the amount of “time”, U xchange for it. Education | Technology | Data Science | Statistics | History

More from Medium

Basic PostgreSQL Commands

COALESCE function in SQL

Master SQL Queries (in 5 minutes)

INTRODUCTION TO AIRFLOW AND A STEP-BY-STEP INSTALLATION GUIDE FOR WINDOWS