python RuntimeError: "mi" not found for replace

Question

I'm trying to write a date into 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)

And I have a error:

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',......

What am I doing wrong?

You can start with not using string formatting to create your SQL statement. It opens you up to an SQL injection attack. It's not much of a FAQ, but it's shows what you should do: https://wiki.python.org/moin/DbApiFaq. Also, some databases treat quotes differently than Python, and Oracle appears to be one of them. — John Szakmeister, Mar 17 '17 at 09:34

score 1 · Answer 1 · answered Mar 17 '17 at 09:32

1

Unlike Python, Oracle DB does not interpret single quote ' like double quote ".

In your case, the date format is written between double quote, which is wrong.

In other words, change: [...], to_date('2017/03/16 11:46:06', "yyyy/mm/dd hh24:mi:ss"), [...] to [...], to_date('2017/03/16 11:46:06', 'yyyy/mm/dd hh24:mi:ss'), [...]

About single quote vs double quote in Oracle DB: https://community.oracle.com/message/3853568#3853568

answered Mar 17 '17 at 09:32

pltrdy

2,069
1
11
29

I changed it to to_date('2017/03/16 11:46:06', 'yyyy/mm/dd hh24:mi:ss'), but result is the same – Rainmaker Mar 17 '17 at 09:38
1

Could you update you post adding this new attempt + the new error? – pltrdy Mar 17 '17 at 09:39

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

Firstly, in SQL you should use single quotes for strings. Double quotes are for identifiers.

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

Also your code is prone to SQL injection. Bind variables instead:

# 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.
})

Now, for some unknown reason the Oracle database is interpreting the :MI and :SS inside a string as placeholders, causing the error seen by OP. I think this is a bug in the Oracle side. As confirmed by OP, it seems can be worked-around by "escaping" the colon by doubling it

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

edited May 23 '17 at 11:55

Community

1
1

answered Mar 17 '17 at 09:37

kennytm

510,854
105
1,084
1,005

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 (:type_event, to_date(:date_event, 'YYYY/MM/DD HH24:MI:SS'), :version_app, :UUID, :name_event, :description, :device_model, :IMEI, :ip_device)". Must be one of ['version_app', 'date_event', 'IMEI', 'UUID', 'device_model', 'description', 'ip_device', 'name_event', 'type_event'] – Rainmaker Mar 17 '17 at 10:00
1

@Rainmaker Looks like a problem of http://stackoverflow.com/questions/7342642/oracle-pl-sql-how-to-escape-colon-being-misinterpreted-for-bind-variable. I don't have an Oracle DB to test now, but what if you tried `HH24::MI::SS` or `HH24\\:MI\\:SS`? – kennytm Mar 17 '17 at 10:08
@Rainmaker OK updated. But I think it is a bug in the Oracle software. Have you tried updating them? – kennytm Mar 17 '17 at 16:26

python RuntimeError: "mi" not found for replace

2 Answers2