MySQL分库分表分库后的查询(8th)

96
文章

16
评论

29/02/2016 01:28:19MySQL已关闭评论10,7552字数 10107阅读33分41秒阅读模式

前言

这边我们以使用python程序要展示一下再分库分表后，我们需要如何对数据库进行操作。

python操作数据库

我们这边还是沿用之前的那5中:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-database-query/

场景1：购买者下订单

#!/usr/bin/env python
# -*- coding:utf-8 -*-
 
# Program: 客户下订单
# Author : HH
# Date   : 2016-02-08
 
import sys
import mysql.connector
import json
import snowflake.client
 
reload(sys)
sys.setdefaultencoding('utf-8')
 
if __name__ == '__main__':
  '''
  这边模拟用户：username77购买 store2 中的 goods27和goods69商品
  '''
  # 设置公共库连接配置
  db_config_common = {
    'user'    : 'root',
    'password': 'root',
    'host'    : '127.0.0.1',
    'port'    : 3306,
    'database': 'test'
  }
  # 设置snowflake链接默认参数
  snowflake_config = {
    'host': '192.168.137.11',
    'port': 30001
  }
  # 配置snowflake
  snowflake.client.setup(**snowflake_config)
  # 获得公共数据库的链接和游标
  conn_common = mysql.connector.connect(**db_config_common)
  cursor_select_common = conn_common.cursor(buffered=True)
  cursor_dml_common = conn_common.cursor(buffered=True)
  # 获得用户:username77的基本信息
  select_sql = '''
    SELECT u.user_id,
      u.table_flag,
      u.db_name,
      ss.value
    FROM user AS u
      LEFT JOIN system_setting AS ss ON u.db_name = ss.name
    WHERE username = 'username77'
  '''
  cursor_select_common.execute(select_sql)
  buy_user_id, buy_table_flag, buy_db_name, buy_db_config_json \
    = cursor_select_common.fetchone()
  # 获得购买者的链接和游标
  conn_buy = mysql.connector.connect(**json.loads(buy_db_config_json))
  cursor_select_buy = conn_buy.cursor(buffered=True)
  cursor_dml_buy = conn_buy.cursor(buffered=True)
  # 通过店铺名称获得导购以及导购所对应的用户所使用的数据库链接描述符
  select_sql = '''
    SELECT s.user_id,
      ug.user_guide_id,
      u.table_flag,
      u.db_name,
      ss.value AS db_config_json
    FROM store AS s
      LEFT JOIN user AS u USING(user_id)
      LEFT JOIN user_guide AS ug USING(user_id)
      LEFT JOIN system_setting AS ss ON ss.name = u.db_name
    WHERE s.user_id = 2
  '''
  cursor_select_common.execute(select_sql)
  sell_user_id, user_guide_id, sell_table_flag, sell_dbname, \
  sell_db_config_json = cursor_select_common.fetchone()
   
  # 获得出售者的数据库链接描述符以及游标
  conn_sell = mysql.connector.connect(**json.loads(sell_db_config_json))
  cursor_select_sell = conn_sell.cursor(buffered=True)
  cursor_dml_sell = conn_sell.cursor(buffered=True)
  # 成订单ID
  order_id = snowflake.client.get_guid()
  # 获得商品信息并生成商品订单信息。
  select_goods_sql = '''
    SELECT goods_id,
      price
    FROM {goods_table}
    WHERE goods_id IN(3794292584748158977, 3794292585729626113)
  '''.format(goods_table = 'goods_' + str(sell_table_flag))
  cursor_select_sell.execute(select_goods_sql)
  # 订单价格
  order_price = 0
  for goods_id, price in cursor_select_sell:
    order_price += price
    # 生成订单商品信息
    insert_order_goods_sql = '''
      INSERT INTO {table_name}
      VALUES({guid}, {order_id}, {goods_id}, {user_guide_id},
        {price}, 1)
    '''.format(table_name = 'order_goods_' + str(sell_table_flag),
               guid = snowflake.client.get_guid(),
               order_id = order_id,
               goods_id = goods_id,
               user_guide_id = user_guide_id,
               price = price)
    cursor_dml_sell.execute(insert_order_goods_sql)
  # 生成订单记录
  insert_order_sql = '''
    INSERT INTO {order_table}
    VALUES({order_id}, {user_guide_id}, {user_id}, 
      {price}, 0)
  '''.format(order_table = 'sell_order_' + str(sell_table_flag),
             order_id = order_id,
             user_guide_id = user_guide_id,
             user_id = buy_user_id,
             price = order_price)
  cursor_dml_sell.execute(insert_order_sql)
  # 生成购买者订单记录
  insert_order_sql = '''
    INSERT INTO {order_buy_table}
    VALUES({order_id}, {user_id}, {user_guide_id})
  '''.format(order_buy_table = 'buy_order_' + str(buy_table_flag),
             order_id = order_id,
             user_id = buy_user_id,
             user_guide_id = user_guide_id)
  cursor_dml_buy.execute(insert_order_sql)
 
  # 提交事物
  conn_buy.commit()
  conn_sell.commit()
  # 关闭有标链接
  cursor_select_common.close()
  cursor_select_buy.close()
  cursor_select_sell.close()
  cursor_dml_common.close()
  cursor_dml_buy.close()
  cursor_dml_sell.close()
 
  conn_common.close()
  conn_buy.close()
  conn_sell.close()

