An Introduction to Data Persistance with Python

Python in Database Management

Image Reference : https://wallpaperaccess.com/database

An Introduction to Python and Databases

Almost every other computer application from a web based application to a simple desktop application stores and retrieves information from some persistent storage. This storage could be a local hard drive or a remote repository or anything. The reason why most recent computer applications are doing what they can is because they have the capability to access and modify data from such storage locations. Such a storage location could be simply termed as a “Database”.

Now, the type of data that is stored in the application could be in a structured form like tables in which the information in each column has some relation to the information in the other.

Some types of structured databases (SQL) are MySQL, Oracle, SQLite etc. Similarly, we can also have data stored in semi-structured form. Such types of databases are referred to as NoSQL (Not just SQL). Some types of NoSQL are MongoDB, Cassandra etc.

Python in Database Management

Well, we do have explicit ways to mess around with databases. We can learn SQL (Structured Query Language) or NoSQL (Not just SQL) to manage databases. Well, well, well, we also need different GUIs/ applications to access such databases. As usual, “Python” to the rescue. We can access databases and play with them directly using Python. All we need is to establish a connection between the Database server and Python. Then just execute the database queries as easy as eating a piece of cake from within Python.

In this introductory article, we try to understand how to establish a connection between a database server and Python. We are choosing MySQL, a form of structured database. The only requirement for us to proceed further is have MySQL workbench installed in our systems. MySQL workbench need to be kept open while working with MySQL through Python. This is essential as the MySQL server needs to be active whilst Python is trying to establish a connection.

All the code in this article with the outputs are are available in my Github repo along with the support files used.

Establishing Connection between Python and MySQL

Python uses the “connector” method from “mysql” module to establish the connection.

In addition, we need the MySQL host name, MySQL user name and MySQL passoword to proceed.

This is available under Database — Connect Database. Password is the MySQL password set during installation.

MySQL Connection Options

MySQL Python Connection Establishment

What does the code say …

Line number 1 imports the necessary modules.

Line number 6 establishes the connection between MySQL and Python. The is_connection() method returns True if the connection gets established successfully.

Just like file operations, it is always good to close the DB connection once done using the close()

Simple, right?

Creating a Database

Let us try creating a MySQL database

MySQL Database Creation (Using Python)

What does the code say …

As usual, we start with establishing the connection with MySQL.

Now we step a bit ahead, we create a cursor. Cursor is basically created to capture the cursor’s position within the database as an when new queries get executed. In other terms, cursor is a pointer to the query.

Query is the actual SQL query used to achieve the action in MySQL

The cursor.execute() executes the query. Always remember, the queries should be in STRING format

Going forward, we just need to know what are the different queries.

Simple, right?

Obtaining a list of existing Databases

Now that we have created a new database, we should be able to see this in the list of databases available.

Obtaining MySQL Database List

Creating a Table within a Database

We are currently dealing with structured databases. The information in such databases are stored in forms of tables. One database can have multiple tables.

Creating a Table in MySQL Database using Python

Cross Checking the updated Table using MySQL

Cross Checking with MySQL

As we can see, an empty table has been created in the Employee Database

Inserting Values into Existing Tables

Inserting Values into Tables

Cross Checking the updated Table using MySQL again

Cross Checking Update with MySQL

Entering multiple lines of information to a Table

Entering Multiple Values into a Table

Cross Checking the updated Table using MySQL again

Cross Checking Update with MySQL

Entering multiple rows of data in one go …

This can be achieved very easily using Pandas, but through conventional methods, if all the information is stored in a .csv file, we could enter a whole lot of information in one go.

If the data from external file is not in .csv format, we can convert it to .csv format

Thanks to ineuron.ai for this bit of code. Please ensure, there is a database “GlassData” exists before executing the code.

Cross Checking the updated Table using MySQL again

Cross Checking wiy MySQL

So, welcome to the world of Structured Databases with Python. Congratulations !!!

All the code in this article are available in Github.