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.0
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.0
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.00
But the print(p.birthdate) shows the seconds as 00
See:
monthfrom in:
2000-04-04 00:00:00.00
2000-03-03 00:00:00.00
2000-02-02 00:00:00.00
2000-01-01 00:00:00.00
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.
