---
title: "Using SQLAlchemy with MariaDB Connector/Python: Part 1"
publish_date: 2021-03-23
updated_date: 2024-03-19
author: "Rob Hedgpeth"
channel:
  - name: "Developer"
    url: "/ja/resources/blog/channel/developer.md"
tags:
  - name: "MariaDB Connector/C"
    url: "/resources/blog/tag/mariadb-connector-c.md"
  - name: "ORM"
    url: "/resources/blog/tag/orm.md"
  - name: "Python"
    url: "/resources/blog/tag/python.md"
  - name: "SQL"
    url: "/resources/blog/tag/sql.md"
---

# Using SQLAlchemy with MariaDB Connector/Python: Part 1

Last year we released the new MariaDB Python connector and published a [blog post](https://staging-mdb.com/resources/blog/how-to-connect-python-programs-to-mariadb/) 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](https://github.com/mariadb-corporation/dev-example-blog-samples/tree/main/mariadb_python_sqlalchemy/part_1).

### 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](https://www.sqlalchemy.org/), 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](https://staging-mdb.com/products/skysql/docs/clients/mariadb-connector-c-for-skysql-services/)
- [Python (version 3+)](https://www.python.org/downloads/)

### 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.

1. Download and install MariaDB Community Server 
    1. [Directly](https://staging-mdb.com/downloads/#mariadb_platform-mariadb_server)
    2. [Using a Docker image](https://hub.docker.com/r/mariadb/server)
2. [Download and install MariaDB Enterprise Server](https://staging-mdb.com/downloads/#mariadb_platform-enterprise_server)
3. [Deploy with MariaDB SkySQL, MariaDB’s database-as-a-service (DBaaS)](https://staging-mdb.com/products/skysql/)

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](https://docs.python.org/3/library/venv.html#venv-def), 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)](https://pypi.org/).

```
$ 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 sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
```

To connect to MariaDB using SQLAlchemy you’ll need to create a new [engine](https://docs.sqlalchemy.org/en/14/core/engines.html), 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: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](https://docs.sqlalchemy.org/en/14/core/engines.html#engine-creation-api).

### Mapping to data

SQLAlchemy features two styles of being able to configure mappings between Python classes and a database table. The “[Classical](https://docs.sqlalchemy.org/en/13/orm/mapping_styles.html#classical-mappings)” style is SQLAlchemy’s original mapping API, whereas “[Declarative](https://docs.sqlalchemy.org/en/13/orm/mapping_styles.html#declarative-mapping)” 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](https://docs.sqlalchemy.org/en/13/orm/tutorial.html#querying).

**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](https://github.com/mariadb-corporation/dev-example-blog-samples/blob/main/mariadb_python_sqlalchemy/part_1/employees.py).

**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: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 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](https://staging-mdb.com/resources/blog/using-sqlalchemy-with-mariadb-connector-python-part-2/), we dive into how you can create and manage object relationships using SQLAlchemy.