Performing CRUD operations with Python and MS SQL Server

In our last two tutorials, we explained how to connect a Python application to PostgreSQL and how to connect a Python application to a MySQL database. We also explained the process of executing create, read, update and delete (CRUD) operations on these databases. In this article, we’ll show you how to connect a Python application to a Microsoft SQL Server database, and how to execute some sample database operations on your MS SQL Server database from within a Python application.

To execute the scripts in this tutorial, you need to have an instance of MS SQL Server database running on your localhost or any server you can access. To see how to install MS SQL Server for various platforms, visit the Microsoft SQL Server official installation guide.

Installing Required Libraries

We’ll be using Python’s pyodbc module to perform the MS SQL Server database operations in this tutorial. To download the pyodbc module, execute the following script on your command terminal.

pip install pyodbc

Performing CRUD Operations on MS SQL Server via Python

Connecting to MS SQL Server

To perform any operation on a MS SQL Server database, you first have to connect to an MS SQL Server instance. To do so, you need your driver name, server instance name, database name and the connection type. Simply create a connection string which contains values for these variables. The following script imports the pyodbc module and creates a connection string that you can use to make a connection to your own SQL Server instance.

import pyodbc

conn_string = ('Driver={SQL Server}; Server=XXX;  database=master; Trusted_Connection=yes;')

In the above script the Driver name is SQL Server, and the Server name is XXX. You need to replace XXX with your own server name. Initially the database name will be master and finally the value for the Trusted_Connnection attribute is set to yes since we happen to be using Windows authentication to connect to our SQL Server instance.

Next, to create a connection object, the connection string is passed to the connect() method of the pyodbc module, as shown below. The autocommit attribute is set to True since we want all the queries executed via this connection to be automatically committed.

my_con = pyodbc.connect(conn_string,  autocommit = True)

The connect() method returns a connection object which can be used to perform whatever SQL operations you want on your Microsoft SQL Server.

Creating a Database

To create a database, you need to fill a string with the query for your database creation command. After that, you’ll get the cursor from the connection object. The cursor object is used to execute queries on a MS SQL server. Finally, to create a new database, the “CREATE DATABASE” query is passed to the execute() method of the cursor object as shown in the following script.

query = "CREATE DATABASE laptop_db;"
cursor = my_con.cursor()
cursor.execute(query)

The above script creates a dummy database named laptop_db.

To perform any operation on the database, you first have to connect to the database. Previously we created a connection to your MS SQL Server master database, but now we need to connect specifically to the laptop_db database, which we’ll do in the example below. You can see in the following query that the value for the database attribute in the connection string is updated to laptop_db.

conn_string = ('Driver={SQL Server}; Server=DESKTOP-IIBLKH1\SQLEXPRESS;  database=laptop_db; Trusted_Connection=yes;')
my_con = pyodbc.connect(conn_string,  autocommit = True)

Once a connection with the laptop_db database is established, we’re ready to execute queries on it.

Creating a Table

Let’s create a new table inside the laptop_db. To create a new table, the query that creates the table is passed to the execute() method of the cursor object as shown below. The following script creates a table named laptops with four attributes: id, brand, model and price.

create_table_query = """
CREATE TABLE laptops (
    id int NOT NULL IDENTITY(1, 1),
    brand varchar(255) NOT NULL,
    model varchar(255) NOT NULL,
    price int,
    PRIMARY KEY (id)
);
"""

cursor = my_con.cursor()
cursor.execute(create_table_query)

)

Get Our Python Developer Kit for Free

I put together a Python Developer Kit with over 100 pre-built Python scripts covering data structures, Pandas, NumPy, Seaborn, machine learning, file processing, web scraping and a whole lot more - and I want you to have it for free. Enter your email address below and I'll send a copy your way.

Inserting Records

Next, we’ll insert some dummy records in the laptops table we just created in the last section. To insert records, you have to pass the INSERT query to the execute() method of the cursor object. The following script creates a query that inserts records of 5 dummy laptops in the laptops table.

insert_records_query = """
INSERT INTO
   laptops (brand, model, price)
VALUES
  ('Dell', 'XX123', 500),
  ('Lenovo', 'ZZLEN', 400),
  ('HP', 'VZ341', 600),
  ('HP', 'GG222', 580),
  ('Dell', 'TTT22', 300)
"""
cursor.execute(insert_records_query)

Reading Records

