How do you insert data into a python and mysql table?

In this tutorial, we will learn how to insert a single row and insert multiple rows of data in a MySQL table using Python.

To Insert data into a MySQL Table or to add data to the MySQL Table which we have created in our previous tutorial, We will use the INSERT SQL statement.

If you are new to SQL, you should first learn about the SQL INSERT statement.

Python MySQL - INSERT Data

Let us see the basic syntax to use INSERT INTO statement to insert data into our table:

INSERT INTO table_name (column_names) VALUES(data)

We can insert data in a table in two ways:

• Inserting a single row at a time

• Inserting multiple rows at a time

Inserting Single Row in MySQL Table

In this section, we will see the code example for inserting a single row of data in a MySQL table:

We will be adding data to the students table we created in Python MySQL - Create Table tutorial.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "himaniroot@99",
    database = "studytonight"
)

cursor = db.cursor()
## defining the Query
query ="INSERT INTO students(name, branch, address) VALUES (%s, %s,%s)"
## There is no need to insert the value of rollno 
## because in our table rollno is autoincremented #started from 1
## storing values in a variable
values = ("Sherlock", "CSE", "220 Baker Street, London")

## executing the query with values
cursor.execute(query, values)

## to make final output we have to run 
## the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "record inserted")

The output of the above code will be:

1 record inserted

Inserting Multiple Rows in MySQL Table

In this section, we will see the code example for inserting multiple rows of data in a MySQL table.

To insert multiple rows into the table, the executemany() method is used. It takes a list of tuples containing the data as a second parameter and the query as the first argument.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "himaniroot@99",
    database = "studytonight"
)

cursor = db.cursor()
## defining the Query
query ="INSERT INTO students(Name, Branch,Address) VALUES (%s, %s, %s)"

## storing values in a variable
values = [
    ("Peter", "ME","Noida"),
    ("Amy", "CE","New Delhi"),
    ("Michael", "CSE","London")
]

## executing the query with values
cursor.executemany(query, values)

## to make final output we have to run 
## the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "records inserted")

If all three rows were entered successfully, the output of the above code will be:

3 records inserted

So in this tutorial we learned how we can insert data into MySQL table in Python.

Inserting or updating data is also done using the handler structure known as a cursor. When you use a transactional storage engine such as InnoDB (the default in MySQL 5.5 and higher), you must commit the data after a sequence of INSERT, DELETE, and UPDATE statements.

This example shows how to insert new data. The second INSERT depends on the value of the newly created primary key of the first. The example also demonstrates how to use extended formats. The task is to add a new employee starting to work tomorrow with a salary set to 50000.

from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

tomorrow = datetime.now().date() + timedelta(days=1)

add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")
add_salary = ("INSERT INTO salaries "
              "(emp_no, salary, from_date, to_date) "
              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# Insert new employee
cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid

# Insert salary information
data_salary = {
  'emp_no': emp_no,
  'salary': 50000,
  'from_date': tomorrow,
  'to_date': date(9999, 1, 1),
}
cursor.execute(add_salary, data_salary)

# Make sure data is committed to the database
cnx.commit()

cursor.close()
cnx.close()

We first open a connection to the MySQL server and store the connection object in the variable cnx. We then create a new cursor, by default a MySQLCursor object, using the connection's cursor() method.

We could calculate tomorrow by calling a database function, but for clarity we do it in Python using the datetime module.

Both INSERT statements are stored in the variables called add_employee and add_salary. Note that the second INSERT statement uses extended Python format codes.

The information of the new employee is stored in the tuple data_employee. The query to insert the new employee is executed and we retrieve the newly inserted value for the emp_no column (an AUTO_INCREMENT column) using the lastrowid property of the cursor object.

Next, we insert the new salary for the new employee, using the emp_no variable in the dictionary holding the data. This dictionary is passed to the execute() method of the cursor object if an error occurred.

Since by default Connector/Python turns autocommit off, and MySQL 5.5 and higher uses transactional InnoDB tables by default, it is necessary to commit your changes using the connection's commit() method. You could also roll back using the rollback() method.

How do you insert data into a Python and SQL table?

How do I add data to a MySQL database using Python?

How do you insert data into MySQL?

How do you enter data into a database in Python?