How to Insert Values into SQL Server Table using Python

In this tutorial, I’ll show you the steps to insert values into SQL Server table using Python. I’ll use a simple example to demonstrate this concept.

To start, here is the general syntax that you may use to insert values into a table created in SQL Server:

INSERT INTO Database_Name.Table_Name (Column1_Name, Column2_Name,...)

VALUES

('Column1_Value1', 'Column2_Value2',...),

('Column1_Value3', 'Column2_Value4',...)

Let’s now see how to apply this syntax in practice.

Steps to Insert Values into SQL Server Table using Python

Step 1: Prepare your data-set

In order to insert values into SQL Server table using Python, you’ll need an existing table for a given database.

For example, let’s say that you created a database in SQL Server, where:

  • The database name is: TestDB
  • The table name is: dbo.Person
  • The dbo.Person contains the following data:
NameAgeCity
Jade20London
Mary119NY
Martin25London
Rob35Geneva
Maria42Paris
Jon28Toronto

You can then retrieve that data in SQL Server by using a simple SELECT query:

SELECT * FROM TestDB.dbo.Person

How to Insert Values into SQL Server Table using Python

The end goal is to insert new values into the dbo.Person table using Python.

Once you have your data ready, proceed to the next step.

Step 2: Establish a connection between Python and SQL Server

Next, you’ll need to establish a connection between Python and SQL Server.

Here is a full guide that explains how to connect Python to SQL Server from scratch.

To establish such a connection, you’ll need to include the following information in the code below:

  • Your Server Name – in my case, the server name is: RON\SQLEXPRESS
  • Your Database Name – in our example, the database name is: TestDB
  • Your table created in SQL Server – in our example, the table name is: dbo.Person
import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=TestDB;'
                      'Trusted_Connection=yes;')
 
cursor = conn.cursor()
cursor.execute('SELECT * FROM TestDB.dbo.Person')
 
for row in cursor:
    print(row)

This is the result you’ll get once you run the Python code above (adjusted to your connection information):

Insert Values into SQL Server Table using Python

Step 3: Insert values into SQL Server table using Python

Now let’s insert the following two records to our dbo.Person table:

NameAgeCity
Bob55Montreal
Jenny66Boston

Using the general syntax you saw at the beginning of this tutorial, you may apply the code below to insert values into SQL Server directly from Python:

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=TestDB;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM TestDB.dbo.Person')

cursor.execute('''
                INSERT INTO TestDB.dbo.Person (Name, Age, City)
                VALUES
                ('Bob',55,'Montreal'),
                ('Jenny',66,'Boston')
                ''')
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 got inserted into the dbo.Person table by running the following SELECT query in SQL Server:

SELECT * FROM TestDB.dbo.Person

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

How to Insert Values into SQL Server Table using Python