Python Insert Into MySQL Database Table

This article demonstrates how to execute INSERT Query from python to add a new row to the MySQL database table. I’ve written multiple examples in Python that shows how to INSERT rows into MySQL table.

Goals of this lesson:

• Insert single and multiple rows into MySQL table.
• Insert Integer, string, float, double,  and datetime variable into a MySQL table.
• Use a parameterized query to insert Python variables and dynamic data into a table.

Prerequisite

Before moving further, Please make sure you have the following in place: –

• Username and password that you need to connect MySQL
• MySQL database table name in which you want to insert data

I have created table “python_users” in MySQL server to insert records in it. See its column structure in the following image.

Python example to insert single row/record into MySQL table

To perform a SQL INSERT query from Python, you just need to follow these simple steps:

• Install MySQL Connector Python using pip.
• First, Establish a  MySQL database connection in Python.
• Then, Define the SQL INSERT Query  (here you need to know the table’s column details).
• Execute the INSERT query using the cursor.execute()and get a number of rows affected.
• After successful execution of a query, Don’t forget to commit your changes to the database.
• Close the MySQL database connection.
• Most important, Catch SQL exceptions if any.
• At last, verify the result by selecting data from MySQL table.

Let’ s see the program now

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
try:
   connection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')

   sql_insert_query = """ INSERT INTO `python_users`
                          (`id`, `name`, `birth_date`, `age`) VALUES (1,'Scott','2018-04-11', 26)"""

   cursor = connection.cursor()
   result  = cursor.execute(sql_insert_query)
   connection.commit()
   print ("Record inserted successfully into python_users table")

except mysql.connector.Error as error :
    connection.rollback() #rollback if any exception occured
    print("Failed inserting record into python_users table {}".format(error))

