datetime date value not updating into MySQL database in Python correctly (updates the integer 2005 instead)

Question

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:

        """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..."""

When I run this, here is what is printed:

2017-01-11
<class 'datetime.date'>
1

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

When I select and output this table, the NextMail field appears as in the image: screenshot

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.

If I go

    self.cur.execute("""SELECT * FROM Mailouts LIMIT 1""")
    self.all_data = self.cur.fetchall()
    print(self.all_data)

the record shows the number 2005 in the NextMail field with the type int.

I am using PyQt for GUI I am using the modules sqlite3, datetime, calendar.

Scratch'N'Purr · Accepted Answer · 2016-09-01 12:12:29Z

1

Try this instead:

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

The string formatter will convert your self.NextMail 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).

answered Sep 1, 2016 at 12:12

Scratch'N'Purr

10.5k2 gold badges39 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

datetime date value not updating into MySQL database in Python correctly (updates the integer 2005 instead)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related