Creating a Table in SQL Server Using Python: A Practical Guide

Creating a Table in SQL Server using Python

In today’s world of data management, SQL Server and Python are two popular tools that provide versatile options for working with databases. While SQL Server has been commonly utilized for executing queries and managing databases, Python allows for the creation of dynamic scripts and data analysis.

The combination of these tools can take data processing to the next level, broadening the scope of developing innovative solutions. This article will provide a practical guide on how to create a table in SQL Server using Python.

Installing Pyodbc package

Pyodbc is a Python library that facilitates connecting to databases via ODBC drivers. This package makes it easier to communicate with various databases, including SQL Server.

Before we begin, we must ensure that Pyodbc is installed on our system. We can do this by running the following command in our command prompt:

pip install pyodbc

Connecting Python to SQL Server

The next step is to establish a connection between our Python code and SQL Server. Pyodbc package comes with a function that enables the connection, making it easy to write the connection code.

Below is an example code that provides a basic connection to SQL Server database.

import pyodbc
server = 'localhost'
database = 'TestDB' 
username = 'my_username' 
password = 'my_password' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

Creating a table in SQL Server

To create a table in SQL Server, we must define the table elements, including columns and their data types. For this example, we will create a table called “Employee” with three columns: “ID,” “Name,” and “Age.” Our data types are as follows: “ID” is an integer with an “Identity” of one and a primary key, “Name” is an nvarchar with a length of 50, and “Age” is an integer.

See below for an example of code that creates our table in SQL Server.

import pyodbc
server = 'localhost'
database = 'TestDB'
username = 'my_username' 
password = 'my_password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute('''
  CREATE TABLE Employee
  (ID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(50),
  Age INT)
  ''')
cnxn.commit()

Checking the table

We need to ensure that our table has been created successfully. To check if the table has been created, run a SELECT query on our table.

cursor.execute('''SELECT * FROM Employee''')
for row in cursor:
    print(row)

Inserting values into the table

Now that we have successfully created our table, we can insert some values into it. In this case, we will add three employees to our “Employee” table, including their name and age.

cursor.execute('''INSERT INTO Employee(Name, Age) VALUES('Abby', 27)''')
cursor.execute('''INSERT INTO Employee(Name, Age) VALUES('Bob', 35)''')
cursor.execute('''INSERT INTO Employee(Name, Age) VALUES('Cindy', 42)''')
cnxn.commit()

Checking the table again

To ensure that our values have been inserted successfully, we can perform another SELECT query.

cursor.execute('''SELECT * FROM Employee''')
for row in cursor:
    print(row)

Example of Creating a Products Table in SQL Server using Python

Defining the products table

In this example, we’ll create a “Products” table in SQL Server. The table will consist of five columns: “Product ID,” “Product Name,” “Price,” “Category,” and “Qty.” The “Product ID” is the primary key with an auto-increment feature and an “INT” data type.

The “Product Name” is an “NVARCHAR” type with a length of 50, and the “Price” is a “DECIMAL” type with a scale of two. The “Category” and “Qty” are “NVARCHAR” and “INT” types, respectively.

Writing the Python code to create the products table

We can create a “Products” table with the above specifications using Python code. See the code below:

import pyodbc
server = 'localhost'
database = 'TestDB'
username = 'my_username' 
password = 'my_password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute('''
  CREATE TABLE Products
  (ProductID INT IDENTITY(1,1) PRIMARY KEY,
  ProductName NVARCHAR(50),
  Price DECIMAL(10,2),
  Category NVARCHAR(50),
  Qty INT)
  ''')
cnxn.commit()

Inserting values into the products table

We’ll insert some values into our products table to make sure it’s working correctly. Here’s how you can insert sample data into the table.

cursor.execute('''INSERT INTO Products(ProductName, Price, Category, Qty) 
    VALUES('Product 1', 9.99, 'Category A', 10)''')
cursor.execute('''INSERT INTO Products(ProductName, Price, Category, Qty) 
    VALUES('Product 2', 19.99, 'Category B', 20)''')
cursor.execute('''INSERT INTO Products(ProductName, Price, Category, Qty) 
    VALUES('Product 3', 29.99, 'Category C', 30)''')
cnxn.commit()

Checking the products table

To ensure that our products table has been created successfully and that the values have been inserted correctly, we can perform a SELECT query on the table.

cursor.execute('''SELECT * FROM Products''')
for row in cursor:
    print(row)

Final Thoughts

Python and SQL Server are powerful tools that can be used together to create dynamic solutions with efficient data analysis. Understanding how to create a table in SQL Server using Python opens up opportunities for data analysts and developers to become more efficient and innovative.

We hope this article provided you with a practical guide to creating a table in SQL Server using Python. In conclusion, this article has provided a practical guide on how to create a table in SQL Server using Python.

We have covered the installation of the Pyodbc package, establishing a connection with SQL Server, defining table elements, writing Python code to create a table, inserting values into the table, and checking the table. Understanding how to create a table in SQL Server using Python enhances data management and processing capabilities, increasing efficiency and innovation for developers and data analysts.

The tutorial provides a foundation for learners to create more complex solutions using Python and SQL Server.