Python Database connection for mariadb using sqlalchemy

Question

I am new to python. I am trying to make a database connection using python to mariadb database which is hosted on my local network. I am using sqlalchmemy to make a connection. But facing some errors this is my code

from sqlalchemy import create_engine
engine = create_engine('MariaDBDialect://username:password@host:port/databasename')

The error I am getting is

`Can't load plugin: sqlalchemy.dialects:MariaDBDialect`

If anyone knows what I am doing wrong please let me know. Thanks in advance.

Answer 1

Just use mysql+mysqldb instead of MariaDB engine, they work pretty much similar.

create_engine('mysql+mysqldb://username:password@host:port/databasename')

Update: You also should install mysql-python for python2

pip install mysql-python

Or mysqlclient for python3:

pip install mysqlclient

Answer 2

Starting from SQLAlchemy 1.4.0b1 you can use mariadb dialect and and native mariadb connector. Use the following url scheme: 'mariadb+mariadbconnector://'.

Prerequisites:

• SQLAlchemy 1.4.0b1 could be installed through pip install --pre sqlalchemy
• MariaDB Connector/Python should be also installed. It has its own system dependency, MariaDB Connector/C.

Answer 3

As Stepan mentioned, the MariaDB dialect is now directly supported within SQLAlchemy, which allows you to use the official MariaDB Python connector.

For example:

import sqlalchemy

# Define the MariaDB engine using MariaDB Connector/Python
engine = sqlalchemy.create_engine("mariadb+mariadbconnector://app_user:[email protected]:3306/database_name")

For more details on this you can check out an official MariaDB blog post on the subject here -> https://mariadb.com/resources/blog/using-sqlalchemy-with-mariadb-connector-python-part-1/

And also a deeper dive into setting up object relationships in part 2 -> https://mariadb.com/resources/blog/using-sqlalchemy-with-mariadb-connector-python-part-2/

Answer 4

I was just confused by this topic and created a short blog post about connector strings.

The following solution works for Python 3:

requirements

pip install SQLAlchemy
pip install PyMySQL

Code

import sqlalchemy

SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://user:password@host/dbname'

# Test if it works
engine = sqlalchemy.create_engine(SQLALCHEMY_DATABASE_URI, echo=True)
print(engine.table_names())

Yes, it really is "mysql" and not "mariadb".