前言
这边我们以使用python程序要展示一下再分库分表后，我们需要如何对数据库进行操作。
python操作数据库
我们这边还是沿用之前的那5中:场景1：购买者下订单#!/usr/bin/envpython
#-*-coding:utf-8-*-
#Program:客户下订单
#Author:HH
#Date:-02-08
importsys
importmysql.connector
importjson
importsnowflake.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='''
SELECTu.user_id,
u.table_flag,
u.db_name,
ss.value
FROMuserASu
LEFTJOINsystem_settingASssONu.db_name=ss.name
WHEREusername='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='''
SELECTs.user_id,
ug.user_guide_id,
u.table_flag,
u.db_name,
ss.valueASdb_config_json
FROMstoreASs
LEFTJOINuserASuUSING(user_id)
LEFTJOINuser_guideASugUSING(user_id)
LEFTJOINsystem_settingASssONss.name=u.db_name
WHEREs.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='''
SELECTgoods_id,
price
FROM{goods_table}
WHEREgoods_idIN(3794292584748158977,3794292585729626113)
'''.format(goods_table='goods_'+str(sell_table_flag))
cursor_select_sell.execute(select_goods_sql)
#订单价格
order_price=0
forgoods_id,priceincursor_select_sell:
order_price+=price
#生成订单商品信息
insert_order_goods_sql='''
INSERTINTO{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='''
INSERTINTO{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='''
INSERTINTO{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)
#提交事物
mit()
mit()
#关闭有标链接
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/envpython
#-*-coding:utf-8-*-
#Program:客户下订单
#Author:HH
#Date:-02-08
importsys
importmysql.connector
importjson
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='''
SELECTu.user_id,
u.table_flag,
u.db_name,
ss.value
FROMuserASu
LEFTJOINsystem_settingASssONu.db_name=ss.name
WHEREusername='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='''
SELECTbuy_order_id,
user_id,
user_guide_id
FROM{buy_order_table}
WHEREuser_id=34
LIMIT0,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'buyorderinfo:',buy_order_id,buy_user_id,user_guide_id
#通过user_guide_id获得出售者用户信息以及其数据所在的库和表(需要通过ajax来实现)
sell_info_sql='''
SELECTu.user_id,
ug.user_guide_id,
u.table_flag,
u.db_name,
ss.valueASdb_config_json
FROMuser_guideASug
LEFTJOINuserASuUSING(user_id)
LEFTJOINsystem_settingASssONss.name=u.db_name
WHEREug.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}
WHEREsell_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获取数据显示在前台
fororder_goodinorder_goods:
print'ordergoodinfo:',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/envpython
#-*-coding:utf-8-*-
#Program:导购下订单
#Author:HH
#Date:-02-09
importsys
importmysql.connector
importjson
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='''
SELECTu.user_id,
ug.user_guide_id,
u.table_flag,
u.db_name,
ss.valueASdb_config_json
FROMuser_guideASug
LEFTJOINuserASuUSING(user_id)
LEFTJOINsystem_settingASssONss.name=u.db_name
WHEREug.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(
SELECTsell_order_id
FROM{sell_order_table}
WHEREuser_guide_id={user_guide_id}
LIMIT0,1
)AStmp_order
LEFTJOIN{sell_order_table}USING(sell_order_id)
LEFTJOIN{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)
#使用打印来模拟现实在前台显示订单详情
forsell_orderincursor_select_sell:
printsell_order
#关闭有标链接
cursor_select_common.close()
cursor_select_sell.close()
conn_common.close()
conn_sell.close()情况4：导购修改订单#!/usr/bin/envpython
#-*-coding:utf-8-*-
#Program:导购修改订单信息
#Author:HH
#Date:-02-13
importsys
importmysql.connector
importjson
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='''
SELECTu.user_id,
ug.user_guide_id,
u.table_flag,
u.db_name,
ss.valueASdb_config_json
FROMuser_guideASug
LEFTJOINuserASuUSING(user_id)
LEFTJOINsystem_settingASssONss.name=u.db_name
WHEREug.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='''
UPDATEsell_order_{table_flag}
SETprice={price}
WHEREsell_order_id=3794292705695109121
'''.format(table_flag=sell_table_flag,
price=5320.00)
cursor_dml_sell.execute(update_sell_order_sql)
mit()
#关闭有标链接
cursor_select_common.close()
cursor_dml_sell.close()
conn_common.close()
conn_sell.close()
情况5：店主为店铺添加商品#!/usr/bin/envpython
#-*-coding:utf-8-*-
#Program:店主添加商品
#Author:HH
#Date:-02-13
importsys
importmysql.connector
importjson
importsnowflake.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='''
SELECTu.user_id,
ug.user_guide_id,
u.table_flag,
u.db_name,
ss.valueASdb_config_json,
ug.store_idASstore_id
FROMuser_guideASug
LEFTJOINuserASuUSING(user_id)
LEFTJOINsystem_settingASssONss.name=u.db_name
WHEREug.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='''
INSERTINTOgoods_{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)
mit()
#关闭有标链接
cursor_select_common.close()
cursor_dml_sell.close()
conn_common.close()
conn_sell.close()
以上就是在分库完之后的一些操作。具体如何查询还是需要和业务相结合的，万事离不开业务嘛。
python脚本下载：mysql分库分表8
文章出自:/mysql/mysql-distributed-database-and-table-database-query/
