how to connect to multiple MySQL DB of same host

[nagarajgond]

Hi,
I was trying to connect to 2 MySQL DB of same host, I couldn't find any solutions.
I tried with SQLALCHEMY_BINDS with bind_key but it always connects to localhost.

SQLALCHEMY_DATABASE_URI = 'mysql://user:password@host:3306/DB1'
SQLALCHEMY_BINDS = {
'mysql://user:password@host:3306/DB2'
}

[dkellner]

Can you check if it works by using Flask-SQLAlchemy without Eve-SQLAlchemy? It might be connected to the way we initialize the Base-Model for Flask-SQLAlchemy. Are you using a plain SQLAlchemy declarative base like in our examples? As in https://github.com/pyeve/eve-sqlalchemy/blob/master/examples/tables.py#L10 .

[nagarajgond]

I was able to connect to multiple db instance of same host in eve-sqlalchemy itself using sqlalchemy_uri. Let me know if we can query multiple tables in eve. Currently doing multiple table join queries using sqlalchemy sessions.

…

On 30-May-2017 5:24 PM, "Dominik Kellner" ***@***.***> wrote: Can you check if it works by using Flask-SQLAlchemy without Eve-SQLAlchemy? It might be connected to the way we initialize the Base-Model for Flask-SQLAlchemy. Are you using a plain SQLAlchemy declarative base like in our examples? As in https://github.com/pyeve/eve- sqlalchemy/blob/master/examples/tables.py#L10 . — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#147 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AH09e4_1MZXEI9eBfv0vfkC7SHIALpq1ks5r_AOAgaJpZM4Nh7wo> .

[dkellner]

Can you clarify your question? Eve / SQLAlchemy will of course use multiple tables for a query if there is a relationship defined.

[luismartingil]

Hi @dkellner ,

I'm also finding it hard to connect to different databases (as referenced here)

Is there anything weird you see in the following example? Any insight will be highly appreciated.

---

domain.py

"""Multiple bindings configuration.
"""

from sqlalchemy import Column, DateTime, ForeignKey, Integer, String, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.dialects import mysql
from eve_sqlalchemy.config import DomainConfig, ResourceConfig

Base = declarative_base()

class Table2(Base):
    __bind_key__ = 'db2'
    __tablename__ = "table2"
    id = Column(Integer, primary_key=True)

class Table1(Base):
    __tablename__ = "table1"
    id = Column(String(255), primary_key=True)

DOMAIN = DomainConfig({
    'table1': ResourceConfig(Table1),
    'table2': ResourceConfig(Table2)
}).render()

---

settings.py

from domain import DOMAIN

SQLALCHEMY_DATABASE_URI = 'mysql://user:pass@ip:3306/db1'
SQLALCHEMY_BINDS = {
    'db2': 'mysql://user:pass@ip:3306/db2'
}

SQLALCHEMY_TRACK_MODIFICATIONS = False
RESOURCE_METHODS = ['GET', 'POST', 'DELETE']
ITEM_METHODS = ['GET', 'PATCH', 'PUT', 'DELETE']

DEBUG = True
HATEOAS = False
IF_MATCH = False
PAGINATION = False
EXTRA_RESPONSE_FIELDS = False
ITEMS = 'data'
SQLALCHEMY_ECHO = True
SQLALCHEMY_RECORD_QUERIES = True

---

app.py

from eve import Eve

from eve_sqlalchemy import SQL
from eve_sqlalchemy.validation import ValidatorSQL
from domain import Base
from settings import on_fetched_resource

app = Eve(validator=ValidatorSQL, data=SQL)

db = app.data.driver

Base.metadata.bind = db.engine
db.Model = Base
# db.create_all()

# using reloader will destroy in-memory sqlite db
app.run(debug=True, use_reloader=False, host='0.0.0.0', port=9003)

---

$ pip list
Package          Version
---------------- -------
Cerberus         0.9.2
Click            7.0
Eve              0.7
Eve-SQLAlchemy   0.7.1
Events           0.2.2
Flask            0.12
Flask-PyMongo    2.3.0
Flask-SQLAlchemy 2.4.1
itsdangerous     0.24
Jinja2           2.10.3
MarkupSafe       0.23
mysqlclient      1.4.4
pip              19.2.3
pymongo          3.9.0
setuptools       41.4.0
simplejson       3.16.0
SQLAlchemy       1.3.9
Werkzeug         0.11.14
wheel            0.33.6

$ python -V
Python 3.6.8

[dkellner]

@luismartingil I've added a working example in PR #199 / Branch multiple-dbs. Can you try if that approach works for you, too?

[luismartingil]

Working well - thanks

[nagarajgond]

Sorry @dkellner, I couldn't respond to your question on time.
Looks like it is working :)