Python MySQL Tutorial – How to Insert Data into a Table Using Python

Last updated 2 weeks, 1 day ago | 35 views 75     5

Tags:- MySQL Python

Once you've created your MySQL tables, the next step is to insert data into them. Python makes it easy to interact with a MySQL database using the mysql-connector-python package. In this tutorial, you'll learn how to insert data into MySQL tables step by step using Python.


Table of Contents

  1. Prerequisites

  2. Install MySQL Connector

  3. Connect to a MySQL Database

  4. Insert a Single Record

  5. Insert Multiple Records

  6. Use lastrowid to Get Inserted ID

  7. Commit Transactions

  8. Full Working Example

  9. Tips and Common Pitfalls


✅ 1. Prerequisites

Make sure you have:

  • Python installed

  • MySQL server running

  • A MySQL database and table created

  • The mysql-connector-python library installed


2. Install MySQL Connector

Install the connector using pip:

pip install mysql-connector-python

3. Connect to a MySQL Database

Use Python to establish a connection to your MySQL database:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

4. Insert a Single Record

Use the INSERT INTO SQL command to add data to your table.

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
val = ("Alice", "[email protected]", 30)

cursor.execute(sql, val)
db.commit()

print(cursor.rowcount, "record inserted.")

Always use placeholders (%s) to avoid SQL injection.


5. Insert Multiple Records

You can insert multiple records at once using executemany():

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = [
    ("Bob", "[email protected]", 25),
    ("Charlie", "[email protected]", 28),
    ("Daisy", "[email protected]", 22)
]

cursor.executemany(sql, values)
db.commit()

print(cursor.rowcount, "records inserted.")

6. Get the ID of the Inserted Row

After inserting a record, use cursor.lastrowid to get the ID of the inserted row:

print("Last inserted ID:", cursor.lastrowid)

✅ 7. Don't Forget to Commit

After any insert operation, you must commit the transaction using db.commit() to save changes to the database.


8. Full Working Example

import mysql.connector
from mysql.connector import Error

def insert_users():
    try:
        # Connect to database
        db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",
            database="mydatabase"
        )

        cursor = db.cursor()

        # Insert multiple users
        sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
        values = [
            ("John", "[email protected]", 35),
            ("Jane", "[email protected]", 29),
            ("Mike", "[email protected]", 40)
        ]

        cursor.executemany(sql, values)
        db.commit()

        print(cursor.rowcount, "records inserted.")
        print("Last inserted ID:", cursor.lastrowid)

    except Error as e:
        print("Error:", e)

    finally:
        if db.is_connected():
            db.close()
            print("Connection closed.")

# Run the function
insert_users()

⚠️ 9. Common Pitfalls

Problem Cause Solution
Data not saved Forgot db.commit() Always call db.commit() after insert
SQL syntax error Incorrect query or missing %s Double-check SQL and placeholders
Wrong number of values Values don't match placeholders Ensure tuples match column count
Special characters break query Manual string formatting Use %s placeholders instead

Tips and Best Practices

  • ✅ Always use parameterized queries to protect against SQL injection.

  • ✅ Use executemany() for batch inserts — it's faster and cleaner.

  • ✅ Validate data before inserting into the database.

  • ✅ Keep column names consistent with your data dictionary.

  • ✅ Wrap your database code in try-except-finally blocks for safe execution.


Summary Table

Task Command
Insert one row cursor.execute()
Insert multiple rows cursor.executemany()
Save changes db.commit()
Get last inserted ID cursor.lastrowid

Final Thoughts

Inserting data into a MySQL table with Python is simple and powerful using mysql-connector-python. Whether you’re adding a single row or multiple records, using proper practices like parameterized queries and transaction management ensures your code is efficient and secure.

 

Tips and Tricks


What is pass in Python?

Python | Pass Statement

The pass statement is used as a placeholder for future code. It represents a null operation in Python. It is generally used for the purpose of filling up empty blocks of code which may execute during runtime but has yet to be written.

 

def myfunction():
    pass

 


How can you generate random numbers?

Python | Generate random numbers

Python provides a module called random using which we can generate random numbers. e.g: print(random.random())

 

 

We have to import a random module and call the random() method as shown below:

 import random

 print(random.random())

The random() method generates float values lying between 0 and 1 randomly.


To generate customized random numbers between specified ranges, we can use the randrange() method
Syntax: randrange(beginning, end, step)
 

