ORM框架Peewee六(连接池、主从、重连、ssh连接)_from peewee import pooledmysqldatabase

ORM框架Peewee(一模型）根据表生成model,根据model生成表
ORM框架Peewee(二增)
ORM框架Peewee(三删)
ORM框架Peewee(四改)
ORM框架Peewee(五查)
ORM框架Peewee六(连接池、主从、重连、ssh连接)
ORM框架Peewee七《INSERT 和 UPDATE扩展》
ORM框架 Peewee 如何使用Mysql的JSON特性？

一、peewee连接池

 from peewee import *
 from playhouse.pool import PooledMySQLDatabase, PooledDatabase

  PooledMySQLDatabase(
     'peewee_test',
     max_connections=8,
     stale_timeout=300,
     user='user', 
     password='password',
     host='127.0.0.0',
     port=3306
    )
1
2
3
4
5
6
7
8
9
10
11
12

二、Peewee主从式 ，读写分离

• 方法一 该方案仅支持mysql pip install pwmd

  from datetime import date
  from peewee import Model, CharField, DateField, BooleanField
  from pwmd import MultiMySQLDatabase
  
  DATABASE = {
  	'master': 'mysql://root@localhost/test_app',
      'slaves': ['mysql://root@localhost/test_app']
      }
  db = MultiMySQLDatabase(DATABASE)
  db.connect()
  
  class BaseModel(Model):
      class Meta:
          database = db
  class Person(BaseModel):
      name = CharField()
      birthday = DateField()
      is_relative = BooleanField()
   
  1. 可以利用普通的方法
  2. 强制使用特殊数据库：
  with db.using('master'):
      pass
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23

• 方法二 主从分离 peewee 2版本 3已经移除除(3的目前我没找到 2022年03月14日)

# 声明一个主副本和两个读取副本。
from playhouse.read_slave import ReadSlaveModel
master = PostgresqlDatabase('master')
replica_1 = PostgresqlDatabase('replica_1')
replica_2 = PostgresqlDatabase('replica_2')

# 声明一个基本模型
class BaseModel(ReadSlaveModel):
    class Meta:
        database = master
        read_slaves = (replica_1, replica_2)

# 申报你的模型。
class User(BaseModel):
    username = CharField()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

三、 peewee 数据库重连，连接池连接不够

from peewee import *
from playhouse.shortcuts import ReconnectMixin
from playhouse.pool import PooledMySQLDatabase

class ReconnectMySQLDatabase(ReconnectMixin, PooledMySQLDatabase):

    _instance = None

    @staticmethod
    def get_db_instance():
        if not ReconnectMySQLDatabase._instance:
            ReconnectMySQLDatabase._instance = ReconnectMySQLDatabase(
                database=DATABASE_NAME,
                host=DATABASE_HOST,
                port=DATABASE_PORT,
                user=DATABASE_USER,
                passwd=DATABASE_PASS,
                charset='utf8',
                max_connections=20,
                stale_timeout=180,
                # sql_mode='NO_AUTO_CREATE_USER'
            )

        return ReconnectMySQLDatabase._instance

db_instance = ReconnectMySQLDatabase.get_db_instance()


from peewee import *
from models import db_instance

class BaseModel(Model):
    class Meta:
        database = db_instance
        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

三、ssh访问数据库的实例

• 方法一

  from peewee import *
  from playhouse.db_url import connect
  from sshtunnel import SSHTunnelForwarder
  
  server = SSHTunnelForwarder(
    (sshServerB_ip, sshServerB_port), # 跳板机配置
    ssh_password=sshServerB_pwd,
    ssh_username=sshServerB_usr,
    remote_bind_address=(databaseA_ip, databaseA_port)  # 远程的MySQL|Redis服务器
    ) 
  server.start()
  destination_lib = connect('mysql://%s:%s@127.0.0.1:%d/%s' % (databaseA_usr, databaseA_pwd, server.local_bind_port, databaseA_db))
  '''
  your code to operate the databaseA
  '''
  server.close()
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16

使用SSHTunnelForwarder隧道，通过跳板机链接MySQL

• 方法二 使用SSHTunnelForwarder隧道，通过跳板机链接Redis

1. 通过SSHTunnelForwarder，paramiko模块，先ssh到跳板机，然后在跳板机上（或者内部服务器上），获取到权限，然后远程Redis。

2. 使用SSHTunnelForwarder模块，通过本地22端口ssh到跳板机，然后本地开启一个转发端口给跳板机远程Redis服务使用。

   import sshtunnel
   with SSHTunnelForwarder(
           ('xxx.xxx.xx.xx', 22),  # 跳板机
           ssh_username=username,
           ssh_pkey="/Users/xxx/.ssh/id_rsa",
           remote_bind_address=('xx.xx.xx.xxx', 6379),  # 远程的Redis服务器
           local_bind_address=('0.0.0.0', 10022)        # 开启本地转发端口
   ) as server:
       server.start()  # 开启隧道
       print(server.local_bind_port)
       # 本地通过local_bind_port端口转发，利用跳板机，链接Redis服务
       cls.red = redis.Redis(host='127.0.0.1', port=server.local_bind_port, db=db, decode_responses=True)
       server.close()  # 关闭隧道
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13