mysql表的relationship_sqlalchemy 配置多连接读写库后的relationship设置

前言

一般来说,解决sqlalchemy 连接多个库的最简单的方式是新建两个或多个db.session 相互没有关联,然后modle配置不同的db.session来连接,这样的话,relationship正常配置就行,不用特殊配置.

如果这样解决的话,也就不用看下面的配置了,下面是使用SQLALCHEMY_BINDS配置多个多个数据库并使用relationship.(个人建议最好不用relationship,很容易发生各种报错...)

# -*- coding:utf-8 -*-

__doc__ = "使用SQLALCHEMY_BINDS 就必须双份Model各自配置__bind_key__ ,同名库的读写分离与relationship配置的示例"

import flask

from flask_sqlalchemy import SQLAlchemy # Flask-SQLAlchemy 2.3.2

from datetime import datetime

from sqlalchemy.orm import backref, foreign # SQLAlchemy 1.3.1

db_name = 'db_name'

SQLALCHEMY_DATABASE_URL_READ = 'mysql://toto:toto123@111.111.111.111:3306/%s?charset=utf8' % db_name

SQLALCHEMY_DATABASE_URL_WRITE = 'mysql://toto1:toto123@222.222.222.222:3306/%s?charset=utf8' % db_name

app = flask.Flask(__name__)

app.config['DEBUG'] = True

app.config['SQLALCHEMY_BINDS'] = {

'read_db': SQLALCHEMY_DATABASE_URI_READ,

'write_db': SQLALCHEMY_DATABASE_URL_WRITE,

}

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

app.config['SQLALCHEMY_ECHO'] = True

db = SQLAlchemy(app)

class RDriver(db.Model):

__bind_key__ = 'read_db'

__tablename__ = 'driver'

# __table_args__ = {'schema': db_name} # __tablename__ 相同的,读库写库至少有一个要加,此示例是读库都不加,写库必须加

id = db.Column(db.Integer, primary_key=True, autoincrement=True)

fk_user_id = db.Column(db.Integer, db.ForeignKey("user.id"))

create_at = db.Column(db.TIMESTAMP, default=datetime.now)

class RUser(db.Model):

__bind_key__ = 'read_db'

__tablename__ = 'user'

# __table_args__ = {'schema': db_name}

id = db.Column(db.Integer, primary_key=True, autoincrement=True)

username = db.Column(db.String(32), index=True, unique=True)

email = db.Column(db.String(32))

create_time = db.Column(db.TIMESTAMP, default=datetime.now)

update_time = db.Column(db.TIMESTAMP, default=datetime.now)

# 如下的五种方式都是可以的

# driver_fk = db.relationship("RDriver", foreign_keys='RDriver.fk_user_id')

# driver_fk = db.relationship("RDriver", primaryjoin=lambda: RDriver.fk_user_id == RUser.id, viewonly=True)

# driver_fk = db.relationship("RDriver", primaryjoin=RDriver.fk_user_id == id)

fk_driver = db.relationship("RDriver", primaryjoin='RDriver.fk_user_id == RUser.id')

# driver_fk = db.relationship("RDriver", backref=db.backref('user', lazy=True),

# primaryjoin=lambda: RDriver.fk_user_id == RUser.id, viewonly=True)

####################################################上面为同名读库,下面为同名写库###########################################

class WDriver(db.Model):

__bind_key__ = 'write_db'

__tablename__ = 'driver'

__table_args__ = {'schema': db_name, 'extend_existing': True} # 这个配置很关键,同名写库都要配置

# __table_args__ = {'extend_existing': True}

# 这样配置的话会报错: sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign

# key columns for primary join condition 'driver.fk_user_id = "user".id' on relationship RUser.fk_driver.

# Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint,

# or are annotated in the join condition with the foreign() annotation.

id = db.Column(db.Integer, primary_key=True, autoincrement=True)

fk_user_id = db.Column(db.Integer, db.ForeignKey("%s.user.id" % db_name)) # db_name.user.id很关键,必须指定用的数据库名

create_at = db.Column(db.TIMESTAMP, default=datetime.now)

class WUser(db.Model):

__bind_key__ = 'write_db'

__tablename__ = 'user'

__table_args__ = {'schema': db_name, 'extend_existing': True} # 这个配置很关键,同名写库都要配置

# __table_args__ = {'extend_existing': True} # 这个配置很关键,同名写库都要配置

id = db.Column(db.Integer, primary_key=True, autoincrement=True)

username = db.Column(db.String(32), index=True, unique=True)

email = db.Column(db.String(32))

create_time = db.Column(db.TIMESTAMP, default=datetime.now)

update_time = db.Column(db.TIMESTAMP, default=datetime.now)

# 以下五种方式都是可以的

# fk_driver = db.relationship("WDriver", foreign_keys='WDriver.fk_user_id', uselist=False)

# fk_driver = db.relationship("WDriver", primaryjoin=lambda: WDriver.fk_user_id == WUser.id)

