Work with SQL Server using Python and pyodbc

How to connect to SQL Server with a Python script and the pyodbc library to create a table, select, update and delete data?

Welcome to this tutorial on how to work with SQL Server databases using Python and pyodbc! In this course, you will learn how to use Python to interact with a SQL Server database. Whether you’re a beginner or an advanced user, this course will provide you with the knowledge and skills you need to work with SQL Server in Python. In the previous Python tutorial, we saw how to manage text files using Python and simple scripts.

1. Get started with Python and pyodbc

In this section, we will cover the basics of connecting to a SQL Server database using Python. We will use the pyodbc library. Pyodbc is a popular library, and it makes much easy to work with SQL Server databases using Python.

1.1 Install the pyodbc library

First, you will need to install the pyodbc library. You can do this by running the following command in your command prompt or terminal.

pip install pyodbc

1.2 Connect to the SQL Server database

Once the library is installed, import it in the Python script using the import function, and use it to create a connection to the SQL Server database. The script below performs these two operations.

• Import the pyodbc library
• Use the connect() method to create a connection to the SQL Server database, ans pass in the necessary information:
  • The SQL driver: {SQL Server}
  • The server name: localhost
  • Database name: Expert-Only
  • And if the connection should be trusted or not: yes

import pyodbc

connection = pyodbc.connect('Driver={SQL Server};'
                            'Server=server_name;'
                            'Database=database_name;'
                            'Trusted_Connection=yes;')

In the above script, we import the pyodbc library and use the connect() method to create a connection to the SQL Server database. We pass in the necessary information such as the server name, database name, and whether the connection should be trusted or not.

Manage text files in Python using read, open and write methods

Once you have a connection to the SQL Server database, you can use it to execute queries. In the next sections, we will cover how to execute different types of queries using Python.

2. Prepare the SQL Server table in Python

To work with SQL Server using Python, the most used database operations are SELECT, UPDATE and DELETE. To use them, prepare first the table and insert a few sample rows.

2.1 Create a SQL Server table in Python

First, import the pyodbc library and create the connection with the server, we then create a cursor object to execute the SQL query that creates the table customers, we then commit the transaction, and finally close the cursor and connection to the server. Below is a practical example of a Python connection string to connect to a SQL Server database.

import pyodbc

connection = pyodbc.connect('Driver={SQL Server};'
                            'Server=localhost;'
                            'Database=Expert-Only;'
                            'Trusted_Connection=yes;')

# Create a cursor object
cursor = connection.cursor()

# Create the customers table
create_table_query = """
CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] 
      PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
"""
cursor.execute(create_table_query)

# Commit the transaction
connection.commit()

# Close the cursor and the connection
cursor.close()
connection.close()

2.2 INSERT sample data using Python

Another common type of query is the INSERT query, which is used to insert data into a table. To execute an INSERT query, you can use the cursor() method to create a cursor object and then use the execute() method to execute the query.

In the following script, we use an INSERT INTO statement to insert 10 lines of data into the Customers table. We pass the values for the CustomerID, FirstName, LastName, City, and Country columns. The values are in the format (1, ‘Ali’, ‘Ahmed’, ‘Cairo’, ‘Egypt’). Make sure that the columns’ names and the data types match your table schema.

# Insert data into the customers table
insert_query = """
INSERT INTO Customers (CustomerID, FirstName, LastName, City, Country)
VALUES (1, 'Ali', 'Ahmed', 'Cairo', 'Egypt'),
       (2, 'Johnny', 'John', 'Toronto', 'Canada'),
       (3, 'John', 'Doe', 'Mexico City', 'Mexico'),
       (4, 'Shu', 'Abbas', 'Paris', 'France'),
       (5, 'Jeane', 'Raffin', 'Liushutun', 'China'),
       (6, 'Legra', 'Leate', 'Baszki', 'Poland'),
       (7, 'Sullivan', 'Goadby', 'Xiaoguwei', 'China'),
       (8, 'Tom', 'Ellams', 'Lop Buri', 'Thailand'),
       (9, 'Trace', 'Fowell', 'Sriwing', 'Indonesia'),
       (10, 'Christoffer', 'Rochford', 'Mburukullu', 'Indonesia')
"""
cursor.execute(insert_query)
print(cursor.rowcount, " rows inserted")

# Commit the changes to the database
connection.commit()

The script also displays the number of lines inserted in the table using the rowcount method.

3. SQL Server SELECT query with a Python script

The SELECT query is the most common type of query used to retrieve data from a table. To execute a SELECT query, you can use the cursor() method to create a cursor object and then the execute() method to execute the query. Once the query is executed, the fetchall() method can be used to retrieve all the rows returned by the query.
In this script, a cursor object is created and a SELECT query is executed on the Customers table. The fetchall() method is used to retrieve all rows from the query and store in the variable rows, then iterates through the rows and print the data. Finally, the cursor and connection are closed.

# Create a cursor object
cursor = connection.cursor()

# Execute the SELECT query to retrieve data from the Customers table
cursor.execute("SELECT * FROM Customers")

# Fetch all rows from the query
rows = cursor.fetchall()

# Iterate through the rows and print the data
for row in rows:
    print(row)

# Close the cursor and the connection
cursor.close()
connection.close()

Keep in mind that if you want to select only specific columns you can change the SELECT statement to SELECT CustomerID, lastname, firstname FROM Customers, for example.

4. MS SQL UPDATE query in Python

Another common type of query is the UPDATE query, which is used to update data in a table. To execute an UPDATE query, you can use the cursor() method to create a cursor object and then use the execute() method to execute the query.

As with all queries that perform a change in the data, it is important to always commit the transaction after executing an UPDATE query to save the changes in the table.

# Create a cursor object
cursor = connection.cursor()

# Execute the UPDATE query to update data in the Customers table
cursor.execute("UPDATE Customers SET City = 'New York' WHERE CustomerID = 1")

# Commit the changes to the database
connection.commit()

# Close the cursor and the connection
cursor.close()
connection.close()

This script creates a cursor object and executes an UPDATE query on the Customers table using the cursor.execute() method. The query updates the value of the City column to ‘New York’ where the CustomerID is 1. The connection.commit() method is used to commit the changes to the database. Finally, the cursor and connection are closed.
Of course, to update multiple columns at once, adapt the list of fields and change the WHERE clause to specify which rows to update by adding conditions.

Other useful scripts when it comes to manage tables is the Python lists data type.

Python lists

5. DELETE Queries

Another common type of query is the DELETE query, which is used to delete data from a table. To execute a DELETE query, you can use the cursor() method to create a cursor object and then use the execute() method to execute the query.

As with the INSERT and UPDATE SQL queries, it is important to always commit the transaction after the execution of a DELETE query to effectively save the changes in the table.

# Create a cursor object
cursor = connection.cursor()

# Execute the DELETE query to delete data from the Customers table
cursor.execute("DELETE FROM Customers WHERE CustomerID = 1")

# Commit the changes to the database
connection.commit()

# Close the cursor and the connection
cursor.close()
connection.close()

6. Conclusion on SQL Server and Python

In this course, we have covered the basic scripts and learned how to work with SQL Server using the Python pyodbc library and methods. For example, how to connect to a SQL Server database, how to execute different types of queries, and how to commit transactions. This is just the beginning of what you can do with Python and a SQL Server database.

With this knowledge, you can start building much more complex scripts and applications that can interact with SQL Server databases. I hope you enjoyed the tutorial and I encourage you to continue learning and experimenting with Python.

To go further and automate your code, use user-defined functions in Python to create tailored code.

User-defined functions in Python