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.

Martin Thoma · Accepted Answer · 2019-06-02 18:56:39Z

27

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

answered Jun 2, 2019 at 18:56

Martin Thoma

139k174 gold badges687 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shashank Over a year ago

I started using pymysql which was super easy to make a connection @Martin Thoma .

Maximilian Burszley Over a year ago

As of the past month, the mariadb org has released a native mariadb connector package so you don't need to use pymysql anymore. It is DBAPI compliant and can be used as a drop-in replacement.

Volodymyr Vyshnevskyi · Accepted Answer · 2019-02-22 19:59:14Z

8

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

edited Feb 22, 2019 at 19:59

answered Feb 22, 2019 at 19:46

Volodymyr Vyshnevskyi

3361 silver badge11 bronze badges

6 Comments

shashank Over a year ago

If my running using this I am getting this error ModuleNotFoundError: No module named 'MySQLdb'

Volodymyr Vyshnevskyi Over a year ago

then just run 'pip install mysqlclient' for python3 or 'pip install mysql-python' for python2. this will install all the dependencies

shashank Over a year ago

tried pip install mysqlclient but got this error Command "python setup.py egg_info" failed with error code 1 in /private/tmp/pip-install-Xhd1Qu/mysqlclient/

Volodymyr Vyshnevskyi Over a year ago

your python's setuptools are probably out of date. please try running 'pip install --upgrade setuptools'

shashank Over a year ago

no luck even after upgrading the tools same error as above

|

Stepan Bogoslovskiy · Accepted Answer · 2020-11-03 07:43:39Z

4

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.

answered Nov 3, 2020 at 7:43

Stepan Bogoslovskiy

414 bronze badges

Comments

Robert Hedgpeth · Accepted Answer · 2021-07-28 15:07:04Z

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/

answered Jul 28, 2021 at 15:07

Robert Hedgpeth

1112 bronze badges

Collectives™ on Stack Overflow

Python Database connection for mariadb using sqlalchemy

4 Answers 4

requirements

Code

2 Comments

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

requirements

Code

2 Comments

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related