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 using this command:

pip install pyodbc

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

Step 2: Connect Python to SQL Server

Next, connect Python to SQL Server.

You may use this template to perform the connection (here is a 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 can 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 NameData Type
product_idint (primary key)
product_namenvarchar(50)
priceint

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, 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_idproduct_nameprice
1Desktop Computer800
2Laptop1200
3Tablet200
4Monitor350
5Printer150

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 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_idproduct_nameprice
1Desktop Computer800
2Laptop1200
3Tablet200
4Monitor350
5Printer150

Leave a Comment