Using SQLAlchemy to work with Databases vs SQL Server Management Studio

By: Levi Masonde   |   Updated: 2024-03-29   |   Comments   |   Related: > Python

Problem

If you develop applications that rely on multiple SQL or other databases on different hosts, you might find it challenging to integrate all the data sources seamlessly, which means having a uniform flow of data to and from your application.

The more you create applications and collaborate with other developers and their data sources, the more difficult it will be to integrate all the data sources into your applications. You might need different forms of authentication on the same application or maybe interact with other databases in one functional action provided by the application. For instance, an application that verifies a user's physical ID card by scanning it and comparing it against an external state-owned database.

Although you can use SQL Server Management System (SSMS) to manage databases across multiple servers, SSMS mainly allows you to interact with your databases graphically. Also, if you want to run complex operations on your databases for your application using SSMS, you will need to write complex T-SQL scripts to carry out operations like automation and custom alerts. Is there another way to interact with databases programmatically?

Solution

Instead of manually interacting with your application databases or writing T-SQL scripts to handle all the operational queries from your application, you can use SQLAlchemy to interact with your databases programmatically. SQLAlchemy is a high-level Object-Relational Mapping (ORM) library made for Python. It enables you to interact with multiple databases concurrently using asyncio's async/await method. This ORM also allows you to write RAW SQL queries in your Python script seamlessly using the Bindparam function.

SQLAlchemy is ideal for managing multiple databases programmatically, and it is even better if you work with Python-based applications. It can seamlessly integrate your Python applications with the databases. Since SQLAlchemy is an ORM, you can map your database tables and data to Python classes and objects. This feature allows you to interact naturally and effectively with your databases, especially when you run complex queries or operations against the databases for your applications.

This tip will discuss why SQLAlchemy is important for developers creating applications using Python or any compatible language.

What is SQLAlchemy?

SQLAlchemy is a Python library created to work with relational databases like PostgreSQL, MySQL, MariaDB, SQLite, Oracle, Microsoft SQL Server, and many more external dialect projects. The library offers an ORM tool that serves as a top layer on SQL, allowing Python code to interact with databases as objects. SQLAlchemy also enables you to execute queries on an SQL database using APIs.

SSMS and Native SQL Tools

Pros

• Microsoft Ecosystem: Using SSMS and native SQL tools enables you to take full advantage of the Microsoft ecosystem. This makes integrating your databases with Microsoft applications easier and is great when working with an organization that uses Microsoft apps.
• Security: Native tools have built-in security measures at every level of access.
• Specialized Tools: Tools like SQL Server Profiler can help you monitor your server's performance.
• Roles: You can easily share the server with other users and control access using built-in role permissions.
• User Interface: The above-mentioned pros have a nice UI, making it easier to work with the tools.

Cons

• Microsoft Dependency: Working seamlessly occurs only when using the Microsoft SQL Server Platform; using SQL Server databases cross-platform is challenging. Although you can manage multiple databases such as MySQL, PostgreSQL, and Cosmos DB using Azure Data Studio, you would be hindered from using many features designed to manage your databases, as shown below:

• SSMS Resources: SSMS can use up plenty of PC resources. It uses up memory creating logs automatically and consumes a lot of CPU to run complex queries.
• Cross-platform: Most tools are made for Windows, including SSMS. This makes it hard to carry the same workflow if you want to work with the same server on a Linux machine. However, Azure Data Studio can work cross-platform, as discussed above. This introduces some constraints to your database management and administration features.
• Interaction style: Although the SSMS UI is a great platform to interact with your databases for both technical and non-technical users, it can be limiting when performing niche operations on your databases, such as reading data from a flat file or writing/updating data to a flat file.
• Learning Curve: To run complex scripts, such as automation scripts, you must write them in T-SQL. If you are unfamiliar with T-SQL, you might find it has a long learning curve. This won't only extend the time it takes to complete your projects but also makes your scripts prone to errors and makes it harder to debug your applications and database operations. Furthermore, if you want to run administrative tasks in Azure Data Studio, it is recommended that you do this via the integrated terminal using SQLCMD or PowerShell.

When to Use SSMS and SQL Native Tools

