Insert Multiple Rows in MySQL Table in Python

MySQL is an open-source, Relational Database Management System (RDBMS) that stores data in a structured format using rows and columns. It is software that enables users to create, manage, and manipulate databases. So this is used in various applications across a wide range of industries and domains.

To run the MySQL server in our systems we need to install it from the MySQL community. In this article, we will learn how to insert multiple rows in a table in MySQL using Python.

To install the MySQL server refer to MySQL installation for Windows and MySQL installation for macOS.

Connect Python with MySQL

Firstly we have to connect the MySQL server to Python using Python MySQL Connector, which is a Python driver that integrates Python and MySQL. To do so we have to install the Python-MySQL-connector module and one must have Python and PIP, preinstalled on their system.

Installation:

To install Python-mysql-connector type the below command in the terminal.

pip install mysql-connector-python

Connecting to MySQL server:

import mysql connector and connect to MySQL server using connect() method.

# import mysql-connector to connect MySQL server
import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password'
)

# check if connected or not
print(conn)

# disconnect to server
conn.close()

Create Database:

After establishing connection to MySQL server create database by creating a 'cursor()' object and execute commands using 'execute()' method. Command to create database is,

CREATE DATABASE DATABASE_NAME

creating MySQL database in python.

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password'
)

# creating cursor object
cursor = conn.cursor()

# creating database
cursor.execute("CREATE DATABASE GFG")

Output:

Create Table:

Command for creating a table is,

CREATE TABLE (
    col_name_1  data_type,
    col_name_2 data_type,
    :
    :
    col_name_n data_type );

Python code for creating table,

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password'
    database='gfg'
)

# creating cursor object
cursor = conn.cursor()

# creating table
table_st = """CREATE TABLE GEEKS (
           NAME VARCHAR(20) NOT NULL,
           ID INT,
           LANGUAGE VARCHAR(20)
           )"""

# created
cursor.execute(table_st)

# display created tables
cursor.execute("show tables")

# disconnect from server
conn.close()

Output:

Inserting data into table:

Insert into query is used to insert table data into MySQL table. Command used to insert data into table is,

INSERT INTO TABLE_NAME ( COL_1,COL_2,....,COL_N) 
VALUES ( COL1_DATA,COL2_DATA,......,COLN_DATA)

Python code for inserting data into tables,

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password'
    database='gfg'
)

# creating cursor object
cursor = conn.cursor()

# insert command/statement
insert_st = """INSERT INTO GEEKS(NAME,ID,LANGUAGE) 
             VALUES("geek1","1234","eng")"""

# row created
cursor.execute(insert_st)
# save changes
conn.commit()

# disconnect from server
conn.close()

Output:

Inserting Multiple rows into MySQL table

We can insert multiple rows into a MySQL table using different methods i.e.,

• Using 'executemany()' Method
• Using 'insert into' Clause With Multiple Value Tuples

Insert Multiple Rows in MySQL Table Using 'executemany( )'

To insert multiple rows into a table we use 'executemany()' method which takes two parameters, one is sql insert statement and second parameter list of tuples or list of dictionaries. Thus 'executemany()' method is helpful in inserting mutliple records at a time or using a single command.

Example1: Using 'executemany()' With List of Tuples

In the below example, 'conn' establishes a MySQL connection with host, username, password, and database. 'cursor' executes SQL statements. 'executemany()' inserts multiple rows. 'commit()' saves changes, and 'close()' disconnects from the MySQL server.

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password'
    database='gfg'
)

# creating cursor object
cursor = conn.cursor()

insert_st = "INSERT INTO GEEKS (NAME, ID, LANGUAGE) VALUES (%s, %s, %s)"
values = [
    ('geek2', '1123', 'Hindi'),
    ('geek3', '3124', 'Telugu'),
    ('geek4', '4567', 'Urdu')
]

# executemany() method
cursor.executemany(insert_st, values)

# save changes
conn.commit()

# disconnect from server
conn.close()

Output:

Example2: Using 'executemany()' With List of Dictionaries

In the below example executemany() method takes two parametres one is 'insert into' command (i.e., insert_st) and other one is list containing dictionaries which are values of multiple rows or multiple records which are to be inserted.

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password'
    database='gfg'
)

# creating cursor object
cursor = conn.cursor()

insert_st = "INSERT INTO GEEKS(NAME, ID, LANGUAGE)
VALUES ( % (NAME)s, % (ID)s, % (LANGUAGE)s)"
values = [
    {'NAME': 'geek5', 'ID': '3678', 'LANGUAGE': 'Eng'},
    {'NAME': 'geek6', 'ID': '7896', 'LANGUAGE': 'punjabi'},
    {'NAME': 'geek7', 'ID': '4016', 'LANGUAGE': 'Hindi'}
]

# executemany() method
cursor.executemany(insert_st, values)

# save changes
conn.commit()

# disconnect from sever
conn.close()

Output:

Using 'INSERT INTO' Clause With Multiple Value Tuples

To insert multiple rows into a table we can also give multiple row values with a 'INSERT INTO' clause separating with coma ' ,' for each row. Syntax for this is,

INSERT INTO TABLE_NAME (COL_1, COL_2, . . . ., COL_N) VALUES
  (COL1_DATA, COL2_DATA, . . . ., COLN_DATA),
  (COL1_DATA, COL2_DATA, . . . ., COLN_DATA),
  :
  :
  (COL1_DATA, COL2_DATA, . . . ., COLN_DATA);

Example 1: Using 'insert into' Clause

In the below example values of multiple rows are inserted using 'insert into' command with multiple value tuples separated with ' , ' which is according to the syntax.

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password'
    database='gfg'
)

# creating cursor object
cursor = conn.cursor()

insert_st = "INSERT INTO GEEKS(NAME, ID, LANGUAGE) VALUES"
values = [
    ('geek8', '3604', 'Telugu'),
    ('geek9', '2495', 'Hindi'),
    ('geek10', '7895', 'Tamil')
]

# constructing sql statment with multiple row values
for row in values:
    insert_st += "('{}','{}','{}'),".format(row[0], row[1], row[2])

insert_st = insert_st[:-1]

# create rows
cursor.execute(insert_st)

# save changes
conn.commit()

# disconnect from server
conn.close()

Output:

Video Illustration of inserting multiple rows after creation of a table using Python :