django之 将字典数据导入数据库以及解决insert自动增长的id不是从1开始或不连续

本文档描述了在对MySQL数据库进行INSERT操作时遇到的ID不连续问题,分析了可能的原因,包括INSERT失败后的自动增1和DELETE操作后重新插入数据。为了解决这个问题,提供了使用Navicate Premium的TRUNCATE TABLE命令来重置表ID计数的步骤,该方法成功解决了ID不从1开始的问题。
摘要由CSDN通过智能技术生成

当我对mysql数据库进行insert插入操作时,发现insert自动增长的id不是从1开始或不连续

这个是我对我的mysql中的数据库zx里的configuration表进行insert字典格式的数据操作的代码:

import json,MySQLdb,pymysql,xlrd,xlwt,sys
from operator import mod
from bs4 import BeautifulSoup
from xlutils.copy import copy
from JobSpider.excel导入数据库 import temp_dict


def write_to_excel(words, filename, sheet_name='sheet1'):
    '''将item存储到excel中。
    :param words: 保存item的list    [{},{}]格式
    :return:
    '''
    try:
        # 1、创建工作薄
        work_book = xlwt.Workbook(encoding='utf-8')
        # 2、创建sheet表单
        sheet = work_book.add_sheet(sheet_name)
        # 3、写表头
        # head = ['英文','中文']
        head = []
        for k in words[0].keys():
            head.append(k)

        for i in range(len(head)):
            sheet.write(0, i, head[i])
        # 4、添加内容
        # 行号
        i = 1
        for item in words:
            for j in range(len(head)):
                sheet.write(i, j, item[head[j]])
            # 写完一行,将行号+1
            i += 1
        # 保存
        work_book.save(filename)
        print('写入excel成功!')
    except Exception as e:
        print('写入excel失败!', e)

def append_to_excel(words, filename):
    ''' 追加数据到excel
    :param words: 【item】 [{},{}]格式
    :param filename: 文件名
    :return:
    '''
    global sum
    try:
        # 打开excel
        word_book = xlrd.open_workbook(filename)
        # 获取所有的sheet表单。
        sheets = word_book.sheet_names()
        # 获取第一个表单
        work_sheet = word_book.sheet_by_name(sheets[0])
        # 获取已经写入的行数
        old_rows = work_sheet.nrows
        # 获取表头信息
        heads = work_sheet.row_values(0)
        # 将xlrd对象变成xlwt
        new_work_book = copy(word_book)
        # 添加内容
        new_sheet = new_work_book.get_sheet(0)
        i = old_rows
        for item in words:
            for j in range(len(heads)):
                new_sheet.write(i, j, item[heads[j]])
            i += 1
        new_work_book.save(filename)
        sum += 1
        print('追加成功!')
    except Exception as e:
        print('追加失败!', e)

def open_excel(excel_file):
    '''读取excel函数
    args:excel_file(excel文件,目录在py文件同目录)
    returns:book
    '''
    try:
        book = xlrd.open_workbook(excel_file) # 文件名,把文件与py文件放在同一目录下
        print(sys.getsizeof(book))
        return book
    except:
        print("打开excel文件失败!!")