fk_driver = db.relationship("WDriver", primaryjoin=WDriver.fk_user_id == id)

# fk_driver = db.relationship("WDriver", primaryjoin='WDriver.fk_user_id == WUser.id')

# fk_driver = db.relationship("WDriver", backref=db.backref('test.user', lazy=True),

# primaryjoin=lambda: WDriver.fk_user_id == WUser.id)

r_user_obj = RUser.query.filter_by().first()

print("r_user_obj:", r_user_obj)

print("r_user_obj.driver_fk:", r_user_obj.fk_driver)

""" 打印出来的sql

SELECT user.id AS user_id, user.username AS user_username, user.email AS user_email, user.create_time AS user_create_time, user.update_time AS user_update_time

FROM user

LIMIT 1

SELECT driver.id AS driver_id, driver.fk_user_id AS driver_fk_user_id, driver.create_at AS driver_create_at

FROM driver

WHERE driver.fk_user_id = %s

"""

w_user_obj = WUser.query.filter_by(id=2188).first()

print("w_user_obj:", w_user_obj)

print("w_user_obj.driver_fk:", w_user_obj.fk_driver)

""" 打印出来的sql,可以看出多了数据库名

SELECT db_name.user.id AS db_name_user_id, db_name.user.username AS db_name_user_username, db_name.user.email AS db_name_user_email, db_name.user.create_time AS db_name_user_create_time, db_name.user.update_time AS db_name_user_update_time

FROM db_name.user

WHERE db_name.user.id = %s

LIMIT %s

SELECT db_name.driver.id AS db_name_driver_id, db_name.driver.fk_user_id AS db_name_driver_fk_user_id, db_name.driver.create_at AS db_name_driver_create_at

FROM db_name.driver

WHERE db_name.driver.fk_user_id = %s

"""

参考文档:

* https://docs.sqlalchemy.org/en/13/orm/relationship_api.html # 值得细看

* https://www.osgeo.cn/sqlalchemy/orm/relationship_api.html # 同上,中文

* https://www.cnblogs.com/srd945/p/9851227.html

* extend_existing: (False)当表已经存在于元数据中时，如果元数据中存在与column_list中的列同名的列，column_list中同名的列会替换掉元数据中已经有的列

* useexisting已被废弃, 新版本使用extend_existing

总结

关系配置参数真的很多,如下,很容易就会出错,需要多读读官方文档,还有就是建立modle时候尽量简洁,风格统一,不要在数据库层建立外键.

sqlalchemy.orm.relationship(argument, secondary=None, primaryjoin=None, secondaryjoin=None, foreign_keys=None,

uselist=None, order_by=False, backref=None, back_populates=None, post_update=False, cascade=False, extension=None,

viewonly=False, lazy='select', collection_class=None, passive_deletes=False, passive_updates=True, remote_side=None,

enable_typechecks=True, join_depth=None, comparator_factory=None, single_parent=False, innerjoin=False,

distinct_target_key=None, doc=None, active_history=False, cascade_backrefs=True, load_on_pending=False,

bake_queries=True, _local_remote_pairs=None, query_class=None, info=None, omit_join=None)

题外话

配置多连接数据库用户访问权限需要提前配置好,我就遇到个坑:

我连接了ip为111.111.111.111的数据库,使用mysql命令好都能很好的正常访问到数据,但是使用SQLALCHEMY_BINDS访问后就报错 :

OperationalError: (_mysql_exceptions.OperationalError) (1142, "SELECT command denied to user 'toto'@'125.121.34.123' for table 'user'") [SQL: u'SELECT test2.user.com_name AS test2_user_com_name, test2.user.com_no AS test2_user_com_no \nFROM test2.user \nWHERE test2.user.id = %s \n LIMIT %s'] [parameters: (3385, 1)]

其实还是自己没有检查清楚,toto用户我只给了test库的权限,而test2库的权限没给,才会造成这样的报错(125.121.34.123别被这个ip吸引了注意力,我上次遇到这样的问题是配置中SQLALCHEMY_DATABASE_URL的连接有点错误,导致前后看到的ip不一样,这次连接中的111.111.111.111和报错中的125.121.34.123不一样是因为ip不在同一网段,125.121.34.123就是111.111.111.111经过多段路由连接过来的后分配到的ip)

mysql toto@111.111.111.111:test> SELECT test2.user.username AS test_user_username, test2.user.id AS test_ship

pers_id FROM test2.user WHERE test2.user.id = 3385 LIMIT 1;

(1142, "SELECT command denied to user 'toto'@'125.121.34.123' for table 'user'")

mysql toto@111.111.111.111:test> SELECT test.user.username AS test_user_username, test.user.id AS

test_user_id FROM test.user WHERE test.user.id = 3385 LIMIT 1;

+--------------------------+-----------------------+

| test_user_username | test_user_id |

+--------------------------+-----------------------+

| XXXXXXX | 123 |

+--------------------------+-----------------------+