这篇文章主要围绕mysql-connector上的LOADDATALOCALINFILE不工作spring-framework和mysqlload_file展开，旨在为您提供一份详细的参考资料。我们将
这篇文章主要围绕mysql-connector 上的 LOAD DATA LOCAL INFILE 不工作 spring-frame work和mysql load_file展开，旨在为您提供一份详细的参考资料。我们将全面介绍mysql-connector 上的 LOAD DATA LOCAL INFILE 不工作 spring-frame work的优缺点，解答mysql load_file的相关问题，同时也会为您带来as.data.frame.default (data) 中的错误无法将类“公式”强制转换为 data.frame、Docker Container MySQL和PHP“ LOAD DATA LOCAL INFILE”权限错误、JPA 通过 LOAD DATA LOCAL INFILE 大批量导入数据到 MySQL、Laravel - 使用 LOAD DATA LOCAL INFILE 导入 CSV - 导入失败的实用方法。
本文目录一览：- mysql-connector 上的 LOAD DATA LOCAL INFILE 不工作 spring-frame work（mysql load_file）
- as.data.frame.default (data) 中的错误无法将类“公式”强制转换为 data.frame
- Docker Container MySQL和PHP“ LOAD DATA LOCAL INFILE”权限错误
- JPA 通过 LOAD DATA LOCAL INFILE 大批量导入数据到 MySQL
- Laravel - 使用 LOAD DATA LOCAL INFILE 导入 CSV - 导入失败
mysql-connector 上的 LOAD DATA LOCAL INFILE 不工作 spring-frame work（mysql load_file）
如何解决mysql-connector 上的 LOAD DATA LOCAL INFILE 不工作 spring-frame work
将数据本地文件“/home/sample.txt”加载到由“|”终止的表临时字段中可选地由 ''"'' 行以 ''\\n'' 结尾
例外：此 MysqL 版本不允许使用的命令。 无法翻译错误代码为“1148”的 sqlException，现在将尝试回退翻译器
在 pom.xml org.springframework.boot spring-boot-starter-parent 2.5.0 MysqL mysql-connector-java runtime 中使用 2.5 spring 版本
MysqL DB 连接中的参数添加：&allowLoadLocalInfile=true 和 DATA LOCAL INFILE =1 是在 DB 上更新
as.data.frame.default (data) 中的错误无法将类“公式”强制转换为 data.frame
如何解决as.data.frame.default (data) 中的错误无法将类“公式”强制转换为 data.frame
我的代码如下：
get_postcoefs <- function(portfo){my_dat <- prerank_betas %>%filter(portfo == portfo) %>%lm(ret ~ ewr,my_dat) %>%coef %>%as.list %>%as_data_frame}
当我想在下一步中使用此代码应用此功能时
postrank <- prerank_betas %>%group_by(portfo) %>%do(get_postcoefs(.$portfo))
我使用的数据框如下所示：
dput(head(prerank_betas,10))structure(list(permco = c(3,4,5,6,7,8,9,11,12,13),pre_beta = c(0.754759259550561,0.631020855428056,0.963497668377108,1.42359914669436,1.88321141160762,0.137054776055511,1.04141132820461,0.170163365604386,1.07633721793778,1.05016503010496),ret = c(0.021630734879652,0.00867405735757635,0.0157192335910029,0.0163030885650139,0.017402600558639,0.0182427638210356,0.015755719798324,0.0348026989282579,0.0120230854319578,0.016944221076395),me = c(12.3938081896552,603.599033139535,36.6372490671642,20.481490497076,2918.12852836134,1.89075555555556,1.21730113636364,5.5216014957265,116.021340472028,8.22907327586207),ewr = c(0.454914743929347,0.65175605642766,1.04015768854358,1.54966348955938,1.46542203513179,0.874404877119168,0.934768449855933,0.296266764535612,0.949971716508229,1.31022003302531),beta_rank = c(3L,3L,5L,8L,10L,1L,6L,6L),portfo = c(4L,2L,3L)),row.names = c(NA,-10L),class = c("tbl_df","tbl","data.frame"))
一点点：10 x 7
permco pre_beta ret me ewr beta_rank portfo<dbl> <dbl> <dbl> <dbl> <dbl> <int> <int>1 3 0.755 0.0216 12.4 0.455 3 42 4 0.631 0.00867 604. 0.652 3 103 5 0.963 0.0157 36.6 1.04 5 64 6 1.42 0.0163 20.5 1.55 8 55 7 1.88 0.0174 2918. 1.47 10 106 8 0.137 0.0182 1.89 0.874 1 17 9 1.04 0.0158 1.22 0.935 6 18 11 0.170 0.0348 5.52 0.296 1 29 12 1.08 0.0120 116. 0.950 6 810 13 1.05 0.0169 8.23 1.31 6 3
我收到以下错误消息：
Error in as.data.frame.default(data) :cannot coerce class ‘"formula"’ to a data.frame
我必须如何调整我的代码才能正常工作。
解决方法
从 OP 代码看来，问题是尝试按投资组合运行 lm() 并为所有投资组合回归创建系数的输出数据框。
如评论中所述，原帖中的代码失败，因为当 R 尝试处理表达式 filter() 时，portfo = portfo 函数包含 quasiquotation 冲突。
缺少一个最小的可重复示例，这里有一种使用 purrr::map() 和 broom::tidy() 在 mtcars 数据框上运行线性模型的方法。
由于我们按 mtcars$cyl 拆分数据，因此不需要 OP 中使用的 filter() 函数。
library(dplyr)library(purrr)library(broom)mtcars %>%split(.$cyl) %>%purrr::map(.,function(x){lm(mpg ~ wt,data = x) %>%tidy(.)}) -> results# combine into a data framedf <- as.data.frame(do.call(rbind,results))# extract cyl from rownamesdf$cyl <- substr(rownames(df),1,1)
...和输出：
term estimate std.error statistic p.value cyl4.1 (Intercept) 39.571196 4.3465820 9.103980 7.771511e-06 44.2 wt -5.647025 1.8501185 -3.052251 1.374278e-02 46.1 (Intercept) 28.408845 4.1843688 6.789278 1.054844e-03 66.2 wt -2.780106 1.3349173 -2.082605 9.175766e-02 68.1 (Intercept) 23.868029 3.0054619 7.941551 4.052705e-06 88.2 wt -2.192438 0.7392393 -2.965803 1.179281e-02 8>
原始海报数据的解决方案
将最近发布的数据修改为每个 portfo 值至少有 5 个观察值后，处理股票数据的解决方案如下所示。
textData <- "id permco pre_beta ret me ewr beta_rank portfo1 3 0.755 0.0216 12.4 0.455 3 12 4 0.631 0.00867 604. 0.652 3 13 5 0.963 0.0157 36.6 1.04 5 14 6 1.42 0.0163 20.5 1.55 8 15 7 1.88 0.0174 2918. 1.47 10 16 8 0.137 0.0182 1.89 0.874 1 17 3 0.755 0.0216 12.4 0.455 3 28 4 0.631 0.00867 604. 0.652 3 29 5 0.963 0.0157 36.6 1.04 5 210 6 1.42 0.0163 20.5 1.55 8 211 7 1.88 0.0174 2918. 1.47 10 212 8 0.137 0.0182 1.89 0.874 1 2"
注意：通过复制值和调整 portfo 标识符，我们可以演示解决方案，知道生成的两个模型将具有完全相同的系数，因为它们具有相同的输入数据.
prerank_betas <- read.table(text=textData,header=TRUE)library(dplyr)library(purrr)library(broom)prerank_betas %>%split(.$portfo) %>%purrr::map(.,function(x){lm(ret ~ ewr,data = x) %>%tidy(.)}) -> results# combine into a data framedf <- as.data.frame(do.call(rbind,results))df$portfo <- as.numeric(gsub(".$","",rownames(df)))df
...和输出：
term estimate std.error statistic p.value portfo1.1 (Intercept) 1.629081e-02 0.005291043 3.078941068 0.03696967 11.2 ewr 2.071675e-05 0.004884269 0.004241526 0.99681887 12.1 (Intercept) 1.629081e-02 0.005291043 3.078941068 0.03696967 22.2 ewr 2.071675e-05 0.004884269 0.004241526 0.99681887 2
Docker Container MySQL和PHP“ LOAD DATA LOCAL INFILE”权限错误
如何解决Docker Container MySQL和PHP“ LOAD DATA LOCAL INFILE”权限错误
我在机器上运行了一个docker容器设置，
- PHP Web服务器容器
- MysqL 8.0容器
- PHPMyAdmin容器
以下是我的docker-compose.yml文件的大致内容：
version: ''3.3''
services:
front:
build:
context: ./front
dockerfile: Dockerfile
container_name: front-end
depends_on:
- db
ports:
- 8000:80
# PHPmyadmin
PHPmyadmin:
container_name: PHPmyadmin
depends_on:
- db
image: PHPmyadmin/PHPmyadmin
restart: always
ports:
- "8080:80"
environment:
PMA_HOST: db
MysqL_ROOT_PASSWORD: derp
db:
container_name: MysqL8
image: MysqL:8.0
command: --default-authentication-plugin=MysqL_native_password
restart: always
environment:
MysqL_ROOT_PASSWORD: root
MysqL_DATABASE: dev_db
MysqL_USER: user
MysqL_PASSWORD: pass
ports:
- 6033:3306
在前面部分，这是Dockerfile
FROM PHP:7.3.3-apache
RUN apt-get update && apt-get upgrade -y libfreetype6-dev libjpeg62-turbo-dev libpng-dev
RUN docker-PHP-ext-install MysqLi
RUN docker-PHP-ext-install pdo pdo_MysqL
RUN docker-PHP-ext-configure gd --with-freetype-dir=/usr --with-jpeg-dir=/usr --with-png-dir=/usr
RUN docker-PHP-ext-install gd
RUN a2enmod rewrite
EXPOSE 80
没有什么太花哨的了吧？可能。
无论如何，我正在开发一项功能，该功能允许上传.csv文件，然后将其加载到MysqL表中。当我尝试执行此操作时，遇到了一些有趣的错误，并且在广泛进行了深入的研究之后，我没有找到可靠的解决方案。
基本上，当我尝试执行此查询时：
try {
$sql = "LOAD DATA LOCAL INFILE ''" . $tmpName . "''
INTO TABLE psy_products_temp_sales
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''\\"''
LInes TERMINATED BY ''\\r\\n''
(@col1,@col2,@col3,@col4,@col5,@col6)
SET
product_temp_name = @col1,product_temp_sku = @col2,product_temp_posted_date = @col3,product_temp_sales_period = @col4,product_temp_qty_sold = @col5,product_temp_total_earned = @col6;";
$sql = $pdo->prepare($sql);
$sql->execute(PDO::MysqL_ATTR_USE_BUFFERED_QUERY);
} catch (Exception $e) {
$output .= "Error: " . $e;
}
我的PDO对象的设置如下：
$dsn = "MysqL:host=" . $host . ";dbname=" . $db . ";charset=" . $charset;
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,PDO::MysqL_ATTR_LOCAL_INFILE => true,PDO::ATTR_EMULATE_PREPARES => false
];
try {
$pdo = new PDO($dsn,$user,$pass,$options);
} catch (\\PDOException $e) {
$log->error(
"Could not establish database connection",["message" => $e->getMessage()]);
throw new \\PDOException($e->getMessage(),(int)$e->getCode());
}
请注意，已设置 PDO :: MysqL_ATTR_LOCAL_INFILE => true 标志，并在我的PHP.ini中将 MysqLi.allow_local_infile 设置为 ON。
因此，当我尝试运行此命令时，没有收到错误，但是MysqL并未读取上载的.csv文件并将其加载到表中。我实际上可以直接使用 fopen（）读取该临时.csv文件，但是MysqL拒绝读取它。这些.csv文件具有成千上万的行，因此我不会遍历每一行并运行查询。没办法。
当我尝试在PHPMyAdmin中运行它时，出现此错误：
禁止LOAD DATA LOCAL INFILE，请检查MysqLi.allow_local_infile
然后，当我尝试在CLI的MysqL容器本身中运行该查询时，出现此错误：
错误2068（HY000）：由于访问限制，拒绝了LOAD DATA LOCAL INFILE文件请求。
唯一有意义的是， MysqL8 容器被拒绝访问 front 容器的/ tmp目录？
在这种情况下，如何设置授予许可的位置？我不是最熟悉CLI或Docker容器交叉通信的人，但是必须有一种方法为此工作。
抱歉，这变得冗长了。预先感谢！
JPA 通过 LOAD DATA LOCAL INFILE 大批量导入数据到 MySQL
连接层代码：
import org.hibernate.Session;
import org.hibernate.internal.SessionFactoryImpl;
import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public int loadFromInputStream(String loadDataSql, InputStream dataStream) throws JPAException {
Session session = (Session) entityManagerFactory.getDelegate();
SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory();
int result = 0;
try {
//获取C3P0连接
Connection conn = sessionFactory.getConnectionProvider().getConnection();
PreparedStatement ps = conn.prepareStatement(loadDataSql);
//将C3P0的连接转换成mysql的
C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
com.mysql.jdbc.Connection mysql_conn = (com.mysql.jdbc.Connection) cp30NativeJdbcExtractor.getNativeConnection(ps.getConnection());
//将C3P0的PreparedStatement转换成mysql的
com.mysql.jdbc.PreparedStatement mysql_ps = mysql_conn.prepareStatement(loadDataSql).unwrap(com.mysql.jdbc.PreparedStatement.class);
//设置文件流
mysql_ps.setLocalInfileInputStream(dataStream);
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}使用连接层代码：
public void testLoadFile() throws ServiceException {
String loadDataSql = "load data LOCAL infile ''C:/Users/Administrator/Desktop/userinfo.txt'' into table userinfo character set utf8 fields terminated by '','' enclosed by ''\"'' lines terminated by ''\r\n'' (`userId`,`userName`,`userNickName`,`createTime`,`userEmail`,`userPassword`,`userSex`,`userMobile`,`lastLoginTime`);";
byte[] bytes = loadDataSql.getBytes();
InputStream is = new ByteArrayInputStream(bytes);
try {
userDAO.testLoadFile(loadDataSql, is);
} catch (JPAException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}userinfo.txt 内容：
"2","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"3","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"4","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"5","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"6","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"7","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"8","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"9","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"10","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"11","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"12","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"13","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"14","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"15","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"16","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
"17","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"
userinfo 表结构：
CREATE TABLE `userinfo` (
`userId` varchar(36) NOT NULL,
`userName` varchar(45) DEFAULT NULL,
`userNickName` varchar(45) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
`userEmail` varchar(45) DEFAULT NULL,
`userPassword` varchar(45) DEFAULT NULL,
`userSex` int(11) DEFAULT NULL,
`userMobile` varchar(45) DEFAULT NULL,
`lastLoginTime` datetime DEFAULT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Laravel - 使用 LOAD DATA LOCAL INFILE 导入 CSV - 导入失败
如何解决Laravel - 使用 LOAD DATA LOCAL INFILE 导入 CSV - 导入失败
我尝试导入 csv 文件
这是我的代码：
private function importFileContents($file_path)
{
$query = sprintf("
LOAD DATA LOCAL INFILE ''%s'' INTO TABLE grantee_lists
CHaraCTER SET latin1
FIELDS TERMINATED BY '',''
LInes TERMINATED BY ''\\r\\n''
IGnorE 1 LInes
(@col1,@col2,@col3,@col4) SET region = @col1,province = @col2,municipality = @col3,barangay = @col4
",addslashes($file_path));
$data = DB::connection()->getpdo()->exec($query);
}
这是 Dump & Die (dd)：
这是导入时的结果
没有显示错误，也没有数据导入到表中 需要帮助，我认为错误出在我的 $file_path
解决方法
我终于明白了。
正确的命令是
LINES TERMINATED BY ''\\n''
而不是
LINES TERMINATED BY ''\\r\\n''
今天关于mysql-connector 上的 LOAD DATA LOCAL INFILE 不工作 spring-frame work和mysql load_file的讲解已经结束，谢谢您的阅读，如果想了解更多关于as.data.frame.default (data) 中的错误无法将类“公式”强制转换为 data.frame、Docker Container MySQL和PHP“ LOAD DATA LOCAL INFILE”权限错误、JPA 通过 LOAD DATA LOCAL INFILE 大批量导入数据到 MySQL、Laravel - 使用 LOAD DATA LOCAL INFILE 导入 CSV - 导入失败的相关知识，请在本站搜索。
本文标签：
