Explore the seamless integration of Python and MySQL databases through SQLAlchemy ORM. Learn how to leverage Python’s powerful tools and best practices for efficient data management, combining the simplicity of MySQL with the flexibility of SQLAlchemy for enhanced database operations.
• Python 3
• Python 3.12
Equipment list
Here you can find the list of equipment used to create this tutorial.
This link will also show the software list used to create this tutorial.
Related tutorial – Python
On this page, we offer quick access to a list of tutorials related to Python.
What is ORM?
ORM stands for Object-Relational Mapping. It is a programming technique that enables you to convert data between incompatible systems in object-oriented programming languages. In the context of databases, ORM is used to map objects to tables in a relational database management system.
Why use ORM in Python?
ORM in Python provides a convenient way to interact with databases by abstracting the underlying SQL statements and allowing developers to work with objects directly in their code. This simplifies database operations, enhances code portability, improves security by preventing SQL injection attacks, facilitates code maintenance by providing a clear and concise syntax for database queries, and enables easier unit testing by allowing objects to be manipulated in memory without the need for separate database setups.
What is SQLAlchemy?
SQLAlchemy is an open-source SQL toolkit and Object-Relational Mapping (ORM) library for the Python programming language. It provides a comprehensive set of tools for working with relational databases, allowing developers to interact with databases using Python objects. SQLAlchemy’s core provides a SQL expression language that allows the creation and execution of SQL statements, while its ORM facilitates the mapping of Python classes to database tables, enabling developers to work with database rows as objects.
Tutorial Python – MySQL database using SQLAlchemy ORM
Install the MySQL database.
In our example, we installed it on a computer running Linux.
Access the MySQL service as the ROOT user.
Create a database.
Create a user account.
Grant privileges over the database created to this user account.
The MY_PYTHON_USER account will be allowed to access the DRAGONBALLZ database.
Test the MySQL connection to the database using the new account.
On your desktop, install SQLAlchemy using PIP.
Install the required Python package to connect to the MySQL server.
Create a Python file.
Import modules and features from SQLAlchemy.
Establish a connection to the database.
The SQLAlchemy engine is used to establish a connection to the database.
The ECHO argument is optional and is used to display on the screen all SQL operations performed.
Create a base class to declare models.
Define a model for the table.
Create the tables in the database.
This creates a table in the connected MySQL database. It defines the table’s structure with five columns: ID as the primary key set to auto-increment, NAME, SPECIAL_MOVE, SS_LEVEL, and EYE_COLOR.
Before any interaction with the database, a session must be created.
Insert data in the table.
Save changes to the table.
Query all information stored in the table.
Display the information retrieved.
Here is the command output.
Display the information retrieved as a formatted string.
Here is the command output.
Optionally, query only for specific fields.
Here is the command output.
Close the connection to the database.
Here is the complete Python script.
Conclusion
Incorporating SQLAlchemy ORM simplifies MySQL database management in Python, enhancing data manipulation efficiency and offering a seamless object-oriented experience. Embrace robust data operations with ease.
