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.
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.
Before jumping into code, you’ll need to make sure you have a few things on your machine:
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
- 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
pip executables into your shell’s
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
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 sqlalchemy from 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/Python engine = sqlalchemy.create_engine("mariadb+mariadbconnector://app_user:Password123email@example.com: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
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()
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()
Once you’ve gotten data into your
employees table you can select it by using the
query() method that’s available on the
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.
A simple way to update a table record by first selecting a mapped object, using the previously mentioned
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 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.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 sqlalchemy from sqlalchemy.ext.declarative import declarative_base # Define the MariaDB engine using MariaDB Connector/Python engine = sqlalchemy.create_engine("mariadb+mariadbconnector://app_user:Password123firstname.lastname@example.org: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 session Session = 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 employees addEmployee("Bruce", "Wayne") addEmployee("Diana", "Prince") addEmployee("Clark", "Kent") # Show all employees print('All Employees') selectAll() print("----------------") # Update employee status updateEmployeeStatus(2,False) # Show active employees print('Active Employees') selectByStatus(True) print("----------------") # Delete employee deleteEmployee(1) # Show all employees print('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’ll dive into how you can create and manage object relationships using SQLAlchemy. Stay tuned!