Mysql语句方法导出导入txt、csv、xls、xlsx2022.3.17

2022-09-22 11:23:07 浏览数 (2)

查看是否有权限

没有的话找到my.ini修改

代码语言:javascript复制
show variables like '%secure%'

导出

代码语言:javascript复制
SELECT * FROM `20220317` INTO OUTFILE "20220317.txt" 
SELECT * FROM `20220317` INTO OUTFILE "20220317.csv" 
SELECT * FROM `20220317` INTO OUTFILE "20220317.xlsx" 

导入,

Error Code: 1300,把xlsx用笔记本打开,另存为utf-8格式

代码语言:javascript复制
LOAD DATA INFILE '20220317.xlsx' INTO TABLE test.20220317

Mysql和几种格式在数据传输上接通了。

代码语言:javascript复制
import pymysql as mysql
    
#填写SQL的账号密码端口号
conn=mysql.connect( host='XXXXXX',port=3308,user='root',password='XXXXXX',database='test')
cour = conn.cursor()
#sql_str = "SELECT * FROM `20220317` INTO OUTFILE '20220317.xlsx'"

sql_str = "LOAD DATA INFILE '20220317.xlsx' INTO TABLE test.20220317"

cour.execute(sql_str)

conn.commit()
cour.close()
conn.close()

0 人点赞