Python SQL Server: Inserting Data

Summary: in this tutorial, you will learn how to insert data into a table in SQL Server from a Python program.

This tutorial begins where the connecting to the SQL Server from the Python program tutorial left off.

We’ll add a new row to the Authors table from a Python program.

Inserting data into a table

Step 1. Create a new file insert.py file within the project directory.

Step 2. Define a function insert_author that inserts a new author into the Authors table:

import logging
from connect import create_connection


def insert_author(first_name: str, last_name:str, birth_date:str) -> int | None:
    # Connect to the SQL Sever
    conn = create_connection()
    if conn is None:
        return None
    
    # Insert a new author
    with (conn, conn.cursor() as cursor):
        cursor.execute(
            "INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (%s, %s, %s)",
            (first_name, last_name, birth_date),
        )
        conn.commit()
        logging.info(f'Author: {first_name} {last_name} inserted successfully.')
        return cursor.lastrowid

Code language: Python (python)

How it works.

First, import the built-in logging module and the create_connection from the connect module.

import logging
from connect import create_connectionCode language: Python (python)

Second, connect to the SQL Server using the create_connection() function:

conn = create_connection()
if conn is None:
   return NoneCode language: Python (python)

If the connection fails, return None immediately.

Third, use the Connection and Cursor objects in the with statement to ensure that they are properly managed.:

with (conn, conn.cursor() as cursor):Code language: Python (python)

Fourth, execute an INSERT statement with parameterized values to prevent SQL injection:

cursor.execute(
    "INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (%s, %s, %s)",
     (first_name, last_name, birth_date),
)Code language: Python (python)

Fifth, call the commit() method to save the changes to the database.

conn.commit()Code language: Python (python)

Sixth, log a success message indicating the author was inserted:

logging.info(f'Author: {first_name} {last_name} inserted successfully.')Code language: Python (python)

Seventh, return the ID of the newly inserted row using the cursor.lastrowid property:

return cursor.lastrowidCode language: Python (python)

Step 3. Modify the main.py to call the insert_author() function:

import logging, sys
from insert import insert_author

# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)

# insert a new author
id = insert_author('Alice', 'Johnson', '1978-05-14');
if id is not None:
    logging.info(f'Author ID: {id}')Code language: PHP (php)

How it works.

First, import the insert_author function from the insert.py module:

from insert import insert_authorCode language: Python (python)

Second, call the insert_author() function to insert a new row into the Authors table:

id = insert_author('Alice', 'Johnson', '1978-05-14');Code language: Python (python)

Third, display the author ID if the row was inserted successfully:

if id is not None:
    logging.info(f'Author ID: {id}')Code language: JavaScript (javascript)

Step 4. Open your terminal and run the following command:

python main.pyCode language: Python (python)

If you see the following output, you have successfully inserted a new author into the database.

INFO:Author: Alice Johnson inserted successfully.
INFO:Author ID: 1Code language: Python (python)

Download the project source code

Download the project source code

Summary

• Call the execute() method of the Cursor object to run an INSERT statement to insert a new row into a table.
• Use parameterized values in the INSERT statement to prevent SQL injection.