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:

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:

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: