import sqlite3
import oracledb
from datetime import datetime, timedelta
import logging
# Dictionary mapping table names to their primary keys
TABLE_PRIMARY_KEYS = {
"OEE_AvaiableTime": "OEE_AvaiableTimeId",
"OEE_CycleTime": "OEE_CycleTimeId",
"OEE_CycleTime_Config": "OEE_CycleTimeConfigId",
"OEE_LineReport": "OEE_LineReportId",
"OEE_PerformanceComment": "OEE_PerformanceCommentId",
"OEE_ProductStatus": "OEE_ProductStatusId",
"OEE_Quality": "OEE_QualityId",
"OEE_ShiftPlan_Config": "OEE_ShiftPlanConfigId",
"OEE_ShiftWorkTime": "OEE_ShiftWorkTimeId",
"OEE_Target_Config": "OEE_TargetConfigId"
}
def sync_sqlite_to_oracle(sqlite_db, oracle_dsn, username, password, tables):
"""
Synchronize data from SQLite to Oracle database.
:param sqlite_db: Path to SQLite database file
:param oracle_dsn: Oracle connection string
:param username: Oracle username
:param password: Oracle password
:param tables: List of tables to synchronize
"""
# Configure logging
logging.basicConfig(filename='db_sync.log', level=logging.INFO,
format='%(asctime)s - %(levelname)s - %(message)s')
# Calculate time range (previous day)
now = datetime.now()
### debug testing ###
now = datetime(year=2025, month=10, day=10, 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:
# Connect to SQLite database
sqlite_conn = sqlite3.connect(sqlite_db)
sqlite_cursor = sqlite_conn.cursor()
# Connect to Oracle database
oracle_conn = oracledb.connect(user=username, password=password, dsn=oracle_dsn)
oracle_cursor = oracle_conn.cursor()
# Iterate through all tables
for table in tables:
# Get the primary key for the current table
primary_key = TABLE_PRIMARY_KEYS.get(table)
if not primary_key:
logging.error(f"Primary key not defined for table: {table}")
continue
logging.info(f"Begin DB Table : {table}, Primary key: {primary_key}")
inserted = 0
updated = 0
# Get SQLite table structure
sqlite_cursor.execute(f"PRAGMA table_info({table})")
columns = [col[1] for col in sqlite_cursor.fetchall()]
# Get Oracle table structure (check if table exists)
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()]
# If Oracle table does not exist, create it (currently commented out)
# if not oracle_columns:
# logging.info(f" {table} in oracle not found, creating new table")
# create_table_sql = generate_create_table_sql(sqlite_cursor, table)
# oracle_cursor.execute(create_table_sql)
# oracle_conn.commit()
# oracle_columns = columns
# Query records from SQLite updated in the previous day
sqlite_cursor.execute(f"""
SELECT * FROM {table}
WHERE UpdateAt BETWEEN ? AND ?
""", (start_time, end_time))
rows = sqlite_cursor.fetchall()
# Process each record
for row in rows:
row_dict = dict(zip(columns, row))
# Check if record exists in Oracle (using primary key)
oracle_cursor.execute(f"""
SELECT COUNT(*)
FROM {table}
WHERE {primary_key} = :pk_value
""", pk_value=row_dict[primary_key])
exists = oracle_cursor.fetchone()[0] > 0
if exists:
# Update record (excluding primary key field)
update_sql = generate_update_sql(table, columns, primary_key)
update_params = {col: row_dict[col] for col in columns if col != primary_key}
update_params["pk_condition"] = row_dict[primary_key]
oracle_cursor.execute(update_sql, update_params)
updated += 1
else:
# Insert new record
insert_sql = generate_insert_sql(table, columns)
oracle_cursor.execute(insert_sql, tuple(row_dict.values()))
inserted += 1
# Commit transaction
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:
# Close connections
if 'sqlite_conn' in locals():
sqlite_conn.close()
if 'oracle_conn' in locals():
oracle_conn.close()
# def generate_create_table_sql(cursor, table_name):
# """Generate SQL statement to create table"""
# cursor.execute(f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{table_name}'")
# create_sql = cursor.fetchone()[0]
# # Convert data types
# 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):
"""Generate SQL INSERT statement"""
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, primary_key):
"""Generate SQL UPDATE statement (excluding primary key)"""
update_columns = [col for col in columns if col != primary_key]
set_clause = ", ".join([f"{col} = :{col}" for col in update_columns])
return f"UPDATE {table} SET {set_clause} WHERE {primary_key} = :pk_condition"
if __name__ == "__main__":
# Configuration parameters
SQLITE_DB = "D:\\IISwebOEE\\App_Data\\webFrameworkEF6.db"
ORACLE_DSN = "at3-pacc-f2db.zf-world.com/AT3PACC2"
USERNAME = "acc_oee2"
PASSWORD = "accZF_2025"
# List of tables to synchronize
TABLES = [
"OEE_AvaiableTime", "OEE_CycleTime", "OEE_LineReport", "OEE_PerformanceComment",
"OEE_ProductStatus", "OEE_Quality", "OEE_ShiftPlan_Config",
"OEE_ShiftWorkTime", "OEE_Target_Config", "OEE_CycleTime_Config"
]
# Execute synchronization
sync_sqlite_to_oracle(SQLITE_DB, ORACLE_DSN, USERNAME, PASSWORD, TABLES)插入Oracle 数据库 时候 ,发送 ORA-01747 错误
本文解决MySQL 5.5版本中更新时间戳字段的问题，由于该版本限制，无法直接设置自动更新时间戳。提供了一种通过创建触发器来实现自动更新时间戳的方法。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
