python 运行时错误 : "mi" not found for replace

Question

我正在尝试将日期写入 Oracle DB

sql = """INSERT INTO app_mobile_scout
    (type_event, date_event, version_app, UUID, name_event, description, device_model, IMEI, ip_device).
    values ('%s', to_date('%s', "yyyy/mm/dd hh24:mi:ss"), '%s', '%s', '%s', '%s', '%s', '%s', '%s')"""%(type_event, date_event, version_app, UUID, name_event, description, device_mod
    res = cur.execute(sql)

我有一个错误:

RuntimeError: "mi" not found for replace in "INSERT INTO app_mobile
    (type_event, date_event, version_app, UUID, name_event, description, device_model, IMEI, ip_device).
    values ('2', to_date('2017/03/16 11:46:06', "yyyy/mm/dd hh24:mi:ss"), '4.0.4',......

我做错了什么？

Answer 1

首先，在 SQL 中你应该 use single quotes for strings. Double quotes are for identifiers .

values ('%s', to_date('%s', 'yyyy/mm/dd hh24:mi:ss')
#                           ^                     ^

您的代码是 prone to SQL injection 。 Bind variables相反:

# Note: Doesn't work yet.
cursor.execute("""
    INSERT INTO app_mobile_scout (
        type_event, 
        date_event, 
        version_app, 
        -- etc
    ) VALUES (
        :type,     -- <-- use the variable 'type' here.
        to_date(:date, 'YYYY/MM/DD HH24:MI:SS'),
        :version,
        -- etc
    );
""", {
    'type': type_event,    # <-- bind `type_event` to the variable 'type'
    'date': date_event,
    'version': version,
    # etc.
})

现在，由于某种未知原因，Oracle 数据库将 字符串 中的 :MI 和 :SS 解释为占位符，从而导致错误OP看到的。我认为这是Oracle这边的一个bug。正如OP所证实的，似乎可以通过加倍“逃避”冒号来解决这个问题

        to_date(:date, 'YYYY/MM/DD HH24::MI::SS'),