datetime date value not updating into MySQL database in Python correctly (updates the integer 2005 instead)
<p>I am having a weird issue while trying to update a record in my database. Here is the section of the code that doesn't make sense:</p>

<pre><code>        """code preceeds"""

        self.NextMail = self.get_NextMail()
        if self.NextMail != None:

            """debugging"""
            print(self.NextMail)
            print(type(self.NextMail))
            print(record[0]) """this is the primary key for Mailouts table and a 1-1 related students table"""

            self.cur.execute("""UPDATE Mailouts
                                SET NextMail={0}
                                WHERE StudentID={1}
                             """.format(self.NextMail,record[0]))

            """code continues..."""
</code></pre>

<p>When I run this, here is what is printed:</p>

<pre><code>2017-01-11
&lt;class 'datetime.date'&gt;
1
</code></pre>

<p>As you can see, the date added to the database should be 2017-01-11. The SQL type for that field is DATE. </p>

<p>When I select and output this table, the NextMail field appears as in the image: <a href="http://i.stack.imgur.com/0x3ow.png" rel="nofollow">screenshot</a></p>

<p>Note that there is no place in my code that ever mentions the year 2005, nor the number 2005. if you go ctrl + f and search 2005, nothing comes up. The database was empty, and the field I was updating was Null before I ran the program. </p>

<p>If I go </p>

<pre><code>    self.cur.execute("""SELECT * FROM Mailouts LIMIT 1""")
    self.all_data = self.cur.fetchall()
    print(self.all_data)
</code></pre>

<p>the record shows the number 2005 in the NextMail field with the type int.</p>

<p>I am using PyQt for GUI
I am using the modules sqlite3, datetime, calendar.</p>

<p>Try this instead:</p>

<pre><code>self.cur.execute("""UPDATE Mailouts
                    SET NextMail=?
                    WHERE StudentID=?
                 """, (self.NextMail, record[0]))
</code></pre>

<p>The string formatter will convert your <code>self.NextMail</code> to a string so when it passes the query to the engine, the engine will end up interpreting it as a mathematical operation (ie. 2017-1-11 = 2005).</p>

python mysql sql