SQLite数据导入导出技巧与实例
2022-06-124.7k 阅读
SQLite数据导入技巧
从CSV文件导入数据
CSV（Comma - Separated Values）是一种常见的数据存储格式，许多软件和工具都支持生成和读取CSV文件。在SQLite中，将CSV文件的数据导入到数据库表是一项常见的操作。
- 创建目标表
在导入数据之前，需要先创建一个对应的数据库表，表结构应与CSV文件中的数据列相匹配。例如，假设我们有一个
employees.csv文件，包含id、name、age和department四列数据。可以使用以下SQL语句创建表：
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
department TEXT
);
- 使用
.mode和.import命令（在SQLite命令行工具中） SQLite命令行工具提供了便捷的导入CSV数据的方式。首先，打开SQLite命令行并连接到目标数据库：
sqlite3 your_database.db
然后设置导入模式为CSV：
.mode csv
最后执行导入命令：
.import employees.csv employees
这里，employees.csv是要导入的CSV文件名，employees是目标表名。
- 在Python中使用
sqlite3模块导入 在Python中，我们可以使用内置的sqlite3模块来实现CSV数据的导入。示例代码如下：
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 打开CSV文件
with open('employees.csv', 'r') as csvfile:
csvreader = csv.reader(csvfile)
next(csvreader) # 跳过标题行
for row in csvreader:
id_value, name_value, age_value, department_value = row
cursor.execute("INSERT INTO employees (id, name, age, department) VALUES (?,?,?,?)",
(int(id_value), name_value, int(age_value), department_value))
# 提交事务并关闭连接
conn.commit()
conn.close()
从JSON文件导入数据
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，在现代应用开发中广泛使用。将JSON数据导入到SQLite数据库需要一些额外的处理步骤。
- 解析JSON数据
在Python中，可以使用
json模块来解析JSON数据。假设我们有一个customers.json文件，内容如下：
[
{
"id": 1,
"name": "Alice",
"email": "alice@example.com"
},
{
"id": 2,
"name": "Bob",
"email": "bob@example.com"
}
]
首先创建目标表：
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT
);
然后使用Python代码导入数据：
import sqlite3
import json
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 读取JSON文件
with open('customers.json', 'r') as jsonfile:
data = json.load(jsonfile)
for item in data:
id_value = item['id']
name_value = item['name']
email_value = item['email']
cursor.execute("INSERT INTO customers (id, name, email) VALUES (?,?,?)",
(id_value, name_value, email_value))
# 提交事务并关闭连接
conn.commit()
conn.close()
从其他数据库导入数据
有时需要将数据从其他数据库（如MySQL、PostgreSQL）导入到SQLite。这通常涉及到先从源数据库导出数据，然后再导入到SQLite。
- 从MySQL导出数据
可以使用
mysqldump命令从MySQL数据库导出数据为SQL文件。例如，要导出名为test_db数据库中的users表：
mysqldump -u your_username -p test_db users > users.sql
- 导入到SQLite
将导出的SQL文件修改为符合SQLite语法，然后在SQLite命令行工具中使用
.read命令导入：
sqlite3 your_database.db
.read users.sql
不过，由于MySQL和SQLite的语法差异，可能需要对导出的SQL文件进行一些调整，例如：
- MySQL中的
AUTO_INCREMENT在SQLite中为AUTOINCREMENT，并且使用方式略有不同。 - MySQL中的日期和时间格式与SQLite可能需要转换。
SQLite数据导出技巧
导出为CSV文件
- 使用SQLite命令行工具
SQLite命令行工具提供了方便的导出功能。假设我们要将
employees表导出为CSV文件。首先连接到数据库：
sqlite3 your_database.db
然后设置输出模式为CSV，并执行导出命令：
.mode csv
.output employees_exported.csv
SELECT * FROM employees;
.output stdout
这里，.output employees_exported.csv指定输出到employees_exported.csv文件，SELECT * FROM employees是要执行的查询语句，.output stdout将输出模式恢复为标准输出。
- 在Python中使用
sqlite3模块导出
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
# 写入CSV文件
with open('employees_exported.csv', 'w', newline='') as csvfile:
csvwriter = csv.writer(csvfile)
csvwriter.writerow([description[0] for description in cursor.description]) # 写入标题行
csvwriter.writerows(rows)
# 关闭连接
conn.close()
导出为JSON文件
- 在Python中实现
要将SQLite表数据导出为JSON文件，我们可以使用
sqlite3和json模块。以下是示例代码：
import sqlite3
import json
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM customers")
rows = cursor.fetchall()
# 将结果转换为JSON格式
data = []
for row in rows:
item = {}
for i, col in enumerate(cursor.description):
item[col[0]] = row[i]
data.append(item)
# 写入JSON文件
with open('customers_exported.json', 'w') as jsonfile:
json.dump(data, jsonfile, indent=4)
# 关闭连接
conn.close()
导出为SQL文件
- 使用SQLite命令行工具
SQLite命令行工具可以将数据库中的数据和表结构导出为SQL文件。使用
.dump命令：
sqlite3 your_database.db ".dump" > your_database_dump.sql
这个命令会将整个数据库的表结构和数据以SQL语句的形式输出到your_database_dump.sql文件中。如果只想导出特定的表，可以在.dump命令后指定表名，例如：
sqlite3 your_database.db ".dump employees" > employees_dump.sql
- 在Python中实现
import sqlite3
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 获取表结构
cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='employees'")
table_schema = cursor.fetchone()[0]
# 获取数据
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
# 生成插入语句
insert_statements = []
for row in rows:
values = ', '.join(['?' for _ in row])
insert_statements.append(f"INSERT INTO employees VALUES ({values});")
# 写入SQL文件
with open('employees_exported.sql', 'w') as sqlfile:
sqlfile.write(table_schema + '\n')
for statement in insert_statements:
sqlfile.write(statement + '\n')
# 关闭连接
conn.close()
导出特定查询结果
- 导出为CSV
假设我们要导出
employees表中年龄大于30岁的员工数据为CSV文件。在SQLite命令行工具中：
sqlite3 your_database.db
.mode csv
.output employees_over_30.csv
SELECT * FROM employees WHERE age > 30;
.output stdout
在Python中：
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM employees WHERE age > 30")
rows = cursor.fetchall()
# 写入CSV文件
with open('employees_over_30.csv', 'w', newline='') as csvfile:
csvwriter = csv.writer(csvfile)
csvwriter.writerow([description[0] for description in cursor.description])
csvwriter.writerows(rows)
# 关闭连接
conn.close()
- 导出为JSON 同样，对于年龄大于30岁的员工数据导出为JSON：
import sqlite3
import json
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM employees WHERE age > 30")
rows = cursor.fetchall()
# 将结果转换为JSON格式
data = []
for row in rows:
item = {}
for i, col in enumerate(cursor.description):
item[col[0]] = row[i]
data.append(item)
# 写入JSON文件
with open('employees_over_30.json', 'w') as jsonfile:
json.dump(data, jsonfile, indent=4)
# 关闭连接
conn.close()
处理大数据量的导入导出
- 批量导入 在导入大数据量时，逐行插入数据效率较低。可以采用批量插入的方式。例如在Python中：
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 打开CSV文件
with open('large_data.csv', 'r') as csvfile:
csvreader = csv.reader(csvfile)
next(csvreader) # 跳过标题行
batch = []
for row in csvreader:
id_value, name_value, age_value, department_value = row
batch.append((int(id_value), name_value, int(age_value), department_value))
if len(batch) == 1000: # 每1000条数据为一批
cursor.executemany("INSERT INTO employees (id, name, age, department) VALUES (?,?,?,?)", batch)
batch = []
if batch:
cursor.executemany("INSERT INTO employees (id, name, age, department) VALUES (?,?,?,?)", batch)
# 提交事务并关闭连接
conn.commit()
conn.close()
- 分块导出 在导出大数据量时，为了避免内存耗尽，可以采用分块导出的方式。例如在Python中导出为CSV：
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 分块大小
chunk_size = 1000
offset = 0
# 写入CSV文件
with open('large_data_exported.csv', 'w', newline='') as csvfile:
csvwriter = csv.writer(csvfile)
# 写入标题行
cursor.execute("SELECT * FROM large_table LIMIT 1")
csvwriter.writerow([description[0] for description in cursor.description])
while True:
cursor.execute(f"SELECT * FROM large_table LIMIT {chunk_size} OFFSET {offset}")
rows = cursor.fetchall()
if not rows:
break
csvwriter.writerows(rows)
offset += chunk_size
# 关闭连接
conn.close()
数据转换与导入导出
- 数据类型转换
在导入数据时，可能需要进行数据类型转换。例如，从CSV文件导入日期数据时，CSV中的日期格式可能与SQLite期望的格式不同。假设CSV中的日期格式为
YYYY - MM - DD，而SQLite使用YYYY - MM - DD HH:MM:SS格式。可以在导入时进行转换。在Python中：
import sqlite3
import csv
from datetime import datetime
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 打开CSV文件
with open('events.csv', 'r') as csvfile:
csvreader = csv.reader(csvfile)
next(csvreader) # 跳过标题行
for row in csvreader:
event_id, event_name, event_date_str = row
event_date = datetime.strptime(event_date_str, '%Y-%m-%d')
new_date_str = event_date.strftime('%Y-%m-%d 00:00:00')
cursor.execute("INSERT INTO events (id, name, date) VALUES (?,?,?)",
(int(event_id), event_name, new_date_str))
# 提交事务并关闭连接
conn.commit()
conn.close()
- 数据编码转换
如果CSV文件或其他数据源使用的编码与SQLite默认编码不同，需要进行编码转换。例如，CSV文件是
GBK编码，而SQLite使用UTF - 8编码。在Python中：
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 打开CSV文件（GBK编码）
with open('data_gbk.csv', 'r', encoding='gbk') as csvfile:
csvreader = csv.reader(csvfile)
next(csvreader) # 跳过标题行
for row in csvreader:
id_value, name_value = row
cursor.execute("INSERT INTO data_table (id, name) VALUES (?,?)",
(int(id_value), name_value))
# 提交事务并关闭连接
conn.commit()
conn.close()
导入导出中的错误处理
- 导入错误处理 在导入数据时，可能会遇到各种错误，如数据类型不匹配、主键冲突等。在Python中，可以使用异常处理机制来处理这些错误。例如：
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 打开CSV文件
try:
with open('employees.csv', 'r') as csvfile:
csvreader = csv.reader(csvfile)
next(csvreader) # 跳过标题行
for row in csvreader:
id_value, name_value, age_value, department_value = row
try:
cursor.execute("INSERT INTO employees (id, name, age, department) VALUES (?,?,?,?)",
(int(id_value), name_value, int(age_value), department_value))
except sqlite3.IntegrityError as e:
print(f"插入数据时出错: {e}")
except FileNotFoundError as e:
print(f"找不到CSV文件: {e}")
# 提交事务并关闭连接
conn.commit()
conn.close()
- 导出错误处理 在导出数据时，也可能出现错误，如文件写入失败等。例如在Python中导出为CSV时：
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
# 写入CSV文件
try:
with open('employees_exported.csv', 'w', newline='') as csvfile:
csvwriter = csv.writer(csvfile)
csvwriter.writerow([description[0] for description in cursor.description])
csvwriter.writerows(rows)
except IOError as e:
print(f"写入CSV文件时出错: {e}")
# 关闭连接
conn.close()
跨平台的导入导出考虑
- 文件路径差异
在不同的操作系统中，文件路径的表示方式不同。在Windows中使用反斜杠（
\），而在Linux和macOS中使用正斜杠（/）。在编写导入导出代码时，要考虑到这一点。在Python中，可以使用os.path.join函数来构建跨平台的文件路径：
import sqlite3
import csv
import os
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 构建CSV文件路径
csv_path = os.path.join('data', 'employees.csv')
# 打开CSV文件
with open(csv_path, 'r') as csvfile:
csvreader = csv.reader(csvfile)
next(csvreader) # 跳过标题行
for row in csvreader:
id_value, name_value, age_value, department_value = row
cursor.execute("INSERT INTO employees (id, name, age, department) VALUES (?,?,?,?)",
(int(id_value), name_value, int(age_value), department_value))
# 提交事务并关闭连接
conn.commit()
conn.close()
- 换行符差异
Windows使用
\r\n作为换行符，而Linux和macOS使用\n。在处理文本文件（如CSV、SQL文件）时，这可能会导致问题。在Python中，以二进制模式打开文件可以避免这个问题，例如在导出CSV时：
import sqlite3
import csv
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
# 写入CSV文件
with open('employees_exported.csv', 'wb') as csvfile:
csvwriter = csv.writer(csvfile)
csvwriter.writerow([description[0] for description in cursor.description])
csvwriter.writerows(rows)
# 关闭连接
conn.close()
与其他工具结合的导入导出
- 与Excel结合
可以将SQLite数据导出为CSV文件，然后在Excel中打开。反之，也可以在Excel中编辑数据，保存为CSV文件后再导入到SQLite。在Python中，利用
openpyxl库可以直接与Excel文件交互。以下是将SQLite表数据导出为Excel文件的示例：
import sqlite3
from openpyxl import Workbook
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
# 创建Excel工作簿
wb = Workbook()
ws = wb.active
# 写入标题行
ws.append([description[0] for description in cursor.description])
# 写入数据行
for row in rows:
ws.append(row)
# 保存Excel文件
wb.save('employees_exported.xlsx')
# 关闭连接
conn.close()
- 与数据可视化工具结合 许多数据可视化工具（如Tableau、PowerBI）支持连接到SQLite数据库。可以直接从SQLite数据库中获取数据进行可视化。或者，将SQLite数据导出为合适的格式（如CSV、JSON），再导入到可视化工具中。例如，将SQLite数据导出为JSON后在Tableau中使用：
- 首先按照前面介绍的方法将SQLite数据导出为JSON文件。
- 然后在Tableau中，选择“连接到数据”，选择JSON文件，按照向导进行数据连接和可视化操作。
通过以上详细的技巧和实例，我们可以灵活地进行SQLite数据的导入导出操作，满足不同场景下的数据处理需求。无论是处理简单的小数据集，还是复杂的大数据量、跨平台以及与其他工具结合的情况，都能找到合适的解决方案。