CODEWITHSUNDEEP
pythonmysqlpython-2.7pymysql
Mike Thomsen
Mike Thomsen

Reputation: 37506

Cannot insert a date with PyMySQL

The insert statement looks like this:

INSERT INTO events(ts, domain, url, lat_lon, sha1_hash)
VALUES (STR_TO_DATE(%s, '%Y-%m-%dT%H:%i:%sZ'), %s, %s, %s, sha1(url))

When I try to use executemany to send a batch, I get this error:

Traceback (most recent call last):
  File "/vagrant/workspace/gdelt/gdelt_all.py", line 41, in <module> read_files()
  File "/vagrant/workspace/gdelt/gdelt_all.py", line 33, in read_files cursor.executemany(INSERT_FOUR, four)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 195, in executemany self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 195, in <genexpr> self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 164, in execute query = self.mogrify(query, args)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 143, in mogrify query = query % self._escape_args(args, conn)
ValueError: unsupported format character 'Y' (0x59) at index 83

I tried escaping the Y to see if that was the right place to start, but that didn't help. Any ideas?

Upvotes: 2

Views: 1210

Answers (1)

falsetru
falsetru

Reputation: 369164

Escape %s used to specify date format; so that it's not processed by Cursor.execute*(...)

insert into events(ts, domain, url, lat_lon, sha1_hash) values 
(STR_TO_DATE(%s, '%%Y-%%m-%%dT%%H:%%i:%%sZ'), %s, %s, %s, sha1(url))

Upvotes: 7

Related Questions