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')"
today = time.strftime('%Y-%m-%d')
insert = "INSERT INTO table(utdate) VALUES(today)"
_mysql_exceptions.OperationalError: (1054, "Unknown column 'today' in 'field list'")
insert = "INSERT INTO table(utdate) VALUES('today')"
insert = "INSERT INTO table(utdate) VALUES('%s')" % (today)
insert = "INSERT INTO table(utdate) VALUES(%s)" % (today)
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=5Why, then, use
%sin 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.