Insert datetime.datetime object in MySQL

Question

I'm trying to insert my datettime object into MySQL

>>> t
datetime.datetime(2013, 5, 21, 19, 33, 36, tzinfo=tzutc())
>>> cursor.execute('INSERT INTO tweets(created_at) VALUES ({created_at})'.format(created_at=t))

Error I'm getting:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '19:33:36+00:00)' at line 1")
>>>

MySQL:

mysql> DESCRIBE mytable;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+

| created_at  | datetime            | YES  |     | NULL    |       |

+-------------+---------------------+------+-----+---------+-------+

Try:>>> cursor.execute('INSERT INTO tweets (created_at) VALUES ("{created_at}")'.format(created_at=t)) Note the space and the " — Gustavo Vargas
– Gustavo Vargas, Commented May 21, 2013 at 20:23
I don't know anything about databases (yet), but the string formatting you're using will implicitly call str on your datetime object and use that string to substitute for {created_at}. From the error, it looks like that string is what is causing mysql to choke. — mgilson
– mgilson, Commented May 21, 2013 at 20:23
Nice! Well I put it as an answer, I'll be glad if your aprove it! — Gustavo Vargas
– Gustavo Vargas, Commented May 21, 2013 at 20:31

unutbu · Accepted Answer · 2013-05-21 20:30:59Z

4

Use parametrized sql instead of string formatting and manual quoting:

cursor.execute('INSERT INTO tweets(created_at) VALUES (%s)', [t])

It's easier, and helps prevent sql injection.

answered May 21, 2013 at 20:30

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

Comments

Gustavo Vargas · Accepted Answer · 2013-05-21 20:30:40Z

-1

Try to insert a space after your table name and to put your text inside "", like this:

cursor.execute('INSERT INTO tweets (created_at) VALUES ("{created_at}")'.format(created_at=t))

answered May 21, 2013 at 20:30

Gustavo Vargas

2,6272 gold badges26 silver badges34 bronze badges

3 Comments

Michael Waterfall Over a year ago

Using the format string method should be avoided. Variables don't get escaped and you'll have SQL injection issues. See Django's docs on the issue.

Gustavo Vargas Over a year ago

I do agree with you, but in his case this variable comes from internal code only. Is this subject to injection too? I'm not being ironic, I'm realy asking you, I'm not a literate in sanitized coding.

Michael Waterfall Over a year ago

It's best to always keep things standardised and to do things the correct way every time. It will avoid confusion, and the possibility that the code may be used or copied without thinking into something that might introduce issues.

Collectives™ on Stack Overflow

Insert datetime.datetime object in MySQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related