macos BigSur 11.5.1 (MacBook M1),
mysql Ver 15.1 Distrib 10.6.4-MariaDB, for osx10.16 (arm64) using readline 5.1

Did you follow the instructions from MariaDB Connector/Python documentation and installed Connector/C ?

Yes, I think I have followed all the instructions:
1. For Posix systems: TLS libraries like GnuTLS or OpenSSL (default)
2. On Posix systems, ensure that the path environment variable contains the directory containing the mariadb_config utility.
(Screenshot attached)

In the code, python directly added the path to the folder, however, the error repeats

import sys
sys.path.insert (0, '/opt/homebrew/Cellar/mariadb/10.6.4/bin/mariadb_config/')
print (sys.path)
import mariadb

It looks like mariadb module can't load Connector/C.
Can you please post output of
otool -L /Users/bolkhovskiydmitriy/Library/Python/3.8/lib/python/site-packages/mariadb/_mariadb.cpython-38-darwin.so

Just checked installation on a MacBox:

admin@DF188 georg % sw_vers

ProductName:    macOS

ProductVersion: 11.6

BuildVersion:   20G165

admin@DF188 georg % which mariadb_config

/usr/local/bin/mariadb_config

admin@DF188 georg % mariadb_config --libs

-L/usr/local/Cellar/mariadb/10.6.4/lib/ -lmariadb

pip3 install mariadb --no-cache-dir

Defaulting to user installation because normal site-packages is not writeable

Collecting mariadb

  Downloading mariadb-1.0.8.zip (85 kB)

      85 kB 19.8 MB/s 

Building wheels for collected packages: mariadb

  Building wheel for mariadb (setup.py) ... done

  Created wheel for mariadb: filename=mariadb-1.0.8-cp38-cp38-macosx_10_14_x86_64.whl size=104494 sha256=b1032cafff91b400f7d263796f9b712bc899d912c7fd9724ad5a599a1e19612f

  Stored in directory: /private/var/folders/dl/z8g99nn12jz558kt4vrcy9rw0000gp/T/pip-ephem-wheel-cache-hqqnu2w7/wheels/70/ff/d4/de616aada9ea90c878444d0d7e482ac9f5c9e45449bd3825e7

Successfully built mariadb

Installing collected packages: mariadb

Successfully installed mariadb-1.0.8

WARNING: You are using pip version 20.2.3; however, version 21.3.1 is available.

You should consider upgrading via the '/Applications/Xcode.app/Contents/Developer/usr/bin/python3 -m pip install --upgrade pip' command.

admin@DF188 georg % python3 -c "import mariadb; print(mariadb.__version__)"

1.0.8

MariaDB solving problem

Last login: Fri Dec 3 14:10:53 on ttys000
bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % otool -L /Users/bolkhovskiydmitriy/Library/Python/3.8/lib/python/site-packages/mariadb/_mariadb.cpython-38-darwin.so
error: /Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/otool-classic: can't open file: /Users/bolkhovskiydmitriy/Library/Python/3.8/lib/python/site-packages/mariadb/_mariadb.cpython-38-darwin.so (No such file or directory)
bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % georg % sw_vers
zsh: command not found: georg
bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % sw_vers
ProductName: macOS
ProductVersion: 11.5.1
BuildVersion: 20G80

bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % which mariadb_config
/opt/homebrew/bin/mariadb_config

bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % mariadb_config --libs
-L/opt/homebrew/Cellar/mariadb/10.6.4/lib/ -lmariadb

bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % pip3 install mariadb --no-cache-dir
Requirement already satisfied: mariadb in /Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages (1.0.8)
WARNING: You are using pip version 21.2.3; however, version 21.3.1 is available.
You should consider upgrading via the '/Library/Frameworks/Python.framework/Versions/3.10/bin/python3.10 -m pip install --upgrade pip' command.

I decided to uninstall mariadb:

bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % pip3 uninstall mariadb
Found existing installation: mariadb 1.0.8
Uninstalling mariadb-1.0.8:
Would remove:
/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/mariadb-1.0.8-py3.10.egg-info
/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/mariadb/*
Proceed (Y/n)? y
Successfully uninstalled mariadb-1.0.8
bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % pip3 uninstall mariadb
WARNING: Skipping mariadb as it is not installed.
bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % pip3 install mariadb --no-cache-dir
Collecting mariadb
Downloading mariadb-1.0.8.zip (85 kB)

bolkhovskiydmitriy@MBP-Bolkhovskiy ~ % python3 -c "import mariadb; print(mariadb._version_)"
1.0.8

Then I created a similar script and ran it from the command line. Everything worked !!!

bolkhovskiydmitriy@MBP-Bolkhovskiy experiments % python3 mariadb_exp.py
1.0.8

But when I run the same script directly from PyCharm - it gives the same error

PyCharm 2021.2.3 (Community Edition)
Build #PC-212.5457.59, built on October 19, 2021
Runtime version: 11.0.12+7-b1504.40 aarch64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
macOS 11.5.1
GC: G1 Young Generation, G1 Old Generation
Memory: 2048M
Cores: 8

____________-
ERROR

/usr/local/bin/python3.8 /Users/bolkhovskiydmitriy/PythonData/aVision/CamGroup02/experiments/mariadb_exp.py
Traceback (most recent call last):
File "/Users/bolkhovskiydmitriy/PythonData/aVision/CamGroup02/experiments/mariadb_exp.py", line 1, in <module>
import mariadb
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mariadb/_init_.py", line 10, in <module>
from ._mariadb import (
ImportError: dlopen(/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mariadb/_mariadb.cpython-38-darwin.so, 2): Symbol not found: _mysql_ps_fetch_functions
Referenced from: /Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mariadb/_mariadb.cpython-38-darwin.so
Expected in: flat namespace
in /Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mariadb/_mariadb.cpython-38-darwin.so

Process finished with exit code 1

It turned out python 3.8 is not friendly with macOS m1 arm. Installed python 3.10 - everything worked. Problem solved, thanks a lot!