一个Python 作为Sqlite 数据传输同步到Oracle ,源码 import sqlite3
import oracledb
from datetime import datetime, timedelta
import logging
import re
# 日志配置
logging.basicConfig(
filename='db_sync_debug.log',
level=logging.DEBUG,
format='%(asctime)s - %(levelname)s - %(message)s',
encoding='utf-8'
)
# 增强的日期格式正则表达式
DATE_REGEX = re.compile(
r'^(\d{4})-(\d{2})-(\d{2})[T ](\d{2}):(\d{2}):(\d{2})(?:\.(\d+))?$'
)
# 主键映射表
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"
}
# Oracle 关键字列表（扩展）
ORACLE_KEYWORDS = {
'ACCESS', 'ADD', 'ALL', 'ALTER', 'AND', 'ANY', 'AS', 'ASC', 'AUDIT', 'BETWEEN',
'BY', 'CHAR', 'CHECK', 'CLUSTER', 'COLUMN', 'COMMENT', 'COMPRESS', 'CONNECT',
'CREATE', 'CURRENT', 'DATE', 'DECIMAL', 'DEFAULT', 'DELETE', 'DESC', 'DISTINCT',
'DROP', 'ELSE', 'EXCLUSIVE', 'EXISTS', 'FILE', 'FLOAT', 'FOR', 'FROM', 'GRANT',
'GROUP', 'HAVING', 'IDENTIFIED', 'IMMEDIATE', 'IN', 'INCREMENT', 'INDEX', 'INITIAL',
'INSERT', 'INTEGER', 'INTERSECT', 'INTO', 'IS', 'LEVEL', 'LIKE', 'LOCK', 'LONG',
'MAXEXTENTS', 'MINUS', 'MLSLABEL', 'MODE', 'MODIFY', 'NOAUDIT', 'NOCOMPRESS',
'NOT', 'NOWAIT', 'NULL', 'NUMBER', 'OF', 'OFFLINE', 'ON', 'ONLINE', 'OPTION',
'OR', 'ORDER', 'PCTFREE', 'PRIOR', 'PRIVILEGES', 'PUBLIC', 'RAW', 'RENAME',
'RESOURCE', 'REVOKE', 'ROW', 'ROWID', 'ROWNUM', 'ROWS', 'SELECT', 'SESSION',
'SET', 'SHARE', 'SIZE', 'SMALLINT', 'START', 'SUCCESSFUL', 'SYNONYM', 'SYSDATE',
'TABLE', 'THEN', 'TO', 'TRIGGER', 'UID', 'UNION', 'UNIQUE', 'UPDATE', 'USER',
'VALIDATE', 'VALUES', 'VARCHAR', 'VARCHAR2', 'VIEW', 'WHENEVER', 'WHERE', 'WITH',
'TYPE', 'MI', 'SS' # 添加了额外的关键字
}
def generate_bind_name(col_name, is_date=False):
"""生成安全的绑定变量名"""
base_name = col_name.lower().replace(' ', '_')
if is_date:
base_name = f"{base_name}_dt"
if base_name.upper() in ORACLE_KEYWORDS:
base_name = f"_{base_name}_"
if re.search(r'[^a-z0-9_]', base_name):
base_name = f"_{base_name}_"
return base_name
def validate_bind_variables(sql, params):
"""
改进的绑定变量验证：
1. 修复未初始化变量问题
2. 增强字符串字面值处理
"""
bind_names = set()
in_string = False
current_name = ""
collecting = False # 关键初始化
# 解析SQL查找所有绑定变量占位符
for char in sql:
# 处理字符串字面值开始/结束
if char == "'":
in_string = not in_string
continue
# 只在非字符串区域处理
if not in_string:
# 遇到冒号开始收集
if char == ':':
collecting = True
continue
# 收集有效绑定变量名字符
if collecting and (char.isalnum() or char == '_'):
current_name += char
continue
# 结束当前绑定变量名收集
if collecting and current_name:
bind_names.add(current_name)
current_name = ""
collecting = False
# 处理最后一个绑定变量（如果存在）
if collecting and current_name:
bind_names.add(current_name)
# 验证参数匹配
param_keys = set(params.keys())
missing = bind_names - param_keys
extra = param_keys - bind_names
if missing:
logging.warning(f"SQL中有但参数字典中缺失的绑定变量: {missing}")
return False
if extra:
logging.warning(f"参数字典中有但SQL中未使用的绑定变量: {extra}")
return False
return True
def debug_log_sql(sql, params):
"""安全的SQL日志记录，避免错误替换字符串字面值"""
logging.debug("SQL语句:")
logging.debug(sql)
logging.debug("绑定参数:")
safe_params = {}
for k, v in params.items():
if isinstance(v, str) and len(v) > 50:
safe_params[k] = f"[{v[:20]}...]"
else:
safe_params[k] = v
logging.debug(safe_params)
def generate_insert_sql(table, columns, oracle_columns, row_data):
"""更健壮的INSERT SQL生成器"""
cols = []
values = []
bind_params = {}
for col in columns:
col_upper = col.upper()
if col_upper not in oracle_columns:
continue
col_type = oracle_columns[col_upper]
value = row_data[col]
# 生成安全的绑定变量名
is_date_field = "DATE" in col_type or "TIMESTAMP" in col_type
bind_name = generate_bind_name(col, is_date_field)
# 处理日期字段
if is_date_field:
values.append(f"TO_DATE(:{bind_name}, 'YYYY-MM-DD HH24:MI:SS')")
bind_params[bind_name] = value
# 处理NULL值
elif value is None:
values.append("NULL")
else:
values.append(f":{bind_name}")
bind_params[bind_name] = value
cols.append(f'"{col_upper}"')
cols_str = ", ".join(cols)
values_str = ", ".join(values)
sql = f"INSERT INTO {table} ({cols_str}) VALUES ({values_str})"
# 验证绑定变量匹配性
if not validate_bind_variables(sql, bind_params):
logging.error("生成的SQL绑定变量验证失败!")
return sql, bind_params
def sync_sqlite_to_oracle(sqlite_db, oracle_dsn, username, password, tables, test_mode):
"""最终优化的数据库同步方案"""
# 计算时间范围 - 支持测试模式
if test_mode:
# 指定固定测试时间范围
start_time = '2025-10-05 08:20:00'
end_time = '2025-10-06 08:20:00'
else:
# 动态计算时间范围
now = datetime.datetime.now()
start_time = (now - datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
end_time = now.strftime('%Y-%m-%d %H:%M:%S')
logging.info(f"同步时间范围: {start_time} 到 {end_time}")
try:
sqlite_conn = sqlite3.connect(sqlite_db)
sqlite_cursor = sqlite_conn.cursor()
oracle_conn = oracledb.connect(
user=username,
password=password,
dsn=oracle_dsn
)
oracle_cursor = oracle_conn.cursor()
# 设置Oracle会话日期格式
oracle_cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'")
oracle_conn.commit()
for table in tables:
primary_key = TABLE_PRIMARY_KEYS.get(table)
if not primary_key:
logging.error(f"未定义主键的表: {table}")
continue
logging.info(f"开始同步表: {table}, 主键: {primary_key}")
# 获取SQLite表结构
sqlite_cursor.execute(f"PRAGMA table_info({table})")
columns = [col[1] for col in sqlite_cursor.fetchall()]
# 获取Oracle列信息
oracle_cursor.execute("""
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = UPPER(:table_name)
""", table_name=table)
oracle_columns = {col[0].upper(): col[1].upper() for col in oracle_cursor.fetchall()}
# 查询变更记录
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_compatible_data = {}
# 格式化数据为Oracle兼容格式
for col_name, value in row_dict.items():
col_upper = col_name.upper()
if col_upper in oracle_columns:
col_type = oracle_columns[col_upper]
# 处理日期字段
if "DATE" in col_type or "TIMESTAMP" in col_type:
formatted = format_date_value(value)
oracle_compatible_data[col_name] = formatted if formatted else value
else:
oracle_compatible_data[col_name] = value
else:
oracle_compatible_data[col_name] = value
# 检查记录是否存在
pk_value = oracle_compatible_data[primary_key]
oracle_cursor.execute(f"""
SELECT COUNT(*)
FROM {table}
WHERE "{primary_key.upper()}" = :pk_value
""", pk_value=pk_value)
if oracle_cursor.fetchone()[0] > 0:
# 更新记录
update_sql, update_params = generate_update_sql(
table, columns, primary_key, oracle_columns, oracle_compatible_data
)
sql_to_execute = update_sql
params_to_execute = update_params
else:
# 插入记录
insert_sql, insert_params = generate_insert_sql(
table, columns, oracle_columns, oracle_compatible_data
)
sql_to_execute = insert_sql
params_to_execute = insert_params
# 执行SQL
try:
debug_log_sql(sql_to_execute, params_to_execute)
oracle_cursor.execute(sql_to_execute, params_to_execute)
except oracledb.DatabaseError as e:
handle_oracle_error(e, sql_to_execute, params_to_execute)
raise
oracle_conn.commit()
logging.info(f"{table} 同步完成")
logging.info("所有表同步成功")
except Exception as e:
logging.error(f"同步失败: {str(e)}", exc_info=True)
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 format_date_value(value):
"""格式化日期值为Oracle兼容格式"""
if isinstance(value, str):
match = DATE_REGEX.match(value)
if match:
groups = match.groups()
return f"{groups[0]}-{groups[1]}-{groups[2]} {groups[3]}:{groups[4]}:{groups[5]}"
elif isinstance(value, datetime):
return value.strftime('%Y-%m-%d %H:%M:%S')
return value
def handle_oracle_error(error, sql, params):
"""详细的Oracle错误处理"""
try:
error_obj = error.args[0]
logging.error(f"Oracle错误: {error_obj.message} (代码: {error_obj.code})")
except:
logging.error(f"数据库错误: {str(error)}")
logging.error(f"问题SQL: {sql}")
logging.error(f"绑定参数: {params}")
# 提供具体解决方案
if "ORA-01745" in str(error):
logging.error("解决方案: 检查绑定变量名是否包含无效字符或Oracle关键字")
elif "DPY-4008" in str(error):
logging.error("解决方案: 确保所有绑定变量在SQL和参数字典中精确匹配")
logging.error("检查步骤: 使用validate_bind_variables函数验证一致性")
def generate_update_sql(table, columns, primary_key, oracle_columns, row_data):
"""更健壮的UPDATE SQL生成器"""
set_clauses = []
bind_params = {}
for col in columns:
if col == primary_key:
continue
col_upper = col.upper()
if col_upper not in oracle_columns:
continue
col_type = oracle_columns[col_upper]
value = row_data[col]
# 生成安全的绑定变量名
is_date_field = "DATE" in col_type or "TIMESTAMP" in col_type
bind_name = generate_bind_name(col, is_date_field)
# 处理日期字段
if is_date_field:
set_clauses.append(f'"{col_upper}" = TO_DATE(:{bind_name}, \'YYYY-MM-DD HH24:MI:SS\')')
bind_params[bind_name] = value
# 处理NULL值
elif value is None:
set_clauses.append(f'"{col_upper}" = NULL')
else:
set_clauses.append(f'"{col_upper}" = :{bind_name}')
bind_params[bind_name] = value
# 添加主键条件
pk_bind_name = generate_bind_name(primary_key)
bind_params[pk_bind_name] = row_data[primary_key]
set_clause = ", ".join(set_clauses)
sql = f"UPDATE {table} SET {set_clause} WHERE \"{primary_key.upper()}\" = :{pk_bind_name}"
# 验证绑定变量匹配性
if not validate_bind_variables(sql, bind_params):
logging.error("生成的SQL绑定变量验证失败!")
return sql, bind_params
if __name__ == "__main__":
# 配置参数
SQLITE_DB = "D:\\IISwebOEE\\App_Data\\webFrameworkEF6.db"
ORACLE_DSN = "at3-pacc-f2db.zf-world.com/AT3PACC2"
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,
test_mode=True # 启用测试模式
)
目前是遇到问题 2025-11-07 09:42:42,481 - INFO - 开始同步表: OEE_AvaiableTime, 主键: OEE_AvaiableTimeId
2025-11-07 09:42:47,025 - DEBUG - SQL语句:
2025-11-07 09:42:47,025 - DEBUG - INSERT INTO OEE_AvaiableTime ("OEE_AVAIABLETIMEID", "STARTTIME", "ENDTIME", "DURATION", "TYPE", "OP", "PARTNO", "LOSSREASON", "COMMENT", "SHIFTWORKTIMEID", "CREATEAT", "UPDATEAT", "BYUSER") VALUES (:oee_avaiabletimeid, :starttime, :endtime, :duration, :_type_, NULL, NULL, NULL, :_comment_, :shiftworktimeid, TO_DATE(:createat_dt, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(:updateat_dt, 'YYYY-MM-DD HH24:MI:SS'), :byuser)
2025-11-07 09:42:47,025 - DEBUG - 绑定参数:
2025-11-07 09:42:47,025 - DEBUG - {'oee_avaiabletimeid': '6b245d77-56ae-429e-809b-f8b71ddf19fb', 'starttime': '176', 'endtime': '179', 'duration': 15, '_type_': '13', '_comment_': '2人，22小时', 'shiftworktimeid': 'd3369a2c-5097-4654-b63c-4a7812b6e54b', 'createat_dt': '2025-10-04 12:37:08', 'updateat_dt': '2025-10-05 18:51:51', 'byuser': 'AD'}
2025-11-07 09:42:47,026 - ERROR - Oracle错误: DPY-4008: no bind placeholder named ":_type_" was found in the SQL text (代码: 0)
2025-11-07 09:42:47,026 - ERROR - 问题SQL: INSERT INTO OEE_AvaiableTime ("OEE_AVAIABLETIMEID", "STARTTIME", "ENDTIME", "DURATION", "TYPE", "OP", "PARTNO", "LOSSREASON", "COMMENT", "SHIFTWORKTIMEID", "CREATEAT", "UPDATEAT", "BYUSER") VALUES (:oee_avaiabletimeid, :starttime, :endtime, :duration, :_type_, NULL, NULL, NULL, :_comment_, :shiftworktimeid, TO_DATE(:createat_dt, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(:updateat_dt, 'YYYY-MM-DD HH24:MI:SS'), :byuser)
2025-11-07 09:42:47,026 - ERROR - 绑定参数: {'oee_avaiabletimeid': '6b245d77-56ae-429e-809b-f8b71ddf19fb', 'starttime': '176', 'endtime': '179', 'duration': 15, '_type_': '13', '_comment_': '2人，22小时', 'shiftworktimeid': 'd3369a2c-5097-4654-b63c-4a7812b6e54b', 'createat_dt': '2025-10-04 12:37:08', 'updateat_dt': '2025-10-05 18:51:51', 'byuser': 'AD'}
2025-11-07 09:42:47,026 - ERROR - 解决方案: 确保所有绑定变量在SQL和参数字典中精确匹配
2025-11-07 09:42:47,027 - ERROR - 检查步骤: 使用validate_bind_variables函数验证一致性
2025-11-07 09:42:47,027 - ERROR - 同步失败: DPY-4008: no bind placeholder named ":_type_" was found in the SQL text
Traceback (most recent call last):
File "C:\UserData\Python\SyncSqlitedb\sync_service.py", line 275, in sync_sqlite_to_oracle
oracle_cursor.execute(sql_to_execute, params_to_execute)
~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\guc3\AppData\Roaming\Python\Python313\site-packages\oracledb\cursor.py", line 708, in execute
impl.execute(self)
~~~~~~~~~~~~^^^^^^
File "src/oracledb/impl/thin/cursor.pyx", line 275, in oracledb.thin_impl.ThinCursorImpl.execute
File "src/oracledb/impl/thin/cursor.pyx", line 182, in oracledb.thin_impl.BaseThinCursorImpl._preprocess_execute
File "src/oracledb/impl/base/cursor.pyx", line 351, in oracledb.base_impl.BaseCursorImpl._perform_binds
File "src/oracledb/impl/thin/var.pyx", line 95, in oracledb.thin_impl.ThinVarImpl._bind
File "C:\Users\guc3\AppData\Roaming\Python\Python313\site-packages\oracledb\errors.py", line 199, in _raise_err
raise error.exc_type(error) from cause
oracledb.exceptions.DatabaseError: DPY-4008: no bind placeholder named ":_type_" was found in the SQL text
请帮忙解决
扫一扫
1931
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