场景2：购买者浏览订单

#!/usr/bin/env python
# -*- coding:utf-8 -*-
 
# Program: 客户下订单
# Author : HH
# Date   : 2016-02-08
 
import sys
import mysql.connector
import json
 
reload(sys)
sys.setdefaultencoding('utf-8')
 
if __name__ == '__main__':
  '''
  这边模拟用户：username34 订单查询分页为每页一笔订单
  '''
  # 设置公共库连接配置
  db_config_common = {
    'user'    : 'root',
    'password': 'root',
    'host'    : '127.0.0.1',
    'port'    : 3306,
    'database': 'test'
  }
  conn_common = mysql.connector.connect(**db_config_common)
  cursor_select_common = conn_common.cursor(buffered=True)
  # 获得用户:username34的基本信息
  select_sql = '''
    SELECT u.user_id,
      u.table_flag,
      u.db_name,
      ss.value
    FROM user AS u
      LEFT JOIN system_setting AS ss ON u.db_name = ss.name
    WHERE username = 'username77'
  '''
  cursor_select_common.execute(select_sql)
  buy_user_id, buy_table_flag, buy_db_name, buy_db_config_json \
    = cursor_select_common.fetchone()
  # 获得购买者的链接和游标
  conn_buy = mysql.connector.connect(**json.loads(buy_db_config_json))
  cursor_select_buy = conn_buy.cursor(buffered=True)
  # 获得购买者的一笔订单, 直接在后台获取数据传到前台
  select_buy_order_sql = '''
    SELECT buy_order_id,
      user_id,
      user_guide_id
    FROM {buy_order_table}
    WHERE user_id = 34
    LIMIT 0, 1
  '''.format(buy_order_table = 'buy_order_' + str(buy_table_flag))
  cursor_select_buy.execute(select_buy_order_sql)
  buy_order_id, buy_user_id, user_guide_id = cursor_select_buy.fetchone()
  # 使用打印来模拟现实在前台
  print 'buy order info: ', buy_order_id, buy_user_id, user_guide_id
   
  # 通过user_guide_id获得出售者用户信息以及其数据所在的库和表(需要通过ajax来实现)
  sell_info_sql = '''
    SELECT u.user_id,
      ug.user_guide_id,
      u.table_flag,
      u.db_name,
      ss.value AS db_config_json
    FROM user_guide AS ug
      LEFT JOIN user AS u USING(user_id)
      LEFT JOIN system_setting AS ss ON ss.name = u.db_name
    WHERE ug.user_guide_id = {user_guide_id}
  '''.format(user_guide_id = user_guide_id)
  cursor_select_common.execute(sell_info_sql)
  sell_user_id, user_guide_id, sell_table_flag, sell_dbname, \
  sell_db_config_json = cursor_select_common.fetchone()
   
  # 获得出售者的数据库链接描述符以及游标(需要通过ajax来实现)
  conn_sell = mysql.connector.connect(**json.loads(sell_db_config_json))
  cursor_select_sell = conn_sell.cursor(buffered=True)
  # 获得订单商品(需要通过ajax来实现)
  order_goods_sql = '''
    SELECT *
    FROM {order_goods_table}
    WHERE sell_order_id = {buy_order_id}
  '''.format(order_goods_table = 'order_goods_' + str(sell_table_flag),
           buy_order_id = buy_order_id)
  cursor_select_sell.execute(order_goods_sql)
  order_goods = cursor_select_sell.fetchall()
  #使用打印来模拟 ajax 获取数据显示在前台
  for order_good in order_goods:
    print 'order good info: ', order_good
   
  # 关闭有标链接
  cursor_select_common.close()
  cursor_select_buy.close()
  cursor_select_sell.close()
 
  conn_common.close()
  conn_buy.close()
  conn_sell.close()

