99 lines
2.6 KiB
Python
99 lines
2.6 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
初始化SQLite数据库并导入联系人数据
|
|
"""
|
|
import sqlite3
|
|
import json
|
|
import os
|
|
|
|
# 数据库文件
|
|
DB_PATH = r"D:\夏骥\微信研究\contacts.db"
|
|
|
|
# JSON数据文件
|
|
JSON_PATH = r"D:\夏骥\微信研究\contacts_data.json"
|
|
|
|
|
|
def init_database():
|
|
"""初始化数据库"""
|
|
# 删除旧数据库（如果存在）
|
|
if os.path.exists(DB_PATH):
|
|
os.remove(DB_PATH)
|
|
|
|
conn = sqlite3.connect(DB_PATH)
|
|
cursor = conn.cursor()
|
|
|
|
# 创建联系人表
|
|
cursor.execute('''
|
|
CREATE TABLE contacts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
category TEXT DEFAULT '',
|
|
blessing TEXT DEFAULT '马年新春快乐！愿您在新的一年里，事业腾飞，马到成功！',
|
|
selected INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
conn.commit()
|
|
return conn
|
|
|
|
|
|
def import_from_json(conn):
|
|
"""从JSON导入联系人"""
|
|
cursor = conn.cursor()
|
|
|
|
# 读取JSON数据
|
|
with open(JSON_PATH, 'r', encoding='utf-8') as f:
|
|
contacts = json.load(f)
|
|
|
|
# 导入联系人
|
|
for contact in contacts:
|
|
cursor.execute('''
|
|
INSERT INTO contacts (name, category, blessing, selected)
|
|
VALUES (?, ?, ?, ?)
|
|
''', (
|
|
contact.get('name', ''),
|
|
contact.get('category', ''),
|
|
contact.get('blessing', '马年新春快乐！愿您在新的一年里，事业腾飞，马到成功！'),
|
|
1 if contact.get('selected', False) else 0
|
|
))
|
|
|
|
conn.commit()
|
|
print(f"已导入 {len(contacts)} 个联系人到数据库")
|
|
|
|
|
|
def main():
|
|
print("=" * 50)
|
|
print("初始化数据库")
|
|
print("=" * 50)
|
|
|
|
# 初始化数据库
|
|
conn = init_database()
|
|
print("数据库表已创建")
|
|
|
|
# 导入数据
|
|
if os.path.exists(JSON_PATH):
|
|
import_from_json(conn)
|
|
else:
|
|
print(f"JSON文件不存在: {JSON_PATH}")
|
|
|
|
# 验证数据
|
|
cursor = conn.cursor()
|
|
cursor.execute("SELECT COUNT(*) FROM contacts")
|
|
count = cursor.fetchone()[0]
|
|
print(f"\n数据库中共有 {count} 个联系人")
|
|
|
|
# 显示示例数据
|
|
cursor.execute("SELECT id, name, category, blessing, selected FROM contacts LIMIT 5")
|
|
print("\n示例数据:")
|
|
for row in cursor.fetchall():
|
|
print(f" ID:{row[0]} 姓名:{row[1]} 分类:{row[2]} 祝福语:{row[3][:20]}...")
|
|
|
|
conn.close()
|
|
print(f"\n数据库文件: {DB_PATH}")
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|