Last year we released the new MariaDB Python connector and published a blog post on how to get started using it in your applications. Based on the overwhelmingly positive response that MariaDB Connector/Python has received, we’re going to develop additional tutorials and sample code to help showcase how easy it is for you to use MariaDB Connector/Python within your solutions.
Python is a very powerful and versatile programming language, capable of being used in a large variety of applications, solutions, and integrations. Ultimately, that’s one of the reasons why the Python language has become so popular.
In this blog, we’ll walk through the process for creating a Python application that uses MariaDB Connector/Python and SQLAlchemy to connect to and communicate with a MariaDB database instance.
For those of you that prefer to jump directly into the code you can find all of the source code for this walkthrough here.
Object-Relational Mapping
It goes without saying that creating new applications is a daunting task. Integrating an application with a backing database, like MariaDB, can involve a significant amount of effort to plan and manage interactions like accessing, querying, and persisting data. For some, object-relational mapping (ORM) tools and libraries help to offload boilerplate code for things like data access and object persistence.
In this walkthrough we’ll be focusing on a library called SQLAlchemy, which is a Python SQL toolkit and object relational mapper that gives application developers the full power and flexibility of SQL.
Recently MariaDB engineers submitted pull requests (PR) to the SQLAlchemy repository to add support for the MariaDB dialect. Now that the PR has been accepted and merged, SQLAlchemy v.1.4+ includes support that enables developers to use MariaDB Connector/Python as the underlying driver.
Requirements
Before jumping into code, you’ll need to make sure you have a few things on your machine:
- MariaDB Connector/C
- Python (version 3+)
Downloading and Installing
In order to use MariaDB Connector/Python you’ll need to have access to an instance of MariaDB Server. There are a variety of ways you can get started with MariaDB on your local machine, on-premises, or even in the cloud.
- Download and install MariaDB Community Server
- Directly
- Using a Docker image
- Download and install MariaDB Enterprise Server
- Deploy with MariaDB SkySQL, MariaDB’s database-as-a-service (DBaaS)
Once you’ve downloaded, installed, and gained access to a MariaDB database instance you’ll need to add the company database that we use for the walkthrough.
Using a client of your choice connect to your MariaDB instance and execute the following statement:
CREATE DATABASE company;
Preparing a Python Environment
After establishing access to MariaDB Server, it’s time to create a new Python application. However, before you can do that you’ll need to have all the necessary PyPi packages installed.
To keep things simple you can start by setting up a new virtual environment, which is simply a directory tree that contains Python executable files and other files which indicate that it is a self contained environment.
$ python3 -m venv venv
Before you can start installing or using packages in your virtual environment, you’ll need to activate it. Activating a virtual environment will put the virtual environment-specific python and pip executables into your shell’s PATH.
Activate the virtual environment using the following command:
$ . venv/bin/activate activate
Finally, install the Maria Connector/Python and SQLAlchemy packages from the Python Package Index (PyPi).
$ pip3 install mariadb SQLAlchemy
Connecting to MariaDB Server with Python and SQLAlchemy
Now that a Python virtual environment has been set up, and the necessary packages have been installed, you’re ready to start creating a new application. For this tutorial, and the sake of simplicity, we’ll keep everything within a single file called tasks.py.
Start by creating a new Python file.
$ touch api.py
Next, using a code editor of your choice, open api.py and add the following import statements.
import sqlalchemyfrom sqlalchemy.ext.declarative import declarative_base
To connect to MariaDB using SQLAlchemy you’ll need to create a new engine, which uses database connection configuration information to connect to and communicate with an underlying database.
# Define the MariaDB engine using MariaDB Connector/Pythonengine = sqlalchemy.create_engine("mariadb+mariadbconnector://app_user:Password123!@127.0.0.1:3306/company")In the preceding engine declaration, the following connection configuration information is indicated:
- User*: app_user
- Password*: Password123!
- Host: 127.0.0.1 (localhost)
- Port: 3306
- Default database: company
* This walkthrough assumes certain credentials, but can be configured to your specific test environment.
For more information on the SQLAlchemy engine capabilities please refer to the official documentation.
Mapping to data
SQLAlchemy features two styles of being able to configure mappings between Python classes and a database table. The “Classical” style is SQLAlchemy’s original mapping API, whereas “Declarative” is the richer and more succinct system that builds on top of “Classical”. It’s important to note that both styles can be used interchangeably, as the end result of each is exactly the same, but for the purposes of this walkthrough you’ll be implementing the “Declarative” approach.
In a declarative mapping configuration the components of the user-defined class as well as the database table metadata to which the class is mapped are defined at once.
Base = declarative_base()class Employee(Base): __tablename__ = 'employees' id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True) first_name = sqlalchemy.Column(sqlalchemy.String(length=100)) last_name = sqlalchemy.Column(sqlalchemy.String(length=100)) active = sqlalchemy.Column(sqlalchemy.Boolean, default=True)
One of the neat things about many object-relational mapping libraries is that they give you the ability to create database entities, like tables, using the mapped class. In SQLAlchemy you can do so by calling the create_all method.
Base.metadata.create_all(engine)
Working with data
Creating new session
To start communicating with a MariaDB database you’ll first need to create a new Session object using SQLAlchemy’s sessionmaker functionality.
Session = sqlalchemy.orm.sessionmaker()Session.configure(bind=engine)Session = Session()
Inserting data
Adding a new employee to the employees table is done by creating a new Employee object, adding it to the existing SQLAlchemy session, and then committing the session.
newEmployee = Employee(firstname=”Rob”, lastname=”Hedgpeth”)session.add(newEmployee)session.commit()
Selecting data
Once you’ve gotten data into your employees table you can select it by using the query() method that’s available on the Session object.
You can use a Query object, which is returned by the query() method to return the entire set of data that exists in your table
employees = session.query(Employee).all()
You can specify a particular record based the primary key
employee = session.query(Employee).get(1)
Or even use the filter_on() method to target specific characteristics of a mapped object
employee = session.query(Employee).filter_on(firstname=”Rob”)
And a heck of a lot more as well.
For more information on the Query object capabilities be sure to check out the official SQLAlchemy documentation.
Updating data
A simple way to update a table record by first selecting a mapped object, using the previously mentioned Query object.
employee = session.query(Employee).get(1)
Then you can modify the mapped properties, and commit the session to update the database record.
employee.firstname = “Robert”session.commit()
Deleting data
Deleting mapped objects is as easy as specifying the records you want to delete, through the use of the filter() method, and then calling the delete(). Then simply commit the transaction by calling the commit() method on the Session object.
session.query(Employee).filter(Employee.id == id).delete()session.commit()
Putting it all together
The following code snippet brings everything together in one concise example. To test it out just copy and paste the code block into your employee.py file. You can also find the code here.
The Full Source
import sqlalchemyfrom sqlalchemy.ext.declarative import declarative_base# Define the MariaDB engine using MariaDB Connector/Pythonengine = sqlalchemy.create_engine("mariadb+mariadbconnector://app_user:Password123!@127.0.0.1:3306/company")Base = declarative_base()class Employee(Base): __tablename__ = 'employees' id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True) firstname = sqlalchemy.Column(sqlalchemy.String(length=100)) lastname = sqlalchemy.Column(sqlalchemy.String(length=100)) active = sqlalchemy.Column(sqlalchemy.Boolean, default=True)Base.metadata.create_all(engine)# Create a sessionSession = sqlalchemy.orm.sessionmaker()Session.configure(bind=engine)session = Session()def addEmployee(firstName,lastName): newEmployee = Employee(firstname=firstName, lastname=lastName) session.add(newEmployee) session.commit()def selectAll(): employees = session.query(Employee).all() for employee in employees: print(" - " + employee.firstname + ' ' + employee.lastname)def selectByStatus(isActive): employees = session.query(Employee).filter_by(active=isActive) for employee in employees: print(" - " + employee.firstname + ' ' + employee.lastname)def updateEmployeeStatus(id, isActive): employee = session.query(Employee).get(id) employee.active = isActive session.commit()def deleteEmployee(id): session.query(Employee).filter(Employee.id == id).delete() session.commit()# Add some new employeesaddEmployee("Bruce", "Wayne")addEmployee("Diana", "Prince")addEmployee("Clark", "Kent")# Show all employeesprint('All Employees')selectAll()print("----------------")# Update employee statusupdateEmployeeStatus(2,False)# Show active employeesprint('Active Employees')selectByStatus(True)print("----------------")# Delete employeedeleteEmployee(1)# Show all employeesprint('All Employees')selectAll()print("----------------")Run the application
To execute the code in employees.py just open a terminal window, navigate to the location of your employees.py file and execute the following:
$ python3 employees.py
The Tip of the Iceberg
Hopefully this walkthrough has helped you take your first steps into the wonderful world of creating applications with MariaDB Connector/Python and SQLAlchemy.
While it’s crucial to get a basic understanding of SQLAlchemy we’ve really only scratched the surface of what it’s capable of. In the second part of this tutorial, we dive into how you can create and manage object relationships using SQLAlchemy.
