On Jan 20, 2011, at 4:53 PM, F.A.Pinkse wrote: > Hi All, > > After some additional peeking around I decided to do a test with SQLAlchemy > alone. > > I took the tutorial fr0m the book Essential SQLAlchemy as my guide.
very very old outdated book. If you want a current book, go to http://www.sqlalchemy.org/docs/ , download the PDF and print it out. I find reading HTML online much easier though (and use the search, it works). > > This is what I got working. > def monthfrom(date): > print(date) > #i do a split because I get seconds as 00.00000 > if date != None: > datesplit=date.split(' ')[0] > a=datetime.strptime(datesplit, '%Y-%m-%d').month > else: > a=1 > return a > > metadata.bind.connect().connection.connection.create_function("monthfrom", 1, > monthfrom) wow, thats in the book ? that is not at all how that should be done. Though I will grant this is a "recipe" that isn't in our main docs (I've never seen SQLite python plugin functions used before), probably worth adding to the SQLite docs. Anyway, this is how you should install your function: from sqlalchemy.interfaces import PoolListener class MyListener(PoolListener): def connect(self, dbapi_con, con_record): dbapi_con.create_function("monthfrom", 1, monthfrom) engine = create_engine('sqlite:///path_to_my_db', listeners=[MyListener()]) reference : http://www.sqlalchemy.org/docs/core/interfaces.html#connection-pool-events SQLite doesn't have a date type. The SQLAlchemy DateTime type stores the value as a string. This isn't the only way to go, you can also store dates as "epochs", that is integers, which on SQLite may be more amenable to date arithmetic on the server, but in any case you're just dealing with extracting here, no big deal. The SQLite DateTime type in SQLA stores the value using the format: "%04d-%02d-%02d %02d:%02d:%02d.%06d" % (value.year, value.month, value.day, value.hour, value.minute, value.second, value.microsecond) so you're seeing the "microsecond" value there. you need to deal with that too. Surprisingly the docs seem a little light on this too so ticket 2029 is added to fill this in. > > # testing the func following the tutorial in the book Essential SQLALchemy > > #pg.25 > from sqlalchemy import * > from datetime import datetime > > metadata=MetaData('sqlite:///tutorial.sqlite') > #metadata.bind.echo=True > > person_table=Table( > 'person', metadata, > Column('id', Integer, primary_key=True), > Column('birthdate', DateTime, default=datetime.now)) > > metadata.create_all() > # > stmt=person_table.insert() > > #stmt.execute(birthdate=datetime(2000, 4, 4, 0, 0)) > #stmt.execute(birthdate=datetime(2000, 3, 3, 0, 0)) > #stmt.execute(birthdate=datetime(2000, 2, 2, 0, 0)) > #stmt.execute(birthdate=datetime(2000, 1, 1, 0, 0)) > > #pg 28 Mapping > from sqlalchemy.orm import * > > class Person(object):pass > > mapper(Person, person_table) > > Session= sessionmaker() > session=Session() > > query =session.query(Person) > > def monthfrom(date): > print(date) > #i do a split because I get seconds as 00.00000 > if date != None: > datesplit=date.split(' ')[0] > a=datetime.strptime(datesplit, '%Y-%m-%d').month > else: > a=1 > return a > > metadata.bind.connect().connection.connection.create_function("monthfrom", 1, > monthfrom) > > print('monthfrom in:') > pp=query.order_by(func.monthfrom(Person.birthdate)).all() > print('result:') > for p in pp: > print (p.birthdate) > > > > > For the first run you have to uncomment the 4 lines with the insert execution > to fill your empty db. > Put the comments back on or your db will fill up > > > Next task will be to get it transplanted to my app. > > > There is still one issue though. > > the function def monthfrom get the date with the seconds as 00.000000 > But the print(p.birthdate) shows the seconds as 00 > See: > > monthfrom in: > 2000-04-04 00:00:00.000000 > 2000-03-03 00:00:00.000000 > 2000-02-02 00:00:00.000000 > 2000-01-01 00:00:00.000000 > result: > 2000-01-01 00:00:00 > 2000-02-02 00:00:00 > 2000-03-03 00:00:00 > 2000-04-04 00:00:00 > > That is why the def monthfrom does a .split > > Question is this a bug? > > > Thanks > > Frans. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
