SQL数据导出怎么写 数据导出的5种常用格式

-- MySQL 示例
SELECT *
FROM your_table
INTO OUTFILE '/tmp/your_table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- PostgreSQL 示例
COPY your_table TO '/tmp/your_table.csv' WITH CSV HEADER;

# Python 示例 (使用 pandas 库)
import pandas as pd
import mysql.connector

# 连接数据库
mydb = mysql.connector.connect(
  host="your_host",
  user="your_user",
  password="your_password",
  database="your_database"
)

# 执行 SQL 查询
query = "SELECT * FROM your_table"
df = pd.read_sql(query, mydb)

# 导出到 Excel
df.to_excel("your_table.xlsx", index=False)

mysqldump -u your_user -p your_password your_database your_table > your_table.sql

# Python 示例
import mysql.connector
import json

mydb = mysql.connector.connect(
  host="your_host",
  user="your_user",
  password="your_password",
  database="your_database"
)

mycursor = mydb.cursor(dictionary=True)
mycursor.execute("SELECT * FROM your_table")
result = mycursor.fetchall()

with open('your_table.json', 'w') as f:
    json.dump(result, f, indent=4)

# Python 示例
import mysql.connector
import xml.etree.ElementTree as ET

mydb = mysql.connector.connect(
  host="your_host",
  user="your_user",
  password="your_password",
  database="your_database"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM your_table")
result = mycursor.fetchall()

root = ET.Element("root")
for row in result:
    record = ET.SubElement(root, "record")
    for i, column in enumerate(mycursor.description):
        field = ET.SubElement(record, column[0])
        field.text = str(row[i])

tree = ET.ElementTree(root)
tree.write("your_table.xml")