帮助MySQL LOAD DATA INFILE(Help with MySQL LOAD DATA INFILE)
我想加载一个如下所示的CSV文件:
Acct. No.,1-15 Days,16-30 Days,31-60 Days,61-90 Days,91-120 Days,Beyond 120 Days 2314134101,898.89,8372.16,5584.23,7744.41,9846.54,2896.25 2414134128,5457.61,7488.26,9594.02,6234.78,273.7,2356.13 2513918869,2059.59,7578.59,9395.51,7159.15,5827.48,3041.62 1687950783,4846.85,8364.22,9892.55,7213.45,8815.33,7603.4 2764856043,5250.11,9946.49,8042.03,6058.64,9194.78,8296.2 2865446086,596.22,7670.04,8564.08,3263.85,9662.46,7027.22 ,4725.99,1336.24,9356.03,1572.81,4942.11,6088.94 ,8248.47,956.81,8713.06,2589.14,5316.68,1543.67 ,538.22,1473.91,3292.09,6843.89,2687.07,9808.05 ,9885.85,2730.72,6876,8024.47,1196.87,1655.29
但是如果你注意到,有些字段是不完整的。 我想MySQL只会跳过缺少第一列的行。 当我运行命令时:
LOAD DATA LOCAL INFILE 'test-long.csv' REPLACE INTO TABLE accounts FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (cf_535, cf_580, cf_568, cf_569, cf_571, cf_572);
MySQL的输出是:
Query OK, 41898 rows affected, 20948 warnings (0.78 sec) Records: 20949 Deleted: 20949 Skipped: 0 Warnings: 20948
行数仅为20,949,但MySQL报告为受影响的41,898行。 为什么这样? 此外,表格中没有任何改变。 我也无法看到所产生的警告是什么。 我想使用LOAD DATA INFILE,因为它需要python半秒来更新每一行,对于一个包含20,000多条记录的文件,它会转换为2.77小时。
更新:修改代码以将自动提交设置为'False'并添加了db.commit()语句:
# Tell MySQLdb to turn off auto-commit db.autocommit(False) # Set count to 1 count = 1 while count < len(contents): if contents[count][0] != '': cursor.execute(""" UPDATE accounts SET cf_580 = %s, cf_568 = %s, cf_569 = %s, cf_571 = %s, cf_572 = %s WHERE cf_535 = %s""" % (contents[count][1], contents[count][2], contents[count][3], contents[count][4], contents[count][5], contents[count][0])) count += 1 try: db.commit() except: db.rollback()
I want to load a CSV file that looks like this:
Acct. No.,1-15 Days,16-30 Days,31-60 Days,61-90 Days,91-120 Days,Beyond 120 Days 2314134101,898.89,8372.16,5584.23,7744.41,9846.54,2896.25 2414134128,5457.61,7488.26,9594.02,6234.78,273.7,2356.13 2513918869,2059.59,7578.59,9395.51,7159.15,5827.48,3041.62 1687950783,4846.85,8364.22,9892.55,7213.45,8815.33,7603.4 2764856043,5250.11,9946.49,8042.03,6058.64,9194.78,8296.2 2865446086,596.22,7670.04,8564.08,3263.85,9662.46,7027.22 ,4725.99,1336.24,9356.03,1572.81,4942.11,6088.94 ,8248.47,956.81,8713.06,2589.14,5316.68,1543.67 ,538.22,1473.91,3292.09,6843.89,2687.07,9808.05 ,9885.85,2730.72,6876,8024.47,1196.87,1655.29
But if you notice, some of the fields are incomplete. I'm thinking MySQL will just skip the row where the first column is missing. When I run the command:
LOAD DATA LOCAL INFILE 'test-long.csv' REPLACE INTO TABLE accounts FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (cf_535, cf_580, cf_568, cf_569, cf_571, cf_572);
And the MySQL output is:
Query OK, 41898 rows affected, 20948 warnings (0.78 sec) Records: 20949 Deleted: 20949 Skipped: 0 Warnings: 20948
The number of lines is only 20,949 but MySQL reports it as 41,898 rows affected. Why so? Also, nothing really changed in the table. I also couldn't see what the warnings generated is all about. I wanted to use the LOAD DATA INFILE because it takes python half a second to update each row which translates to 2.77 hours for a file with 20,000+ records.
UPDATE: Modified the code to set auto-commit to 'False' and added a db.commit() statement:
# Tell MySQLdb to turn off auto-commit db.autocommit(False) # Set count to 1 count = 1 while count < len(contents): if contents[count][0] != '': cursor.execute(""" UPDATE accounts SET cf_580 = %s, cf_568 = %s, cf_569 = %s, cf_571 = %s, cf_572 = %s WHERE cf_535 = %s""" % (contents[count][1], contents[count][2], contents[count][3], contents[count][4], contents[count][5], contents[count][0])) count += 1 try: db.commit() except: db.rollback()
原文:https://stackoverflow.com/questions/1236971
最满意答案
您应该使用Label组件的content属性来创建自己的自定义Label,因为children和value属性只接受字符串或数字。 使用content属性,您可以传递组件:
<Label width={30} position="center" content={<CustomLabel value1={totalTasks} value2={tasksNameLabel}/>}> </Label>
和CustomLabel组件:
function CustomLabel({viewBox, value1, value2}){ const {cx, cy} = viewBox; return ( <text x={cx} y={cy} fill="#3d405c" className="recharts-text recharts-label" textAnchor="middle" dominantBaseline="central"> <tspan alignmentBaseline="middle" fontSize="26">{value1}</tspan> <tspan fontSize="14">{value2}</tspan> </text> ) }
You should use the content property of the Label component in order to create your own custom Label because both the children and the value property only accept string or numbers. With the content property you can pass a component as such:
<Label width={30} position="center" content={<CustomLabel value1={totalTasks} value2={tasksNameLabel}/>}> </Label>
and the CustomLabel component:
function CustomLabel({viewBox, value1, value2}){ const {cx, cy} = viewBox; return ( <text x={cx} y={cy} fill="#3d405c" className="recharts-text recharts-label" textAnchor="middle" dominantBaseline="central"> <tspan alignmentBaseline="middle" fontSize="26">{value1}</tspan> <tspan fontSize="14">{value2}</tspan> </text> ) }
相关问答
更多-
我将使用绘图事件将标签居中到图表的中间位置。 检查这个垃圾箱http://jsbin.com/hipafu/edit?html,css,js,output 还要注意风格: .ct-label { dominant-baseline: central; text-anchor: middle; } I'd use the draw event to center labels to the middle of the chart. Check this bin http://jsbin.com/hi ...
-
那么,现在这个百分比显然是硬编码的。要用数据的平均值替换那个硬编码的百分比,你需要用数据的平均值替换56% 。 由于用于计算每个圆环图的数据数组随时可用作data (最初为scope.data ),因此您可以执行此操作以显示平均值: svg.append("text") .attr("dy", ".35em") .style("text-anchor", "middle") .attr("class", "inside") .text(func ...
-
您可以设置textinfo='none'以获取以下圆环图,其中饼图中没有文本但显示悬停信息。 You could set textinfo='none' to get the following donut plot which has no text in the pie elements but shows info on hovering.
-
您应该使用Label组件的content属性来创建自己的自定义Label,因为children和value属性只接受字符串或数字。 使用content属性,您可以传递组件: 和CustomLabel组件: function CustomLabel({viewBox, valu ...
-
查看饼图插件代码,图例可见性控制标签可见性,如果将pie.radius设置为auto (如果未明确设置,则为默认值),该标签可见性又控制图表的半径 - 适用的代码下面)。 你偶然选择.75作为图表的innerRadius ,这就是插件在这种情况下设置为半径的内容。 当radius和innerRadius相等时,会出现您正在描述的消失现象。 // set labels.show if (options.series.pie.label.show == "auto") { if (options.leg ...
-
对于舍入,我们可以用round(百分比,2)替换百分比,对于重叠,我们可以使用ggrepel包中的geom_label_repel library(ggrepel) donut = ggplot(colour.df, aes(fill = col, ymax = ymax, ymin = ymin, xmax = 100, xmin = 80)) + geom_rect(colour = "black") + coord_polar(theta = "y") + xlim(c(0, ...
-
d3带有弯曲标签的圆环图(d3 donut chart with curved labels)[2022-04-10]
错误是您正在设置svg:text的.text属性,但您应该在svg:textPath上设置它 以下是通过在append("textPath")之后移动.text来在代码中执行此操作的方法: var text = g.append("text") .style("font-size", 20) .append("textPath") .text(function(d, i) { return segments[i]; }) .attr("textLength", functio ... -
我不知道任何设置你想要的特定位置的选项,但你可以欺骗Telerik去做。 关键是将标签放在没有背景颜色的第三个外部甜甜圈中,并排列切片的值,使它们与现在不再需要标签的第二个(交替灰色)甜甜圈相匹配。 $("#chart").kendoChart({ legend: { visible: false }, chartArea: { background: "" }, seriesDefaults: { type: "donut", startAngle: 9 ...
-
甜甜圈图表python(Donut chart python)[2022-10-02]
从文档字符串: If *autopct* is not *None*, return the tuple (*patches*, *texts*, *autotexts*), where *patches* and *texts* are as above, and *autotexts* is a list of :class:`~matplotlib.text.Text` instances for the numeric labels. 因此,如果要使用autopct解压缩pie() ... -
如何将组合图中嵌入的圆环图中的文本居中(how to center text inside donut chart embedded in combination chart)[2023-01-11]
您可以使用renderer在图表上添加自定义文本。 然后,您可以使用element.on()添加事件。 查看实例: http : //jsfiddle.net/sJfuA/2/ $('#container').highcharts({ chart: { events: { load: function() { var chart = this, ...