我正在尝试创建一个脚本来加载一些CSV数据到MAMP托管的数据库中。
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Load CSV to database</title>
</head>
<body>
<?php
$elements = array(
"host" => "localhost:8888",
"username" => "inky",
"password" => "pinky",
"dbname" => "po"
);
I originally used this array to plug in the values, but hard-coded because it didn't work.
// $database = new mysqli($elements['host'], $elements['username'], $elements['password'], $elements['dbname']);
$database = new mysqli("localhost:8888", "inky", "pinky", "po");
if ($database->connect_errno) {
die("Connection failed (Error Code: " . $database->connect_errno . "): " . $database->connect_error);
}
echo "Load file<br>";
$csv_file = fopen('df2.csv', 'r');
if (!$csv_file) {
die("Failed to open file.");
}
$headers = fgetcsv($csv_file);
$columnMapping = [
'Name' => 'Name',
'Gender' => 'Gender',
'Value' => 'Value',
'Year' => 'Year',
'Measure' => 'Measure'
// ... Add mappings for all columns
];
while (($line = fgetcsv($csv_file)) !== FALSE) {
$columns = [];
$values = [];
foreach ($line as $key => $value) {
if (isset($columnMapping[$headers[$key]])) {
$columns[] = $columnMapping[$headers[$key]];
$values[] = "'" . $database->real_escape_string($value) . "'";
}
}
$query = sprintf(
"INSERT INTO baby_data (%s) VALUES (%s)",
implode(", ", $columns),
implode(", ", $values)
);
// Execute the query and handle any errors
if (!$database->query($query)) {
echo "Error executing query: " . $database->error . "<br>";
}
}
fclose($csv_file);
$database->close();
?>
</body>
</html>我相当肯定的凭据是正确的，因为我已经使用他们登录到mysql通过终端，但得到这个错误与php脚本。
Fatal error: Uncaught mysqli_sql_exception: Access denied for user 'root'@'localhost' (using password: YES) in some "/sites/csv_converter/test_connection.php":17
Stack trace:
#0 /Users/davidelks/Dropbox/Personal/php/sites/csv_converter/test_connection.php(17): mysqli->__construct('127.0.0.1', 'root', Object(SensitiveParameterValue), 'houston')
#1 {main}
thrown in /Users/davidelks/Dropbox/Personal/php/sites/csv_converter/test_connection.php on line 17让事情变得更加混乱：http：//localhost：8888/返回：您没有权限浏览此页面！
我需要执行哪些步骤才能运行此程序？
更新：只有这样你才有绝对的原创。
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Load CSV to database</title>
</head>
<body>
<?php
$elements = array(
"host" => "127.0.0.1",
"username" => "root",
"password" => "root",
"dbname" => "houston"
);
$database = new mysqli("127.0.01", "root", "root", "houston");
// $database = new mysqli($elements['host'], $elements['username'], $elements['password'], $elements['dbname']);
if ($database->connect_errno) {
die("Connection failed (Error Code: " . $database->connect_errno . "): " . $database->connect_error);
}
echo "Load file<br>";
$csv_file = fopen('../../../baby_names/df2.csv', 'r');
if (!$csv_file) {
die("Failed to open file.");
}
$headers = fgetcsv($csv_file);
$columnMapping = [
'Name' => 'Name',
'Gender' => 'Gender',
'Value' => 'Value',
'Year' => 'Year',
'Measure' => 'Measure'
// ... Add mappings for all columns
];
while (($line = fgetcsv($csv_file)) !== FALSE) {
$columns = [];
$values = [];
foreach ($line as $key => $value) {
if (isset($columnMapping[$headers[$key]])) {
$columns[] = $columnMapping[$headers[$key]];
$values[] = "'" . $database->real_escape_string($value) . "'";
}
}
$query = sprintf(
"INSERT INTO baby_data (%s) VALUES (%s)",
implode(", ", $columns),
implode(", ", $values)
);
// Execute the query and handle any errors
if (!$database->query($query)) {
echo "Error executing query: " . $database->error . "<br>";
}
}
fclose($csv_file);
$database->close();
?>
</body>
</html>
[Running] php "/Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php"
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Load CSV to database</title>
</head>
<body>
PHP Fatal error: Uncaught mysqli_sql_exception: Unknown database 'houston' in /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php:16
Stack trace:
#0 /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php(16): mysqli->__construct('127.0.01', 'root', Object(SensitiveParameterValue), 'houston')
#1 {main}
thrown in /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php on line 16
Fatal error: Uncaught mysqli_sql_exception: Unknown database 'houston' in /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php:16
Stack trace:
#0 /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php(16): mysqli->__construct('127.0.01', 'root', Object(SensitiveParameterValue), 'houston')
#1 {main}
thrown in /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php on line 16
[Done] exited with code=255 in 0.096 seconds
1条答案
按热度按时间lf5gs5x21#
CSV文件中的某些内容通常不适合数据库。当我使用PHP来调整字符串时，我通常会发现下面的操作实际上更容易。
使用带有临时表的
LOAD DATA LOCAL INFILE ...作为目标。简单的调整可以通过在
LOAD语句中使用@variables来完成。在将数据复制到“真实的”表中时，可以使用SQL语句进行更复杂的调整。
幸运的是，所有的加载和调整都可以在SQL中完成;不需要PHP（或其他应用程序）代码。快得多;更不用说打字了犯错误的机会更少。此外，我可以查看临时表，看看数据是否像这样，并在计算如何进行调整时对它执行
SELECTs。