How to Insert Values into SQL Server Table using Python

October 1, 2021

In this short guide, you’ll see the complete 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 the command below (under Windows):

pip install pyodbc

Step 2: Connect Python to SQL Server

There are several items that you may retrieve before you connect Python to SQL Server, including the:

  • Server name
  • Database name
  • Table name

For illustration purposes, let’s use the following information to establish the connection:

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

The ‘product’ table contains the following data:

product_id product_name price
1 Computer 800
2 TV 1200
3 Printer 150
4 Desk 400

Based on the information above, the following code can be used to connect Python to SQL Server for our example (you may check the following guide for the complete steps to connect Python to SQL Server):

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)

Here is the result that you’ll get once you run the code in Python (adjusted to your database connection information):

(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_id product_name price
5 Chair 120
6 Tablet 300

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_id product_name price
1 Computer 800
2 TV 1200
3 Printer 150
4 Desk 400
5 Chair 120
6 Tablet 300

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