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.
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:
- Make a connection to a MySQL server.
- 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 :
connectionis the connection object to the database server that you want to interact with.queryis 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.
