python zlib压缩存储到mysql列

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

数据太大压缩存储，可以使用zlib中的压缩函数，代码如下：

 import ujson

 import MySQLdb

 import zlib

 import base64

 kwargs = {

     'host': '0.0.0.0',

     'port': 3307,

     'db': 'test',

     'user': 'test_user',

     'passwd': 'xxxxxxxxxxxx'

 }

 def trans_data():

     """

     将json数据dumps存储到mysql中

     其中一列数据较大

     从业务角度压缩数据，存储压缩后的数据，解压数据

     :return:

     """

     conn = MySQLdb.connect(**kwargs)

     cur = conn.cursor()

     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]]}]

     compressed_rows = zlib.compress(ujson.dumps(rows))

     # to be able to transmit the data we need to encode it

     final_data = base64.b64encode(compressed_rows)

     sql_w = '''INSERT INTO test_table (result) VALUES ('{}');'''.format(final_data)

     cur.execute(sql_w)

     conn.commit()

     sql_r = """SELECT * FROM test_table WHERE id>13"""

     cur.execute(sql_r)

     d = cur.fetchall()

     for _d in d:

         _dd = base64.b64decode(_d[1])

         zlib.decompress(_dd)

     conn.close()