To read records, you need to pass the SELECT query and the connection object to the read_sql() method of the Pandas module. The read_sql() method returns a Pandas dataframe which you can then print using the head() method, as shown in the following script:

import pandas as pd

select_query = "SELECT * FROM laptops"
data = pd.read_sql(select_query, my_con)
data.head()

Output:

From the above output, you can see all the records in the laptops table.

Updating Records

To update a record, the UPDATE query is passed to the execute() method of the cursor object. The following script updates the price of all the HP laptops in our database to 300.

update_laptops_query = """
UPDATE
  laptops
SET
  price = 300
WHERE
  brand = 'HP'

"""

cursor.execute(update_laptops_query )

To see if the records are actually updated, execute the following SELECT statement.

import pandas as pd

select_query = "SELECT * FROM laptops"
data = pd.read_sql(select_query, my_con)
data.head()

The output below confirms that the prices of HP laptops have all been updated to 300.

Output:

Deleting Records

To delete a record, the DELETE query is passed to the execute() method of the cursor object. The following script deletes all the records of “Lenovo” brand from the laptops table.

delete_laptops_query = "DELETE laptops WHERE brand = 'Lenovo'"

cursor.execute(delete_laptops_query)

Let’s verify whether the records of laptops of Lenovo brand were actually deleted.

import pandas as pd

select_query = "SELECT * FROM laptops"
data = pd.read_sql(select_query, my_con)
data.head()

The output below shows that records of all the Lenovo branded laptops no longer exist.

Output:

Complete Code

The complete code for connecting and performing create, read, update and delete operation on a MS SQL Server database using the Python language is presented below to make it easier for you to get started with your own Python database project:

# Create a Connection with MS SQL Server

import pyodbc

conn_string = ('Driver={SQL Server}; Server=DESKTOP-IIBLKH1\SQLEXPRESS;  database=master; Trusted_Connection=yes;')

my_con = pyodbc.connect(conn_string,  autocommit = True)


# Create a Database on MS SQL Server
cursor = my_con.cursor()
query = "CREATE DATABASE laptop_db;"


cursor.execute(query)


# Create a Connection with laptops_db
conn_string = ('Driver={SQL Server}; Server=DESKTOP-IIBLKH1\SQLEXPRESS;  database=laptop_db; Trusted_Connection=yes;')
my_con = pyodbc.connect(conn_string,  autocommit = True)

# Create a Table in laptops_db

create_table_query = """
CREATE TABLE laptops (
    id int NOT NULL IDENTITY(1, 1),
    brand varchar(255) NOT NULL,
    model varchar(255) NOT NULL,
    price int,
    PRIMARY KEY (id)
);
"""


cursor = my_con.cursor()
cursor.execute(create_table_query)

# Insert records in laptops table

insert_records_query = """
INSERT INTO
   laptops (brand, model, price)
VALUES
  ('Dell', 'XX123', 500),
  ('Lenovo', 'ZZLEN', 400),
  ('HP', 'VZ341', 600),
  ('HP', 'GG222', 580),
  ('Dell', 'TTT22', 300)
"""

cursor.execute(insert_records_query)

# Select records from laptops table

import pandas as pd

select_query = "SELECT * FROM laptops"
data = pd.read_sql(select_query, my_con)
data.head()


# Update records in laptops table

update_laptops_query = """
UPDATE
  laptops
SET
  price = 300
WHERE
  brand = 'HP'

"""

cursor.execute(update_laptops_query )

# Delete records from laptops table

delete_laptops_query = "DELETE laptops WHERE brand = 'Lenovo'"

cursor.execute(delete_laptops_query)

import pandas as pd

select_query = "SELECT * FROM laptops"
data = pd.read_sql(select_query, my_con)
data.head()

This is just one sample of how you can use Python to interact with databases. We have several Python SQL tutorials, including

• how to import a SQL data query into a Pandas DataFrame and
• how to add and read database data with Python sqlite3.

We also have application-specific Python/SQL tutorials showing you how to

• copy data from Excel to a sqlite3 database with Python and
• convert sqlite data types for Python.

We find these tutorials really helpful, and we hope you do, too. For more tutorials like these, subscribe using the form below:

Get Our Python Developer Kit for Free

I put together a Python Developer Kit with over 100 pre-built Python scripts covering data structures, Pandas, NumPy, Seaborn, machine learning, file processing, web scraping and a whole lot more - and I want you to have it for free. Enter your email address below and I'll send a copy your way.