[sqlalchemy] Re: I need a final push
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.
This is what I got working.
# 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.
[sqlalchemy] Re: I need a final push
HEllo Michael, Thanks for your answer. I tried order_by(func.monthfrom(datetime(2000, 1, 2, 0, 0, 0))) but get the error: global name func not defined. I guess something must be put in front of it. Any idea before I dig in. My app works with Elixir. Tnaks. 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.
[sqlalchemy] I need a final push
Hi All,
I need a last pushand I hope someone here in SQLAlcheny cab give me
some.
My application uses elixir on a sqlite database.
I Have a table Person with a field birthdate, now I want to sort on the
month of this field.
From examples and a lot of peeking I have worked out how to add
Userdefined functions.
session.bind = metadata.bind
def monthfrom(date):
return datetime.strptime(date, '%Y-%m-%d %H:%M:%S').month
con = session.bind.connect().connection
con.create_function(monthfrom, 1, monthfrom)
but now come the part I have made very little progress on.
personslist=Person.query.filter(Person.birthdate!=None).order_by(func.monthfrom(:date),
{date: datetime(2000, 1, 2, 0, 0, 0), })
I get an SQLAlchemy.exc.OperationalError
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.
[sqlalchemy] order_by(datetime)
Hi All. I have a datetime column in my model. If I do an .order_by I get year-month-day but how do I do an order_by to get month-day-year? or even a day-month-year Thanks, Frans. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] Re: order_by(datetime)
Hi Michael, Thanks for your quick reply. I understand. I am using SQLite as the engine. I have not found the functions needed in the doc. I will leave this sort option open and stick with SQlite for a while. Frans Op 1/10/2011 4:03 PM, Michael Bayer schreef: you'd need to use SQL functions that break the datetime into its component parts, and order by them. such as: order_by(func.datepart(MONTH, my_date_col), func.datepart(DAY, my_date_col), func.datepart(YEAR, my_date_col)) datepart routines vary by database backend with very little cross compatibility - you'd have to consult the documentation for your database on the recommended way to break dates up into components. On Jan 10, 2011, at 5:35 AM, F.A.Pinkse wrote: Hi All. I have a datetime column in my model. If I do an .order_by I get year-month-day but how do I do an order_by to get month-day-year? or even a day-month-year Thanks, Frans. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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.
[sqlalchemy] Re: Not.How to do a not contains()
Hi Michael, Thanks for your swift answer. Had to find the right place for the ~ though to get it working under Elixir. .filter(~Person.address.contians(someaddress)) Thanks again you saved me a lot of searching. Frans. .Op 1/4/2011 5:02 PM, Michael Bayer schreef: The ~ symbol is interpreted as not, which is a synonym for sqlalchemy.not_(): ~Person.address.contains(foo) not_(Person.address.contains(foo)) On Jan 4, 2011, at 5:37 AM, F.A.Pinkse wrote: Hi All, I can do a .filter(Person.address.contians(someaddress)) but how do I do the negated version? meaning not contains() Thanks, Frans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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.
