前言

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

python操作数据库

我们这边还是沿用之前的那5中:

  • 场景1：购买者下订单

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
#!/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：购买者浏览订单

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
#!/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：导购查看订单

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
#!/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：导购修改订单

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
#!/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：店主为店铺添加商品

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
#!/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()

以上就是在分库完之后的一些操作。具体如何查询还是需要和业务相结合的，万事离不开业务嘛。

python脚本下载：mysql分库分表8


文章出自:http://www.ttlsa.com/mysql/mysql-distributed-database-and-table-database-query/