It would be ideal to use SSMS and SQL tools when working on projects for an organization that uses the Microsoft ecosystem and when sharing servers with a team with different roles. SSMS makes database administration easy for both technical and non-technical users. Microsoft's Azure also allows you to scale your databases on the cloud. This is ideal if you want to use cloud storage and services for your projects. So, if you are looking to manage your organization's databases from a user-friendly GUI with ready-to-go functionality, you should consider sticking with SSMS and other native tools. SQLAlchemy is not meant to replace this purpose but is mainly to enable developers to have a simple way to interact with databases when developing applications.

SQLAlchemy

Pros

• Pythonic Style: If you are a Python developer, this is like a dream come true. Using Pythonic code, you can interact with your databases and sprinkle in some T-SQL to help you execute queries.
• Core: SQLAlchemy has a Core component that houses a full SQL toolkit. The Core also provides an SQL Expression Language, which allows the expression of the SQL language via generative Python expressions.
• Workflow: SQLAlchemy lets you fit all your database interactions into your project file, making the whole project flow naturally and intuitively for a Python developer.
• Minimum Resources Usage: If you already have VS code installed, you only need to install the SQLAlchemy library and are ready to go. T-SQL queries are run using an API; there's no need for graphics and memory usage.
• Cross-platform Databases: It supports different databases, making your project flexible on backend integrations.
• ORM: SQLAlchemy creates an abstract layer between SQL and Python. This enables you to interact with the databases using Python classes, which makes data manipulation simple and straightforward for a developer. SQLAlchemy allows you to interact with your database tables like Python objects.
• Code Reusability: SQLAlchemy's API enables developers to have reusable code across different databases, making the code easy to maintain across various projects.
• Documentation: SQLAlchemy has a fantastic documentation page. Even if it is your first time on the page, you will appreciate the Pythonic style of all their code. The page covers all the functionality with working examples.

Cons

• Security: SSMS is designed to simplify security with default graphics for access control, permissions, and user management. Using SQLAlchemy, you have to write code to manage these security issues.
• User Interface: There is no UI when working with SQLAlchemy. This might not be a problem for developers who are used to working with the console, but it is not ideal for a developer or system admin who is comfortable using user interfaces to interact with databases. Code also needs to be written for the simplest operations that could have been accomplished by clicking a button on SSMS.
• Performance: Since SQLAlchemy has an abstraction layer, SSMS has a slight advantage when performing certain operations. SSMS uses raw SQL, allowing you to interact directly with your databases.

When to Use SQLAlchemy

You should use SQLAlchemy if you are building applications that interact with SQL databases using Python or looking to manage multiple SQL databases programmatically to help you develop scripts for repetitive tasks and work with your database tables as objects.

You can also use SQLAlchemy when working alone or with a small team of people planning to have an elegant Python project with a simple, seamless workflow. SQLAlchemy is also great when you create a project that barely takes in any human input and relies mostly on APIs and integrating different API systems. Python's web frameworks, like Flask and Django, can be integrated with SQLAlchemy to communicate smoothly with the application's database. SQLAlchemy is database-agnostic, which enables you to write similar code to interact with different databases. With SQLAlchemy, you can power applications that interact with SQL, MySQL, PostgreSQL, and more databases seamlessly.

Since SQLAlchemy enables you to interact with your databases using Python classes, you can easily integrate your databases with other classes or functions in your code.

Conclusion

Most applications require the use of multiple databases scattered across different hosts. When you create applications using Python, it is important to ensure that how you interact with your databases fits into the ecosystem setup around the application. This allows your application to run smoothly and makes it easy to maintain. Consider all the points above before deciding to use SQLAlchemy or if you need SSMS and other SQL native tools, like Azure Data Studio, for your project.

Next Steps

• Learn how to execute Python scripts in SSMS.
• Run Python Scripts in a SQL Server Agent Job
• Create SQL Server Notebooks in Azure Data Studio
• Learn how to execute Python scripts in SSMS.
• Azure Data Studio Step By Step Tutorial
• Introduction to SQL Server's sqlcmd utility

About the author

Levi Masonde is a developer passionate about analyzing large datasets and creating useful information from these data. He is proficient in Python, ReactJS, and Power Platform applications. He is responsible for creating applications and managing databases as well as a lifetime student of programming and enjoys learning new technologies and how to utilize and share what he learns.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips