PYnative

Python Programming

  • Tutorials
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks

Python Insert Into MySQL Table

Last updated on February 22, 2021

TweetF  sharein  shareP  Pin

This article demonstrates how to execute INSERT Query from Python to add a new row into the MySQL table.

In this lesson, you’ll learn the following Python MySQL insert operations using a ‘MySQL Connector’ module.

  • Insert single and multiple rows into the database table.
  • Use a parameterized query to insert a Python variable value (Integer, string, float, double, and DateTime) into a database table.

Further Reading:

  • Solve Python MySQL Exercise
  • Read Python MySQL Tutorial (Complete Guide)

Table of contents

  • Prerequisite
  • Insert a Single Row into MySQL table from Python
  • Use Python Variables in a MySQL Insert Query
  • Insert multiple rows into MySQL table using the cursor’s executemany()
  • Insert timestamp and DateTime into a MySQL table using Python
  • Next Steps:

Prerequisite

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

  • Username and password to connect MySQL
  • MySQL table name in which you want to insert data.

I have created a table ‘Laptop’ in the MySQL server to insert records in it. See its column structure in the image.

Empty MySQL Laptop table
Empty MySQL Laptop table

If a table is not present in your MySQL server, you can refer to our article to create a MySQL table from Python.

You can also download a SQL query file, which contains SQL queries for table creation and data so that you can use this table for your INSERT operations.

Insert a Single Row into MySQL table from Python

How to Insert Into MySQL table from Python

  1. Connect to MySQL from Python

    Refer to Python MySQL database connection to connect to MySQL database from Python using MySQL Connector module

  2. Define a SQL Insert query

    Next, prepare a SQL INSERT query to insert a row into a table. in the insert query, we mention column names and their values to insert in a table.
    For example, INSERT INTO mysql_table (column1, column2, …) VALUES (value1, value2, …);

  3. Get Cursor Object from Connection

    Next, use a connection.cursor() method to create a cursor object. This method creates a new MySQLCursor object.

  4. Execute the insert query using execute() method

    Execute the insert query using the cursor.execute() method. This method executes the operation stored in the Insert query.

  5. Commit your changes

    After the successful execution of a query make changes persistent into a database using the commit() of a connection class.

  6. Get the number of rows affected

    After a successful insert operation, use a cursor.rowcount method to get the number of rows affected. The count depends on how many rows you are Inserting.

  7. Verify result using the SQL SELECT query

    Execute a MySQL select query from Python to see the new changes.

  8. Close the cursor object and database connection object

    use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

Python insert into MySQL table
python insert into MySQL table

Let’ s see the program now

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) 
                           VALUES 
                           (15, 'Lenovo ThinkPad P71', 6459, '2019-08-14') """

    cursor = connection.cursor()
    cursor.execute(mySql_insert_query)
    connection.commit()
    print(cursor.rowcount, "Record inserted successfully into Laptop table")
    cursor.close()

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

finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

Output

Record inserted successfully into Laptop table MySQL connection is closed
MySQL table after inserting the first row from Python
MySQL table after inserting the first row from Python

Use Python Variables in a MySQL Insert Query

Sometimes you need to insert a Python variable value into a table’s column. For example, in the user signup form user enter his/her details. You can take those values in Python variables and insert them into a table.

  • We can insert Python variables into the table using the prepared statement and parameterized query.
  • Using a parameterized query, we can pass Python variables as a query parameter in which placeholders (%s) used for parameters.

Example

import mysql.connector

def insert_varibles_into_table(id, name, price, purchase_date):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='Electronics',
                                             user='pynative',
                                             password='pynative@#29')
        cursor = connection.cursor()
        mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) 
                                VALUES (%s, %s, %s, %s) """

        record = (id, name, price, purchase_date)
        cursor.execute(mySql_insert_query, record)
        connection.commit()
        print("Record inserted successfully into Laptop table")

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

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


insert_varibles_into_table(2, 'Area 51M', 6999, '2019-04-14')
insert_varibles_into_table(3, 'MacBook Pro', 2499, '2019-06-20')

