Python MySQL INSERT Query: Step by Step Guide in Python

Python MySQL INSERT Query: Step by Step Guide in Python

In this article, you will learn how to use Python MySQL INSERT query to add data to your database. If you have a background in SQL, this tutorial will improve your knowledge in handling your database in Python.

Prerequisite

First things first – You need to set up your project. If in case you do not know how to do it, click the link to go to our step by step guide.

Also, if you have any trouble in using the PIP command, just click the link to go to our easy step by step fix. Make sure you have a database to work with.

Sample Code

Let us take a look at the example code. The database used here is “python_db” with a “students” table.

import mysql.connector
import mysql.connector.errors

try:
    dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")

    sqlInsertQuery = "INSERT INTO students VALUES (20200011, 'Rodrigo', 'Go', 'Roque')"
    dbCursor = dbConnection.cursor()
    dbCursor.execute(sqlInsertQuery)
    dbConnection.commit()
    print(dbCursor.rowcount, "Record/s successfully added into students table.")
    dbCursor.close()

except mysql.connector.Error as error:
    print("Failed to insert record into students table {}".format(error))

finally:
    if (dbConnection.is_connected()):
        dbConnection.close()
        print("MySQL connection is closed")

Output

1 Record successfully added into students table.
MySQL connection is closed

Process finished with exit code 0

Query Variation

You can also make this as a parameterized query. For this example, we have a “insertStudentRecord” function with “id”,”fn”,”mn” and “ln” as parameters.

import mysql.connector
import mysql.connector.errors

# ------ Parameterized Query------

def insertStudentRecord(id, fn, mn, ln):

    try:
        dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")

        sqlInsertQuery = "INSERT INTO students (studentID, fName, mName, lName) VALUES (%s, %s, %s, %s)"
        dbCursor = dbConnection.cursor()
        studentData = (id, fn, mn, ln)
        dbCursor.execute(sqlInsertQuery, studentData)
        dbConnection.commit()
        print(dbCursor.rowcount, "Record/s successfully added into students table.")
        dbCursor.close()

    except mysql.connector.Error as error:
        print("Failed to insert record into students table {}".format(error))

    finally:
        if (dbConnection.is_connected()):
            dbConnection.close()
            print("MySQL connection is closed")


insertStudentRecord(20200012, "John","Medina", "Martin" )

You will get the same output as before.

MySQL INSERT Multiple Row Insertion

To insert multiple rows, you will use the executemany() function. With this, you can use an array containing your multiple records to pass to this function. Take a look at the example below.

import mysql.connector
import mysql.connector.errors

try:

    dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")

    sqlInsertQuery = "INSERT INTO students (studentID, fName, mName, lName) VALUES (%s, %s, %s, %s)"
    dbCursor = dbConnection.cursor()
    studentBatchData = [(20200013, 'Marian', 'Ignacio', 'Rivera'),
                        (20200014, 'Clarence', 'Yulo', 'Dimagiba'),
                        (20200015, 'Justin', 'Vidal', 'Martin')]
    dbCursor.executemany(sqlInsertQuery, studentBatchData)
    dbConnection.commit()
    print(dbCursor.rowcount, "Record/s successfully added into students table.")
    dbCursor.close()

except mysql.connector.Error as error:
    print("Failed to insert record into students table {}".format(error))

finally:
    if (dbConnection.is_connected()):
            dbConnection.close()
            print("MySQL connection is closed")

You will get an output like this:

3 Record/s successfully added into students table.
MySQL connection is closed

Process finished with exit code 0

Conclusion

That’s how you use Python MySQL INSERT query in your projects. You can always expand and try different ways in implementing the INSERT statement in your Python projects. Check out the different Python – related projects below:

• Python MySQL SELECT Query Guide
• Hotel Management System in Python
• Patient Information System in Python
• Hospital Management System in Python
• Library Management System in Python
• Student Management System in Python

Inquiries

If you have any questions or suggestions about the python mysql insert query, please feel free to leave a comment below.

Technology stack and requirements

To run this Python project on your development machine, you need:

• Python 3.10 or higher. Download from python.org or install via Anaconda if you prefer bundled packages.
• pip package manager. Comes with Python. Used to install project dependencies from requirements.txt.
• Virtual environment. Use venv or conda to isolate project dependencies from your global Python install.
• VS Code or PyCharm. Free code editors with Python syntax highlighting, IntelliSense, and debugging.
• Git. For version control and cloning source code repositories.

Installing the source code

1. Download or clone the repository. Get the ZIP archive from the download link on this page and extract it.
2. Create a virtual environment. Open a terminal in the project folder and run: python -m venv venv, then activate it (venv\Scripts\activate on Windows or source venv/bin/activate on Mac/Linux).
3. Install dependencies. Run pip install -r requirements.txt to install all libraries the project needs.
4. Configure environment variables. If the project uses API keys (OpenAI, Anthropic, database), create a .env file and set the required keys.
5. Run the project. Follow the run command in the README (usually python main.py or streamlit run app.py).

Using this project for your BSIT capstone

• Chapter 1 (Introduction). Discuss the real-world problem this system solves. Cite Philippine or international use cases where the manual process could be automated.
• Chapter 2 (RRL). Compare your project against 5-10 similar published works. Cite ACM, IEEE, or arXiv papers for academic-standard sources.
• Chapter 3 (Methodology). Document the model architecture, training data, hyperparameters, and evaluation metrics used.
• Chapter 4 (Results). Report accuracy, precision, recall, F1-score, and confusion matrix. Screenshot the running app on real inputs.
• Chapter 5 (Conclusion). Identify features for Version 2: better model, larger dataset, mobile deployment, or REST API.

Modules typical of Python MySQL INSERT Query: Step by Step Guide

• Core Python logic. Main functions implementing the business logic of the system.
• Data storage. SQLite for simple projects, PostgreSQL or MongoDB for larger data.
• User interface. Tkinter for desktop, Streamlit for data dashboards, or Flask/FastAPI for web.
• Input validation. Type checking and range validation before processing user data.
• Reports. CSV or PDF export using pandas.to_csv() or ReportLab.
• Testing. pytest unit tests covering core functions.

Common enhancements for capstone review

• Add REST API. Convert desktop app to FastAPI service for mobile or web front-ends.
• Multi-user support. Add login, roles, and per-user data isolation.
• Cloud deployment. Deploy to Render, Railway, or Fly.io for public access.
• Docker containerization. Package the app in Docker for portable deployment.