Getting Started

Python - IntroductionPython - Hello World ProgramPython - SyntaxPython - Data TypesPython - Variables

Operators

Python - Arithmetic OperatorsPython - Relational OperatorsPython - Logical OperatorsPython - Assignment OperatorsPython - Bitwise OperatorsPython - Membership OperatorsPython - Identity OperatorsPython - Increment and Decrement Operators

Conditions

Python - If Else statement

Loop

Python - While LoopPython - For Loop

Numbers

Python - NumbersPython - Number Conversion

Strings

Python - StringsPython - String OperatorsPython - String FormattingPython - String MethodsPython - String Format Method

List

Python - ListPython - List Methods

Tuple

Python - Tuple

Set

Python - SetPython - Set Methods

Dictionary

Python - DictionaryPython - Dictionary Methods

Functions

Python - FunctionsPython - Functions - Variable length argumentsPython - Lambda Function

Scope of Variables

Python - Scope of Variables

Modules

Python - ModulesPython - Math ModulePython - JSON ModulePython - datetime ModulePython - time Module

I/O

Python - Read input from keyboard

File

Python - File Handling

Exception Handling

Python - Exception Handling

OOP

Python - Classes and ObjectsPython - Class Constructor __init__ methodPython - Class Destructor __del__ methodPython - Built-in Class AttributesPython - InheritancePython - Method OverridingPython - Method Overloading

Package Management

Python - PIP

Python - MySQL

Python - MySQL - Getting StartedPython - MySQL - Insert dataPython - MySQL - Select dataPython - MySQL - Update dataPython - MySQL - Delete data

Python - CSV

Python - Read data from CSV filePython - Write data in CSV file

Python - MySQL - Insert data

Python

python logo

In this tutorial we will learn to insert data into MySQL database using Python.

Prerequisite

To understand this tutorial you must have some knowledge of SQL, SQL queries and MySQL database.

Note!

For complete MySQL tutorial click here.

To learn more about INSERT query in MySQL check out this MySQL - INSERT INTO Table tutorial.

In this tutorial we will be working with an employee table.

The table already has some rows which are shown below.

mysql< SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2018-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2018-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2018-01-04 12:00:00 |
+------------+-----------+----------+---------------------+
5 rows in set (0.00 sec)

Here is the create table query for the employee table.

CREATE TABLE `employee` (
  `employeeid` varchar(32) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`employeeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert data into table

To insert data into a table we have the following steps.

  1. Connect to the MySQL server using the mysql.connector.connect() method.
  2. Get MySQL cursor from the connection created in step 1.
  3. Using cursor execute the INSERT statement.
  4. Commit the changes.
  5. Close the connection.

Insert one row into a table

To insert data into a table we take help of the execute method which takes two arguments - a SQL query and values in tuple form.

In the following Python program we are inserting a new employee data.

# import module
import mysql.connector

# import errorcode
from mysql.connector import errorcode

# get db connection
try:
  
    cnx = mysql.connector.connect(
        user='root',
        password='root1234',
        host='127.0.0.1',
        database='mydb'
    )
  
except mysql.connector.Error as err:
    
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Invalid credential. Unable to access database.')
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exists')
    else:
        print('Failed to connect to database')
        
except:
    print("Unknown error occurred!")

# insert operation
try:
    # cursor
    cnxCursor = cnx.cursor()
    
    # sql query
    query = "INSERT INTO employee (employeeid, firstname, lastname, created_at) VALUES (%s, %s, %s, %s)"
    
    # values
    values = ("e06", "Tony", "Stark", "2018-01-05, 12:13:14")
    
    # insert data into the table
    cnxCursor.execute(query, values)
    
    # commit
    cnx.commit()
    
    # total number of rows inserted
    print("Total rows inserted: %d" % cnxCursor.rowcount)
    
except mysql.connector.Error as err:
    print("Error: %s" % err.message)
    
except:
    print("Unknown error occurred!")
    
finally:
    # close cursor
    cnxCursor.close()
    # close connection
    cnx.close()

On success we will get the following output.

Total rows inserted: 1

If we now check the employee table we will get the new entry.

mysql< SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2018-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2018-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2018-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2018-01-05 12:13:14 |
+------------+-----------+----------+---------------------+
6 rows in set (0.00 sec)

Insert multiple records into table

To insert multiple rows we create a list of values that are in tuple form and take help of the executemany method.

In the following Python program we are inserting two new employee records into the employee table.

# import module
import mysql.connector

# import errorcode
from mysql.connector import errorcode

# get db connection
try:
  
    cnx = mysql.connector.connect(
        user='root',
        password='root1234',
        host='127.0.0.1',
        database='mydb'
    )
  
except mysql.connector.Error as err:
    
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Invalid credential. Unable to access database.')
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exists')
    else:
        print('Failed to connect to database')
        
except:
    print("Unknown error occurred!")

# insert operation
try:
    # cursor
    cnxCursor = cnx.cursor()
    
    # sql query
    query = "INSERT INTO employee (employeeid, firstname, lastname, created_at) VALUES (%s, %s, %s, %s)"
    
    # values
    values = [
        ("e07", "Doctor", "Strange", "2018-01-05, 13:14:15"),
        ("e08", "Doctor", "Who", "2018-01-05, 13:14:15")
    ]
    
    # insert data into the table
    cnxCursor.executemany(query, values)
    
    # commit
    cnx.commit()
    
    # total number of rows inserted
    print("Total rows inserted: %d" % cnxCursor.rowcount)
    
except mysql.connector.Error as err:
    print("Error: %s" % err.message)
    
except:
    print("Unknown error occurred!")
    
finally:
    # close cursor
    cnxCursor.close()
    # close connection
    cnx.close()

The above code will print the following output on success.

Total rows inserted: 2

If we now check the employee table we will get to see the new entries.

mysql< SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2018-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2018-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2018-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2018-01-05 12:13:14 |
| e07        | Doctor    | Strange  | 2018-01-05 13:14:15 |
| e08        | Doctor    | Who      | 2018-01-05 13:14:15 |
+------------+-----------+----------+---------------------+
8 rows in set (0.00 sec)

In the next tutorial we will learn how to fetch data from the table.

Have fun coding.

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT