How to Create a Table in SQL Server using Python

August 21, 2021

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, let’s create a new table, where the:

  • Server name is: RON\SQLEXPRESS
  • Database name is: test_database
  • New table name is: products

Where the ‘products’ table would contain the following columns and data types:

Column Name Data Type
product_id int (primary key)
product_name nvarchar(50)
price int

Here is the complete code to create the table in SQL Server using Python (note that 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=test_database;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

cursor.execute('''
		CREATE TABLE products (
			product_id int primary key,
			product_name nvarchar(50),
			price int
			)
               ''')

conn.commit()

Run the code in Python (adjusted to your server and database information), and an empty table called ‘products‘  (with a dbo schema) will be created.

You can quickly check that an empty table was created by running the following SELECT query in SQL Server:

SELECT * FROM test_database.dbo.products

Let’s say that you want to insert the following values into the products table:

product_id product_name price
1 Desktop Computer 800
2 Laptop 1200
3 Tablet 200
4 Monitor 350
5 Printer 150

You can then apply the code below to insert those values into the table:

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

cursor = conn.cursor()

cursor.execute('''
		INSERT INTO products (product_id, product_name, price)
		VALUES
			(1,'Desktop Computer',800),
			(2,'Laptop',1200),
			(3,'Tablet',200),
			(4,'Monitor',350),
			(5,'Printer',150)
                ''')
conn.commit()

Run the code in Python (adjusted to your server and database information) in order to insert the values into the table.

Then, rerun the following query in SQL Server:

SELECT * FROM test_database.dbo.products

You’ll now see the values in the table:

product_id product_name price
1 Desktop Computer 800
2 Laptop 1200
3 Tablet 200
4 Monitor 350
5 Printer 150