通过CSV将数据导入Google Cloud SQL [英] Importing Data to Google Cloud SQL via CSV

问题描述

Google Cloud SQL 限制在SQL语句中使用 LOAD DATA INFILE 。

我正在尝试制作一项功能，允许用户批量上传用户数据到我的网络应用程序，这样他们就不必逐个输入每个条目。

如何在 GAE PHP 和 Cloud SQL的限制内完成此操作？

更新：我的应用的用户可以管理学生列表。我希望我的用户能够提交CSV文件并将其输入到他们的学生数据库中。我不能使用同步或MySQL工作台等，它必须在Web应用程序内完成。

感谢您的输入！

解决方案

我最近也在寻找一种方式让电力用户批量上传。我第一次成功的尝试是变量特定的，所以检查我的映射此试用版在没有前端的情况下完成，因此您需要为用户创建一个表单以上传至您的存储桶。我的答案假定您熟悉Google Storage。

 <？php 
 
 $ databasehost =：/ CLOUDSQL /东西：这里; // CLOUD SQL INSTANCE ID的名称
 $ databasename =db; 
 $ databaseusername =insert_usr; 
 $ databasepassword =pwd; 
 $ csv =gs：//APPID.appspot.com/csv_uploads/bulk.csv; // APPID是应用程序的特定名称，其后是其他存储桶（如果有）以及filename.csv 
 
 $ column0 =''; //插入你的列的名称，如MySQL 
 $ column1 =''; 
 $ column2 =''; 
 $ column3 =''; 
 $ column4 =''; 
 $ column5 =''; 
 $ column6 =''; 
 $ column7 =''; 
 $ column8 =''; 
 $ column9 =''; 
 $ column10 =''; 
 $ column11 =''; 
 
 $ con = @mysql_connect（$ databasehost，$ databaseusername，$ databasepassword）或die（mysql_error（））; //不记得为什么我加'@'可能是错误
 @mysql_select_db（$ databasename）或者死（mysql_error（））; 
 
 $ csv_file = $ csv; // CSV文件的名称
 $ csvfile = fopen（$ csv_file，'r'）; 
 $ theData = fgets（$ csvfile）; 
 $ i = 0; $（！feof（$ csvfile））{
 $ csv_data [] = fgets（$ csvfile）; 
 
 $ csv_array = explode（，，$ csv_data [$ i]）; 
 $ insert_csv = array（）; 
 $ insert_csv [$ column0] = $ csv_array [0]; //数组[＃]按顺序与列对应
 $ insert_csv [$ column1] = $ csv_array [1]; 
 $ insert_csv [$ column2] = $ csv_array [2]; 
 $ insert_csv [$ column3] = $ csv_array [3]; 
 $ insert_csv [$ column4] = $ csv_array [4]; 
 $ insert_csv [$ column5] = $ csv_array [5]; 
 $ insert_csv [$ column6] = $ csv_array [6]; 
 $ insert_csv [$ column7] = $ csv_array [7]; 
 $ insert_csv [$ column8] = $ csv_array [8]; 
 $ insert_csv [$ column9] = $ csv_array [9]; 
 $ insert_csv [$ column10] = $ csv_array [10]; 
 $ insert_csv [$ column11] = $ csv_array [11]; 
 
 $ query =
 INSERT INTO TABLENAME（。$ column1。，。$ column1。，。$ column2。，。$ column3。，。 $ column4。  $ column5。  $ column6。  $ column7。  $ column7。  $ column9。  $ column10。 。$ ），
 VALUES（'。$ insert_csv [$ column0]。'，'。$ insert_csv [$ column1]。'，'。$ insert_csv [$ column2]。'， ' $。insert_csv [$栏3]。' ' $ insert_csv [$ column4]'， ' $。insert_csv [$ column5]'， $。insert_csv [$ column6。 '' $ insert_csv [$ column7] '' $。insert_csv [$ column8] '' $。insert_csv [$ column9] '' $。insert_csv [$ column10] 。  '' $ insert_csv [$ column11] '）; 
 $ n = mysql_query（$ query，$ con）; 
 $ i ++; 
} 
 fclose（$ csvfile）; 
 
 echo文件数据已成功导入数据库!!; 
 mysql_close（$ con）; 
？> 
  

在我的试用版中，有特殊字符的行（单引号和双引号以及连字符，我记得）没有导入所以要注意你的row_count（）。

Google Cloud SQL restricts use of LOAD DATA INFILE in SQL statements.

I'm trying to make a feature that would allow users to batch upload user data to my web app so they don't have to make each entry one by one.

How would I go about accomplishing this within the restrictions of GAE PHP and Cloud SQL?

Update: Users of my app are able to manage a list of students. I want my users to be able to submit a CSV file and have that input into their student database. I can't use sync or MySQL workbench, etc. It has to be done within the web app.

Thanks for your input!

解决方案

I too was recently looking for a way for power users to bulk upload. My first successful attempt was variable specific so check my mapping. This trial was completed without a frontend so you'll need to build a form for users to upload to your bucket. My answer assumes familiarity with Google Storage.

<?php

$databasehost = ":/cloudsql/something:here"; //Name of your CLOUD SQL INSTANCE ID
$databasename = "db";
$databaseusername ="insert_usr";
$databasepassword = "pwd";
$csv = "gs://APPID.appspot.com/csv_uploads/bulk.csv"; //APPID is the specific name of your app, followed by other bucket(s), if any, and the filename.csv

$column0 = ''; //insert the name of your columns as appeared in MySQL
$column1 = '';
$column2 = '';
$column3 = '';
$column4 = '';
$column5 = '';
$column6 = '';
$column7 = '';
$column8 = '';
$column9 = '';
$column10 = '';
$column11 = '';

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error()); //Don't remember why I added '@' might be error
@mysql_select_db($databasename) or die(mysql_error());

$csv_file = $csv; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile)) {
$csv_data[] = fgets($csvfile);
$csv_array = explode(",", $csv_data[$i]);
$insert_csv = array();
$insert_csv[$column0] = $csv_array[0]; //array[#] correspondes to columns in order
$insert_csv[$column1] = $csv_array[1];
$insert_csv[$column2] = $csv_array[2];
$insert_csv[$column3] = $csv_array[3];
$insert_csv[$column4] = $csv_array[4];
$insert_csv[$column5] = $csv_array[5];
$insert_csv[$column6] = $csv_array[6];
$insert_csv[$column7] = $csv_array[7];
$insert_csv[$column8] = $csv_array[8];
$insert_csv[$column9] = $csv_array[9];
$insert_csv[$column10] = $csv_array[10];
$insert_csv[$column11] = $csv_array[11];

$query = "
INSERT INTO TABLENAME(".$column1.",".$column1.",".$column2.",".$column3.",".$column4.",".$column5.",".$column6.",".$column7.",".$column7.",".$column9.",".$column10.",".$column11.",)
VALUES('".$insert_csv[$column0]."','".$insert_csv[$column1]."','".$insert_csv[$column2]."','".$insert_csv[$column3]."','".$insert_csv[$column4]."','".$insert_csv[$column5]."','".$insert_csv[$column6]."','".$insert_csv[$column7]."','".$insert_csv[$column8]."','".$insert_csv[$column9]."','".$insert_csv[$column10]."','".$insert_csv[$column11]."')";
$n=mysql_query($query, $con );
$i++;
}
fclose($csvfile);

echo "File data successfully imported to database!!";
mysql_close($con);
?>

In my trial, rows with special characters (single and double quotes and hyphens, that I can recall) did not import so pay attention to your row_count().

这篇关于通过CSV将数据导入Google Cloud SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！