
How to dynamically load data into a database
A data analyst or data scientist get lots of opportunity to work on Python and SQL. Python with its enormous library support provides data analyst with a magic wand to manipulate and visualize data. Likewise SQL helps us store millions of records and establish relationships with other tables for optimal data management. There are scenarios that require a Python program to directly load data into a back-end database. For example, if a data frame is dynamically modified periodically or at random instances, uploading the csv file for each data update into the database is not feasible. In this post, we will go through how to integrate a Python program with MySQL database.
First, I created a small dataset in Python. It has two columns, first column stores index and second column stores time stamp.
from datetime import datetime # Current date time in local system
col_1 = col_1+1
col_2 = datetime.now()
print('ID:',col_1)
print('Timestamp:',col_2)

Creating a table in MySQL
Meanwhile we can create a table in MySQL db which will be used to store the data that we generated in the first step. It is also possible to create a table in MySQL from python itself. As this is a one time process, it will be easier to create the table in the database directly.
#Create table called details.
create table `details`(
Id int(20) not null,
Time datetime null
);

Connecting Python to MySQL
First we need to import the mysql.connector package, if it is not available then we need to install it using below code
pip install mysql.connector
#or
!pip install mysql.connector
We need to import the error code from mysql.connector. This will be helpful for identifying the issues, if any, while inserting into the DB.Some of the errors that one might encounter will be primary key null error and duplicate entries (if the table is set with a primary key).
We need to specify our MySQL credentials in the code. We first open a connection to the MySQL server and store the variable connection object in the variable cnct. We then create a new cursor, using the connection’s cursor() method.
We will store the insert statement in the variable _sqlquery and the data in variable info is used to replace the %s markers in the query .
The execute statement will execute the query which is stored in the variable _sqlquery
Once the above code is executed, let’s check the database to see if the data is loaded dynamically.

Wonderful!! The data has been loaded.
Now lets see how the data is loaded into the database, if I ran the code 5 times.

Each time you run the code or job , the database will be dynamically loaded and refreshed.
Conclusion
Similarly we can perform any operation like creating database or tables, insert, update, delete and select operations from the Python interface itself and it will be reflected in the MySQL database.
This integration will prove useful for scenarios that involve ever changing data. For example, data that is scraped often from website or fetching live data from Twitter or Facebook feeds.
Jupyter notebook is available on Github.
Reference :
[1] https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html