import random

print(random.randrange(5,100,2))

 


What is lambda in Python?

Python | Lambda function

A lambda function is a small anonymous function. This function can have any number of parameters but, can have just one statement.
 

 

Syntex: 
lambda arguments : expression
 

a = lambda x,y : x+y

print(a(5, 6))

It also provides a nice way to write closures. With that power, you can do things like this.

def adder(x):
    return lambda y: x + y

add5 = adder(5)

add5(1)    #6

As you can see from the snippet of Python, the function adder takes in an argument x and returns an anonymous function, or lambda, that takes another argument y. That anonymous function allows you to create functions from functions. This is a simple example, but it should convey the power lambdas and closures have.
 


What is swapcase() function in the Python?

Python | swapcase() Function

It is a string's function that converts all uppercase characters into lowercase and vice versa. It automatically ignores all the non-alphabetic characters.
 

string = "IT IS IN LOWERCASE."  

print(string.swapcase())  

 


How to remove whitespaces from a string in Python?

Python | strip() Function | Remove whitespaces from a string 

To remove the whitespaces and trailing spaces from the string, Python provides a strip([str]) built-in function. This function returns a copy of the string after removing whitespaces if present. Otherwise returns the original string.
 

string = "  Python " 
 
print(string.strip())  

 


What is the usage of enumerate() function in Python?

Python | enumerate() Function

The enumerate() function is used to iterate through the sequence and retrieve the index position and its corresponding value at the same time.
 

lst = ["A","B","C"] 
 
print (list(enumerate(lst)))

#[(0, 'A'), (1, 'B'), (2, 'C')]

 


Can you explain the filter(), map(), and reduce() functions?

Python | filter(), map(), and reduce() Functions

  • filter()  function accepts two arguments, a function and an iterable, where each element of the iterable is filtered through the function to test if the item is accepted or not.
    >>> set(filter(lambda x:x>4, range(7)))
    
    # {5, 6}
    
    

     

  • map() function calls the specified function for each item of an iterable and returns a list of result

    >>> set(map(lambda x:x**3, range(7)))
    
    # {0, 1, 64, 8, 216, 27, 125}

     

  • reduce() function reduces a sequence pair-wise, repeatedly until we arrive at a single value..
     

    >>> reduce(lambda x,y:y-x, [1,2,3,4,5])
    
    # 3
    

    Let’s understand this:

    2-1=1
    3-1=2
    4-2=2
    5-2=3

    Hence, 3.

 


What is a namedtuple?

Python | namedtuple

A namedtuple will let us access a tuple’s elements using a name/label. We use the function namedtuple() for this, and import it from collections.

>>> from collections import namedtuple

#format
>>> result=namedtuple('result','Physics Chemistry Maths') 

#declaring the tuple
>>> Chris=result(Physics=86,Chemistry=92,Maths=80) 

>>> Chris.Chemistry
# 92

 


Write a code to add the values of same keys in two different dictionaries and return a new dictionary.

We can use the Counter method from the collections module

from collections import Counter

dict1 = {'a': 5, 'b': 3, 'c': 2}
dict2 = {'a': 2, 'b': 4, 'c': 3}

new_dict = Counter(dict1) + Counter(dict2)


print(new_dict)
# Print: Counter({'a': 7, 'b': 7, 'c': 5})


 


Python In-place swapping of two numbers

 Python | In-place swapping of two numbers

>>> a, b = 10, 20
>>> print(a, b)
10 20

>>> a, b = b, a
>>> print(a, b)
20 10

 


Reversing a String in Python

Python | Reversing a String

>>> x = 'PythonWorld'
>>> print(x[: : -1])
dlroWnohtyP

 


Python join all items of a list to convert into a single string

Python | Join all items of a list to convert into a single string

>>> x = ["Python", "Online", "Training"]
>>> print(" ".join(x))
Python Online Training

 


python return multiple values from functions

Python | Return multiple values from functions

>>> def A():
	return 2, 3, 4

>>> a, b, c = A()

>>> print(a, b, c)
2 3 4

 


Python Print String N times

Python | Print String N times

>>> s = 'Python'
>>> n = 5

>>> print(s * n)
PythonPythonPythonPythonPython

 


Python check the memory usage of an object

Python | Check the memory usage of  an object

>>> import sys
>>> x = 100

>>> print(sys.getsizeof(x))
28