从Python更新sqlite表时出现问题（将列作为其他两个列值的商添加）

2024-04-24 19:48:01 发布

您现在位置：Python中文网/ 问答频道 /正文
2078 3

以下是我想做的：

  1. 从csv（附件）创建sqlite表
  2. 为正常运行时间百分比添加一个新列

  3. 计算正常运行时间百分比并更新新列(状态（向上/组总计）

    从输出中可以看出，百分比结果是无效的。我猜我的alter\u db函数还是有问题的，但是我还没有在文档中找到正确的方法

任何建议都非常感谢

import csv
import sqlite3
import sys

sys.path.append('/home/run/today/')

db = sqlite3.connect(':memory:')

def init_db(cur):
    cur.execute('''CREATE TABLE groupstats (
        group_name TEXT,
        group_alias TEXT,
        group_total REAL,
        status_up REAL,
        status_down REAL)''')

def populate_db(cur, csv_fp):
    rdr = csv.reader(csv_fp)
    cur.executemany('''
        INSERT INTO groupstats (group_name, group_alias, group_total, status_up, status_down)
        VALUES (?,?,?,?,?)''', rdr)

def alter_db(cur):
    cur.execute('''ALTER TABLE groupstats ADD COLUMN percent_up REAL''')

def update_db(cur):
    cur.execute('''UPDATE groupstats SET percent_up = round(2, status_up / group_total)''')

cur = db.cursor()

init_db(cur)
populate_db(cur, open('/home/run/today/service_stats.csv'))

以下是csv：

group_name,group_alias,group_total,status_up,status_down
Group_1,Group_1 Alias,22,18,4
Group_2,Group_2 Alias,7,7,0
Group_3,Group_3 Alias,22,22,0
Group_4,Group_4 Alias,14,14,0
Group_5,Group_5 Alias,50,21,29
Group_6,Group_6 Alias,30,30,0
Group_7,Group_7 Alias,3,2,1
Group_8,Group_8 Alias,10,10,0
Group_9,Group_9 Alias,10,10,0
Group_10,Group_10 Alias,12,11,1
Group_11,Group_11 Alias,9,9,0
Group_12,Group_12 Alias,1,1,0
Group_13,Group_13 Alias,16,14,2
Group_14,Group_14 Alias,18,8,10
Group_15,Group_15 Alias,25,24,1
Group_16,Group_16 Alias,2,2,0
Group_17,Group_17 Alias,8,1,7
Group_18,Group_18 Alias,6,1,5
Group_19,Group_19 Alias,2,2,0
Group_20,Group_20 Alias,44,39,5
Group_21,Group_21 Alias,12,12,0
Group_22,Group_22 Alias,8,8,0
Group_23,Group_23 Alias,8,8,0
Group_24,Group_24 Alias,56,54,2
Group_25,Group_25 Alias,3,1,2
Group_26,Group_26 Alias,14,12,2
Group_27,Group_27 Alias,10,10,0
Group_28,Group_28 Alias,2,2,0
Group_29,Group_29 Alias,2,1,1
Group_30,Group_30 Alias,23,23,0

query = 'SELECT * FROM groupstats'
cur.execute(query)
cur.fetchall()

[(u'group_name', u'group_alias', u'group_total', u'status_up', u'status_down'), (u'Group_1', u'Group_1 Alias', 22.0, 18.0, 4.0), (u'Group_2', u'Group_2 Alias', 7.0, 7.0, 0.0), (u'Group_3', u'Group_3 Alias', 22.0, 22.0, 0.0), (u'Group_4', u'Group_4 Alias', 14.0, 14.0, 0.0), (u'Group_5', u'Group_5 Alias', 50.0, 21.0, 29.0), (u'Group_6', u'Group_6 Alias', 30.0, 30.0, 0.0), (u'Group_7', u'Group_7 Alias', 3.0, 2.0, 1.0), (u'Group_8', u'Group_8 Alias', 10.0, 10.0, 0.0), (u'Group_9', u'Group_9 Alias', 10.0, 10.0, 0.0), (u'Group_10', u'Group_10 Alias', 12.0, 11.0, 1.0), (u'Group_11', u'Group_11 Alias', 9.0, 9.0, 0.0), (u'Group_12', u'Group_12 Alias', 1.0, 1.0, 0.0), (u'Group_13', u'Group_13 Alias', 16.0, 14.0, 2.0), (u'Group_14', u'Group_14 Alias', 18.0, 8.0, 10.0), (u'Group_15', u'Group_15 Alias', 25.0, 24.0, 1.0), (u'Group_16', u'Group_16 Alias', 2.0, 2.0, 0.0), (u'Group_17', u'Group_17 Alias', 8.0, 1.0, 7.0), (u'Group_18', u'Group_18 Alias', 6.0, 1.0, 5.0), (u'Group_19', u'Group_19 Alias', 2.0, 2.0, 0.0), (u'Group_20', u'Group_20 Alias', 44.0, 39.0, 5.0), (u'Group_21', u'Group_21 Alias', 12.0, 12.0, 0.0), (u'Group_22', u'Group_22 Alias', 8.0, 8.0, 0.0), (u'Group_23', u'Group_23 Alias', 8.0, 8.0, 0.0), (u'Group_24', u'Group_24 Alias', 56.0, 54.0, 2.0), (u'Group_25', u'Group_25 Alias', 3.0, 1.0, 2.0), (u'Group_26', u'Group_26 Alias', 14.0, 12.0, 2.0), (u'Group_27', u'Group_27 Alias', 10.0, 10.0, 0.0), (u'Group_28', u'Group_28 Alias', 2.0, 2.0, 0.0), (u'Group_29', u'Group_29 Alias', 2.0, 1.0, 1.0), (u'Group_30', u'Group_30 Alias', 23.0, 23.0, 0.0)]

alter_db(cur)

cur.execute(query)
cur.fetchall()

[(u'group_name', u'group_alias', u'group_total', u'status_up', u'status_down', None), (u'Group_1', u'Group_1 Alias', 22.0, 18.0, 4.0, None), (u'Group_2', u'Group_2 Alias', 7.0, 7.0, 0.0, None), (u'Group_3', u'Group_3 Alias', 22.0, 22.0, 0.0, None), (u'Group_4', u'Group_4 Alias', 14.0, 14.0, 0.0, None), (u'Group_5', u'Group_5 Alias', 50.0, 21.0, 29.0, None), (u'Group_6', u'Group_6 Alias', 30.0, 30.0, 0.0, None), (u'Group_7', u'Group_7 Alias', 3.0, 2.0, 1.0, None), (u'Group_8', u'Group_8 Alias', 10.0, 10.0, 0.0, None), (u'Group_9', u'Group_9 Alias', 10.0, 10.0, 0.0, None), (u'Group_10', u'Group_10 Alias', 12.0, 11.0, 1.0, None), (u'Group_11', u'Group_11 Alias', 9.0, 9.0, 0.0, None), (u'Group_12', u'Group_12 Alias', 1.0, 1.0, 0.0, None), (u'Group_13', u'Group_13 Alias', 16.0, 14.0, 2.0, None), (u'Group_14', u'Group_14 Alias', 18.0, 8.0, 10.0, None), (u'Group_15', u'Group_15 Alias', 25.0, 24.0, 1.0, None), (u'Group_16', u'Group_16 Alias', 2.0, 2.0, 0.0, None), (u'Group_17', u'Group_17 Alias', 8.0, 1.0, 7.0, None), (u'Group_18', u'Group_18 Alias', 6.0, 1.0, 5.0, None), (u'Group_19', u'Group_19 Alias', 2.0, 2.0, 0.0, None), (u'Group_20', u'Group_20 Alias', 44.0, 39.0, 5.0, None), (u'Group_21', u'Group_21 Alias', 12.0, 12.0, 0.0, None), (u'Group_22', u'Group_22 Alias', 8.0, 8.0, 0.0, None), (u'Group_23', u'Group_23 Alias', 8.0, 8.0, 0.0, None), (u'Group_24', u'Group_24 Alias', 56.0, 54.0, 2.0, None), (u'Group_25', u'Group_25 Alias', 3.0, 1.0, 2.0, None), (u'Group_26', u'Group_26 Alias', 14.0, 12.0, 2.0, None), (u'Group_27', u'Group_27 Alias', 10.0, 10.0, 0.0, None), (u'Group_28', u'Group_28 Alias', 2.0, 2.0, 0.0, None), (u'Group_29', u'Group_29 Alias', 2.0, 1.0, 1.0, None), (u'Group_30', u'Group_30 Alias', 23.0, 23.0, 0.0, None)]

update_db(cur)

cur.execute(query)
cur.fetchall()

[(u'group_name', u'group_alias', u'group_total', u'status_up', u'status_down', None), (u'Group_1', u'Group_1 Alias', 22.0, 18.0, 4.0, 2.0), (u'Group_2', u'Group_2 Alias', 7.0, 7.0, 0.0, 2.0), (u'Group_3', u'Group_3 Alias', 22.0, 22.0, 0.0, 2.0), (u'Group_4', u'Group_4 Alias', 14.0, 14.0, 0.0, 2.0), (u'Group_5', u'Group_5 Alias', 50.0, 21.0, 29.0, 2.0), (u'Group_6', u'Group_6 Alias', 30.0, 30.0, 0.0, 2.0), (u'Group_7', u'Group_7 Alias', 3.0, 2.0, 1.0, 2.0), (u'Group_8', u'Group_8 Alias', 10.0, 10.0, 0.0, 2.0), (u'Group_9', u'Group_9 Alias', 10.0, 10.0, 0.0, 2.0), (u'Group_10', u'Group_10 Alias', 12.0, 11.0, 1.0, 2.0), (u'Group_11', u'Group_11 Alias', 9.0, 9.0, 0.0, 2.0), (u'Group_12', u'Group_12 Alias', 1.0, 1.0, 0.0, 2.0), (u'Group_13', u'Group_13 Alias', 16.0, 14.0, 2.0, 2.0), (u'Group_14', u'Group_14 Alias', 18.0, 8.0, 10.0, 2.0), (u'Group_15', u'Group_15 Alias', 25.0, 24.0, 1.0, 2.0), (u'Group_16', u'Group_16 Alias', 2.0, 2.0, 0.0, 2.0), (u'Group_17', u'Group_17 Alias', 8.0, 1.0, 7.0, 2.0), (u'Group_18', u'Group_18 Alias', 6.0, 1.0, 5.0, 2.0), (u'Group_19', u'Group_19 Alias', 2.0, 2.0, 0.0, 2.0), (u'Group_20', u'Group_20 Alias', 44.0, 39.0, 5.0, 2.0), (u'Group_21', u'Group_21 Alias', 12.0, 12.0, 0.0, 2.0), (u'Group_22', u'Group_22 Alias', 8.0, 8.0, 0.0, 2.0), (u'Group_23', u'Group_23 Alias', 8.0, 8.0, 0.0, 2.0), (u'Group_24', u'Group_24 Alias', 56.0, 54.0, 2.0, 2.0), (u'Group_25', u'Group_25 Alias', 3.0, 1.0, 2.0, 2.0), (u'Group_26', u'Group_26 Alias', 14.0, 12.0, 2.0, 2.0), (u'Group_27', u'Group_27 Alias', 10.0, 10.0, 0.0, 2.0), (u'Group_28', u'Group_28 Alias', 2.0, 2.0, 0.0, 2.0), (u'Group_29', u'Group_29 Alias', 2.0, 1.0, 1.0, 2.0), (u'Group_30', u'Group_30 Alias', 23.0, 23.0, 0.0, 2.0)]

Tags： csvnamenoneexecutedbstatusgroupalias
1条回答
网友
1楼 · 发布于 2024-04-24 19:48:01

上面写着：

The round(X,Y) function returns a floating-point value X rounded to Y digits to the right of the decimal point.

在您的程序中，它返回值2，四舍五入到status_up / group_total位

相关问题 更多 >

    编程相关推荐