How to Create a Table in SQL Server using Python

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):

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:

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: