CODEWITHSUNDEEP
pythonsqlalchemymariadbapple-m1nas
yts61
yts61

Reputation: 1619

How to connect MariaDB database on Synology NAS from SQLalchemy in python?

I would like to connect to my MariaDB10 database in a Synology NAS using python SQLalchemy. I installed PhpMyAdmin, and created a database named "test", and a random table called "company". I inserted a few rows of dummy data in the table already through the interface. Here is a snapshot of it. enter image description here

My code is like this:

# Module Imports
import sqlalchemy
import pymysql
from sqlalchemy.ext.declarative import declarative_base
import config_maria_us

# Define the MariaDB engine using MariaDB Connector/Python
user = "username"
passwd = "password"
host = "192.168.1.111"
db = "test"
port= "3307"

engine = sqlalchemy.create_engine(f'mysql+pymysql://{user}:{passwd}@{host}:{port}/{db}')

sql_df = engine.execute("SELECT * FROM company" ).fetchall()

But this returns an error:

OperationalError: (2003, "Can't connect to MySQL server on '192.168.1.111' ([Errno 61] Connection refused)")

Because of this page, so I keep using create_engine("mysql+pymysql:. It says to connect to a MariaDB database, no changes to the database URL are required.

I followed this page, and tried to install mariadb SQLAlchemy by brew install mariadb SQLAlchemy. But it shows a warning Warning: No available formula with the name "sqlalchemy". Did you mean sqlancer?

Then I ofcourse installed MariaDB Connector/C (by following this page) with brew install mariadb-connector-cand installed PyMySQL with pip install PyMySQL. Actually, to start with, i tried to installed mariadb with brew install mariadb, but after loading a pile of things, it shows failure,

Error: Cannot install mariadb because conflicting formulae are installed.
  mysql: because mariadb, mysql, and percona install the same binaries

Please `brew unlink mysql` before continuing.

Unlinking removes a formula's symlinks from /opt/homebrew. You can
link the formula again after the install finishes. You can --force this
install, but the build may fail or cause obscure side effects in the
resulting software.

I did not go on installing it, because i don't know how to "relink" MySQL after the unlink.

That's pretty much it, would anyone please tell me what to do? by running the "engine = ..." syntax, it looks like i at least reached my server, but it still fail to connect as '(pymysql.err.OperationalError) (2003, "Can't connect to MySQL server'

Upvotes: 0

Views: 1867

Answers (1)

Jansen
Jansen

Reputation: 139

OP probably resolved by himself/herself but in case someone else still face the similar issue. In my case, after following steps, I can have access from python script to mariadb hosted in NAS.

  1. Make sure MariaDB turn on TCP/IP Connection

  2. Make sure your username from working machine IP has permission to the database. You can set this up by

    GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

Upvotes: 0

Related Questions