from sqlalchemy import Date
itemDate = Column("itemDate", Date)
SQLite Date type only accepts Python date objects as input.
CHAR works fine but I need date. Any thoughts?
SQLite natively has nothing like a ‘date type’ and ‘2011-03-15’ is str in Python and TEXT in SQLite 
I forgot, the object date of python has convenient methods isoformat & fromisoformat for conversion (I think the whole datetime module is real fun 
)
So, date fields are captured as text and converted to dates in the program for processing?
As an aside, I’m not a programmer in my day job. I find the SQL Alchemy docs take a lot of reading to get the functionality I need for my programs. There are no examples from what I can see. Programmers all seem to differ in their implementation. It might be that the language is above my head, but I have found it very frustrating getting a small db program working the way I envision it. Other than the usual forums, here, stack, YouTube, where do you get your information from. How do you decipher the documentation?
you don’t have to be a programmer to understand what’s going on in a program (just think of all mothers having kids & get along: one gets accustomed to it sort of)
what I don’t understand is you say
mfones:
to get the functionality I need for my programs
which is, at least for me, much more interesting & important than the coding (most projects go down the drain because there is simply no conception)
if that is agreed the programming is simply an eager reading of docus & tutorials plus a solid experimenting
so if you have decided to use Python for scripting then why not use SQLite as part of python (to consume all that may take time but remember wxPython is supposed to be fun)
so all in all give us an idea of what you are after and someone may throw in some useful ideas (this is a friendly forum, I think 
)
PS oh I forgot to link the maintenance bit of sqlite (may be the best introduction to it 
)
I would like to swap out the current TextCtrl for adv.DatePickerCtrl to capture the date. The date comes back as date which causes this error…
sqlalchemy.exc.PendingRollbackError: This Session’s transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type.
[SQL: INSERT INTO ledger (“Date”, “Description”, “Budget Item”, “Credit”, “Debit”, “Balance”) VALUES (?, ?, ?, ?, ?, ?)]
[parameters: (<wx.DateTime: “2023-04-04 12:00:00 AM”>, ‘’, ‘’, ‘’, ‘’, ‘’)]
(Background on this error at: https://sqlalche.me/e/14/rvf5) (Background on this error at: https://sqlalche.me/e/14/7s2a)
SQLAlchemy needs the date as text as you mention above. Can I convert the datetime back to text? How?
I like this format…and would like to use it.
I see you don’t want to take joy in diving into my offered links (should I be pleased or relieved 
total immunity to advice or seller of Alchemy?)
however, seeing the stability & reflection of the sqlite interface (thanks to SQLAlchemy 100% python, I suppose) will please all those trusting in SQLite and its related products
what we haven’t been at so far is that it looks like you use a single table ledger, so why all this Alchemy in between (is it Chinese or only advanced?)
Sorry, I didn’t notice the links until you pointed them out. I do use SQLite DB Browser. The project I’m working on uses SQLAlchemy and SQLite. I will read the tutorial on SQLite you provided in the link. Thanks. There is only one table so far. I will be adding more later. I need to establish some ForeignKeys. With respect to my question regarding date, I think what I need to do is convert he date retrieved to text to store in the database. This additional work makes me think I’m missing something.
Can you tell me what your native language is and what part of the world you live?
I have not used SQLAlchemy (I just use sqlite3 in my projects). It seems to me that SQLAlchemy is a python layer on top of a number of database implementations or “dialects”. I would have assumed that its API would expect datetime.date objects for dates, irrespective of how any dialect stores them.
Have you tried converting the wx.DateTime object returned by the DatePickerCtrl into a datetime.date object and passing the result to SQLAlchemy?
I use the following function to convert wx.DateTime objects into datetime.date objects:
import wx
import datetime
def wxDateTime2PyDate(wx_datetime):
"""Convert a wx.DateTime to a datetime.date.
:param wx_datetime: a wx.DateTime object.
:raise: ValueError, if the date is not valid.
:return: a datetime.date object.
assert isinstance(wx_datetime, wx.DateTime)
if wx_datetime.IsValid():
ymd = map(int, wx_datetime.FormatISODate().split('-'))
return datetime.date(*ymd)
else:
raise ValueError("Invalid date")
Example:
wx_dt = wx.DateTime.Now()
py_date = wxDateTime2PyDate(wx_dt)
print(type(wx_dt), wx_dt)
print(type(py_date), py_date)
Which outputs:
<class 'wx._core.DateTime'> Thu Apr 6 10:53:05 2023
<class 'datetime.date'> 2023-04-06
However, if the SQLAlchemy API really does expect dates to be passed as strings, then you would need to know what format it expects. If it expects the ISO 8601 format "YYYY-MM-DD", then you could just call the wx.DateTime object’s FormatISODate() method.
Thanks for the response @RichardT. The code you provided helps a lot. I had considered converting the values as a next step.
The choice of using SQLAlchemy was due to the original code and some past projects. All inherited and didn’t see the need to change. As I understand it SQLAlchemy provides the means to direct the programs data to any database. Being relatively new to programming I thought that this might provide some benefit later on. However, using SQLAlchemy as opposed to using SQLite alone has a steeper learning curve. I have considered removing the SQLAlchemy code but with the progress I’ve made so far, I’ll keep going.
The project I’m working on is a financial program to manage the financials of a hockey team I manage in my spare time. There are many requirements, the ledger is at the heart of the program. I need to provide monthly reporting that tracks budgeting. The date field is required for the ledger, the heart of the program. I’m using ObjectListView to map the fields for my main ListView.
When I get the date problem sorted the next thing I need to do is to add import/export functionality so that I’m not having to manually enter in records when developing. I have that mostly working.
Then I need to add more tables to maintain budget items and other items for import as ForeignKey’s into ledger.
Thanks again for the code. I’ll give it a try.
Not using SQLAlchemy myself, I do use sqlite3 extensively in my projects and I declare a date to be an INT, then convert the input, whatever its format to yyyymmdd and store that.
Coming the other way, I convert the INT to whatever date format is required.
While it may seem cumbersome, it does work flawlessly.
I suppose there is no reason the the date couldn’t be stored as text, I imagine it would work just as well but storing it as a numeric feels right.
In a similar vein, I store deadlines i.e. date+time as a numeric timestamp although this may be an issue sometime after 2038, when, if I’m still coding, I will have to come up with a resolution. 
In one of my applications I need to store meteorological reports in an sqlite database.
In the application, the reports’ date-time values are held as datetime.datetime objects.
When I create the database, I define the date_time column to be of type TIMESTAMP which causes sqlite to store them as strings.
By default sqlite3 uses the convert_timestamp() function in dbapi2.py to convert the strings back to datetime.datetime objects. However that produces timezone unaware objects, whereas meteorological reports are always given in UTC. To ensure the database returns the correct timezone aware objects I use the following function:
import sqlite3
from dateutil.parser import parse
def registerTimestampConverter():
"""Register the timestamp converter.
Unlike the default convert_timestamp() function in dbapi2.py,
the parse() function handles timestamps with time zone offsets,
e.g. '2020-02-13 16:00:00+00:00'.
For these timestamps, the returned datetime object will have
its tzinfo attribute set to dateutil.tz.tzutc().
sqlite3.register_converter("TIMESTAMP", parse)
rolfofsaxony:
I declare a date to be an INT, then convert the input, whatever its format to yyyymmdd and store that.
that is a strong reminder of the packed decimals of Cobol, just think of the glorious luxury using XML these days
to skin the cat sqlite3 completely
typeof(X)
The typeof(X) function returns a string that indicates the datatype of the expression X: “null”, “integer”, “real”, “text”, or “blob”.
# from sqlite3 test
import sqlite3 as sqlite
from datetime import date
class DateTimeTests():
def setUp(self):
self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
self.cur = self.con.cursor()
self.cur.execute("create table test(d date, ts timestamp)")
def tearDown(self):
self.cur.close()
self.con.close()
def test_sqlite_date(self):
d = date(2004, 2, 14)
print(f'{d=} {type(d)=}')
self.cur.execute("insert into test(d) values (?)", (d,))
self.cur.execute("select d, typeof(d) from test")
d2, sqltype = self.cur.fetchone()
print(f'{d2=} {type(d2)=} {sqltype=}')
print(f'{(d is d2)=} {(d == d2)=}')
dtt = DateTimeTests()
dtt.setUp()
dtt.test_sqlite_date()
dtt.tearDown()
if you start off with the wx.DateTime object it’s more efficient to store the ISO format right away, saves the work of the dbapi2 (unless you want to do calculations after reading) 
# from sqlite3 test
import sqlite3 as sqlite
from wx import DateTime
class DateTimeTests():
def setUp(self):
self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
self.cur = self.con.cursor()
self.cur.execute("create table test(d, ts timestamp)")
def tearDown(self):
self.cur.close()
self.con.close()
def test_sqlite_date(self):
d = DateTime(14, DateTime.Feb, 2004).FormatISODate()
print(f'{d=} {type(d)=}')
self.cur.execute("insert into test(d) values (?)", (d,))
self.cur.execute("select d, typeof(d) from test")
d2, sqltype = self.cur.fetchone()
print(f'{d2=} {type(d2)=} {sqltype=}')
print(f'{(d is d2)=} {(d == d2)=}')
dtt = DateTimeTests()
dtt.setUp()
dtt.test_sqlite_date()
dtt.tearDown()
Still stuck. Not a coding error per se, I’m struggling with sizers. Just when I think I get it I’m missing something. The only issue now is to expand the notebook fully into the panel. However the panel does not fill the frame completely. NO idea why. I’m hoping the diagram helps if you can read it. Notice that the panel sizer is set to notebook_sizer. This was the only way so far that I could get the notebook and buttons to appear in the notebook thus in the panel.
I want the buttons to appear at the bottom of he panel and the ovl to expand to the tops of the button sizer. The space above in the panel needs to be filled with notebook. The color coding is to hopefully display the borders of the objects.
Any thoughts would be appreciated.
Comp-1 to comp-4 I remember them well.
Back when disc space was frighteningly expensive and you had to worry about the size of each record and aligning on word boundaries. Bytes? We worried about nibbles!
Tell that to the kids today and they won’t believe yer!