情况3：导购查看订单

#!/usr/bin/env python
# -*- coding:utf-8 -*-
 
# Program: 导购下订单
# Author : HH
# Date   : 2016-02-09
 
import sys
import mysql.connector
import json
 
reload(sys)
sys.setdefaultencoding('utf-8')
 
if __name__ == '__main__':
  '''
  这边模拟导购：6 查询订单的情况
  '''
  # 设置公共库连接配置
  db_config_common = {
    'user'    : 'root',
    'password': 'root',
    'host'    : '127.0.0.1',
    'port'    : 3306,
    'database': 'test'
  }
  conn_common = mysql.connector.connect(**db_config_common)
  cursor_select_common = conn_common.cursor(buffered=True)
  # 获得导购:6的基本信息
  sell_info_sql = '''
    SELECT u.user_id,
      ug.user_guide_id,
      u.table_flag,
      u.db_name,
      ss.value AS db_config_json
    FROM user_guide AS ug
      LEFT JOIN user AS u USING(user_id)
      LEFT JOIN system_setting AS ss ON ss.name = u.db_name
    WHERE ug.user_guide_id = 6
  '''
  cursor_select_common.execute(sell_info_sql)
  sell_user_id, user_guide_id, sell_table_flag, sell_db_name, \
  sell_db_config_json = cursor_select_common.fetchone()
  # 获得出售者的链接和游标
  conn_sell = mysql.connector.connect(**json.loads(sell_db_config_json))
  cursor_select_sell = conn_sell.cursor(buffered=True)
  # 获得者的一笔订单以及订单商品
  select_sell_order_sql = '''
    SELECT *
    FROM (
      SELECT sell_order_id 
      FROM {sell_order_table}
      WHERE user_guide_id = {user_guide_id}
      LIMIT 0, 1
    ) AS tmp_order
      LEFT JOIN {sell_order_table} USING(sell_order_id)
      LEFT JOIN {order_goods_table} USING(sell_order_id)
  '''.format(sell_order_table = 'sell_order_' + str(sell_table_flag),
             user_guide_id = user_guide_id,
             order_goods_table = 'order_goods_' + str(sell_table_flag))
  cursor_select_sell.execute(select_sell_order_sql)
  # 使用打印来模拟现实在前台显示订单详情
  for sell_order in cursor_select_sell:
    print sell_order
 
  # 关闭有标链接
  cursor_select_common.close()
  cursor_select_sell.close()
 
  conn_common.close()
  conn_sell.close()

情况4：导购修改订单

#!/usr/bin/env python
# -*- coding:utf-8 -*-
 
# Program: 导购修改订单信息
# Author : HH
# Date   : 2016-02-13
 
import sys
import mysql.connector
import json
 
reload(sys)
sys.setdefaultencoding('utf-8')
 
