一个主机一个域名做网站,企业通讯软件app,小程序快速搭建平台,云主机能干什么在平时的工作中#xff0c;经常会遇到需要将文件数据导入到数据库中的情况。有些客户之前可能只使用Excel表格作为记录工具#xff0c;但当数据量达到一定程度或者需要将数据导入到其他系统中时#xff0c;就会很emo,因为Excel表格虽然方便#xff0c;但在数据处理和管理方…在平时的工作中经常会遇到需要将文件数据导入到数据库中的情况。有些客户之前可能只使用Excel表格作为记录工具但当数据量达到一定程度或者需要将数据导入到其他系统中时就会很emo,因为Excel表格虽然方便但在数据处理和管理方面却存在很多限制。此时就需要将Excel表格中的数据导入到MySQL数据库中以便更好地管理和利用这些数据。除了Excel表格有时还需要处理CSV或XML格式的数据比如某些数据源或第三方平台获取的数据可能就是这两种格式我遇到过某设备导出的数据为欧洲千分位CSV的格式用户自己处理起来就更emo了。
给MySQL数据库读取文件赋权
在进行转换前需要先给MySQL数据库赋权因为MySQL服务器通常配置了一个安全选项限制了LOAD DATA INFILE语句可以加载文件的目录。这个--secure-file-priv选项就是用于限制LOAD DATA INFILE和SELECT ... INTO OUTFILE操作可以访问的文件目录。 赋权需要先输入命令查看--secure-file-priv指定的目录路径随后使用cp命令将要转换的CSV、Excel(需要转化为CSV文件)文件移动到这个路径下
SHOW VARIABLES LIKE secure_file_priv;复制文件到查询到的/var/lib/mysql-files/文件夹里Amazon Dataset.csv是我从kaggle上下载的CSV文件名称
sudo cp /数据文件存放的路径/Amazon Dataset.csv /var/lib/mysql-files/赋予MySQL读取文件的权利
sudo chown mysql:mysql /var/lib/mysql-files/Amazon Dataset.csvsudo chmod 640 /var/lib/mysql-files/Amazon Dataset.csv重启MySQL服务器
sudo systemctl restart mysqlCSV文件转换
接下来就可以创建MySQL数据库了,根据原数据格式来建立
CREATE DATABASE amazon;
USE amazon;CREATE TABLE IF NOT EXISTS daily_prices ( Date DATE PRIMARY KEY, Open FLOAT, High FLOAT, Low FLOAT, Close FLOAT, Adj_Close FLOAT, Volume BIGINT
);接下来进行数据读取和转换CSV文件以 , 作为分割所以使用FIELDS TERMINATED BY ,提示数据库原文件是,分割格式ENCLOSED BY 指定每个字段的值都应该在双引号内LINES TERMINATED BY \n指定每条记录之间的分隔符是换行符\n,IGNORE 1 ROWS;指定指定导入数据时忽略文件的第一行。
LOAD DATA INFILE /var/lib/mysql-files/Amazon Dataset.csv
INTO TABLE daily_prices
FIELDS TERMINATED BY ,
ENCLOSED BY
LINES TERMINATED BY \n
IGNORE 1 ROWS;查询下数据库是否转换成功
SELECT * FROM daily_prices;Excel文件转换
安装gnumeric将xls或xlsx文件转换为.csv文件
udo apt-get install gnumeric安装完gnumeric后使用ssconvert命令来将.xls文件转换为.csv文件
ssconvert 数据.xls 数据.csv随后既可以像操作CSV文件一样进行操作。
XML文件转换
我从国家数据官网下载的XML格式数据,官网链接国家数据官网在下载完数据后先看一下数据结构 根据数据结构来建立一个数据库
CREATE DATABASE month_data;
USE month_data; CREATE TABLE monthly ( id INT AUTO_INCREMENT PRIMARY KEY, indicator VARCHAR(255), time VARCHAR(255), data DECIMAL(10, 2)
);接下来编写Python脚本需要提前下载lxml库lxml库相对于xml库更灵活方便处理编码方式效果更好
# import_xml.py
from lxml import etree
import mysql.connector conn mysql.connector.connect( host主机名, user用户名, password用户密码, databasedatabase的名字
)
cursor conn.cursor()
tree etree.parse(network.xml)
root tree.getroot() for record in root.findall(data/record): indicator record.find(field[name指标]).text time record.find(field[name时间]).text data record.find(field[name数据]).text cursor.execute( INSERT INTO monthly (indicator, time, data) VALUES (%s, %s, %s), (indicator, time, data) ) conn.commit()
cursor.close()
conn.close()随后在终端运行它
python3 import_xml.py运行成功后查询一下
SELECT * FROM monthly;转换成功
