CODEWITHSUNDEEP
pythonmysql
VladimirS
VladimirS

Reputation: 600

Escaping % in python mysql execute

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.

Upvotes: 1

Views: 221

Answers (2)

Parfait
Parfait

Reputation: 107767

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()

Upvotes: 1

VladimirS
VladimirS

Reputation: 600

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)

Upvotes: 0

Related Questions