Created
June 19, 2018 15:15
-
-
Save akira345/867855a9cdae7d72da28e3fac49849f0 to your computer and use it in GitHub Desktop.
phpでpostgresqlのcopyを使ってcsv取り込み
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
// https://stackoverflow.com/questions/9261916/php-postgresql-pdo-copy-from-stdin より | |
function executeMetaCommand($dbUser, $dbName, $dbHost, $dbPort, $command) | |
{ | |
$command = sprintf( | |
"psql -U %s -h %s -p %s -d %s -f - <<EOT\n%s\nEOT\n", | |
$dbUser, $dbHost, $dbPort, $dbName, $command | |
); | |
$streams = array( | |
array('pipe', 'r'),// stdin | |
array('pipe', 'w'),// stdout | |
array('pipe', 'w') // stderr | |
); | |
$process = proc_open($command, $streams, $pipes); | |
if (!is_resource($process)) { | |
throw new Exception("Cannot open process:\n$command"); | |
} else { | |
list(, $stdout, $stderr) = $pipes; | |
$error = stream_get_contents($stderr); | |
fclose($stderr); | |
if (strlen($error) > 0) { | |
throw new Exception("Process error:\n$error"); | |
} else { | |
$output = stream_get_contents($stdout); | |
fclose($stdout); | |
$returnCode = proc_close($process); | |
if ($returnCode === -1) { | |
throw new Exception("Process was completed incorrectly:\n$output"); | |
} else { | |
return array( | |
$returnCode, | |
$output | |
); | |
} | |
} | |
} | |
} | |
//インポート時""をNULLとして取り込む項目を指定 | |
$force_null="ins_date,upd_date"; | |
//DB接続 | |
$db_server= "127.0.0.1"; | |
$db_user="postgres"; | |
$db_name="testDb"; | |
$csv_file="/tmp/test.csv";//絶対パス | |
$table_name="test"; | |
$command = sprintf("\\copy %s FROM '%s' WITH (format csv,encoding 'utf-8',force_null(%s))", $table_name, $csv_file, $force_null); | |
//PostgreSQLへの接続は.pgpassかpg_hba.confでPWなし接続のこと。 | |
$ret = executeMetaCommand($db_user, $db_name, $db_server, '5432', $command); | |
var_dump($ret); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment