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 illustrate 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 table in SQL Server (called the ‘dbo.Person’ table) under a database called ‘TestDB’

The table stores some demographics data about people:

 

How to Insert Values into SQL Server Table using Python

 

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

 

SELECT * FROM TestDB.dbo.Person

 

Our 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: DORON\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 
cnxn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DORON\SQLEXPRESS;'
                      'Database=TestDB;'
                      'Trusted_Connection=yes;')
 
cursor = cnxn.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 we’ll insert the following two records to our ‘dbo.Person’ table:

 

NameAgeCity
Bob55Montreal
Jenny66Boston

 

Using the general syntax we saw at the beginning of this tutorial, you may apply this code to insert values into SQL Server directly from Python (do not forget to add cnxn.commit() at the end of the code, to ensure that the insert command would get executed):

 

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

cursor = cnxn.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')

                ''')

cnxn.commit()

 

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