Insert into Table in Python MySQL

Python MySQL - Insert into Table

To insert a record into MySQL table in Python,

  1. Create a connection to the MySQL database with user credentials and database name, using connect() function.
  2. Get cursor object to the database using cursor() function.
  3. Take INSERT INTO table query, and values tuple for the record.
  4. Call execute() function on the cursor object, and pass the query string, and values tuple as arguments to execute() function.

Example

Consider that there is a schema named mydatabase in MySQL. The credentials to access this database are: user - root and password - admin1234, and there is a table named fruits in mydatabase.

In the following program, we get the first record from fruits table.

Python Program

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="admin1234",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO fruits (name, quantity, country) VALUES (%s, %s, %s)"
val = ("Apple", 25, "Canada")

try:  
  mycursor.execute(sql, val)
  print(mycursor.rowcount, "record inserted.")
except:
  print('Something went wrong.')

mydb.commit()

Output

1 record inserted.

Table before inserting record

Python MySQL - Insert into Table

Table after inserting record

Python MySQL - Insert into Table

Summary

In this tutorial of Python Examples, we learned how to insert a record into table in MySQL database from a Python program.