finally:
    #closing database connection.
    if(connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

You will get below output after executing the above program: –

Record inserted successfully into python_users table
MySQL connection is closed

Understand Python MySQL insert program in detail

1. At first, import mysql.connector  imports MySQL Connector Python module in your program so you can use this modules API to connect MySQL database.
2. Next, We used the mysql.connector.connect()  function to connect the MySQL Database from Python
3. Next, we prepared SQL INSERT query to insert a row into a table. in the insert query, we mentioned column names and their values to insert in a table.
4. Next, Using a connection.cursor  method we created a cursor object, This method creates a new MySQLCursor object.
5. We are using the cursor.execute() method of a cursor object to execute the operation stored in the INSERT query. Also, this method returns the number of rows affected.
6. At last, we are closing cursor and connection object inside the finally block once we complete insert operation. It is always good practice to close connection to avoid database issues.

Refer to How to fetch rows from MySQL table using a select query in Python to check the data that you just inserted.

Using Python variables in a MySQL INSERT query

You can read more on What is a Parameterized Query and its performance benefits. Let see how to do this with an example program.

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
from datetime import datetime

def insertPythonVaribleInTable(userId, userName, joinDate, age):
    try:
        connection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')

        cursor = connection.cursor(prepared=True)

        sql_insert_query = """ INSERT INTO `python_users`
                          (`id`, `name`, `birth_date`, `age`) VALUES (%s,%s,%s,%s)"""

        insert_tuple = (userId, userName, joinDate, age)

        result  = cursor.execute(sql_insert_query, insert_tuple)
        connection.commit()
        print ("Record inserted successfully into python_users table")

    except mysql.connector.Error as error :
        connection.rollback()
        print("Failed to insert into MySQL table {}".format(error))

    finally:
        #closing database connection.
        if(connection.is_connected()):
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

insertPythonVaribleInTable(11, "Ault", "2018-07-14", 34)
insertPythonVaribleInTable(12, "Mackee", "2017-03-28", 30)

Output:

Record inserted successfully into python_users table
MySQL connection is closed
Record inserted successfully into python_users table
MySQL connection is closed

Python Insert multiple rows into the MySQL table using the cursor’s executemany()

What if you want to insert multiple rows into the table in single insert query from python application. You can insert multiple rows into MySQL table. You can use the cursor’s executemany() function to insert multiple records into a table.

Note:- in the previous example we have used execute () method of cursor object to insert a single record.

The syntax of the executemany() method.

cursor.executemany(operation, seq_of_params)

• This method prepares a database operation (i.e., query or command) and executes it against all parameter sequences in the sequence “seq_of_params” argument.
• You need to include lists of tuples in “seq_of_params” argument along with insert query.
• Each tuple inside the list contains a single row that you want to insert. So you can add as much as rows in the list and pass a list to cursor.executemany() function along with insert query.

Here we are using the prepared statement (parameterized query) to insert multiple rows into a table.

why use a prepared statement?

• Because to repeatedly execute the same statement with different data we use the prepared statement.
• Each tuple is enclosed within parentheses and separated by commas. For example, to insert multiple rows in a python_user table, we can use the following SQL Query:

INSERT INTO python_users (id, name, birth_date, age) VALUES (%s,%s,%s,%s)

And in “seq_of_params” we are passing below List.

records_to_insert = [ (2,'Jon','2018-01-11', 26) ,
                         (3,'Jane','2017-12-11', 27),
                         (4,'Bill','2018-03-23', 26) ]

Let’s demonstrate this with an example.

Python example to INSERT multiple rows into a MySQL table

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
try:
   connection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')

   records_to_insert = [ (2,'Jon','2018-01-11', 26) ,
                         (3,'Jane','2017-12-11', 27),
                         (4,'Bill','2018-03-23', 26) ]

   sql_insert_query = """ INSERT INTO python_users (id, name, birth_date, age) 
                       VALUES (%s,%s,%s,%s) """

   cursor = connection.cursor(prepared=True)
   #used executemany to insert 3 rows
   result  = cursor.executemany(sql_insert_query, records_to_insert)
   connection.commit()
   print (cursor.rowcount, "Record inserted successfully into python_users table")

except mysql.connector.Error as error :
    print("Failed inserting record into python_users table {}".format(error))

finally:
    #closing database connection.
    if(connection.is_connected()):
        cursor.close()
        connection.close()
        print("connection is closed")

You should get the following output

3 Record inserted successfully into python_users table
connection is closed

Let’s understand the above program in detail: –.

1. After a successful database connection, we prepared a list of rows to insert into the table.
2. SQL INSERT statement contains the parameterized query which uses the placeholder (%s) for each column value.
3. connection.cursor(prepared=True)  allows the cursor to execute the prepared statement.
4. Next, Using cursor.executemany(sql_insert_query, records_to_insert)we are inserting multiple rows (from a List) into the table.
5. Using cursor.rowcount method we can find the number of records inserted. Finally, we are committing our changes to the database.

You can refer to our article on how to fetch data from MySQL table to verify your result.

Python Insert timestamp and DateTime into a MySQL table

For example, you have a date column in a MySQL table, and you want to insert a datetime.datetime() object into this column. Then how to prepare an insert query to add datetime into a table column. Let’s see this with an example.

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
from datetime import datetime
try:
   connection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')

   cursor = connection.cursor(prepared=True)

   sql_insert_date_query = """ INSERT INTO `python_users`
                          (`id`, `name`, `birth_date`, `age`) VALUES (%s,%s,%s,%s)"""

   current_Date = datetime.now()
   formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')
   insert_tuple = (6, 'James', current_Date, 1)

   result  = cursor.execute(sql_insert_date_query,insert_tuple)
   connection.commit()
   print ("Date Record inserted successfully into python_users table")

except mysql.connector.Error as error :
    print("Failed inserting date object into MySQL table {}".format(error))

finally:
    #closing database connection.
    if(connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

You can get output like this after execution of the above code.

Date Record inserted successfully into python_users table
MySQL connection is closed

Next Steps:

To practice what you learned in this article, I have created a Python Database programming Quiz and Exercise Project.

That’s it. Folks Let me know your comments and questions in the section below.