Astrodude11 Astrodude11 - 4 years ago 1342
SQL Question

Insert Date into Python MySQLdb

I'm trying to insert the current date into MySQL using Python and its MySQLdb module. I can successfully insert the data as such:

insert = "INSERT INTO table(utdate) VALUES('2015-12-31')"


However, I don't want to hard code the date and would rather use a variable or function like:

today = time.strftime('%Y-%m-%d')


I've tried all of the following queries but without success. A successful entry into the database should appear as datetime.date(2016, 01, 01). Below each query is the error message or the resulting entry into the database.

insert = "INSERT INTO table(utdate) VALUES(today)"
_mysql_exceptions.OperationalError: (1054, "Unknown column 'today' in 'field list'")

insert = "INSERT INTO table(utdate) VALUES('today')"
(None)

insert = "INSERT INTO table(utdate) VALUES('%s')" % (today)
(None)

insert = "INSERT INTO table(utdate) VALUES(%s)" % (today)
(None)


My hunch is that the issue has to do something with the today variable since it is a string and I must use quotes to insert it. What are your thoughts and suggestions?

Thanks in advance.

Answer Source

Use a prepared statement and the db api will handle the type mapping for you. From the documentation at http://mysql-python.sourceforge.net/MySQLdb.html#some-examples

import MySQLdb
db=MySQLdb.connect(passwd="moonpie",db="thangs")

To perform a query, you first need a cursor, and then you can execute queries on it:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

In this example, max_price=5 Why, then, use %s in the string? Because MySQLdb will convert it to a SQL literal value, which is the string '5'. When it's finished, the query will actually say, "...WHERE price < 5".

Why the tuple? Because the DB API requires you to pass in any parameters as a sequence. Due to the design of the parser, (max_price) is interpreted as using algebraic grouping and simply as max_price and not a tuple. Adding a comma, i.e. (max_price,) forces it to make a tuple.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download