从PDO准备语句写入大型CSV文件 [英] Writing large CSV file from PDO prepared statement

问题描述

首先，已经有一个具有相同标题的问题（编写一个非常大的CSV文件从PHP中的DB输出），虽然处理一个单独的问题（不适当地使用 file_put_contents ）。 / p>

我正在处理一个无缓冲的PDO预准备语句，逐行执行，并将输出写入文件。然而，无论什么原因，PHP产生超过500M的内存。无论数据集有多大，我都不希望PHP不使用任何东西。

 <？php 
 $ format = [
'name'=> function（$ e）{return $ e} 
]; 
 
 $ sth = $ db 
  - > prepare（[..]）; 
 
 $ db 
  - > execute（[]，[\ PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY => false]）; //这是不正确的。 $ driver_options是`prepare`方法的一个参数。 
 
 
 $ output_file = tempnam（'/ tmp'，'mama-report-'）; 
 
 $ fp = fopen（$ output_file，'wb'）; 
 
 while（$ row = $ this-> stmt-> fetch（\ PDO :: FETCH_ASSOC））{＃line 49（see comments）
 foreach name => $ f）{
 if（isset（$ row [$ name]））{
 $ row [$ name] = $ f（$ row [$ name]）; 
} 
} 
 
 fputcsv（$ fp，array_values（$ row））; 
} 
 
 fclose（$ fp）; 
  

如何将预处理语句的已处理输出写入CSV？

$ b $看看你的实现我有一种感觉，一组PDOStatement的 $ driver_options 不同于PDO对象的设置选项。从文档中不清楚，但关于手册页的注意事项有点怀疑我。它似乎只接受只影响语句，而不是整个连接的选项。

所以，我会设置一个选项通常的方式，像你做的：

  $ db-> setAttribute（PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY，0）; 
  

First of, there is already a question with the same title (Writing a very large CSV file From DB output in PHP), though dealing with a separate issue (improper use of file_put_contents).

I am processing an unbuffered PDO prepared statement, doing this row by row, and writing the output to a file. However, for whatever reason PHP is spawning more than 500M of memory. Regardless of how big the dataset is, I don't expect PHP to use nothing as much.

<?php
$format = [
    'name' => function ($e) { return $e }
];

$sth = $db
    ->prepare("[..]");

$db
    ->execute([], [\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false]); // This is incorrect. $driver_options is a parameter of `prepare` method.


$output_file = tempnam('/tmp', 'mama-report-');

$fp = fopen($output_file, 'wb');

while ($row = $this->stmt->fetch(\PDO::FETCH_ASSOC)) { # line 49 (see comments)
    foreach ($format as $name => $f) {
        if (isset($row[$name])) {
            $row[$name] = $f($row[$name]);
        }
    }

    fputcsv($fp, array_values($row));
}

fclose($fp);

How to I write a processed output of the prepared statement to CSV?

解决方案

Looking at your implementation I have a feeling that set of PDOStatement's $driver_options is different from PDO object's set of options. It is unclear from documentation, but the note on manual page looks somewhat suspicious to me. It seems it accepts only options that affect statement only, not the whole connection.

So, I would set an option usual way, like you did:

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, 0);

这篇关于从PDO准备语句写入大型CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！