if __name__ == '__main__':
  '''
  这边模拟修改导购ID：6，订单id：3794292705695109121 的订单
  '''
  # 设置公共库连接配置
  db_config_common = {
    'user'    : 'root',
    'password': 'root',
    'host'    : '127.0.0.1',
    'port'    : 3306,
    'database': 'test'
  }
  conn_common = mysql.connector.connect(**db_config_common)
  cursor_select_common = conn_common.cursor(buffered=True)
  # 获得导购:6的基本信息
  sell_info_sql = '''
    SELECT u.user_id,
      ug.user_guide_id,
      u.table_flag,
      u.db_name,
      ss.value AS db_config_json
    FROM user_guide AS ug
      LEFT JOIN user AS u USING(user_id)
      LEFT JOIN system_setting AS ss ON ss.name = u.db_name
    WHERE ug.user_guide_id = 6
  '''
  cursor_select_common.execute(sell_info_sql)
  sell_user_id, user_guide_id, sell_table_flag, sell_db_name, \
  sell_db_config_json = cursor_select_common.fetchone()
  # 获得出售者的链接和游标
  conn_sell = mysql.connector.connect(**json.loads(sell_db_config_json))
  cursor_dml_sell = conn_sell.cursor(buffered=True)
  # 修改订单3794292705695109121的价格
  update_sell_order_sql = '''
    UPDATE sell_order_{table_flag}
    SET price = {price}
    WHERE sell_order_id = 3794292705695109121
  '''.format(table_flag = sell_table_flag,
             price = 5320.00)
 
  cursor_dml_sell.execute(update_sell_order_sql)
  conn_sell.commit()
 
  # 关闭有标链接
  cursor_select_common.close()
  cursor_dml_sell.close()
 
  conn_common.close()
  conn_sell.close()
情况5：店主为店铺添加商品
#!/usr/bin/env python
# -*- coding:utf-8 -*-
 
# Program: 店主添加商品
# Author : HH
# Date   : 2016-02-13
 
import sys
import mysql.connector
import json
import snowflake.client
 
reload(sys)
sys.setdefaultencoding('utf-8')
 
if __name__ == '__main__':
  '''
  这边模拟修改导购ID：7 为商店添加商品
  '''
  # 设置公共库连接配置
  db_config_common = {
    'user'    : 'root',
    'password': 'root',
    'host'    : '127.0.0.1',
    'port'    : 3306,
    'database': 'test'
  }
  # 设置snowflake链接默认参数
  snowflake_config = {
    'host': '192.168.137.11',
    'port': 30001
  }
  # 配置snowflake
  snowflake.client.setup(**snowflake_config)
  # 获得公共数据库链接和游标
  conn_common = mysql.connector.connect(**db_config_common)
  cursor_select_common = conn_common.cursor(buffered=True)
  # 获得导购:7的基本信息
  sell_info_sql = '''
    SELECT u.user_id,
      ug.user_guide_id,
      u.table_flag,
      u.db_name,
      ss.value AS db_config_json,
      ug.store_id AS store_id
    FROM user_guide AS ug
      LEFT JOIN user AS u USING(user_id)
      LEFT JOIN system_setting AS ss ON ss.name = u.db_name
    WHERE ug.user_guide_id = 7
  '''
  cursor_select_common.execute(sell_info_sql)
  sell_user_id, user_guide_id, sell_table_flag, sell_db_name, \
  sell_db_config_json, sell_store_id = cursor_select_common.fetchone()
  # 获得出售者的链接和游标
  conn_sell = mysql.connector.connect(**json.loads(sell_db_config_json))
  cursor_dml_sell = conn_sell.cursor(buffered=True)
  # 修改订单3794292705695109121的价格
  insert_goods_sql = '''
    INSERT INTO goods_{table_flag}
    VALUES({gid}, 'goods101', 5320.00, {store_id})
  '''.format(gid = snowflake.client.get_guid(),
             table_flag = sell_table_flag,
             store_id = sell_store_id)
 
  cursor_dml_sell.execute(insert_goods_sql)
  conn_sell.commit()
 
  # 关闭有标链接
  cursor_select_common.close()
  cursor_dml_sell.close()
 
  conn_common.close()
  conn_sell.close()

以上就是在分库完之后的一些操作。具体如何查询还是需要和业务相结合的，万事离不开业务嘛。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-database-query/

python脚本下载：mysql分库分表8文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-database-query/

文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-database-query/

感觉本文内容不错，读后有收获？文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-database-query/

逛逛衣服店，鼓励作者写出更好文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-database-query/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-database-query/

我的微信

微信公众号

扫一扫关注运维生存时间公众号，获取最新技术文章~

MySQL分库分表分库后的查询(8th)

前言

python操作数据库

zabbix监控mysql性能(106)

2013年8月NoSQL数据库就业趋势

Out of resources when opening file ‘./xxx.MYD’ (Errcode: 24)解决方法

HandlerSocket安装配置