在PY 程序中 ,主要功能 是 同步 Sqlite的数据 到Oracle 数据库 ,一共10个表,每个表的主键是 不一样的,每次安装Sqlite 表中的 UpdateAt 字段 来筛选数据 ,将已有的数据 更新,将不存在的数据新增.表的名字 已经写死到程序中,现在需要考虑把 对应的主键也放进去,import sqlite3
import oracledb
from datetime import datetime, timedelta
import logging
def sync_sqlite_to_oracle(sqlite_db, oracle_dsn, username, password, tables):
"""
同步 SQLite 到 Oracle 数据库
:param sqlite_db: SQLite 数据库文件路径
:param oracle_dsn: Oracle 连接字符串
:param username: Oracle 用户名
:param password: Oracle 密码
:param tables: 需要同步的表列表
"""
# 配置日志
logging.basicConfig(filename='db_sync.log', level=logging.INFO,
format='%(asctime)s - %(levelname)s - %(message)s')
# 计算时间范围（前一天）
now = datetime.now()
### debug testing ###
now = datetime(year=2025, month=10, day=1, hour=8, minute=20, second=0)
logging.info(f"now: {now} ")
start_time = (now - timedelta(days=1)).strftime('%Y-%m-%d 08:30:00')
end_time = (now - timedelta(days=0)).strftime('%Y-%m-%d 08:30:00')
logging.info(f"Sync datetime: {start_time} to {end_time}")
try:
# 连接SQLite数据库
sqlite_conn = sqlite3.connect(sqlite_db)
sqlite_cursor = sqlite_conn.cursor()
# 连接Oracle数据库
oracle_conn = oracledb.connect(user=username, password=password, dsn=oracle_dsn)
oracle_cursor = oracle_conn.cursor()
# 遍历所有表
for table in tables:
logging.info(f"Begin DB Table : {table}")
inserted = 0
updated = 0
# 获取SQLite表结构
sqlite_cursor.execute(f"PRAGMA table_info({table})")
columns = [col[1] for col in sqlite_cursor.fetchall()]
# 获取Oracle表结构（检查表是否存在）
oracle_cursor.execute(f"""
SELECT column_name
FROM user_tab_columns
WHERE table_name = UPPER('{table}')
""")
oracle_columns = [col[0].lower() for col in oracle_cursor.fetchall()]
# 如果Oracle表不存在，则创建
if not oracle_columns:
logging.info(f" {table} in oracle not find,creat new table")
create_table_sql = generate_create_table_sql(sqlite_cursor, table)
oracle_cursor.execute(create_table_sql)
oracle_conn.commit()
oracle_columns = columns
# 查询SQLite中前一天的数据
sqlite_cursor.execute(f"""
SELECT * FROM {table}
WHERE UpdateAt BETWEEN ? AND ?
""", (start_time, end_time))
rows = sqlite_cursor.fetchall()
# 处理每条记录
for row in rows:
row_dict = dict(zip(columns, row))
# 检查记录是否存在
oracle_cursor.execute(f"""
SELECT COUNT(*)
FROM {table}
WHERE ID = :id
""", id=row_dict['id'])
exists = oracle_cursor.fetchone()[0] > 0
if exists:
# 更新记录
update_sql = generate_update_sql(table, columns)
oracle_cursor.execute(update_sql, tuple(row_dict.values()))
updated += 1
else:
# 插入新记录
insert_sql = generate_insert_sql(table, columns)
oracle_cursor.execute(insert_sql, tuple(row_dict.values()))
inserted += 1
# 提交事务
oracle_conn.commit()
logging.info(f" {table} sync done: add {inserted} , update {updated} ")
logging.info("All Table Sync done!")
except Exception as e:
logging.error(f"Sync error: {str(e)}")
if 'oracle_conn' in locals():
oracle_conn.rollback()
finally:
# 关闭连接
if 'sqlite_conn' in locals():
sqlite_conn.close()
if 'oracle_conn' in locals():
oracle_conn.close()
def generate_create_table_sql(cursor, table_name):
"""生成创建表的SQL语句"""
cursor.execute(f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{table_name}'")
create_sql = cursor.fetchone()[0]
# 转换数据类型
create_sql = create_sql.replace('INTEGER', 'NUMBER(10)')
create_sql = create_sql.replace('REAL', 'NUMBER(20,10)')
create_sql = create_sql.replace('TEXT', 'VARCHAR2(4000)')
create_sql = create_sql.replace('BLOB', 'BLOB')
return create_sql
def generate_insert_sql(table, columns):
"""生成插入SQL语句"""
cols = ", ".join(columns)
placeholders = ", ".join([f":{i+1}" for i in range(len(columns))])
return f"INSERT INTO {table} ({cols}) VALUES ({placeholders})"
def generate_update_sql(table, columns):
"""生成更新SQL语句"""
set_clause = ", ".join([f"{col} = :{i+1}" for i, col in enumerate(columns)])
return f"UPDATE {table} SET {set_clause} WHERE id = :1"
if __name__ == "__main__":
# 配置参数
SQLITE_DB = "D:\\IISwebOEE\\App_Data\\webFrameworkEF6.db"
ORACLE_DSN = "at3-pacc-f2db.zf-world.com/AT3PACC2" # 例如: "localhost:1521/ORCLPDB1"
USERNAME = "acc_oee2"
PASSWORD = "accZF_2025"
# 需要同步的表列表
TABLES = [
"OEE_AvaiableTime", "OEE_CycleTime", "OEE_LineReport", "OEE_PerformanceComment",
"OEE_ProductStatus", "OEE_Quality", "OEE_ShiftPlan_Config",
"OEE_ShiftWorkTime", "OEE_Target_Config", "OEE_CycleTime_Config"
]
# 执行同步
sync_sqlite_to_oracle(SQLITE_DB, ORACLE_DSN, USERNAME, PASSWORD, TABLES)
主键信息 1 OEE_AvaiableTime OEE_AvaiableTimeId
2 OEE_CycleTime OEE_CycleTimeId
3 OEE_CycleTime_Config OEE_CycleTimeConfigId
4 OEE_LineReport OEE_LineReportId
5 OEE_PerformanceComment OEE_PerformanceCommentId
6 OEE_ProductStatus OEE_ProductStatusId
7 OEE_Quality OEE_QualityId
8 OEE_ShiftPlan_Config OEE_ShiftPlanConfigId
9 OEE_ShiftWorkTime OEE_ShiftWorkTimeId
10 OEE_Target_Config OEE_TargetConfigId
本文介绍如何在数据库中创建一个新用户userdbpr，并为其分配一系列权限，包括CONNECT、RESOURCE等，以及创建表、序列、触发器等的能力。
扫一扫
7938
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
