通过php在网页中导入CSV文件到postgres [英] import CSV file into postgres via php in web page

问题描述

我试图建立一个网络界面，将允许人们将一个csv文件的内容导入到postgresql数据库。下面是我想要做的php组件，但是当我运行我只是得到有一个问题上传您的数据：从我的命令，但没有其他错误。

对于这个例子，我已经硬编码csv文件的值，但是用户的目的是指向他们的机器或网络位置的csv文件。 / p>

 <？php 
 
 if（$ _POST ['submit']）{
 //尝试连接
 $ dbh = pg_connect（host = localhost dbname = blah user = user password = pass123）; 
 if（！$ dbh）{
 die（Error in connection：。pg_last_error（））; 
} 
 //执行查询
 $ sql =COPY个人详细信息（姓名，电子邮件，邮政地址，电话，用户名，密码）FROM'../Test/Book1.csv'WITH DELIMITER' ，'CSV'; 
 
 $ result = pg_query（$ dbh，$ sql）; 
 if（！$ result）{
 die（上传您的数据时出现问题：pg_last_error（））; 
} 
 
 echo数据成功插入！ 
 
 //可用内存
 pg_free_result（$ result）; 
 
 //关闭连接
 pg_close（$ dbh）; 
} 
 
？> 
  

我非常感谢您提供帮助。

解决方案

COPY FROM文件必须由超级用户帐户运行，此限制通常不适合Web使用

摘自文档< a>：

COPY命名文件只允许数据库超级用户，因为
允许读取或写入任何文件服务器具有
访问的权限。

但是PHP提供对 COPY FROM stdin 没有这个限制。
这个例子从php文档显示如何完成：

  $ conn = pg_pconnect（dbname = foo）; 
 pg_query（$ conn，create table bar（a int4，b char（16），d float8））; 
 pg_query（$ conn，copy bar from stdin）; 
 pg_put_line（$ conn，3\thello world\t4.5 \\\
）; 
 pg_put_line（$ conn，4\tgoodbye world\t7.11 \\\
）; 
 pg_put_line（$ conn，\\.\\\
）; 
 pg_end_copy（$ conn）; 
  

在文件的情况下，您需要使用php函数打开文件，逐行到 pg_put_line（）

的postgres连接

Hi I'm trying to build a web interface that will allow people to import the contents of a csv file into a postgresql database. Below is the php component of what I'm trying to do, however when I run I just get "There was a problem uploading your data:" from my die command but with no other error.

For this example, I have hardcoded a value for the csv file, however the intent is for the user to point at a csv file on their machine or network location.

<?php

if ($_POST['submit']) {
// attempt a connection
$dbh = pg_connect("host=localhost dbname=blah user=user password=pass123");
if (!$dbh) {
die("Error in connection: " . pg_last_error());
}
// execute query
$sql = "COPY personaldetails(name, email, postaladdress, phone, username, password) FROM '../Test/Book1.csv' WITH DELIMITER ',' CSV";

$result = pg_query($dbh, $sql);
if (!$result) {
die("There was a problem uploading you data: " . pg_last_error());
}

echo "Data successfully inserted!";

// free memory
pg_free_result($result);

// close connection
pg_close($dbh);
}   

?>

I appreciate any help that can be offered.

解决方案

COPY FROM file must be run by a superuser account and this restriction makes it generally unsuitable for web usage

Excerpt from the documentation:

COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

But PHP provides support for COPY FROM stdin which does not have this restriction. This example from php documentation shows how it's done:

  $conn = pg_pconnect("dbname=foo");
  pg_query($conn, "create table bar (a int4, b char(16), d float8)");
  pg_query($conn, "copy bar from stdin");
  pg_put_line($conn, "3\thello world\t4.5\n");
  pg_put_line($conn, "4\tgoodbye world\t7.11\n");
  pg_put_line($conn, "\\.\n");
  pg_end_copy($conn);

In the case of a file, you'll need to open the file with php functions and feed it line by line to the postgres connection with pg_put_line()

这篇关于通过php在网页中导入CSV文件到postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！