Output:

Record inserted successfully into Laptop table MySQL 
connection is closed 

Record inserted successfully into Laptop table MySQL connection is closed
MySQL Laptop table after inserting Python variables
MySQL Laptop table after inserting Python variables

Refer to fetch rows from MySQL table in Python to check the data you inserted.

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

In the previous example, we have used execute() method of cursor object to insert a single record.

What if you want to insert multiple rows into a table in a single insert query from the Python application. Use the cursor’s executemany() function to insert multiple records into a table.

Syntax of the executemany() method.

cursor.executemany(operation, seq_of_params)

This method executes Insert operation against all parameter sequences in the sequence seq_of_params argument.

You need to include lists of tuples in the seq_of_params argument along with the insert query.

Each tuple inside the list contains a single row that you want to insert. So you can add as many rows in the list and pass a list to a cursor.executemany() function along with the insert query.

Note: Each tuple is enclosed within parentheses and separated by commas. For example, to insert multiple rows in a laptop table, we can use the following SQL Query:

INSERT INTO Laptop (Id, Name, Price, Purchase_date) VALUES (%s, %s, %s, %s)

And in seq_of_params we are passing the below List.

records_to_insert = [(4, 'HP Pavilion Power', 1999, '2019-01-11'),
                     (5, 'MSI WS75 9TL-496', 5799, '2019-02-27'),
                     (6, 'Microsoft Surface', 2330, '2019-07-23')]

Example to INSERT multiple rows into a MySQL table

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29')

    mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) 
                           VALUES (%s, %s, %s, %s) """

    records_to_insert = [(4, 'HP Pavilion Power', 1999, '2019-01-11'),
                         (5, 'MSI WS75 9TL-496', 5799, '2019-02-27'),
                         (6, 'Microsoft Surface', 2330, '2019-07-23')]

    cursor = connection.cursor()
    cursor.executemany(mySql_insert_query, records_to_insert)
    connection.commit()
    print(cursor.rowcount, "Record inserted successfully into Laptop table")

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

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

Output:

3 Record inserted successfully into Laptop table MySQL connection is closed
MySQL Laptop table after inserting multiple rows
MySQL Laptop table after inserting multiple rows

Refer to fetch data from the MySQL table to verify your result.

Note:

  • Using cursor.executemany(sql_insert_query, records_to_insert) we are inserting multiple rows (from a List) into the table.
  • Using the cursor.rowcount we can find the number of records inserted.

Insert timestamp and DateTime into a MySQL table using Python

For example, you have a date column in a MySQL table. Let’s see how to prepare an insert query to add DateTime into a table from Python

from datetime import datetime

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29')

    mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) 
                            VALUES (%s, %s, %s, %s) """

    cursor = connection.cursor()
    current_Date = datetime.now()
    # convert date in the format you want
    formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')
    insert_tuple = (7, 'Acer Predator Triton', 2435, current_Date)

    result = cursor.execute(mySql_insert_query, insert_tuple)
    connection.commit()
    print("Date Record inserted successfully")

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

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

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

Date Record inserted successfully
MySQL connection is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Filed Under: Python MySQL

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Keep Reading Python

Python regex Python Input & Output Python MySQL Python PostgreSQL Python SQLite Python JSON Python Quizzes Python Exercises Python Generate random data

Leave a Reply Cancel reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your code </pre>

25 Comments

