Escaping % in python mysql execute

Question

The escaped date format

load_data = """LOAD DATA LOCAL INFILE %s INTO TABLE table1
   FIELDS TERMINATED BY '|' ESCAPED BY '' (@ADate)
  SET ADate = 
  IF(@ADate = '', NULL, STR_TO_DATE(@ADate,'%%c/%%e/%%Y %%h:%%i:%%s %%p'))"""
cur.execute(load_data, (ed_file,))

keeps triggering on execute line:

mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Any suggestions how to fix the problem?

create table table1 (ADate datetime);
ed_file could be empty single line.

Parfait · Accepted Answer · 2019-06-18 16:12:20Z

1

Consider using two parameters for file name and string date format:

load_data = """LOAD DATA LOCAL INFILE %s 
               INTO TABLE table1
               FIELDS TERMINATED BY '|' 
               ESCAPED BY '' (@ADate)
               SET ADate = IF(@ADate = '', NULL, STR_TO_DATE(@ADate, %s))
            """

dt_format = '%c/%e/%Y %h:%i:%s %p'

cur.execute(load_data, (ed_file, dt_format))
conn.commit()

answered Jun 18, 2019 at 16:12

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vladimir Sakharuk · Accepted Answer · 2019-06-18 16:11:56Z

0

Wow, I have found a workaround. replace %%s with %%S since both equivalent and big S works. %S Seconds (00 to 59) %s Seconds (00 to 59)

answered Jun 18, 2019 at 16:11

Vladimir Sakharuk

6007 silver badges15 bronze badges

Collectives™ on Stack Overflow

Escaping % in python mysql execute

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related