max_allowed_packet

from mysql_tool import *
import copy

s = '''
INSERT INTO `qqzone`.`myu` (`id`, `uid`, `age`, `gender`, `marriageStatus`, `education`, `consumptionAbility`, `LBS`, `interest1`, `interest2`, `interest3`, `interest4`, `interest5`, `kw1`, `kw2`, `kw3`, `kw4`, `kw5`, `topic1`, `topic2`, `topic3`, `topic4`, `topic5`, `ct`, `appIdAction`, `appIdInstall`, `os`, `carrier`, `house`) VALUES ('33', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
'''.replace('
', '')

indb_fields_s = '{}{}'.format(s.split('VALUES')[0], ' VALUES ').replace('`id`,', '')
fields_l = [i.replace(' ', '').replace('`', '') for i in s.split('(')[1].split(')')[0].split(',')]

val_d = {}
for i in fields_l:
    val_d[i] = 'NULL'
del val_d['id']

indb_step, indb_step_s = 2000, ''
f = 'userFeature.data'
with open(f, 'r') as fr:
    sql_d_l, indb_step_c = [], 0
    for i in fr:
        break
        sql_d = copy.deepcopy(val_d)
        l = i.replace('
', '').split('|')
        for ii in l:
            try:
                ix_ = ii.index(' ')
                k, v = ii[0:ix_], ii[ix_ + 1:]
                # if k == 'appIdInstall':
                #     continue
                sql_d[k] = v
            except Exception as e:
                print(e)

        sql_d_l.append(sql_d)
        indb_step_c += 1
        if indb_step_c % indb_step == 0:
            s_l = []
            for d in sql_d_l:
                s_l.append('("{}")'.format('","'.join([d[k] for k in d])))
            indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))
            try:
                mysql_write(indb_step_s)
                indb_step_c = 0
            except Exception as e:
                logs_l = [e, indb_step_s]
                logs_s = '||'.join(logs_l)
                print(logs_s)
            sql_d, sql_d_l, indb_step_c = val_d, [], 0

if indb_step_c % indb_step != 0:
    s_l = []
    for d in sql_d_l:
        s_l.append('("{}")'.format('","'.join([d[k] for k in d])))
    indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))
    try:
        mysql_write(indb_step_s)
        indb_step_c = 0
    except Exception as e:
        logs_l = [e, indb_step_s]
        logs_s = '||'.join(logs_l)
        print(logs_s)

f = 'train.csv'
indb_step, indb_step_s = 20000, ''
indb_fields_s = 'INSERT INTO `qqzone`.`myt` ( `aid`, `uid`, `label`) VALUES '
with open(f, 'r') as fr:
    sql_l, indb_step_c = [], 0
    for i in fr:
        if 'aid' in i:
            continue
        sql_l.append([ii for ii in i.replace('
', '').split(',')])
        indb_step_c += 1
        if indb_step_c % indb_step == 0:
            s_l = []
            for l in sql_l:
                s_l.append('("{}")'.format('","'.join(l)))
            indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))
            try:
                mysql_write(indb_step_s)
                indb_step_c = 0
            except Exception as e:
                logs_l = [e, indb_step_s]
                logs_s = '||'.join(logs_l)
                # print(logs_s)
                print(e)
            sql_d, sql_d_l, indb_step_c = val_d, [], 0
if indb_step_c % indb_step == 0:
    s_l = []
    for l in sql_l:
        s_l.append('("{}")'.format('","'.join(l)))
    indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))
    try:
        mysql_write(indb_step_s)
        indb_step_c = 0
    except Exception as e:
        logs_l = [e, indb_step_s]
        logs_s = '||'.join(logs_l)
        # print(logs_s)
        print(e)
    sql_d, sql_d_l, indb_step_c = val_d, [], 0

