pythondatesqlitesqlalchemy

Date and datetime support


I'm currently creating a small application with PySide to learn the basics Python and GUIs. The this is I need to store some information into a small database. The information is mostly, name of account, price, date. And I need to be able to view it in a QTableView (Exemple : total of a certain account). I've manage to do it with SQLITE3 due to the code being really ugly, I've decided to rewrite it. One thing that I coudn't get to work is to select data from the table between 2 dates chosen by the user from the QDateEdit.

Now my question is, since I need to do some query depending on which date the user select with QDateEdit (Or any other widget better suited for this). Which database could handle the 'date' stuff easily. I might be wrong but storing date in SQLITE3 gave me a string 'yyyy-mm-dd'.

What would be the "best" way to acheive this.


Solution

  • This can be done using SQLAlchemy.

    Date and Time Types

    SQLite does not have built-in DATE, TIME, or DATETIME types, and pysqlite does not provide out of the box functionality for translating values between Python datetime objects and a SQLite-supported format. SQLAlchemy’s own DateTime and related types provide date formatting and parsing functionality when SQlite is used. The implementation classes are DATETIME, DATE and TIME. These types represent dates and times as ISO formatted strings, which also nicely support ordering. There’s no reliance on typical “libc” internals for these functions so historical dates are fully supported.