Comments

  1. Helmi says

    March 24, 2020 at 7:27 pm

    Hey Vishal, thx for the tutorial.
    How if we want to input data into multiple tables? Let’s say, we want to store the name value in html form into ‘item table’ and ‘item stock’?

    Reply
  2. huhu says

    December 26, 2019 at 6:27 pm

    Hi…Do you know why I got this error, ‘Failed to insert the record into MySQL table Failed executing the operation; Python type tuple cannot be converted’. Hope you can help me…thank you

    Reply
    • Vishal says

      December 26, 2019 at 7:51 pm

      Hey, Can you please let me the data you are trying to insert.

      Reply
      • huhu says

        December 27, 2019 at 6:48 am

        It is okay..I got it…Thank you 🙂

        Reply
  3. Jason says

    November 22, 2019 at 2:04 am

    Hi Vishal,

    I appreciate the great detail you put into your examples!

    One thing I noticed is you get ‘result’ from cursor.execute(), but I don’t see examples of it being used. You mention it is the number of rows affected.

    Thanks again!

    Reply
    • Vishal says

      November 22, 2019 at 9:53 am

      Than you Jason. To get the number of rows affected we can use the cursor.rowcount I have modified the example accordingly.

      Reply
  4. Masiroh says

    October 21, 2019 at 1:11 pm

    Hi Vishal, can you help me, please…
    I using Python variables in a MySQL INSERT query,
    how to insert variables if not exist?

    Reply
    • Vishal says

      October 21, 2019 at 9:57 pm

      Hi Masiroh,

      You need to perform a select operation on the table. if data is not there you can insert record else skip it.
      Refer to this https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table

      Reply
      • Masiroh says

        October 26, 2019 at 8:45 am

        my values are parameters that I get from a sensor, what should i add to my code?

        this is my code is work:

        def insertPythonVaribleInTable(start_, finish, start_count, end_count, gross_deliver, avg_flowrate, sale_number):
            try:
                connection = mysql.connector.connect(host='localhost',
                                                     database='mydatabase',
                                                     user='root',
                                                     password='')
                cursor = connection.cursor()
                sql_insert_query = """ INSERT INTO `ticket`
                                        (`start_`, `finish`, `start_count`, `end_count`, `gross_deliver`, `avg_flowrate`, `sale_number`) VALUES (%s,%s,%s,%s,%s,%s,%s)"""
                insert_tuple = (start_, finish, start_count, end_count,
                                gross_deliver, avg_flowrate, sale_number)
                cursor.execute(sql_insert_query, insert_tuple)
                connection.commit()
                print("Record inserted successfully into ticket 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")
        
        
        regex = r"((?PSTART\s+\d.+)|(?PFINISH\s+\d.+)|(?PSTART COUNT\s+(\d|\.)+)|(?PEND COUNT\s+(\d|\.)+)|(?PGROSS DELIVER\s+(\d|\.)+)|(?PAVG FLOW RATE\s+(\d|\.)+)|(?PSALE NUMBER\s+(\d|\.)+)|(?PDUPLICATE TICKET\s+(\d|\.)+))"
        ser = serial.Serial(
            port='COM4',
            baudrate=9600,
            parity=serial.PARITY_NONE,
            stopbits=serial.STOPBITS_ONE,
            bytesize=serial.EIGHTBITS,
            timeout=100)
        print("connected to: " + ser.portstr)
        
        
        test_str = ""
        while True:
            test_str += ser.readline().decode('utf-8')
            if "*" in test_str:
                ser.flushInput()
                matches = re.finditer(regex, test_str, re.MULTILINE)
                # print(matches)
                #     print(matches.groupdict())
                data = {}
                for matchNum, match in enumerate(matches, start=1):
                    for grup in match.groupdict():
                       # print(grup)
                        val = match.group(grup)
                        if val is not None:
                            if not grup in data:
                                se = re.search(r"\d+?[\d\.\/\s\:]*?$", val)
                                if se is not None:
                                    try:
                                        data[grup] = int(se.group().strip())
                                    except:
                                        try:
                                            data[grup] = float(se.group().strip())
                                        except:
                                            data[grup] = se.group().strip()
                                else:
                                    data[grup] = val.strip()
                print(data)
        
                insertPythonVaribleInTable(data['start_'], data['finish'], data['start_count'],
                                           data['end_count'], data['gross_deliver'], data['avg_flowrate'], data['sale_number'])
                test_str = ""
        
        Reply
  5. wale says

    July 12, 2019 at 11:04 am

    i have a problem of storing user input to the database table , how can i go about that ?

    Reply
    • Vishal says

      July 13, 2019 at 12:56 pm

      Hi wale, you can study how to get user input in Python. There are many ways. once you get input into Python variables or data structure (list or dictionary) you can pass it to insert query to store it in the database.

      Python Input and Output

      Reply
  6. jared says

    July 10, 2019 at 9:29 am

    So I created the python_users table as follows:
    create table python_users (id int, name varchar(50), birth_date varchar (50), age int);

    Then I copied your insert multiple rows example and made changes so it looks like this:

     import mysql.connector
    from mysql.connector import Error
    from mysql.connector import errorcode
    from mysql.connector.cursor import MySQLCursorPrepared
    try:
        connection = mysql.connector.connect (host="localhost", 
                                              user="root", 
                                              passwd="***********", 
                                              database="SampleDb")
        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 (cursor_class=MySQLCursorPrepared)
        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")
    

    The table is not populating and this is the error I’m receiving:
    connection is closed
    —————————————————————————
    NotImplementedError Traceback (most recent call last)
    in
    14 VALUES (%s,%s,%s,%s) “””
    15 cursor = connection.cursor (cursor_class=MySQLCursorPrepared)
    —> 16 result = cursor.executemany (sql_insert_query, records_to_insert)
    17 connection.commit()
    18 print (cursor.rowcount, “Record inserted successfully into python_users table”)

    F:\Anaconda\lib\site-packages\mysql\connector\cursor.py in executemany(self, operation, seq_params)
    1231 try:
    1232 for params in seq_params:
    -> 1233 self.execute (operation, params)
    1234 if self.with_rows and self._have_unread_result():
    1235 self.fetchall()

    F:\Anaconda\lib\site-packages\mysql\connector\cursor.py in execute (self, operation, params, multi)
    1196
    1197 try:
    -> 1198 self._prepared = self._connection.cmd_stmt_prepare (operation)
    1199 except errors.Error:
    1200 self._executed = None

    F:\Anaconda\lib\site-packages\mysql\connector\abstracts.py in cmd_stmt_prepare (self, statement)
    967 def cmd_stmt_prepare (self, statement):
    968 “””Prepare a MySQL statement”””
    –> 969 raise NotImplementedError
    970
    971 def cmd_stmt_execute(self, statement_id, data=(), parameters=(), flags=0):

    NotImplementedError:

    I’ve tried a few tweeks but can’t seem to figure out what is going wrong here.

    Reply
    • Vishal says

      July 13, 2019 at 1:59 pm

      Hi jared, Try replacing

      cursor = connection.cursor (cursor_class=MySQLCursorPrepared) 
      

      with following code

      cursor = connection.cursor()
      Reply
  7. Alex Ioan says

    May 28, 2019 at 2:03 pm

    Hi Vishal,

    I have similar issues with the code as in the above comments. Please see below the code I am using. The output is “if connection.is_connected():
    UnboundLocalError: local variable ‘connection’ referenced before assignment”

    import mysql.connector
    from mysql.connector import Error
    from mysql.connector import errorcode
    from datetime import datetime
    from mysql.connector.cursor import MySQLCursorPrepared
    
    
    def insert_output(keypoints_one, keypoints_two, percentage_comparison, result_comparison):
    
        try:
            connection = mysql.connector.connect (host='localhost',
                                                 database='output_db',
                                                 user='root',
                                                 protocol='3306',
                                                 password='')
    
            cursor = connection.cursor (cursor_class=MySQLCursorPrepared)
    
            sql_insert_query = """ INSERT INTO `output_table`
                              (`keypoints1`, `keypoints2`, `percentage`, `result`) VALUES (%s,%s,%s,%s)"""
    
            insert_tuple = (keypoints_one, keypoints_two, percentage_comparison, result_comparison)
    
            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")
    
    
    insert_output(11, 22, 5, "Fail")
    

    Thank you!

    Reply
    • Alex Ioan says

      May 28, 2019 at 2:11 pm

      I just figured it out. The issue was with ‘protocol’ which wasn’t supported. I have removed that line and now it works perfectly.

      Thanks for the nice example!

      Reply
      • Vishal says

        May 29, 2019 at 8:46 am

        Alex Ioan, Thank you! it should be port attribute instead of protocol. if database server listening on different port other than default(3306) you need to mention the port [parameter in connection argument list. Refer Python MySQL All Connection arguments list for more details

        Reply
        • Alex Ioan says

          May 31, 2019 at 11:52 am

          Hi Vishal! Yes, you are right. Thank you as well for the referral article. Now the connection looks as follows:

          connection = mysql.connector.connect(host='localhost',
                                                       database='output_db',
                                                       user='root',
                                                       port=3308,
                                                       password='')
          

          It works perfectly. Thanks again!

          Reply
          • Vishal says

            June 2, 2019 at 9:43 am

            cool

  8. Isha says

    February 13, 2019 at 8:18 pm

    I am getting error: “UnboundLocalError: local variable ‘cursor’ referenced before assignment” for the second example, of inserting data using python variables.

    Reply
    • Vishal says

      February 14, 2019 at 7:58 am

      Hey Isha. create a cursor using connection first then use it.

      Reply
      • Murugan says

        April 8, 2019 at 12:49 pm

        I changed , but agian showing same ERROR : UnboundLocalError: local variable ‘connection’ referenced before assignment

        Reply
        • Vishal says

          April 8, 2019 at 11:06 pm

          Murugan, Can you please let me know the code you are trying

          Reply
  9. george el. says

    December 11, 2018 at 10:37 pm

    the first example works.
    the second and third with the parameters throws an exception. i think it has to do with prepared=True.

    PS D:\projects\mysql-mysql-connector-python> python mysql3.py
    Traceback (most recent call last):
      File "mysql3.py", line 11, in insertPythonVaribleInTable
        cursor = connection.cursor(prepared=True)
      File "D:\Anaconda3\lib\site-packages\mysql\connector\connection_cext.py", line 479, in cursor
        return (types[cursor_type])(self)
      File "D:\Anaconda3\lib\site-packages\mysql\connector\cursor_cext.py", line 820, in __init__
        "Alternative: Use connection.MySQLCursorPrepared")
    NotImplementedError: Alternative: Use connection.MySQLCursorPrepared
    

    During handling of the above exception, another exception occurred:

    Traceback (most recent call last):
      File "mysql3.py", line 28, in 
        insertPythonVaribleInTable("Ault", "2018-07-14", 34)
      File "mysql3.py", line 24, in insertPythonVaribleInTable
        cursor.close()
    UnboundLocalError: local variable 'cursor' referenced before assignment
    PS D:\projects\mysql-mysql-connector-python>
    
    Reply
    • Vishal says

      December 11, 2018 at 10:51 pm

      It’s working at our end. Can you please check your code. I guess you are using cursor class before creating it from connection

      Reply
    • Vishal says

      December 18, 2018 at 9:31 am

      You can try by replacing

      cursor = connection.cursor (prepared=True)
      

      with the following code

      cursor = connection.cursor (cursor_class=MySQLCursorPrepared)
      

      You need to import this first

      from mysql.connector.cursor import MySQLCursorPrepared
      
      Reply

Leave a Reply Cancel reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your code </pre>

 Python MySQL

  • Python MySQL Connection Guide
  • Python MySQL Insert
  • Python MySQL Select
  • Python MySQL Update
  • Python MySQL Delete
  • Call MySQL Stored Procedure
  • Python MySQL Parameterized Query
  • Python MySQL Transactions
  • Python MySQL Connection Pooling
  • Python MySQL BLOB
  • Python Database Exercise

All Python Topics

Python regex Python Input and Output Python MySQL Python PostgreSQL Python SQLite Python JSON Python Quizzes Python Exercises Python Generate random data
TweetF  sharein  shareP  Pin

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Python

  • Python Tutorials
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • RSS
  • Sitemap

Legal Stuff

  • About Us
  • Privacy Policy
  • Cookie Policy
  • Terms Of Use
  • Contact Us
DMCA.com Protection Status

Copyright © 2018-2021 · [pynative.com]