Insert Record in MySQL Database Table Using SQLAlchemy

Interactive Google Colab Practice Notebook

Practice inserting single and batch records into MySQL using SQLAlchemy 2.0+ context managers, parameterized queries, and lastrowid tracking directly inside Google Colab.

View on GitHub

We will use SQL INSERT to add new records to our database student table using SQLAlchemy connection engines.

We defined my_conn in our database connection guide. In modern SQLAlchemy 2.0+, connections are safely opened and managed using explicit with engine.begin() or with engine.connect() blocks, which automatically manage transactions and commits.

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://userid:password@localhost/database_name")

We are using the standard student table. You can download the complete schema dump or read about password security below:

Inserting a Single Record

Using SQLAlchemy context managers, transactions are automatically committed when the block exits without raising an error:

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://userid:password@localhost/database_name")

with engine.begin() as conn:
    query = text("INSERT INTO `student` (`name`, `class`, `mark`, `gender`) VALUES ('King1', 'Five', '45', 'male')")
    result = conn.execute(query)
    print("Row Added =", result.rowcount)

Output:

Row Added = 1

Retrieving Auto-Increment Primary Key (lastrowid)

Our student table includes an auto-incremented ID column. You can inspect the primary key generated for a newly inserted row using the lastrowid attribute on the result object:

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://userid:password@localhost/database_name")

with engine.begin() as conn:
    query = text("INSERT INTO `student` (`name`, `class`, `mark`, `gender`) VALUES ('King1', 'Five', '45', 'male')")
    result = conn.execute(query)
    print("ID of Row Added =", result.lastrowid)

Output:

ID of Row Added = 36

Using Parameterized Queries for Security

Always use parameterized queries when inserting data originating from forms or external user inputs to prevent SQL injection attacks. In SQLAlchemy, named placeholders are prefixed with a colon (:placeholder_name) and passed as a dictionary:

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://root:test@localhost/my_tutorial")

query = text("INSERT INTO `student` (`name`, `class`, `mark`, `gender`) VALUES (:name, :class, :mark, :gender)")
my_data = {
    "name": 'King Li',
    "class": 'Four',
    "mark": 58,
    "gender": 'female'
}

with engine.begin() as conn:
    result = conn.execute(query, my_data)
    print("ID of Row Added =", result.lastrowid)

Output:

ID of Row Added = 37

Inserting Multiple Records (Batch / Bulk Insertion)

To insert multiple rows efficiently in a single batch operation, pass a list of dictionaries to conn.execute() alongside the parameterized SQL statement:

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://userid:password@localhost/database_name")

query = text("INSERT INTO `student` (`name`, `class`, `mark`, `gender`) VALUES (:name, :class, :mark, :gender)")
my_data = [
    {"name": "King", "class": "Five", "mark": 45, "gender": "male"},
    {"name": "Queen", "class": "Four", "mark": 44, "gender": "female"},
    {"name": "Jack", "class": "Three", "mark": 42, "gender": "male"}
]

try:
    with engine.begin() as conn:
        result = conn.execute(query, my_data)
        print("Rows Added =", result.rowcount)
except Exception as e:
    print("Database error:", e)

Output:

Rows Added = 3

Capturing Detailed SQLAlchemy Exceptions

Catching SQLAlchemyError allows you to extract specific database error messages returned by MySQL (such as duplicate keys or missing columns):

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

engine = create_engine("mysql+pymysql://root:test@localhost/my_tutorial")

query = text("INSERT INTO `student` (`name`, `class`, `mark`, `gender`) VALUES (:name, :class, :mark, :gender)")
my_data = [
    {"name": "King", "class": "Five", "mark": 45, "gender": "male"},
    {"name": "Queen", "class": "Four", "mark": 44, "gender": "female"}
]

try:
    with engine.begin() as conn:
        result = conn.execute(query, my_data)
        print("Rows Added =", result.rowcount)
except SQLAlchemyError as e:
    error = str(e.__dict__.get('orig', e))
    print("Captured Error:", error)

Inspecting Tables and Verifying Inserted Records

After performing insert operations, you can list all database tables and retrieve updated records using SELECT queries:

with engine.connect() as conn:
    # List all tables
    r_tables = conn.execute(text("SHOW TABLES"))
    for row in r_tables:
        print("Table:", row)
        
    # List inserted records
    r_set = conn.execute(text("SELECT * FROM student ORDER BY id DESC LIMIT 5"))
    for row in r_set:
        print(row)

Frequently Asked Questions & Common Errors

Related Tutorials & Next Steps

• Python MySQL Insert Record Guide

  Learn standard MySQL Connector insert operations with parameterized inputs and error handling.

• Python MySQL Connection Guide

  Learn how to configure SQLAlchemy engines, connection strings, and drivers like pymysql and mysqlclient.

• Tkinter GUI for MySQL Data Insertion

  Build a desktop GUI application using Python Tkinter to capture user input and insert rows into MySQL.

• Student Table SQL Dump File

  Download the complete student.sql database dump file featuring sample records with the updated gender column schema.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: