Insert Values into SQL Server Table using Python

In this short guide, you’ll see the full steps to insert values into SQL Server table using Python.

Here are the steps that you may follow.

Steps to Insert Values into SQL Server Table using Python

Step 1: Install the Pyodbc Package

If you haven’t already done so, install the pyodbc package using this command:

pip install pyodbc

Step 2: Connect Python to SQL Server

Now you can connect Python to SQL Server.

For illustration purposes, let’s use the following information to connect Python to SQL Server:

  • The server name is: RON-SQLEXPRESS
  • The database name is: test_database
  • The table name is: product 

Where the ‘product‘ table contains the following data:

product_idproduct_nameprice
1Computer800
2TV1200
3Printer150
4Desk400

Here is the code to connect Python to SQL Server for our example (make sure to adjust the code to reflect your server, database and table information):

import pyodbc

conn = pyodbc.connect(
"Driver={SQL Server};"
"Server=RON-SQLEXPRESS;"
"Database=test_database;"
"Trusted_Connection=yes;"
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM product")

for i in cursor:
print(i)

The result after running the code:

(1, 'Computer', 800)
(2, 'TV', 1200)
(3, 'Printer', 150)
(4, 'Desk', 400)

Step 3: Insert values into SQL Server table using Python

Now let’s insert the following two records into the product table:

product_idproduct_nameprice
5Chair120
6Tablet300

Here is the complete Python code to insert those records:

import pyodbc

conn = pyodbc.connect(
"Driver={SQL Server};"
"Server=RON-SQLEXPRESS;"
"Database=test_database;"
"Trusted_Connection=yes;"
)

cursor = conn.cursor()

cursor.execute(
"""
INSERT INTO product (product_id, product_name, price)
VALUES
(5,'Chair',120),
(6,'Tablet',300)
"
""
)

conn.commit()

Don’t forget to add conn.commit() at the end of the code to ensure that the insert command would get executed.

Step 4: Verify the results

Finally, you can verify that the new records were inserted into the product table by running the following SELECT query in SQL Server:

SELECT * FROM product

You should now see the two additional records at the bottom of the table:

product_idproduct_nameprice
1Computer800
2TV1200
3Printer150
4Desk400
5Chair120
6Tablet300

You may also want to check the following tutorials to learn how to:

Leave a Comment