(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(1153, "Got a packet bigger than 'max_allowed_packet' bytes")
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(2013, 'Lost connection to MySQL server during query ([WinError 10053] 您的主机中的软件中止了一个已建立的连接。)')
(1153, "Got a packet bigger than 'max_allowed_packet' bytes")
from mysql_tool import *
import copy

s = '''
INSERT INTO `qqzone`.`myu` (`id`, `uid`, `age`, `gender`, `marriageStatus`, `education`, `consumptionAbility`, `LBS`, `interest1`, `interest2`, `interest3`, `interest4`, `interest5`, `kw1`, `kw2`, `kw3`, `kw4`, `kw5`, `topic1`, `topic2`, `topic3`, `topic4`, `topic5`, `ct`, `appIdAction`, `appIdInstall`, `os`, `carrier`, `house`) VALUES ('33', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
'''.replace('
', '')

indb_fields_s = '{}{}'.format(s.split('VALUES')[0], ' VALUES ').replace('`id`,', '')
fields_l = [i.replace(' ', '').replace('`', '') for i in s.split('(')[1].split(')')[0].split(',')]

val_d = {}
for i in fields_l:
    val_d[i] = 'NULL'
del val_d['id']

indb_step, indb_step_s = 2000, ''
f = 'userFeature.data'
with open(f, 'r') as fr:
    sql_d_l, indb_step_c = [], 0
    for i in fr:
        break
        sql_d = copy.deepcopy(val_d)
        l = i.replace('
', '').split('|')
        for ii in l:
            try:
                ix_ = ii.index(' ')
                k, v = ii[0:ix_], ii[ix_ + 1:]
                # if k == 'appIdInstall':
                #     continue
                sql_d[k] = v
            except Exception as e:
                print(e)

        sql_d_l.append(sql_d)
        indb_step_c += 1
        if indb_step_c % indb_step == 0:
            s_l = []
            for d in sql_d_l:
                s_l.append('("{}")'.format('","'.join([d[k] for k in d])))
            indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))
            try:
                mysql_write(indb_step_s)
                indb_step_c = 0
            except Exception as e:
                logs_l = [e, indb_step_s]
                logs_s = '||'.join(logs_l)
                print(logs_s)
            sql_d, sql_d_l, indb_step_c = val_d, [], 0

if indb_step_c % indb_step != 0:
    s_l = []
    for d in sql_d_l:
        s_l.append('("{}")'.format('","'.join([d[k] for k in d])))
    indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))
    try:
        mysql_write(indb_step_s)
        indb_step_c = 0
    except Exception as e:
        logs_l = [e, indb_step_s]
        logs_s = '||'.join(logs_l)
        print(logs_s)

f = 'train.csv'
indb_step, indb_step_s = 10000, ''
indb_fields_s = 'INSERT INTO `qqzone`.`myt` ( `aid`, `uid`, `label`) VALUES '
with open(f, 'r') as fr:
    sql_l, indb_step_c = [], 0
    for i in fr:
        if 'aid' in i:
            continue
        sql_l.append([ii for ii in i.replace('
', '').split(',')])
        indb_step_c += 1
        if indb_step_c % indb_step == 0:
            s_l = []
            for l in sql_l:
                s_l.append('("{}")'.format('","'.join(l)))
            indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))
            try:
                mysql_write(indb_step_s)
                indb_step_c = 0
            except Exception as e:
                logs_l = [e, indb_step_s]
                logs_s = '||'.join(logs_l)
                # print(logs_s)
                print(e)
            sql_l, indb_step_c = [], 0
if indb_step_c % indb_step == 0:
    s_l = []
    for l in sql_l:
        s_l.append('("{}")'.format('","'.join(l)))
    indb_step_s = '{}{};'.format(indb_fields_s, ','.join(s_l))
    try:
        mysql_write(indb_step_s)
        indb_step_c = 0
    except Exception as e:
        logs_l = [e, indb_step_s]
        logs_s = '||'.join(logs_l)
        # print(logs_s)
        print(e)
    sql_l, indb_step_c = [], 0

　　

 https://dev.mysql.com/doc/refman/8.0/en/program-variables.html

       Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:

shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading dashes:

 
[mysql]
max_allowed_packet=16777216

　　

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/rsapaper/p/8975793.html