if __name__ == '__main__':

    dict = [{
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 149,
                    "green_st": 0,
                    "green_end": 67.05,
                    "greenrate": 0.45
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 149,
                    "green_st": 67.05,
                    "green_end": 74.5,
                    "greenrate": 0.05
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 149,
                    "green_st": 74.5,
                    "green_end": 141.55,
                    "greenrate": 0.35
                }
            },
            
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 132,
                    "green_st": 66.0,
                    "green_end": 85.8,
                    "greenrate": 0.32
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 132,
                    "green_st": 85.8,
                    "green_end": 132,
                    "greenrate": 0.35
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 100,
                    "green_st": 0,
                    "green_end": 31.0,
                    "greenrate": 0.31
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 100,
                    "green_st": 31.0,
                    "green_end": 50.0,
                    "greenrate": 0.19
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 100,
                    "green_st": 50.0,
                    "green_end": 81.0,
                    "greenrate": 0.22
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 100,
                    "green_st": 81.0,
                    "green_end": 100,
                    "greenrate": 0.19
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 103,
                    "green_st": 0,
                    "green_end": 44.29,
                    "greenrate": 0.43
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 103,
                    "green_st": 44.29,
                    "green_end": 51.5,
                    "greenrate": 0.07
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 103,
                    "green_st": 51.5,
                    "green_end": 95.78999999999999,
                    "greenrate": 0.21
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 103,
                    "green_st": 95.78999999999999,
                    "green_end": 103,
                    "greenrate": 0.07
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 128,
                    "green_st": 0,
                    "green_end": 46.08,
                    "greenrate": 0.36
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 128,
                    "green_st": 46.08,
                    "green_end": 64.0,
                    "greenrate": 0.14
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 128,
                    "green_st": 64.0,
                    "green_end": 110.08,
                    "greenrate": 0.45
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 128,
                    "green_st": 110.08,
                    "green_end": 128,
                    "greenrate": 0.14
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 132,
                    "green_st": 0,
                    "green_end": 34.32,
                    "greenrate": 0.26
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 132,
                    "green_st": 34.32,
                    "green_end": 66.0,
                    "greenrate": 0.24
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 132,
                    "green_st": 66.0,
                    "green_end": 100.32,
                    "greenrate": 0.39
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 132,
                    "green_st": 100.32,
                    "green_end": 132,
                    "greenrate": 0.24
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 134,
                    "green_st": 0,
                    "green_end": 28.14,
                    "greenrate": 0.21
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 134,
                    "green_st": 28.14,
                    "green_end": 67.0,
                    "greenrate": 0.29
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 134,
                    "green_st": 67.0,
                    "green_end": 95.14,
                    "greenrate": 0.4
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 134,
                    "green_st": 95.14,
                    "green_end": 134,
                    "greenrate": 0.29
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 120,
                    "green_st": 0,
                    "green_end": 30.0,
                    "greenrate": 0.25
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 120,
                    "green_st": 30.0,
                    "green_end": 60.0,
                    "greenrate": 0.25
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 120,
                    "green_st": 60.0,
                    "green_end": 90.0,
                    "greenrate": 0.39
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 120,
                    "green_st": 90.0,
                    "green_end": 120,
                    "greenrate": 0.25
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 102,
                    "green_st": 0,
                    "green_end": 36.72,
                    "greenrate": 0.36
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 102,
                    "green_st": 36.72,
                    "green_end": 51.0,
                    "greenrate": 0.14
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 102,
                    "green_st": 51.0,
                    "green_end": 87.72,
                    "greenrate": 0.29
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 102,
                    "green_st": 87.72,
                    "green_end": 102,
                    "greenrate": 0.14
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 122,
                    "green_st": 0,
                    "green_end": 28.06,
                    "greenrate": 0.23
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 122,
                    "green_st": 28.06,
                    "green_end": 61.0,
                    "greenrate": 0.27
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 122,
                    "green_st": 61.0,
                    "green_end": 89.06,
                    "greenrate": 0.16
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 122,
                    "green_st": 89.06,
                    "green_end": 122,
                    "greenrate": 0.27
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 99,
                    "green_st": 0,
                    "green_end": 37.62,
                    "greenrate": 0.38
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 99,
                    "green_st": 37.62,
                    "green_end": 49.5,
                    "greenrate": 0.12
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 99,
                    "green_st": 49.5,
                    "green_end": 87.12,
                    "greenrate": 0.37
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 99,
                    "green_st": 87.12,
                    "green_end": 99,
                    "greenrate": 0.12
                }
            },
            {
                "level": "phase",
                "id": 1,
                "content": {
                    "period": 127,
                    "green_st": 0,
                    "green_end": 39.37,
                    "greenrate": 0.31
                }
            },
            {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 127,
                    "green_st": 39.37,
                    "green_end": 63.5,
                    "greenrate": 0.19
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 127,
                    "green_st": 63.5,
                    "green_end": 102.87,
                    "greenrate": 0.34
                }
            },
            {
                "level": "phase",
                "id": 4,
                "content": {
                    "period": 127,
                    "green_st": 102.87,
                    "green_end": 127,
                    "greenrate": 0.19
                }
            },
            
            
             {
                "level": "phase",
                "id": 2,
                "content": {
                    "period": 137,
                    "green_st": 26.03,
                    "green_end": 68.5,
                    "greenrate": 0.31
                }
            },
            {
                "level": "phase",
                "id": 3,
                "content": {
                    "period": 137,
                    "green_st": 68.5,
                    "green_end": 94.53,
                    "greenrate": 0.2
                }
            }
        }
    ]
    # write_to_excel(dict,'effect.xlsx')
    # 创建数据库连接
    conn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='zx', port=3306, charset='utf8')
    # 创建数据库游标对象cursor
    cur = conn.cursor()
    temp_dict = {}
    n = 1
    for li in dict:
        temp_dict['level'] = li['level']
        temp_dict['effect_id'] = li['id'] / 15 + 1
        temp_dict['period'] = li['content']['period']
        temp_dict['green_st'] = li['content']['green_st']
        temp_dict['green_end'] = li['content']['green_end']
        temp_dict['greenrate'] = li['content']['greenrate']
        temp_dict['effect_optimized_id'] = mod(li['id'],15)
        n += 1

        # 表名需修改
        table = "configuration"  # 表名
        keys = ','.join(temp_dict.keys())  # 列字段
        values = ', '.join(['%s'] * len(temp_dict))  # 行字段
        sql = 'insert into {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
        # 将字段的value转化为元祖存入
        cur.execute(sql, tuple(temp_dict.values()))
        conn.commit()
        # 关闭游标
    cur.close()
    # 关闭数据库连接
    conn.close()

出现这种现象的原因是:

原因:

1.insert 失败后,id 已自动增加,会造成 id 不连续

2.delete 后,再重新插入数据后,id 会从删除前最后的 id 开始增加,造成 id 不是从1开始增加

当mysql数据库中的表被清空后,但是insert新插入的数据的id不是从1开始, 可以试试这个方法,亲测有效!!

解决方法:

 我是在Navicate Premium15中进行mysql数据库的可视化的,

(1)先打开Navicate Premium15,连接好数据库之后,点击“新建查询”,就会出现这么一个页面:

 (2)在这个新建的查询页面里,输入命令:

truncate table table_name[表名];

注意:这块儿要写入自己数据库中存在的表名,不然会报错。 写入之后检查一遍是否写对。

(3)点击旁边的“运行”按钮之后,再次点开我的数据库zx中的表configuration,

 

发现这个id不是从1开始的问题消失了,好了,问题成功解决!!

 OK,问题解决。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

水w

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值