Inserting Records Into a Database From Python

One of the basic roles of a data analyst/scientist is to extract patterns and insights from raw data. Since much of the world’s government and corporate data is organized in relational databases, it makes sense that data analysts/scientists need to know how to work with these database structures. Apart from that, what is more useful is to work with Pandas dataframes in SQL environment.

Most of the times, the data analyst/scientist works with languages like Python and R which are basically useful from statistical analysis, visualization and interpretation. So, in this article, we will learn how to build a bridge between SQL and Python(pandas).

How to establish a database connection in Python and insert records into a Database

We can send and receive data to a MySQL database by establishing a connection between Python and MySQL. Here, we will use pymysql for database connectivity; although there are other ways for database connectivity as well.

Here are the broad steps we’ll need to work through to get pymysql connected, insert our data, and then extract the data from MySQL:

Library for MySQL connection : pymysql

Library for Oracle connection : cx_Oracle

Similarly, you can check for other Database softwares.

If it is not available, then you can install it using the pip command as shown :

Step 1: Import the pymysql module.

Step 2 : Create connection to the MySQL database using pymysql‘s connect() function with the parameters host, user, database name, and password.

Step 3: Create a cursor using the cursor() function. This will allow us to execute the SQL query once we’ve written it.

Step 4: Execute the required SQL query, commit the changes using the commit() function, and check the inserted records. Note that we can create a variable called sql, assign our query’s syntax to it, and then pass sql and the specific data we want to insert as arguments to cursor.execute().

Then, we’ll commit these changes using commit().

Step 5: Now that we’re done, we should close the database connection using close() method.

##Step 1
import pymysql
## Step 2
try:
    # Connect to the database
    connection = pymysql.connect(host='localhost',
                             user='username',
                             password='*****',
                             db='employee')
## Step3
    cursor=connection.cursor()
    # Create a new record
    sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (1009,'Morgan',1,4000,'HR','Mumbai'))
    # connection is not autocommit by default. So we must commit to save our changes.
    connection.commit()
## Step 4
    # Execute query
    sql = "SELECT * FROM `employee`"
    cursor.execute(sql)
    # Fetch all the records
    result = cursor.fetchall()
    for i in result:
        print(i)
except Error as e:
    print(e)
## Step 5
finally:
    # close the database connection using close() method.

    
    connection.close()