python zlib压缩存储到mysql列
数据太大压缩存储,可以使用zlib中的压缩函数,代码如下:
1 import ujson 2 import MySQLdb 3 import zlib 4 import base64 5 6 kwargs = { 7 'host': '0.0.0.0', 8 'port': 3307, 9 'db': 'test', 10 'user': 'test_user', 11 'passwd': 'xxxxxxxxxxxx' 12 } 13 14 def trans_data(): 15 """ 16 将json数据dumps存储到mysql中 17 其中一列数据较大 18 从业务角度压缩数据,存储压缩后的数据,解压数据 19 :return: 20 """ 21 conn = MySQLdb.connect(**kwargs) 22 cur = conn.cursor() 23 rows = [200,"success",{"success":[[1229,16,0],[232,6,0],[12690,78,0],[9208,28,0],[13729,89,0],[11247,66,0],[10253,529,0],[11421,424,20],[4725,292,02],[467,242,0],[2213,14,0],[52,6,0],[68,9,0],[69,13,0],[69,9,0],[895,83,0],[818,95,0],[590,65,0],[78,13,0],[0,0,0],[3,1,0]]}] 24 compressed_rows = zlib.compress(ujson.dumps(rows)) 25 # to be able to transmit the data we need to encode it 26 final_data = base64.b64encode(compressed_rows) 27 sql_w = '''INSERT INTO test_table (result) VALUES ('{}');'''.format(final_data) 28 cur.execute(sql_w) 29 conn.commit() 30 31 sql_r = """SELECT * FROM test_table WHERE id>13""" 32 cur.execute(sql_r) 33 d = cur.fetchall() 34 for _d in d: 35 _dd = base64.b64decode(_d[1]) 36 zlib.decompress(_dd) 37 38 conn.close()