How Python With Mysql, MongoDB Atlas, and Sqlite3 Works

What Is a Database?

Defining the database

In a computer system, a database is a logically organised collection of structured data.

A database management system is generally in charge of a database (DBMS).

A database system—often abbreviated as the simply database—refers to the data, the DBMS, and the applications that go with them.

The most common forms of databases in use today frequently describe data in rows and columns in a series of tables to facilitate processing and data querying.

Then, it will be simple to access, manage, update, control, and organise data.

For creating and querying data, most databases employ structured query language (SQL).

What is Structured Query Language (SQL)?

SQL is a computer language that almost all relational databases use to query, change, and define data as well as govern access. SQL was initially created in the 1970s at IBM with Oracle as a key contribution, leading to the adoption of the SQL ANSI standard. Since then, SQL has spawned several extensions from businesses such as IBM, Oracle, and Microsoft. Despite the fact that SQL is still commonly used today, other programming languages are emerging.

Note: You must have installed the SQL workbench in your system

To perform the connectivity between SQL and python we use Jupyter

In your Jupyter install MySQL–connector-python

First Import the MySQL connector as whatever you prefer as allies. Then make the connection between your workbench and python using the username and password which you entered while installing the SQL workbench in your system.

Here, the host you can represent as ‘localhost’, if you’re using any cloud or any server then provide the URL for it.

After the connection, we will create the cursor to execute the SQL queries. cursor.execute(‘SQL query’) for execution and to retrieve the data perform cursor.fetchall() rest of the results you will be got.

Let’s create the database and table too.

Mine create table query is as follow:

“””create table school.student(studentid INT(10) ,firstname VARCHAR(30) ,lastname VARCHAR(30) ,regid INT(10) ,classname VARCHAR(30))”””

Let’s insert the record now,

After any mutation, we have to commit to fulfilling the action. Thus we have finished the connection of python with SQL. You can check these records in SQL workbench.

Now let’s see how we can establish the connection between MongoDB and python.

Information about MongoDB and Prerequisites

MongoDB is a NoSQL database management system that is open source. Traditional relational databases replace by NoSQL databases. Working with massive quantities of dispersed data is a breeze with NoSQL databases. MongoDB is a database management system that can store and retrieve document-oriented data.

MongoDB can handle a wide range of data types. It’s one of several nonrelational database systems that developed in the mid-2000s under the NoSQL banner, usually for use in big data applications and other processing operations requiring data that doesn’t fit well in a conventional relational paradigm. Like relational databases, MongoDB stores data as collections and documents rather than tables and rows.

What is MongoDB Atlas?

In addition to managing your MongoDB Atlas installation on AWS, Azure, or Google Cloud Platform (GCP), MongoDB Atlas offers full monitoring and recovery for all its installations. The best approach to deploy, run, and grow MongoDB on the cloud is with MongoDB Atlas.

Visit the following link. And make your account at atlas.

Reference
https://www.mongodb.com/basics/mongodb-atlas-tutorial#:~:text=MongoDB%20Atlas%20is%20a%20fully,scale%20MongoDB%20in%20the%20cloud

After the above step, create the new cluster for the new project and select the environment for python above 3.6-8.

pip install pymongo or !pip install pymongo[srv] for connectivity, and add you’re username and password which you enter while creating the cluster.

If you’re having an error regarding the server the add following line as the second parameter in pymongo.Mongoclient

Add : tls= True, tlsAllowInvalidCertificates=True

Create the database name ‘rushi’ and create a new collection. Here we use a dictionary to add a new record to our collection.

Here I am not all showing the action in MongoDB, this blog concludes the connectivity between different-different database service providers.

How can we use the Database locally using SQLite3 pip

What is SQLite?

SQLite is a relational database management system implemented as a software library. In terms of setup, database management, and necessary resources, the lite in SQLite stands for “lightweight.”

Self-contained, serverless, zero-configuration, and transactional are some of SQLite’s notable characteristics.

SQLite is quite similar to SQL, both work the same but SQLite holds the data locally. It creates the .db file locally and holds all structural data within it.

You also have to create a cursor to execute SQL queries as I did before.

Now you know how to connect any database service and how to use the database locally. For more visit their documentation and comprehension.

Conclusion

Using and manipulating databases is one of the most prominent skills that every python developer should grab it well because it is a high-demand skill in the market.

So, if you want to be a python developer, engineer, or data scientist, it is very important to be familiar with dealing with python and databases of all kinds.

In this blog, you have learned how to use different types of databases with the Python programming language, from beginning to professionalism.

You will master the skills of integrating python with SQL and SQLite, and also NoSQL through MongoDB.

References

https://pymongo.readthedocs.io/en/stable/
https://www.sqlite.org/docs.html
https://dev.mysql.com/doc/
https://www.mongodb.com/docs/