How to Create a Table in SQL Server using Python

Python / June 7, 2020

In this article, you’ll see how to create a table in SQL Server using Python. An example is also included for demonstration purposes.

Steps to Create a Table in SQL Server using Python

Step 1: Install the Pyodbc package

If you haven’t already done so, install the Pyodbc package in Python using this command (under Windows):

pip install pyodbc

You may refer to this guide for the instructions to install a package in Python using PIP.

Step 2: Connect Python to SQL Server

Next, you’ll need to connect Python to SQL Server.

You may use this template to perform the connection (if needed, you can check the following guide for the full steps to connect Python to SQL Server):

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=database_name;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

Step 3: Create the table in SQL Server using Python

Now you should be able to create your table in SQL Server using Python.

For example, I created a new table, where the:

  • Server name is: RON\SQLEXPRESS
  • Database name is: TestDB
  • New table name is: People
  • New People table would contain the following columns and data types:
Column Name Data Type
Name nvarchar(50)
Age int
City nvarchar(50)

For our example, here is the complete code that I used to create the table in SQL Server using Python (you’ll need to adjust the code to reflect your server and database names):

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

cursor = conn.cursor()

cursor.execute('''

               CREATE TABLE People
               (
               Name nvarchar(50),
               Age int,
               City nvarchar(50)
               )

               ''')

conn.commit()

Run the code in Python (adjusted to your connection string information).

Then, click on the refresh button in SQL Server, and you’ll see the new People table (with a dbo schema):

How to Create a Table in SQL Server using Python

You can then run a simple SELECT query in SQL Server to display the table:

SELECT * FROM TestDB.dbo.People

You’ll notice that the table is currently empty:

Empty table

Let’s say that you want to add the following values into the dbo.People table:

Name Age City
Jade 20 London
Mary 47 Boston
Jon 35 Paris

You can then apply the code below to insert the values into the table (you may check the following guide for the complete steps to insert values into SQL Server table):

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

cursor = conn.cursor()

cursor.execute('''

                INSERT INTO TestDB.dbo.People (Name, Age, City)
                VALUES
                ('Jade',20,'London'),
                ('Mary',47,'Boston'),
                ('Jon',35,'Paris')  

                ''')
conn.commit()

Run the code in Python (adjusted to your connection string) to insert the values into the table.

Then, rerun the following query in SQL Server:

SELECT * FROM TestDB.dbo.People

You’ll now see the values in the table:

Create a Table in SQL Server using Python