Ben Richardson

Performing CRUD operations with a Python SQL Library for SQL Server

November 13, 2019 by

This article covers how to connect a Python application to Microsoft SQL Server using a 3rd party Python SQL library. The library that we are going to use is called “pyodbc”, which is freely available. We will use “pyodbc” to perform CRUD (Create Read Update and Delete) operations on a Microsoft SQL Server database.

Creating a dummy database

The first step is to create a dummy database. Execute at the following script:

The script above creates a database named “LibraryDB” in MS SQL Server.

We will create one table i.e. Books in the LibraryDB as shown in the following script:

The above script creates a table named “Books” in the “LibraryDB” database that we created earlier. The “Books” table contains three columns: Id, Name, and Price. The Id column is the primary key column and it cannot be NULL.

Working with PYODBC Python SQL Library

To work with the pyodbc library, you first need to install it. In Python environments, you can use pip installers to install Python libraries. Before you can run Python SQL queries on MS SQL Server, from within a Python application, execute the following command at the terminal to install pyodbc library:

Connecting with the Database

To execute Python SQL queries from a Python application, first, you need to make a connection to the MS SQL Server database. Execute the following script in your Python application:

In the above script, we first import the pyodbc module and then call the “connect()” method to connect to an MS SQL Server database.

For the Server parameter, you need to specify the name of your own MS SQL database server. Similarly, you will need to update the database name if you want to connect to a different database. The connect() method returns a connection object.

To execute queries, the “cursor()” object of the connection object is used. The following script stores the cursor object in the “cursor” variable:

Now is the time to perform CRUD operation on our database by executing Python SQL queries from within a Python application.

Creating records

There are two ways to insert records in a database via pyodbc library. You can insert a single record with the “execute()” method of the cursor object or you can insert multiple records via “executemany()” method.

Inserting a single record

Let’s first see how to insert a single record:

The INSERT query in pyodbc is similar to any other SQL query. However, for the database values in the VALUES clause, you can pass question marks.

To insert records, the query string is passed to the “execute()” method as a parameter along with the values to be inserted. The above script will insert a single record in the Books table of the LibraryDB database. It is important to mention that you have to call the “commit()” method of the connection object once you insert, update, or delete records from a database.

Inserting multiple records

The following script inserts multiple records in the Books table:

To insert multiple records, the “executemany()” method is used. The first parameter to the method is the INSERT query string, the second parameter is the list of records that you want to insert. In the script above, we create two lists i.e. record_1 and record_2. These two lists are then appended in another list named record_list, which is in turn passed as the second parameter to the “executemany()” method.

We have inserted a total of three records in the libraryDB dataset via Python SQL INSERT query. Let’s now see how we can select records.

Selecting records

To select records, the SELECT query is passed to the “execute()” method. The following script retrieves all the records from the Books table:

The SELECT query returns an iterator object where each item corresponds to a database record. The iterator object can be iterated and the records can be printed via a For loop. Once you execute the above script, you should see the following records in the output:

Result of SELECT query

You can see the three records that we inserted in the Books table, in the last section.

Updating records

To update records, again the “execute()” method of the cursor object can be used. After the execute() method, you need to call the “commit()” method of the connection object. The following query updates the price of the book whose Id is 1:

Now if you again select all the records from the Books table, you will see the following output:

Using Python SQL to update records

You can see that the price of the book with Id 1 has been updated to 400.

Deleting records

To delete records from an MS SQL Server database, using a Python SQL DELETE query from a Python application, you can use the following script:

The above script deletes all the records from the Books table, where Id is 1.

To delete records, again you can use the “execute()” method of the cursor object. The query for deleting records is passed as a parameter to the “execute()” method. You need to call the “commit()” method, once the records are deleted.

Now, if you select all the records from the Books table, you should see the following output:

Screenshot of deleting from a table.

You can see that the record with Id 1 has been deleted.

Conclusion

Python SQL queries are used to interact with different database servers from within a Python application. This article explains how to execute Python SQL queries using pyodbc library, in order to interact with Microsoft SQL Server Database.

Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson
Ben Richardson
396 Views